public inbox for [email protected]help / color / mirror / Atom feed
DROP ROLE as SUPERUSER 8+ messages / 3 participants [nested] [flat]
* DROP ROLE as SUPERUSER @ 2025-02-20 15:48 Dominique Devienne <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Dominique Devienne @ 2025-02-20 15:48 UTC (permalink / raw) To: [email protected] Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently did nothing, even with CASCADE, when I was running it as SUPERUSER, preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do the REVOKE, which DID something this time, and then I could DROP the role. That's hardly convenient :). And I was helping someone else who couldn't figure out how to drop that role. Isn't there a better way? I thought SUPERUSER was more powerful that than. Why isn't it? Thanks, --DD ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 15:55 David G. Johnston <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: David G. Johnston @ 2025-02-20 15:55 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: [email protected] <[email protected]> On Thursday, February 20, 2025, Dominique Devienne <[email protected]> wrote: > Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently > did nothing, even with CASCADE, when I was running it as SUPERUSER, > preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do > the REVOKE, which DID something this time, and then I could DROP the role. > > That's hardly convenient :). And I was helping someone else who couldn't > figure out how to drop that role. Isn't there a better way? > > I thought SUPERUSER was more powerful that than. Why isn't it? > This has nothing to do with power/permissions. It is about not specifying “granted by” in your SQL command and thus failing to fully and correctly specify the single permission you want to revoke. David J. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 15:56 David G. Johnston <[email protected]> parent: David G. Johnston <[email protected]> 1 sibling, 0 replies; 8+ messages in thread From: David G. Johnston @ 2025-02-20 15:56 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: [email protected] <[email protected]> On Thursday, February 20, 2025, David G. Johnston < [email protected]> wrote: > On Thursday, February 20, 2025, Dominique Devienne <[email protected]> > wrote: > >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently >> did nothing, even with CASCADE, when I was running it as SUPERUSER, >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do >> the REVOKE, which DID something this time, and then I could DROP the role. >> >> That's hardly convenient :). And I was helping someone else who couldn't >> figure out how to drop that role. Isn't there a better way? >> >> I thought SUPERUSER was more powerful that than. Why isn't it? >> > > This has nothing to do with power/permissions. It is about not specifying > “granted by” in your SQL command and thus failing to fully and correctly > specify the single permission you want to revoke. > Well, not “single permission” but the ALL only applies to the permission types, not actually everything for all grantors. David J. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 16:05 Tom Lane <[email protected]> parent: David G. Johnston <[email protected]> 1 sibling, 2 replies; 8+ messages in thread From: Tom Lane @ 2025-02-20 16:05 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Dominique Devienne <[email protected]>; [email protected] <[email protected]> "David G. Johnston" <[email protected]> writes: > On Thursday, February 20, 2025, Dominique Devienne <[email protected]> > wrote: >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently >> did nothing, even with CASCADE, when I was running it as SUPERUSER, >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the GRANTOR, do >> the REVOKE, which DID something this time, and then I could DROP the role. > This has nothing to do with power/permissions. It is about not specifying > “granted by” in your SQL command and thus failing to fully and correctly > specify the single permission you want to revoke. It used to be that if a superuser issued GRANT/REVOKE, the operation was silently done as the owner of the affected object. That was always a bit of a wart, since among other things it meant that the object owner could undo it. Now you have to say "GRANTED BY <owner>" to get that effect. I'm not entirely sure, but I think this is closer to what the SQL standard says. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 16:20 David G. Johnston <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: David G. Johnston @ 2025-02-20 16:20 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Dominique Devienne <[email protected]>; [email protected] <[email protected]> On Thu, Feb 20, 2025 at 9:05 AM Tom Lane <[email protected]> wrote: > "David G. Johnston" <[email protected]> writes: > > On Thursday, February 20, 2025, Dominique Devienne <[email protected]> > > wrote: > >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently > >> did nothing, even with CASCADE, when I was running it as SUPERUSER, > >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the > GRANTOR, do > >> the REVOKE, which DID something this time, and then I could DROP the > role. > > > This has nothing to do with power/permissions. It is about not > specifying > > “granted by” in your SQL command and thus failing to fully and correctly > > specify the single permission you want to revoke. > > It used to be that if a superuser issued GRANT/REVOKE, the operation > was silently done as the owner of the affected object. > That is still the case according to the docs (REVOKE): "If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object." The docs seem to be missing reasonable exposition regarding "granted by". The clause isn't even formally mentioned on the page; though I suppose it is because it is delegated to the GRANT page specification. Though the description there says it is basically an ignored compatibility clause - not something that a superuser can use to make things more explicit than using SET ROLE (not sure if it can ATM...). David J. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 16:21 Dominique Devienne <[email protected]> parent: Tom Lane <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Dominique Devienne @ 2025-02-20 16:21 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> On Thu, Feb 20, 2025 at 5:05 PM Tom Lane <[email protected]> wrote: > "David G. Johnston" <[email protected]> writes: > > On Thursday, February 20, 2025, Dominique Devienne <[email protected]> > > wrote: > >> Hi. Today I was surprised that REVOKE ALL ON DATABASE FROM ROLE silently > >> did nothing, even with CASCADE, when I was running it as SUPERUSER, > >> preventing DROP'ing the ROLE. I had to manually SET ROLE to the > GRANTOR, do > >> the REVOKE, which DID something this time, and then I could DROP the > role. > > > This has nothing to do with power/permissions. It is about not > specifying > > “granted by” in your SQL command and thus failing to fully and correctly > > specify the single permission you want to revoke. > > It used to be that if a superuser issued GRANT/REVOKE, the operation > was silently done as the owner of the affected object. That was > always a bit of a wart, since among other things it meant that the > object owner could undo it. Now you have to say "GRANTED BY <owner>" > to get that effect. I'm not entirely sure, but I think this is closer > to what the SQL standard says. > I wasn't aware of GRANTED BY, thanks for that. But that's not much better. It's basically like the SET ROLE to the GRANTOR I did. I guess what I want is GRANTED BY ANYONE! And not have to figure out GRANTOR(s). Also, note that GRANTOR is not even the owner of the DATABASE in my case. --DD ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 16:29 David G. Johnston <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: David G. Johnston @ 2025-02-20 16:29 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] <[email protected]> On Thu, Feb 20, 2025 at 9:21 AM Dominique Devienne <[email protected]> wrote: > But that's not much better. It's basically like the SET ROLE to the > GRANTOR I did. > I guess what I want is GRANTED BY ANYONE! And not have to figure out > GRANTOR(s). > Your stated use case is dropping a role. Does the combination of reassign and drop owned not fulfill the requirements? https://www.postgresql.org/docs/current/role-removal.html Also, you had to know the role you wanted to drop so you already figured out the grantor. David J. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-20 16:52 Tom Lane <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Tom Lane @ 2025-02-20 16:52 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Dominique Devienne <[email protected]>; [email protected] <[email protected]> "David G. Johnston" <[email protected]> writes: > On Thu, Feb 20, 2025 at 9:05 AM Tom Lane <[email protected]> wrote: >> It used to be that if a superuser issued GRANT/REVOKE, the operation >> was silently done as the owner of the affected object. > That is still the case according to the docs (REVOKE): [ scratches head ... ] I thought we had changed that, but nope I'm wrong: regression=# create user alice; CREATE ROLE regression=# create user bob; CREATE ROLE regression=# \c - alice You are now connected to database "regression" as user "alice". regression=> create table alices_table (f1 int); CREATE TABLE regression=> grant select on alices_table to bob; GRANT regression=> \dp alices_table Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+--------------+-------+----------------------+-------------------+---------- public | alices_table | table | alice=arwdDxtm/alice+| | | | | bob=r/alice | | (1 row) regression=> \c - postgres You are now connected to database "regression" as user "postgres". regression=# grant update on alices_table to bob; GRANT regression=# \dp alices_table Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+--------------+-------+----------------------+-------------------+---------- public | alices_table | table | alice=arwdDxtm/alice+| | | | | bob=rw/alice | | (1 row) regression=# revoke update on alices_table from bob; REVOKE regression=# \dp alices_table Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+--------------+-------+----------------------+-------------------+---------- public | alices_table | table | alice=arwdDxtm/alice+| | | | | bob=r/alice | | (1 row) regression=# revoke select on alices_table from bob; REVOKE regression=# \dp alices_table Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+--------------+-------+----------------------+-------------------+---------- public | alices_table | table | alice=arwdDxtm/alice | | (1 row) So grants and revokes are still being done as the object owner by default. Now I'm unclear on exactly what was happening in Dominique's case. Was the problematic permission granted by somebody other than the database's owner? regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-02-20 16:52 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-20 15:48 DROP ROLE as SUPERUSER Dominique Devienne <[email protected]> 2025-02-20 15:55 ` David G. Johnston <[email protected]> 2025-02-20 15:56 ` David G. Johnston <[email protected]> 2025-02-20 16:05 ` Tom Lane <[email protected]> 2025-02-20 16:20 ` David G. Johnston <[email protected]> 2025-02-20 16:52 ` Tom Lane <[email protected]> 2025-02-20 16:21 ` Dominique Devienne <[email protected]> 2025-02-20 16:29 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox