Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?

On Tue, Jul 8, 2025 at 8:53 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Tue, 2025-07-08 at 06:16 -0600, Scott Ribe wrote:
> > I don't have an answer for you, just a question out of curiosity. Is
> this a prelude
> > to dropping the role? Thus, if it existed, DROP ROLE ... CASCADE would
> have worked
> > for your use case?
>
> If dropping the role is the reason why the privileges should go, the
> canonical
> procedure is:
>
> - connect to each database in the cluster in turn; in each:
> - REASSIGN OWNED BY role_to_drop ...
> to transfer ownership
> - DROP OWNED BY role_to_drop
> to remove owned objects *and privileges*
>

That scares me. Just like "and privileges" is an unexpected addition to
DROP OWNED (who thinks that grants are owned by the grantee?), REASSIGN
OWNED BY might have some unexpected exceptions.

Cascading statements really need a DRY RUN option.

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!