Thread: DROP ROLE as SUPERUSER
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
On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com> 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.
On Thursday, February 20, 2025, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com> 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.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com>
> 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
On Thu, Feb 20, 2025 at 9:05 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com>
> 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.
On Thu, Feb 20, 2025 at 5:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, February 20, 2025, Dominique Devienne <ddevienne@gmail.com>
> 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
On Thu, Feb 20, 2025 at 9:21 AM Dominique Devienne <ddevienne@gmail.com> 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?
Also, you had to know the role you wanted to drop so you already figured out the grantor.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Feb 20, 2025 at 9:05 AM Tom Lane <tgl@sss.pgh.pa.us> 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
On Thu, Feb 20, 2025 at 5:52 PM Tom Lane <tgl@sss.pgh.pa.us> 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
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
|-----------------------------------|-----------------------------------|-----------|-----------|
6 ACLs to 3 Grantees from 2 Grantors
Dominique Devienne <ddevienne@gmail.com> 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
On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dominique Devienne <ddevienne@gmail.com> 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
Dominique Devienne <ddevienne@gmail.com> writes:
> On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <tgl@sss.pgh.pa.us> 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
On Friday, February 21, 2025, Dominique Devienne <ddevienne@gmail.com> wrote:
On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:Dominique Devienne <ddevienne@gmail.com> 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.
On Fri, Feb 21, 2025 at 3:45 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Friday, February 21, 2025, Dominique Devienne <ddevienne@gmail.com> wrote:On Fri, Feb 21, 2025 at 3:33 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:Dominique Devienne <ddevienne@gmail.com> 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. --DDFrom “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