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

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Tue, Jul 8, 2025 at 12:54 PM DINESH NAIR <Dinesh_Nair@xxxxxxxxxxxxxxxxx> wrote:

When we create a role ,it has no inherent dependencies on other database objects.

 

That is patently false otherwise it wouldn't be able to, say, perform: select version();  See the PUBLIC pseudo-role.

So, when we execute cascade option for role then >> drop role .. Cascade then impact will be high 

Yeah, don't use cascade.

It will drop all objects owned by the role: This is the most significant effect. It will drop tables, views, sequences, functions, schemas, and any other database objects that the role owns.

Which is why you reassign them first.  This is the only real harmful action given the fact that the role is going away.  And why one should not use cascade.

Revokes all privileges granted to the role: Any GRANT statements that gave permissions to role_name will be undone.

Good...and what "drop owned by" does once you get the truly owned objects out of the way via reassigned owned.

Removes the role from any roles it is a member of.

Good...

Removes any roles that are members of role_name from role_name.

This could have knock-on effects so yeah, this needs to be considered and dealt with.

Also, could you please start doing inline/bottom posting like everyone else here does?

David J.