agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedREVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
9+ messages / 6 participants
[nested] [flat]
* REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 11:52 Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2025-07-08 11:52 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
REASSIGN OWNED is great, but "DETAIL: privileges for ..." is also a cause
for DROP ROLE to fail.
Thus (via a script, of course, that loops through every database and every
schema in that database, and for every object you can grant to), I've got
to REVOKE ALL ON {object type} IN SCHEMA ... FROM some_role, then REVOKE
ALL ON SCHEMA ... FROM some_role.
Is there a single command that I'm missing?
(Yes, I know privileges should be granted to groups. The archaic some_role
which I just dropped is a group role.)
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 12:16 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Scott Ribe @ 2025-07-08 12:16 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 12:26 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2025-07-08 12:26 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
https://www.postgresql.org/docs/17/sql-droprole.html
(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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 12:35 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Scott Ribe @ 2025-07-08 12:35 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
> 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" ;-)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 12:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 9+ messages in thread
From: Laurenz Albe @ 2025-07-08 12:53 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 12:59 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 9+ messages in thread
From: Ron Johnson @ 2025-07-08 12:59 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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!
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 13:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2025-07-08 13:17 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 19:54 DINESH NAIR <Dinesh_Nair@iitmpravartak.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: DINESH NAIR @ 2025-07-08 19:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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?
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role?
@ 2025-07-08 20:22 David G. Johnston <david.g.johnston@gmail.com>
parent: DINESH NAIR <Dinesh_Nair@iitmpravartak.net>
0 siblings, 0 replies; 9+ messages in thread
From: David G. Johnston @ 2025-07-08 20:22 UTC (permalink / raw)
To: DINESH NAIR <Dinesh_Nair@iitmpravartak.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ron Johnson <ronljohnsonjr@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
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:* 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.
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2025-07-08 20:22 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-07-08 11:52 REVOKE ALL ON ALL OBJECTS IN ALL SCHEMAS FROM some_role? Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-08 12:16 ` Scott Ribe <scott_ribe@elevated-dev.com>
2025-07-08 12:26 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-08 12:35 ` Scott Ribe <scott_ribe@elevated-dev.com>
2025-07-08 12:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-07-08 12:59 ` Ron Johnson <ronljohnsonjr@gmail.com>
2025-07-08 13:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-07-08 19:54 ` DINESH NAIR <Dinesh_Nair@iitmpravartak.net>
2025-07-08 20:22 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox