S
Supabase•5d ago
xkenneth86

moddatetime failing to install extension on `supabase db reset`?

Get this every time I run a migration in one project but not the other? ERROR: permission denied for function pg_read_file (SQLSTATE 42501) At statement: 1 create extension if not exists moddatetime schema extensions
4 Replies
ibrahim
ibrahim•5d ago
A similar query seems to have happened to this user on stack exchange and it looks like upgrading the database solved the problem for them https://stackoverflow.com/questions/79512759/supabase-error-permission-denied-to-create-extension-moddatetime
Stack Overflow
Supabase error: permission denied to create extension "moddatetime"
While trying to create a local supabase dev environment, supabase start is failing with Seeding globals from roles.sql... Applying migration 20250315203000_remote_schema.sql... Stopping containers...
SA
SA•5d ago
You can do this easily - enable the moddatetime extension manually from the Supabase Dashboard > Database > Extensions, or run this once in the SQL editor as the admin: CREATE EXTENSION IF NOT EXISTS moddatetime SCHEMA extensions;
xkenneth86
xkenneth86OP•5d ago
Every time I run supabase db reset this gets wiped out, or should it not?
silentworks
silentworks•2d ago
I have that extension enabed by my migrations with any issue. What version of the CLI are you using? and what version of Postgres is your project on? Ok I just ran into this issue with my migration to a new project just now. Something might be broken with how migrations work. So it turns out the extension is already installed it seems but now you have to specify the schema name before the extension name when referencing it. In my case I had to change 
create trigger handle_updated_at 
    before update on profiles
    for each row execute procedure moddatetime(updated_at);
create trigger handle_updated_at 
    before update on profiles
    for each row execute procedure moddatetime(updated_at);
to 
create trigger handle_updated_at 
    before update on profiles
    for each row execute procedure extensions.moddatetime(updated_at);
create trigger handle_updated_at 
    before update on profiles
    for each row execute procedure extensions.moddatetime(updated_at);

Did you find this page helpful?