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/ ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
