Re: BUG #3319: Superuser can't revoke grants on a schema given by aother user

The following bug has been logged online:

Bug reference: 3319
Logged by: Pedro Gimeno
Email address: pgsql(at)personal(dot)formauri(dot)es
PostgreSQL version: 8.2.4
Operating system: Linux
Description: Superuser can't revoke grants on a schema given by
aother user
Details:

When a USAGE grant on a SCHEMA is given by an user (non-superuser in my
case), the superuser can't revoke it; instead the REVOKE statement is
silently ignored. If a SET ROLE to the grantor is issued then the REVOKE
works as expected. I have not tried with a CREATE privilege.

Here's a test case suitable for psql:

CREATE USER user1;
CREATE USER user2;
CREATE DATABASE test1;
\c test1
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO user1 WITH GRANT OPTION;
SET SESSION AUTHORIZATION user1;
GRANT USAGE ON SCHEMA public TO user2;
RESET SESSION AUTHORIZATION;
REVOKE USAGE ON SCHEMA public FROM user2;
\dn+ public
-- {...,user1=U*/postgres,user2=U/user1}
-- note user2 has still usage privileges
SET ROLE user1;
REVOKE USAGE ON SCHEMA public FROM user2;
RESET ROLE;
\dn+ public
-- {...,user1=U*/postgres}
-- this is expected

"Pedro Gimeno" <pgsql(at)personal(dot)formauri(dot)es> writes:
> When a USAGE grant on a SCHEMA is given by an user (non-superuser in my
> case), the superuser can't revoke it; instead the REVOKE statement is
> silently ignored.

This is not a bug. If you want to revoke the privilege, revoke the
GRANT OPTION you originally gave. For example:

test1=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+--------------------------------------------------------+------------------------
public | postgres | {postgres=UC/postgres,user1=U*/postgres,user2=U/user1} | Standard public schema
(1 row)

test1=# revoke grant option for usage on schema public from user1;
ERROR: dependent privileges exist
HINT: Use CASCADE to revoke them too.
test1=# revoke grant option for usage on schema public from user1 cascade;
REVOKE
test1=# \dn+ public
List of schemas
Name | Owner | Access privileges | Description
--------+----------+-----------------------------------------+------------------------
public | postgres | {postgres=UC/postgres,user1=U/postgres} | Standard public schema
(1 row)

Alternatively, since you are superuser, you can become user1 and revoke
the privilege he gave ...

regards, tom lane

On 05/29/2007 03:35:00 PM, Tom Lane wrote:

> "Pedro Gimeno" <pgsql(at)personal(dot)formauri(dot)es> writes:
> > When a USAGE grant on a SCHEMA is given by an user (non-superuser
> > in my case), the superuser can't revoke it; instead the REVOKE
> > statement is silently ignored.
>
> This is not a bug. If you want to revoke the privilege, revoke the
> GRANT OPTION you originally gave.

Why should I? I want to revoke the privilege, not the grant option, as
part of a database administration task. During development some of the
privileges were incorrectly set and I wanted to adjust them. The grant
option was correct; the privilege wasn't, thus I issued a REVOKE and no
error was printed, so I thought everything was correct again. Only
later, when I doublechecked the ACLs, I realized that the REVOKE had
not been effective.

From the docs, chapter 18.2:

"A database superuser bypasses all permission checks. This is a
dangerous privilege and should not be used carelessly; it is best to do
most of your work as a role that is not a superuser.[...]"

If this behaviour is really by design, the documentation should at
least state that a database superuser bypasses all permission checks
EXCEPT the permission to revoke roles granted by other users.

But even in that case, at the very least some kind of notification
should be issued so that the superuser knows that the permission has
NOT been revoked. Failing silently is not the proper action in this
case.

> Alternatively, since you are superuser, you can become user1 and
> revoke the privilege he gave ...

That's right, assuming that you noticed that the REVOKE statement you
previously used and that seemed to work actually didn't work. That can
be undetected for an undefined time and is a security risk IMO.

By the way, I have tried with permissions given on tables, not just
schemas, and the situation is the same.

Pedro Gimeno Fortea <pgsql(at)personal(dot)formauri(dot)es> writes:
> On 05/29/2007 03:35:00 PM, Tom Lane wrote:
>> This is not a bug. If you want to revoke the privilege, revoke the
>> GRANT OPTION you originally gave.

> Why should I?

Because that's how the SQL spec defines that it works.

regards, tom lane

On 05/30/2007 06:57:39 PM, Tom Lane wrote:
> Pedro Gimeno Fortea <pgsql(at)personal(dot)formauri(dot)es> writes:
> > On 05/29/2007 03:35:00 PM, Tom Lane wrote:
> >> This is not a bug. If you want to revoke the privilege, revoke
> >> the GRANT OPTION you originally gave.
>
> > Why should I?
>
> Because that's how the SQL spec defines that it works.

Still, is silently ignoring the command the proper action to take when
the REVOKE is executed by the superuser and not by the grantor?

Pedro Gimeno Fortea <pgsql(at)personal(dot)formauri(dot)es> writes:
> On 05/30/2007 06:57:39 PM, Tom Lane wrote:
>> Because that's how the SQL spec defines that it works.

> Still, is silently ignoring the command the proper action to take when
> the REVOKE is executed by the superuser and not by the grantor?

You want a warning when REVOKE didn't do anything because there was no
prior grant to be revoked? According to the code comments, this was
considered and rejected as "too noisy, as well as inconsistent with the
GRANT case". I can't find the discussion right now, but it would have
probably been in May 2004 or a bit before, because the comment seems to
date from a commit on 1 June 2004.

regards, tom lane

On 05/30/2007 07:55:58 PM, Tom Lane wrote:

> Pedro Gimeno Fortea <pgsql(at)personal(dot)formauri(dot)es> writes:
>
> > Still, is silently ignoring the command the proper action to take
> > when the REVOKE is executed by the superuser and not by the
> > grantor?
>
> You want a warning when REVOKE didn't do anything because there was
> no prior grant to be revoked?

No, I want a warning when REVOKE didn't do anything because there *was*
a grant to be revoked, but the user who wanted to revoke it was not the
grantor.

Actually I'd rather prefer the REVOKE to be effective when the user who
wants to do it is a superuser; otherwise at a minimum a NOTICE-level
message would be desirable. If that is "too noisy", then I guess that
other NOTICEs are too and the DBA should disable notices. I really
think that this kind of notification is more important than e.g. the
implicit creation of a primary-key index, because of the security
implications (the superuser may think that the permission is revoked
when it actually isn't, so the grantee can do Bad Things).

Note that this is not similar to the GRANT case. I'd say it's similar
to wanting to delete a table created by another user: if you're not the
owner, you can't, unless you're a superuser. The similarity becomes
obvious when replacing "delete a table created by" with "revoke a
privilege granted by" and "owner" by "grantor".

At the very least, if nothing is changed then this quirk should be
documented, perhaps in the REVOKE statement.

> According to the code comments, this was considered and rejected as
> "too noisy, as well as inconsistent with the GRANT case". I can't
> find the discussion right now, but it would have probably been in May
> 2004 or a bit before, because the comment seems to date from a commit
> on 1 June 2004.

In a situation as you state it (the destination user doesn't have that
privilege on the object at all), I would agree, but the scenario I'm
stating is different.

On 05/30/2007 08:44:19 PM, Pedro Gimeno Fortea wrote:

> Note that this is not similar to the GRANT case. I'd say it's similar
> to wanting to delete a table created by another user: if you're not
> the owner, you can't, unless you're a superuser. The similarity
> becomes obvious when replacing "delete a table created by" with
> "revoke a privilege granted by" and "owner" by "grantor".

To further ellaborate on this, let me compare the REVOKE case with the
DROP TABLE IF EXISTS <table_name> case. If it does not exist,
PostgreSQL issues a NOTICE-level message (in the parallel case, REVOKE
prints nothing, which is OK to me). But if it exists and the user who
wants to drop the table is not the owner or a superuser, an ERROR-level
message is printed:

"ERROR: must be owner of relation auxiliar"

But, in the parallel case with REVOKE, nothing at all is printed. This
is a quite unexpected behaviour in my opinion.

I got a broader view of the whole picture and obviously my proposal
that the superuser automatically revokes the privileges granted by all
others does not make sense. So let me state the solutions I propose to
the problem I'm facing:

(1) In the documentation for REVOKE, after the paragraph that begins
with "A user can only revoke privileges that were granted directly by
that user." add another paragraph similar to this:

"The rule stated in the previous paragraph is also valid for the
superuser. The superuser can however issue SET ROLE commands to revoke
the privileges granted by the desired users."

(2) In the documentation for REVOKE, state clearly that REVOKE will
fail silently if the user issuing the command is not the grantor. Do so
preferably near the bit about the superuser above.

(3) When issuing the command REVOKE <PRIV> ON <OBJ> FROM <USER>, issue
a NOTICE or WARNING message when, after executing it, the user <USER>
has still privilege <PRIV> on object <OBJ>.

(4) Add a GRANTED BY <USER> extension to the REVOKE command which
allows to revoke permissions given by other users, where <USER> can be
ALL. Obviously it would be subject to other checks which could make it
fail.

Of course 2 and 3 are mutually exclusive. Solution 1+2 is the simplest,
as it only involves documentation. Solution 1+3 would be enough to
avoid most surprises. Solution 1+3+4 would be ideal.