Re: Role incompatibilities - Mailing list pgsql-hackers

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Stephen Frost wrote:
> > Eh, it does and it doesn't.  The SQL standard says that no roles are
> > automatically inheirited and that you have to 'set role' to them.
> > Thus, all non-user roles which are granted to users in Postgres would
> > need to be defined 'noinherit' to have things work as the spec wants.
>
> This is not what the SQL standard says.  According to the SQL standard,
> I can do
>
> CREATE ROLE a;
> CREATE ROLE b;
> CREATE ROLE c;
> GRANT SELECT ON TABLE foo TO c;
> GRANT c TO b;
> GRANT b TO a;
> GRANT a TO myuser;
> SET ROLE a;
> SELECT * FROM foo;
>
> This contradicts the theory that all roles are mandated to be
> "noinherit".

Sorry, Tom corrected me, 'users' should be 'noinherit' to follow the
spec.

> This currently works in PostgreSQL but it loses the privileges of
> myuser, which violates the standard.

The standard wants them tracked seperately.

> > So while the spec doesn't explicitly define 'NOINHERIT', it's
> > implicit for roles granted to users.
>
> This is the important distinction.  The "inherit" flag should not be a
> property of a role but should be determined at run time.  Roles granted
> to the current role are automatically activated, roles granted to the
> current user need to be set first.  So it's not even a matter of
> "granted to users", it's a matter of whether the grantee is the
> "current user" or the "current role" of the session.

I really don't see a whole lot of value in this.

> I think the only reasonable way to achieve standard comformance is to
> track current user and current role separately.  To achieve traditional
> group behavior we can extend the standard by the simple feature that
> the current role is initialized to be the current user (rather than
> being empty), which would automatically draw in the privileges from all
> contained roles.  Then we can forget about the inherit flag altogether.

Yes, to conform exactly to the standard you'd have to again seperate
roles from users.  I don't think this is actually a useful thing to do
though.  We allow users to set things up the way the spec wants them
(except for the complication wrt set role dropping the current 'user'
permissions but I don't see a good way to avoid that without a great deal
of trouble), if they choose to, while retaining backwards compatibility
with prior versions (for the most part anyway, user/groups with the same
name can't be handled correctly, for example).

Dropping the current user permissions when doing a 'set role' is
actually something I like, personally...  I also like the 'noinherit'
flag which I think is more flexible than what the SQL spec requires.

If there are use cases where what we're doing is causing problems for
users who are looking for what the SQL spec has exactly, please let us
know..  Given that other databases don't religiously follow the SQL spec
wrt roles either makes me suspect you won't find too cases like that..
Alternatively, come up with a patch to change the current behaviour and
ask folks to review it.
Thanks,
    Stephen