Thread: table_privileges view always show object owner as a grantor
Hi All, 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. As an example, consider the following case. create user u1; create user u2 with superuser; create user u3; \c postgres u1 create table t1(a integer); \c postgres u2 grant select on t1 to u3; -- it's u2 who is granting select privileges on t1 to u3 \c postgres u3 select * from table_privileges where table_name = 't1'; postgres=# \c postgres u3 You are now connected to database "postgres" as user "u3". postgres=> select * from information_schema.table_privileges where table_name = 't1'; grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy ---------+---------+---------------+--------------+------------+----------------+--------------+---------------- u1 | u3 | postgres | public | t1 | SELECT | NO | YES (1 row) postgres=> select * from t1; a --- (0 rows) Above output of table_privilges shows 'u1' (who is the object owner of t1) as a grantor instead of u2. Isn't that a wrong information ? If incase that isn't wrong then may i know why does the postgresql documentation on "table_privilegs" describes grantor as "Name of the role that granted the privilege". Here is the documentation link for table_privilges view. https://www.postgresql.org/docs/current/infoschema-table-privileges.html -- With Regards, Ashutosh Sharma EnterpriseDB:http://www.enterprisedb.com
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. > Isn't that a wrong information ? If > incase that isn't wrong then may i know why does the postgresql > documentation on "table_privilegs" describes grantor as "Name of the > role that granted the privilege". Here is the documentation link for > table_privilges view. > > https://www.postgresql.org/docs/current/infoschema-table-privileges.html Currently the grantor of a privilege is the owner if a superuser grants a privilege on the object. If that were not so, how would you disambiguate between privileges granted by a superuser and privileges passed on by somebody who has been granted the privilege WITH GRANT OPTION? Or, with an example: If A grants SELECT to a table WITH GRANT OPTION to B, and B grants the privilege to C, A cannot directly revoke the privilege from C. All A can to is revoke the privilege from B with the CASCADE option. This distiction would be lost if B could appear as grantor just because he has been superuser at some time in the past (and doesn't hold the privilege himself). So I'd say the behavior is fine as it is, but it would not harm to document it better (or at all). Yours, Laurenz Albe
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
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