CODEWITHSUNDEEP
postgresqlsql-grantuser-roles
Arthur
Arthur

Reputation: 344

Revoking rights granted by another user in PostgreSQL

In PostgreSQL, when a superuser grants any right (such as GRANT SELECT ON TABLE table_name), only said SU can revoke them.

Is there any way another superuser can revoke said grants?

EDIT: Here is a sample query I am running: ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema REVOKE SELECT ON TABLES FROM public CASCADE; However, said default privilege still shows up when I look at the schema, even after refreshing everything. I've run it with both my superuser account and the postgres account, neither worked.

Upvotes: 1

Views: 926

Answers (1)

Patrick
Patrick

Reputation: 32161

From the documentation:

If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object.

So ANY superuser can revoke privileges, even those granted by other superusers.

You can use the command ALTER DEFAULT PRIVILEGES to set the initial privileges (those assigned when creating an object) to something other than the PostgreSQL default privileges. Running that command, however, only affects (from the documentation):

the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.)

Altering the privileges of existing objects requires separate GRANTs and REVOKEs, although you can also apply shortcuts like:

{GRANT | REVOKE} [privileges] ON ALL TABLES IN SCHEMA [schema name];

and similarly for sequences and functions.

Upvotes: 1

Related Questions