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

Open
#1,308 27 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
48/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Quiet
Tech stack
postgresql
Domain
databases

Research direction

Start with the supplied SQL reproduction for GRANT, ALTER ROLE, and ALTER DATABASE, then inspect how the postgres project configures session_preload_libraries and the safeupdate extension. Done means identifying a supported way to enable safeupdate at the database level, or documenting why the requested configuration cannot be enabled.

Written by the indexing model from the issue text.

Description

bug

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

image
Dominant language
Nix
Stars
1.8k
Forks
266
Avg merge
2d 8h
Merged PRs (30d)
25

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from supabase/postgres

All issues in supabase/postgres

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.