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".
This currently works in PostgreSQL but it loses the privileges of
myuser, which violates the standard.
> 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 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.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/