Setting GUCs
Hey guys, Supabase Pro plan here. I'm trying to set some app settings but I keep hitting an error: ERROR: 42501: permission denied to set parameter "app.settings_email_sender_url"
ERROR: 42501: permission denied to set parameter "app.settings_email_senderurl"d
It seems I don't have the privileges to run ALTER DATABASE … SET app.settings*, so Supabase returns 42501 permission denied.
Anyone know any way around this/to get it done?
8 Replies
You should explain more what this app.settings... thing is you are trying to set in the db.
sorry im trying to Set Postgres settings app.settings_email_sender_url (my email-sender function endpoint) and app.settings_email_sender_key so a email sending queue kick-off succeeds for my sites registration loop. So users who register will be sent a welcome email + subscription confirmation email as soon as the access to granted (via stripe purchase/beta access). We're offering a beta access and although the user is granted auth, the emails arent triggering.
We’re trying to fire off a “Your access is active” email as soon as a subscription/beta grant is written. Our queue helper lives in Postgres: the grant trigger calls enqueue_subscription_access_email, which queues the message and then invokes our Edge Function email-sender via net.http_post.
Initially we tried to keep the Edge endpoint + cron key in database GUCs (app.settings_email_sender_url, app.settings_email_sender_key) so the helper could read them with current_setting. But on managed Supabase we don’t have privileges to set custom app.* parameters—ALTER DATABASE … SET app.settings_email_sender_url fails with 42501 permission denied.
Use Vault
You can't access many Postgres settings directly on hosted.
Ah, gotcha. I'm thinking of trying to load the endpoint and key from a small config table instead (public.site_config), seeded with the defaults. maybe set the helper to look up email_sender_url / email_sender_key there, and then calls email-sender.
I haven't used Vault but i will look into that if this doesn't work. I appreciate your response!
Use Vault instead as it hides the keys in your backups.
Good point! Thanks!
This shows using Vault (service role key)
https://github.com/GaryAustin1/supa-file-helper/blob/main/main.sql
oh that's dope! definitely going that route!