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;
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;