Re: table_privileges view always show object owner as a grantor

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)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