Consider the following example:
regression=# create user u1;
CREATE ROLE
regression=# create user u2;
CREATE ROLE
regression=# create user u3;
CREATE ROLE
regression=# grant u1 to u2;
GRANT ROLE
regression=# \c - u1
You are now connected as new user "u1".
regression=> create table t1(f1 int);
CREATE TABLE
regression=> grant select on t1 to u3;
GRANT
regression=> \c - u2
You are now connected as new user "u2".
regression=> grant update on t1 to u3;
GRANT
regression=> \z t1
Access privileges for database "regression"
Schema | Name | Type | Access privileges
--------+------+-------+---------------------------------
public | t1 | table | {u1=arwdRxt/u1,u3=r/u1,u3=w/u2}
(1 row)
It's correct that u2 can grant privileges as if he were u1, but I think
that the privileges need to be shown as granted *by* u1. We learned
this lesson some time ago in connection with grants issued by
superusers. Given the above configuration, u1 (or other members of his
role) cannot revoke the privileges granted by u2, which is surely
undesirable since u2 had no independent right to grant those privileges.
I seem to recall that there were some other bad consequences stemming
from having rights appearing in an ACL that could not be traced via
GRANT OPTIONs to the actual object owner.
I think this means that pg_class_ownercheck and related routines can't
simply return "yes, you have this privilege" ... they need to show which
role you have the privilege as. And what happens if you actually have
the privilege via multiple paths --- which one gets chosen? Or imagine
that you do "GRANT SELECT,UPDATE ON ..." and you have grant options for
SELECT via one role, for UPDATE via another.
This is looking a bit messy. Maybe for GRANT/REVOKE, we have to insist
that privileges do not inherit, you have to actually be SET ROLE'd to
whatever role has the authority to do the grant. I haven't figured out
how the SQL spec avoids this problem, considering that they do have the
concept of rights inheriting for roles.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
