Re: GRANT/roles problem: grant is shown as from login role

[ finally got back to considering this issue ]

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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 up to 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