Re: About revoking large number of privileges; And the PUBLIC role. - Mailing list pgsql-general

Dominique Devienne <ddevienne@gmail.com> writes:
> Hi. I'm struggling to delete databases because of grants to roles on
> objects of those DBs.

> These DBs can have a large'ish number of schemas, 100-300 is typical.
> and define a bunch of ROLEs "specific" to those schemas. Normally "login user"
> ROLEs are never granted explicit access to objects, instead only the
> "db specific" ROLEs
> get those grants, and regular users are granted some of those ROLEs.

> So my goal is to delete all those "db specific" ROLEs, then the DB
> with all its schemas.
> Which implies REVOKE'ing grants on all those "db specific" ROLEs first.

You should not really have to revoke those manually.
The normal process for that is to use DROP OWNED BY.

https://www.postgresql.org/docs/current/role-removal.html

            regards, tom lane