Re: table_privileges view always show object owner as a grantor - Mailing list pgsql-hackers

Laurenz Albe <laurenz.albe@cybertec.at> writes:
> Ashutosh Sharma wrote:
>> I noticed that irrespective of whoever grants privileges on an object,
>> it's always the object owner who is seen as a grantor in the output of
>> table_privileges view.

The above is demonstrably false ...

regression=# create user alice;
CREATE ROLE
regression=# create user bob;
CREATE ROLE
regression=# create user charlie;
CREATE ROLE
regression=# \c - alice
You are now connected to database "regression" as user "alice".
regression=> create table a1(f int);
CREATE TABLE
regression=> grant select on table a1 to bob with grant option;
GRANT
regression=> \c - bob
You are now connected to database "regression" as user "bob".
regression=> grant select on table a1 to charlie;
GRANT
regression=> select * from information_schema.table_privileges where table_name = 'a1';
 grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
 bob     | charlie | regression    | public       | a1         | SELECT         | NO           | YES
 alice   | bob     | regression    | public       | a1         | SELECT         | YES          | YES
(2 rows)

> Currently the grantor of a privilege is the owner if a superuser
> grants a privilege on the object.

Yes, that is true.

> So I'd say the behavior is fine as it is, but it would not harm to
> document it better (or at all).

It is documented, see under GRANT:

    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. In particular, privileges granted via such a command will
    appear to have been granted by the object owner. (For role membership,
    the membership appears to have been granted by the containing role
    itself.)

    GRANT and REVOKE can also be done by a role that is not the owner of
    the affected object, but is a member of the role that owns the object,
    or is a member of a role that holds privileges WITH GRANT OPTION on
    the object. In this case the privileges will be recorded as having
    been granted by the role that actually owns the object or holds the
    privileges WITH GRANT OPTION. For example, if table t1 is owned by
    role g1, of which role u1 is a member, then u1 can grant privileges on
    t1 to u2, but those privileges will appear to have been granted
    directly by g1. Any other member of role g1 could revoke them later.

    If the role executing GRANT holds the required privileges indirectly
    via more than one role membership path, it is unspecified which
    containing role will be recorded as having done the grant. In such
    cases it is best practice to use SET ROLE to become the specific role
    you want to do the GRANT as.

The point about other members of the owning role being able to revoke
the privileges is why it's done this way.

            regards, tom lane