Supabase•4y ago

Getting a permission denied error when trying to set parameter 'pgrst.db_pre_request'

In our NextJS/supabase project I'm trying to configure postgREST with a db_pre_request function according to the documentation https://postgrest.org/en/stable/configuration.html#db-pre-request and also following this github issue https://github.com/supabase/supabase/issues/3233 It works fine when I spin up my environment locally using supabase start and supabase db reset. The parameter gets set and my function runs correctly. Unfortunately I get an error when I try to run the migrations for a linked supabase project. Here is the SQL in the migration that is failing

-- inside of migration 20220914185059...
ALTER ROLE postgres SET pgrst.db_pre_request to db_pre_request;
NOTIFY pgrst, 'reload config';

-- inside of migration 20220914185059...
ALTER ROLE postgres SET pgrst.db_pre_request to db_pre_request;
NOTIFY pgrst, 'reload config';

Here is the command and error I'm seeing

Run supabase link --project-ref $NEXT_PUBLIC_SUPABASE_ID --password SUPABASE_DB_PASSWORD
Finished supabase link.
Applying unapplied migrations...
Error: ERROR: permission denied to set parameter "pgrst.db_pre_request" (SQLSTATE 42501); while executing migration 20220914185059
Error: Process completed with exit code 1.

Run supabase link --project-ref $NEXT_PUBLIC_SUPABASE_ID --password SUPABASE_DB_PASSWORD
Finished supabase link.
Applying unapplied migrations...
Error: ERROR: permission denied to set parameter "pgrst.db_pre_request" (SQLSTATE 42501); while executing migration 20220914185059
Error: Process completed with exit code 1.

Can anyone help? My first thought is that whatever role is being used to run the migrations doesn't have the right GRANTs, but I don't know how to check that. Any other ideas, or solutions?

GitHub

Expose PostgREST's pre-request config option to enable revoking l...

Feature request Expose PostgREST's pre-request config option to enable revoking long-lived tokens Is your feature request related to a problem? Please describe. This issue is based on this ...

3 Replies

NanoBit•4y ago

You may need to run as supabase_admin and not postgres (aka, paste this into query tab in Studio instead of migrations)

HeathOP•4y ago

Thanks for the suggestion. I ended up running alter role postgres superuser; which fixed the issue. I ran the migrations, and then alter role postgres nosuperuser; to put it back. This github issue was helpful. https://github.com/supabase/cli/issues/96#issuecomment-1015083871 thanks @NanoBit for pointing in the right direction.

NanoBit•4y ago

Np. Be careful of running this and forgetting to set it back!

Gaming

Programming

Getting a permission denied error when trying to set parameter 'pgrst.db_pre_request'

Did you find this page helpful?