Repository navigation
Cannot use safeupdate for the database nor the postgres user (session_preload_libraries) #1308
Description
Bug report
Basically, this worked in older projects. In newer projects, the permissions are stricter and one cannot enable this, safeguarding, safeupdate extension. But this is a crucial help for avoiding human failure.
-- 1. doesn't work
GRANT SET ON PARAMETER session_preload_libraries TO postgres;
ALTER ROLE postgres SET session_preload_libraries = 'safeupdate';
-- 2. also doesn't work
alter database postgres set session_preload_libraries = 'safeupdate';
-- test
CREATE TABLE IF NOT EXISTS todos (id SERIAL NOT NULL PRIMARY KEY, title TEXT);
INSERT INTO todos (title) VALUES ('test-todo-123');
DELETE FROM todos; -- should fail with safeupdate- I confirm this is a bug with Supabase, not with my own application.
- I confirm I have searched the Docs, GitHub Discussions, and Discord.
Describe the bug
It's all said.
To Reproduce
Try either of the provided options in the intro.
Expected behavior
safeupdate can be enabled in the database (I don't think we necessarily need postgres role setting but rather one config that enables this in the database or not).
Screenshots
Activity
encima commentedon Jun 24, 2024
Hi @activenode ,
Thanks for opening. Can you confirm your instance is running the latest version:
15.1.1.64?Trying both options work for me with the
anonandpostgresusers.Enabling it for the entire database is the second option, per user can be useful for those using the API or wanting to enforce it in to more specific use cases.
activenode commentedon Jun 24, 2024
I've created a new project on supabase.com and tried this. I can do it again. Will get back to you soon
activenode commentedon Jun 24, 2024
(I need to add: This is definitely still possible with older projects if you've tried that)
activenode commentedon Jun 24, 2024
I deleted my test project and created a new one.
activenode commentedon Jun 27, 2024
Can you confirm this? @encima ?
encima commentedon Jun 27, 2024
hey @activenode
just tried with a new project and cannot confirm 😕
If you run the 2 commands separately, do you experience this also?
I just tried running both as a single transaction and 2 separate ones without issue 🤔
activenode commentedon Jun 28, 2024
Will create a new one and make a video
activenode commentedon Jun 28, 2024
Completely fresh project on supbase.com (I try setting it first, then altering, then trying again, just to show it doesn't work no matter what I do)
ne.mov
activenode commentedon Jun 28, 2024
Also tried it now on a FRESH
npx supabase initinstance. Same problem. I'm not sure which kind of magic instance you have which I don't :Dactivenode commentedon Jun 28, 2024
Just to give it another way of trying, I also added it now to a migration file and called
npx supabase db reset. Same thingFiras752 commentedon Jun 28, 2024
My team is facing the same issue actually
Cuzart commentedon Jun 28, 2024
I just followed the instruction from the docs with replacing "anon" by postgres and I also get a permission denied error (ERROR: 42501)
imownbey commentedon Jun 28, 2024
I am seeing the same error
encima commentedon Jul 1, 2024
Hey folks, thanks for confirming these!
What I have found in testing is this:
Can you confirm this is what you are experiencing as well?
Update: it seems like the comments above are from new projects so there is not much of a pattern 🤷♀️
activenode commentedon Jul 1, 2024
Cannot confirm. Happens for new projects as well as happened for old projects on my end.
What I can confirm though is: On SOME old projects it worked.
Cuzart commentedon Jul 1, 2024
For me this also appeared on a freshly created project
encima commentedon Jul 1, 2024
Thanks folks, this has been escalated to @supabase/postgres and I will get back here when we know more
encima commentedon Jul 2, 2024
Hey all, quick update from our side:
This issue is being resolved through our included utils library
supautilsand the documentation will be updated to reflect the steps. It is a bug that you are able to setsession_preload_librariesas it needs superuser access and should go through thesupautilsinterface.We will link the PR when it is out and then update the documentation to reflect the changes.
Thanks for reporting this and for your patience!
imownbey commentedon Jul 9, 2024
Any update on this?
encima commentedon Jul 9, 2024
hey @imownbey update to the docs incoming tomorrow but we have a fix in place that is implemented by supautils.
encima commentedon Jul 16, 2024
Hello all,
It has been identified that modifying
safeupdatesuch that it is loaded for all projects and enabled globally can impact other extensions that also require shared libraries (i.e. Timescaledb). It has caused a few support issues so, while we investigate further, it will be disabled as an extension and we will temporarily hide the page from the docs.Apologies for the inconvenience, we really want to make sure that we minimise breaking changes and maintain a consistent experience as much as possible.
activenode commentedon Jan 28, 2025
Wanted to ask if there are any updates?
olirice commentedon Jan 29, 2025
The interaction between safeupdate and other preload extensions like timescale is still present and reproducible so we are currently holding with encima's comment ^
We are re-working our pg17 release (coming soon) in a way that is expected to support re-enabling safeupdate, though that has not been completely de-risked yet
aichi-t commentedon Mar 28, 2025
Just wanted to confirm, does this apply to
pg_plan_filteras well?I tried to enable the extension by running
alter database postgres set session_preload_libraries = 'plan_filter';such that I can setplan_filter.statement_cost_limitas per this doc on aggregate functions.However, it resulted in
ERROR: 42501: permission denied to set parameter "session_preload_libraries"error and was wondering if this issue was related.https://supabase.com/docs/guides/database/extensions/pg_plan_filter
samrose commentedon Apr 30, 2025
@aichi-t I think you may have identified a problem with plan_filter investigating it now. May create a new issue to track
samrose commentedon Apr 30, 2025
@aichi-t thanks for making us aware that the docs need updating. In the latest release you can just skip the
load 'plan_filter';step, and it will otherwise work as described inhttps://supabase.com/docs/guides/database/extensions/pg_plan_filter
brainrake commentedon Jul 17, 2026
Status update: PR #2105 addresses this by loading safeupdate from
$libdir/plugins/(avoiding thesession_preload_librariesconflict with timescaledb that blocked the earlier attempt), andsafeupdate.enabledis already insupautils.privileged_role_allowed_configs.