Re: About revoking large number of privileges; And the PUBLIC role.

On Thu, Jul 7, 2022 at 3:52 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Dominique Devienne <ddevienne(at)gmail(dot)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.

Except we already went through that, that DROP OWNED BY acquires too many locks.
Increasing max_locks_per_transaction when it fails is just not an option IMHO.
One user had to raise it to 32K for his particular DB, which is not
even that large.

Or are you saying setting it to 1M or 1B is "safe", and should be
required setup for users?

Is revoking privileges taking locks? Is dropping a DB taking locks?
If neither are, then I can work around the limitations of DROP OWNED BY.

So will the community help me figure this out?

BTW, I'm also hoping revoking privs, and dropping roles and dbs will
be faster than DROP OWNED BY.
That DB took 30min to 1h to get rid of, via DROP OWNED BY, which seems
to long to delete a bunch of files, no?