Hi,
When we create a role ,it has no inherent dependencies on other database objects. It's a standalone entity until we perform:
Grant it privileges on tables, databases, functions, etc.
Make it a member of other roles.
Assign ownership of objects to it.
So, when we execute cascade option for role then >> drop role .. Cascade then impact will be high
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.
Revokes all privileges granted to the role: Any GRANT statements that gave permissions to role_name will be undone.
Removes the role from any roles it is a member of.
Removes any roles that are members of role_name from role_name.
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Tuesday, July 8, 2025 6:47 PM
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
Caution: This email was sent from an external source. Please verify the sender’s identity before clicking links or opening attachments.
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> Cascading statements really need a DRY RUN option.
[ shrug ] BEGIN/ROLLBACK serves that purpose fine, in fact better
than a per-statement "dry run" option would do: you can run several
dependent DDL statements and then look around at the results before
committing (or not).
Your claim that rollback is slow seems to be born of experience with
some other DBMS.
regards, tom lane
