Re: table_privileges view always show object owner as a grantor

On Fri, Mar 29, 2019 at 8:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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 ...
>

Okay. Seems like that is only true when the grantor of a privilege is superuser.

> 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:
>

Okay, Thanks for the pointer. I was actually referring to the
documentation on table_privileges view where the description for
grantor column says : "Name of the role that granted the privilege"

> 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.
>

--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com