0

I have a schema "test_db" and a role "test_user" and I'd like to strip DELETE privilege from "test_user" on all tables in the schema "test_db" - how to achieve this? If possible I'd like to not resort to triggers / rules.

What I tried so far is to set up DEFAULT PRIVILEGES as a postgres on schema to this user: I tried both granting only select and update or revoking delete priv, but still, if I log into as a "test_user" I can delete from tables in this schema.

Here is the statement I used:

alter default privileges for role postgres in schema test_db
   revoke delete, truncate on tables from test_user;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
zizu_zaza
  • 95
  • 1
  • 8
  • Altering the default privileges will change the privileges on *future* tables created by a certain user, not the existing tables. – Bergi Aug 16 '21 at 00:07
  • @Bergi yeah, I know, thanks, I'm doing this at the db initialization stage, so there's no tables yet. – zizu_zaza Aug 16 '21 at 22:01
  • @zizu_zaza So how (and with what role) are you creating the tables? How does the `test_user` gain access to them in the first place? – Bergi Aug 17 '21 at 00:04

1 Answers1

1

That SQL statement will do nothing useful, since test_user doesn't have any privileges on tables newly created by postgres anyway, so revoking those privileges is unnecessary and won't have any effect.

To revoke the privileges from existing tables, use

REVOKE DELETE, TRUNCATE ON ALL TABLES IN SCHEMA test_db FROM test_user;
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263