PostgreSQL – How to Revoke All Roles from a Schema

permissionspostgresql

How can I revoke all roles (or all roles except the creator of the schema) from a specific schema in Postgres 14?

I have an ETL process that periodically dynamically creates a Postgres schema and authorizes roles on it based on a steering table. The authorizations could change, so I want to drop all roles every time, and re-authorize them based on what that table specifies.

Lets say adminalice runs this ETL proces.
I execute this as the adminalice user:

CREATE SCHEMA demo_schema AUTHORIZE adminalice;
CREATE ROL johndoe;
CREATE ROL janedoe;
GRANT ALL ON ALL TABLES IN SCHEMA demo_schema TO johndoe;
GRANT ALL ON ALL TABLES IN SCHEMA demo_schema TO janedoe;

Now I would like to revoke all the roles I granted to the demo_schema.
However, the script that executes this (as adminalice) does not know which roles exists, and as a precaution I want to drop all roles always.

I thought I could do this by issuing a:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA demo_schema FROM PUBLIC;

Postgres 14 says:

REVOKE [ GRANT OPTION FOR ]
    { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
    ON SCHEMA schema_name [, ...]
    FROM role_specification [, ...]
    [ GRANTED BY role_specification ]
    [ CASCADE | RESTRICT ]

where role_specification can be:

    [ GROUP ] role_name
  | PUBLIC
  | CURRENT_ROLE
  | CURRENT_USER
  | SESSION_USER

The REVOKE command revokes previously granted privileges from one or
more roles. The key word PUBLIC refers to the implicitly defined group
of all roles.

Ideally adminalice retains her authorization (I have the impression that this is possible), but this is not very important as I can simply issue a fresh authorization after the drop of all roles.

Best Answer

There is no single statement for that. You have to do that per role. In your example, you could drop the roles and all their privileges with

DROP OWNED BY johndoe;
DROP ROLE johndoe;
DROP OWNED BY janedoe;
DROP ROLE janedoe;

That will only work if the roles have no privileges or objects in any other database.

Related Question