Cannot use safeupdate for the database nor the postgres user (session_preload_libraries)

[activenode]

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

[encima]

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.

[activenode]

I've created a new project on supabase.com and tried this. I can do it again. Will get back to you soon

[activenode]

(I need to add: This is definitely still possible with older projects if you've tried that)

[activenode]

I deleted my test project and created a new one.

[activenode]

Can you confirm this? @encima ?

[encima]

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]

Will create a new one and make a video

[activenode]

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]

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

[activenode]

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

[Firas752]

My team is facing the same issue actually

[Cuzart]

I just followed the instruction from the docs with replacing "anon" by postgres and I also get a permission denied error (ERROR: 42501)

[imownbey]

I am seeing the same error

[encima]

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 🤷‍♀️

[activenode]

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.

[Cuzart]

For me this also appeared on a freshly created project

[encima]

Thanks folks, this has been escalated to @supabase/postgres and I will get back here when we know more

[encima]

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!

[imownbey]

Any update on this?

[encima]

Any update on this?

hey @imownbey update to the docs incoming tomorrow but we have a fix in place that is implemented by supautils.

[encima]

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.