public inbox for [email protected]
help / color / mirror / Atom feedRe: DROP ROLE as SUPERUSER
8+ messages / 3 participants
[nested] [flat]
* Re: DROP ROLE as SUPERUSER
@ 2025-02-21 10:56 Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[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 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 ` Re: DROP ROLE as SUPERUSER 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 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
@ 2025-02-21 14:35 ` Dominique Devienne <[email protected]>
2025-02-21 14:44 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-02-21 14:45 ` Re: DROP ROLE as SUPERUSER David G. Johnston <[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 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-02-21 14:35 ` Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
@ 2025-02-21 14:44 ` Tom Lane <[email protected]>
2025-04-29 14:50 ` Re: DROP ROLE as SUPERUSER 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 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-02-21 14:35 ` Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:44 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
@ 2025-04-29 14:50 ` Dominique Devienne <[email protected]>
2025-04-29 15:31 ` Re: DROP ROLE as SUPERUSER 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-02-21 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-02-21 14:35 ` Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:44 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-04-29 14:50 ` Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
@ 2025-04-29 15:31 ` Tom Lane <[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
* Re: DROP ROLE as SUPERUSER
2025-02-21 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-02-21 14:35 ` Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
@ 2025-02-21 14:45 ` David G. Johnston <[email protected]>
2025-02-21 15:02 ` Re: DROP ROLE as SUPERUSER 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 10:56 Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:33 ` Re: DROP ROLE as SUPERUSER Tom Lane <[email protected]>
2025-02-21 14:35 ` Re: DROP ROLE as SUPERUSER Dominique Devienne <[email protected]>
2025-02-21 14:45 ` Re: DROP ROLE as SUPERUSER David G. Johnston <[email protected]>
@ 2025-02-21 15:02 ` Dominique Devienne <[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
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