Обсуждение: Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
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? (Even so, I could still see other uses for your request.)
(1) I don't see CASCADE as an option to DROP ROLE.
(2) CASCADE without a DRY RUN option scares me. Yes, I could put it in a transaction block, but accidents happen, and rollback could take a long time.
On Tue, Jul 8, 2025 at 8:16 AM Scott Ribe <scott_ribe@elevated-dev.com> 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?
(Even so, I could still see other uses for your request.)
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
> On Jul 8, 2025, at 6:26 AM, Ron Johnson <ronljohnsonjr@gmail.com> wrote: > > (1) I don't see CASCADE as an option to DROP ROLE. "if it existed" ;-)
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*
- DROP ROLE role_to_drop
Yours,
Laurenz Albe
On Tue, Jul 8, 2025 at 8:53 AM Laurenz Albe <laurenz.albe@cybertec.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!
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
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.Thanks
Dinesh Nair
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?
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
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
On Tue, Jul 8, 2025 at 12:54 PM DINESH NAIR <Dinesh_Nair@iitmpravartak.net> 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: AnyGRANTstatements that gave permissions torole_namewill 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 ofrole_namefromrole_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.