Обсуждение: 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@personal.formauri.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@personal.formauri.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

Pedro Gimeno Fortea <pgsql@personal.formauri.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/29/2007 03:35:00 PM, Tom Lane wrote:

> "Pedro Gimeno" <pgsql@personal.formauri.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.
>=20
> 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=20=
=20
part of a database administration task. During development some of the=20=
=20
privileges were incorrectly set and I wanted to adjust them. The grant=20=
=20
option was correct; the privilege wasn't, thus I issued a REVOKE and no=20=
=20
error was printed, so I thought everything was correct again. Only=20=20
later, when I doublechecked the ACLs, I realized that the REVOKE had=20=20
not been effective.

 From the docs, chapter 18.2:

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

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

But even in that case, at the very least some kind of notification=20=20
should be issued so that the superuser knows that the permission has=20=20
NOT been revoked. Failing silently is not the proper action in this=20=20
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=20=20
previously used and that seemed to work actually didn't work. That can=20=
=20
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=20=20
schemas, and the situation is the same.

On 05/30/2007 06:57:39 PM, Tom Lane wrote:
> Pedro Gimeno Fortea <pgsql@personal.formauri.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.
>=20
> > Why should I?
>=20
> Because that's how the SQL spec defines that it works.

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

Pedro Gimeno Fortea <pgsql@personal.formauri.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@personal.formauri.es> writes:
>=20
> > Still, is silently ignoring the command the proper action to take
> > when the REVOKE is executed by the superuser and not by the
> > grantor?
>=20
> You want a warning when REVOKE didn't do anything because there was=20=20
> no prior grant to be revoked?

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

Actually I'd rather prefer the REVOKE to be effective when the user who=20=
=20
wants to do it is a superuser; otherwise at a minimum a NOTICE-level=20=20
message would be desirable. If that is "too noisy", then I guess that=20=20
other NOTICEs are too and the DBA should disable notices. I really=20=20
think that this kind of notification is more important than e.g. the=20=20
implicit creation of a primary-key index, because of the security=20=20
implications (the superuser may think that the permission is revoked=20=20
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=20=20
to wanting to delete a table created by another user: if you're not the=20=
=20
owner, you can't, unless you're a superuser. The similarity becomes=20=20
obvious when replacing "delete a table created by" with "revoke a=20=20
privilege granted by" and "owner" by "grantor".

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

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

In a situation as you state it (the destination user doesn't have that=20=
=20
privilege on the object at all), I would agree, but the scenario I'm=20=20
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=20=
=20
> to wanting to delete a table created by another user: if you're not=20=20
> the owner, you can't, unless you're a superuser. The similarity=20=20
> becomes obvious when replacing "delete a table created by" with=20=20
> "revoke a privilege granted by" and "owner" by "grantor".

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

"ERROR:  must be owner of relation auxiliar"

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

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

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

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

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

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

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

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