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

On Thu, 2022-07-07 at 16:36 +0200, Dominique Devienne wrote:
> > > 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?

If you want to do this on a routine basis, you are doing something wrong.
Never grant a user privileges if the user could be removed.
Use groups in that case.

For a one-time cleanup operation, increasing "max_locks_per_transaction"
and restarting is painful, but not impossible. See it as down time.

> Is revoking privileges taking locks?

Yes.

> Is dropping a DB taking locks?

Not a lot. That should never be a problem.

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

As I wrote, avoid getting there in the first place.

Yours,
Laurenz Albe