Thread

1. Grants where grantor = grantee?

   Colin 't Hart <colinthart@gmail.com> — 2014-04-16T12:15:59Z

   Hi,
   
   Looking through our database I find some cases of grants where grantor
   = grantee.
   
   Is this ever a useful thing to do? I can't imagine why.
   
   Thanks,
   
   Colin
   
   
   

2. Re: Grants where grantor = grantee?

   Tom Lane <tgl@sss.pgh.pa.us> — 2014-04-16T14:32:48Z

   "Colin 't Hart" <colinthart@gmail.com> writes:
   > Looking through our database I find some cases of grants where grantor
   > = grantee.
   > Is this ever a useful thing to do? I can't imagine why.
   
   Revoke them and you'll find out ;-)
   
   I suppose you are referring to the object owner's own default privileges,
   which are treated as being granted by herself to herself.  For example:
   
   regression=# create user alice;
   CREATE ROLE
   regression=# create user bob;
   CREATE ROLE
   regression=# \c - alice
   You are now connected to database "regression" as user "alice".
   regression=> create table alicestab(f1 int);
   CREATE TABLE
   regression=> \z alicestab 
                                Access privileges
    Schema |   Name    | Type  | Access privileges | Column access privileges 
   --------+-----------+-------+-------------------+--------------------------
    public | alicestab | table |                   | 
   (1 row)
   
   regression=> grant select on alicestab to bob;
   GRANT
   regression=> \z alicestab 
                                 Access privileges
    Schema |   Name    | Type  |  Access privileges  | Column access privileges 
   --------+-----------+-------+---------------------+--------------------------
    public | alicestab | table | alice=arwdDxt/alice+| 
           |           |       | bob=r/alice         | 
   (1 row)
   
   We can now see that alice's own permissions on the table were all granted
   by herself.  (Those default privileges are not shown in the first \z
   command because we don't instantiate them until there's another reason to
   make the table's ACL entry non-null; as long as the entry is null, the
   permissions machinery will simply assume that the only granted privileges
   are to the object owner.  But as soon as you do any GRANT/REVOKE, the
   default privileges are instantiated explicitly and now you can see them.)
   
   A better question to ask would be when would it be sensible to *revoke*
   default privileges of this sort.  The canonical example is making a table
   read-only to yourself (I'm not sure if there are any other use-cases that
   are as compelling).  That application is why we do it like this: if those
   privileges didn't appear to be granted by alice, she couldn't revoke them
   either.  The fine print in the SQL standard says that an object owner's
   own privileges on the object are granted by an abstract entity "_SYSTEM",
   but Postgres doesn't do it like that because then the owner could not
   revoke her own privileges.
   
   			regards, tom lane