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

Search Discussions

  • Stephen Frost at Sep 2, 2005 at 3:23 pm

    * 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
  • Tom Lane at Oct 7, 2005 at 6:22 pm
    [ 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 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
  • Kevin Brown at Oct 13, 2005 at 12:46 am

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedSep 1, '05 at 8:47p
activeOct 13, '05 at 12:46a
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase