public inbox for [email protected]help / color / mirror / Atom feed
Re: DROP ROLE as SUPERUSER 8+ messages / 3 participants [nested] [flat]
* Re: DROP ROLE as SUPERUSER @ 2025-02-21 10:56 Dominique Devienne <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Dominique Devienne @ 2025-02-21 10:56 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:52 PM Tom Lane <[email protected]> wrote: > 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? > Here's my exact situation (with some renames). The DB Owner (Acme-DBA:...) is not the one that made the GRANT that prevented role foobar from being DROP'd. REVOKE as SUPERUSER was silently doing nothing, until I SET ROLE "SCH1:9XabXbNRbVABafYYGiP7nY" before doing it. ROLE foobar doesn't OWN anything, so David's REASSIGN or DROP OWNED as not relevant here. The point I'm trying to make, is that "hunting down" grantor(s) to connect to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish there was an easier way to drop a role in that situation. --DD D:\>ppg ... -d acmedb10 --impersonate foobar cluster --databases Connected OK (postgresql://postgres@.../acmedb10) Warning: Impersonating user: foobar ======================================== | Privs | dbname | owner | ======================================== | c- | acmedb10 | "Acme-DBA:004k1n" | | c- | postgres | postgres | ======================================== (where c = CONNECT privilege; and C = CREATE privilege) Can CONNECT to 2 databases (out of 4; 4 matching) D:\>ppg ... -d acmedb10 database --acls Connected OK (postgresql://postgres@.../acmedb10) |-----------------------------------|-----------------------------------|-----------|-----------| | Grantor | Grantee | Privilege | Grantable | |-----------------------------------|-----------------------------------|-----------|-----------| ... | "SCH1:9XabXbNRbVABafYYGiP7nY" | foobar | CONNECT | NO | |-----------------------------------|-----------------------------------|-----------|-----------| 6 ACLs to 3 Grantees from 2 Grantors ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-21 14:33 Tom Lane <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Tom Lane @ 2025-02-21 14:33 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> Dominique Devienne <[email protected]> writes: > The point I'm trying to make, is that "hunting down" grantor(s) to connect > to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish > there > was an easier way to drop a role in that situation. --DD REASSIGN OWNED then DROP OWNED is the recommended path. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-21 14:35 Dominique Devienne <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 2 replies; 8+ messages in thread From: Dominique Devienne @ 2025-02-21 14:35 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <[email protected]> wrote: > Dominique Devienne <[email protected]> writes: > > The point I'm trying to make, is that "hunting down" grantor(s) to > connect > > to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish > > there > > was an easier way to drop a role in that situation. --DD > > REASSIGN OWNED then DROP OWNED is the recommended path. > Hi. Am I missing something? foobar does not OWN anything in this case. So I don't see how these recommendations are relevant to this particular case. --DD ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-21 14:44 Tom Lane <[email protected]> parent: Dominique Devienne <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: Tom Lane @ 2025-02-21 14:44 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> Dominique Devienne <[email protected]> writes: > On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <[email protected]> wrote: >> REASSIGN OWNED then DROP OWNED is the recommended path. > Hi. Am I missing something? foobar does not OWN anything in this case. > So I don't see how these recommendations are relevant to this particular > case. --DD DROP OWNED also removes relevant permission entries (which can be thought of as things owned by the role, if you hold your head at the right angle). See its man page. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-21 14:45 David G. Johnston <[email protected]> parent: Dominique Devienne <[email protected]> 1 sibling, 1 reply; 8+ messages in thread From: David G. Johnston @ 2025-02-21 14:45 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] <[email protected]> On Friday, February 21, 2025, Dominique Devienne <[email protected]> wrote: > On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <[email protected]> wrote: > >> Dominique Devienne <[email protected]> writes: >> > The point I'm trying to make, is that "hunting down" grantor(s) to >> connect >> > to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish >> > there >> > was an easier way to drop a role in that situation. --DD >> >> REASSIGN OWNED then DROP OWNED is the recommended path. >> > > Hi. Am I missing something? foobar does not OWN anything in this case. > So I don't see how these recommendations are relevant to this particular > case. --DD > From “drop owned”: Any privileges granted to the given roles on objects in the current database or on shared objects (databases, tablespaces, configuration parameters) will also be revoked. So, the command does more than the name suggests. David J. ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-02-21 15:02 Dominique Devienne <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Dominique Devienne @ 2025-02-21 15:02 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Tom Lane <[email protected]>; [email protected] <[email protected]> On Fri, Feb 21, 2025 at 3:45 PM David G. Johnston < [email protected]> wrote: > On Friday, February 21, 2025, Dominique Devienne <[email protected]> > wrote: > >> On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <[email protected]> wrote: >> >>> Dominique Devienne <[email protected]> writes: >>> > The point I'm trying to make, is that "hunting down" grantor(s) to >>> connect >>> > to DB(s) to be able to "force drop" a ROLE is a PITA. And I really wish >>> > there >>> > was an easier way to drop a role in that situation. --DD >>> >>> REASSIGN OWNED then DROP OWNED is the recommended path. >>> >> >> Hi. Am I missing something? foobar does not OWN anything in this case. >> So I don't see how these recommendations are relevant to this particular >> case. --DD >> > > From “drop owned”: > > Any privileges granted to the given roles on objects in the current > database or on shared objects (databases, tablespaces, configuration > parameters) will also be revoked. > > So, the command does more than the name suggests. > OK, thanks Tom and David. I was misled by the name indeed. --DD ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-04-29 14:50 Dominique Devienne <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 8+ messages in thread From: Dominique Devienne @ 2025-04-29 14:50 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> On Fri, Feb 21, 2025 at 3:44 PM Tom Lane <[email protected]> wrote: > Dominique Devienne <[email protected]> writes: > > On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <[email protected]> wrote: > >> REASSIGN OWNED then DROP OWNED is the recommended path. > > > Hi. Am I missing something? foobar does not OWN anything in this case. > > So I don't see how these recommendations are relevant to this particular > > case. --DD Hi. Resurrecting this older thread, as I'm running into an issue with this. > DROP OWNED also removes relevant permission entries (which can be > thought of as things owned by the role, if you hold your head at > the right angle). See its man page. Except when it doesn't... I'm doing the DROP OWNED, and I'm getting several "WARNING: no privileges could be revoked for ..." for SCHEMAs and ROUTINEs. (which BTW break my unit tests, which monitor outputs). So the "removes relevant permission entries" part is kinda broken IMHO. Or at least, not at all as convenient as one might hope it to be. FWIW. --DD ^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: DROP ROLE as SUPERUSER @ 2025-04-29 15:31 Tom Lane <[email protected]> parent: Dominique Devienne <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Tom Lane @ 2025-04-29 15:31 UTC (permalink / raw) To: Dominique Devienne <[email protected]>; +Cc: David G. Johnston <[email protected]>; [email protected] <[email protected]> Dominique Devienne <[email protected]> writes: > On Fri, Feb 21, 2025 at 3:44 PM Tom Lane <[email protected]> wrote: >> DROP OWNED also removes relevant permission entries (which can be >> thought of as things owned by the role, if you hold your head at >> the right angle). See its man page. > Except when it doesn't... I'm doing the DROP OWNED, and I'm getting > several "WARNING: no privileges could be revoked for ..." for SCHEMAs > and ROUTINEs. (which BTW break my unit tests, which monitor outputs). [ shrug... ] With no details, it's impossible to comment on this. regards, tom lane ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2025-04-29 15:31 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-21 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]> 2025-02-21 14:33 ` Tom Lane <[email protected]> 2025-02-21 14:35 ` Dominique Devienne <[email protected]> 2025-02-21 14:44 ` Tom Lane <[email protected]> 2025-04-29 14:50 ` Dominique Devienne <[email protected]> 2025-04-29 15:31 ` Tom Lane <[email protected]> 2025-02-21 14:45 ` David G. Johnston <[email protected]> 2025-02-21 15:02 ` Dominique Devienne <[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