postgres
postgres copied to clipboard
supabase
Cannot use safeupdate for the database nor the postgres user (session_preload_libraries)
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
- [x] I confirm this is a bug with Supabase, not with my own application.
- [x] 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
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 anon and postgres users.
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.
I've created a new project on supabase.com and tried this. I can do it again. Will get back to you soon
(I need to add: This is definitely still possible with older projects if you've tried that)
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 🤔
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)
https://github.com/supabase/supabase/assets/9058762/c783854a-7789-44e8-80c4-b94b3acadbcc
Also tried it now on a FRESH npx supabase init instance. Same problem. I'm not sure which kind of magic instance you have which I don't :D
Just to give it another way of trying, I also added it now to a migration file and called npx supabase db reset . Same thing
I just followed the instruction from the docs with replacing "anon" by postgres and I also get a permission denied error (ERROR: 42501)
Hey folks, thanks for confirming these!
What I have found in testing is this:
- New projects do not have this issue
- Old projects do not have this issue
- Old projects updated to the latest version DO have this issue
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 🤷♀️
What I have found in testing is this: New projects do not have this issue Old projects do not have this issue Old projects updated to the latest version DO have this issue
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.
Thanks folks, this has been escalated to @supabase/postgres and I will get back here when we know more
Hey all, quick update from our side:
This issue is being resolved through our included utils library supautils and the documentation will be updated to reflect the steps. It is a bug that you are able to set session_preload_libraries as it needs superuser access and should go through the supautils interface.
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!
Any update on this?
hey @imownbey update to the docs incoming tomorrow but we have a fix in place that is implemented by supautils.
Hello all,
It has been identified that modifying safeupdate such 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.
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
Just wanted to confirm, does this apply to pg_plan_filter as well?
I tried to enable the extension by running alter database postgres set session_preload_libraries = 'plan_filter'; such that I can set plan_filter.statement_cost_limit as 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
@aichi-t I think you may have identified a problem with plan_filter investigating it now. May create a new issue to track
@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 in
https://supabase.com/docs/guides/database/extensions/pg_plan_filter