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

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

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