Re: Role incompatibilities

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> Stephen Frost wrote:
> > You were talking about 'enabled' vs. 'applicable' roles. Above
> > they're talking about 'enabled authorization identifiers' (the list
> > of roles you currently have the permissions of) and 'applicable
> > privileges' (the specific privileges you have as that set of roles).
>
> According to the definition, an authorization identifier is either a
> user or a role, so I don't see where the problem is.
>
> enabled authorization identifiers -- as defined
> applicable authorization identifiers -- as defined

I didn't find 'applicable authorization identifiers' in my copy of
SQL2003..

> enabled roles -- all enabled authorization identifiers that are roles
>
> applicable roles -- all applicable authorization identifiers that are
> roles

'enabled roles' don't appear to be discussed in 'Foundation'
unfortunately, just 'applicable roles', which only comes up in the
grant/revoke statements. 'applicable roles', according to the
information_schema view in the spec, would appear to be 'MEMBER'
rights from pg_has_role.

'enabled roles' view in the spec information_schema appears to
correspond to 'USAGE' rights from pg_has_role.

And these are different because of the user/role distinction in the Spec
which Postgres doesn't have but can emulate with the 'noinherit' flag.

> > > > For 'applicable' roles:
> > > >
> > > > pg_has_role('abc','MEMBER');
> > >
> > > What you get from this has no equivalent in the SQL standard.
> >
> > This doesn't apply from what you've quoted above,
>
> The set of roles pg_has_role('abc','MEMBER') minus
> pg_has_role('abc','USAGE') can only be nonempty if you define roles
> with NOINHERIT, but the SQL standard doesn't provide for that. QED.

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.

So while the spec doesn't explicitly define 'NOINHERIT', it's implicit
for roles granted to users. Thus, when the question comes up "what
roles can user X 'set role' to?" (which does happen in the SQL spec, ie:
'applicable_roles'), the "pg_has_role('abc','MEMBER')" needs to be used
to find the answer.

Thanks,

Stephen