Обсуждение: GRANT/roles problem: grant is shown as from login role

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

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> 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.

There is 'revoke update on t1 from u3 granted by u2;' syntax in the
SQL 2003 specification. It doesn't look like we support that syntax
(looking at 8.0.3 anyway)- would that solve the problem if we did?

From your example, u1 couldn't revoke it because u1 couldn't become u2,
and we don't support syntax for saying "revoke this priviledge which was
granted by someone else", but the SQL spec has that syntax and if we did
then I think we'd allow the owner to use it.

Does that help?
Thanks,
    Stephen

[ finally got back to considering this issue ]

Stephen Frost <sfrost@snowman.net> writes:
> Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> 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.

> There is 'revoke update on t1 from u3 granted by u2;' syntax in the 
> SQL 2003 specification. It doesn't look like we support that syntax 
> (looking at 8.0.3 anyway)- would that solve the problem if we did?

> From your example, u1 couldn't revoke it because u1 couldn't become u2,
> and we don't support syntax for saying "revoke this priviledge which was
> granted by someone else", but the SQL spec has that syntax and if we did
> then I think we'd allow the owner to use it.  

> Does that help?

Not a lot.  After further consideration, there's a related problem,
which is brought on by the fact that we store privilege information
per-database rather than globally: what happens when a user's membership
in a role is revoked?  Take the same scenario I gave before:

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=> \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=w/u2}
(1 row)

Suppose now that we REVOKE u2's membership in u1 --- what should happen
to the privilege granted to u3?  There are two plausible theories,
I think:

1. The privilege granted to u3 is dependent on u2's membership in u1  and so it should be revoked too.

2. u2 should have been considered to act in her capacity as member of  u1, therefore the privilege should remain; it is
upto some other  member of u1 to revoke u3's privilege if wanted.
 

The SQL99 spec is exceedingly convoluted but I think it probably wants
interpretation #1 (not totally sure about it though).

However, we can't implement #1 with the present data structure --- if
the REVOKE u1 FROM u2 is done while connected to a different database
than t1 is in, we can't even see that there's an issue, much less clean
it up.  The only way to support interpretation #1 would be, every time
we *use* an ACL entry, to go and check that the grantor of the privilege
still has it.  Given that the same rule would apply to whatever ACL says
the grantor has it, this would be a double recursion with unbelievably
bad performance (did you study Ackermann's function in school?).  And it
still doesn't really do the right thing: consider what happens if u2's
membership is re-granted.  The subsidiary privileges would come back to
life, which they should not under theory #1.

AFAICS, to implement #1 we'd have to switch over to a global data
structure, ie, all privilege descriptors of all databases carried in a
single shared catalog.  This strikes me as a pretty bad idea from both
performance and security viewpoints.

So I think we don't have much choice but to implement theory #2; which
is essentially the same thing I said earlier, ie, ACLs have to record
the grantor of a privilege as being the role actually holding the grant
option, not the role-member issuing the GRANT.

I think it's probably OK to still allow role-members to issue GRANTs,
with the understanding that if you've inherited a grant option via more
than one path, it's indeterminate which source role will get logged as
the grantor.  This seems like it would be a seldom-seen corner case, so
I'm not too concerned about it.  (We'll tell people that if they care,
they should SET ROLE to the specific role they want recorded as the
grantor.  The code's search can be set up to guarantee that the current
role is recorded as the grantor if suitable.)

Comments?
        regards, tom lane

Tom Lane wrote:
> So I think we don't have much choice but to implement theory #2; which
> is essentially the same thing I said earlier, ie, ACLs have to record
> the grantor of a privilege as being the role actually holding the grant
> option, not the role-member issuing the GRANT.

There are really two different considerations here.

The first is the meaning of the role relationships involved.  With
respect to this, I'm in agreement that the recorded grantor of the
privilege should be the role actually holding the option.

But the second is auditing.  It's useful to know which user/role
actually performed the grant in question, independent of the grant
relationships themselves.

These two are at odds with each other only if the system can record
only one of the two things.  The auditing consideration really argues
for the implementation of an audit trail table/structure, if one
doesn't already exist (and if it already exists, then clearly the ACLs
should be storing the id of the role holding the grant, since the
audit structure will separately record the user/role issuing the
grant).



-- 
Kevin Brown                          kevin@sysexperts.com