Re: Role incompatibilities

Stephen Frost wrote:
> Well..  Applicable roles are roles which you can "SET ROLE" to, but
> which you don't automatically get the permissions of (inherit).  As I
> recall, the spec wants all roles to be like this until an explicit
> "SET ROLE" is done.  When a "SET ROLE" is done, then that role (and
> all other roles granted to it) are "enabled".

I admit that I had thought exactly that until just the other day when I 
started researching this, but in my current understanding the standard 
means something altogether different.

Let's start in part 2, 4.34.4:
 The term enabled authorization identifiers denotes the set of authorization identifiers whose members are the current
useridentifier, the current role name, and every role name that is contained in the current role name.
 
 The term applicable privileges for an authorization identifier A denotes the union of the set of privileges whose
granteeis PUBLIC with the set of privileges whose grantees are A and, if A is a role name, every role name contained in
A.
 The term current privileges denotes the union of the applicable privileges for the current user identifier with the
applicableprivileges for the current role name.
 

This means approximately that the applicable privileges are the enabled 
privileges plus the privileges granted to PUBLIC.

This is also consistent with the definitions of the views 
applicable_roles and enabled_roles in the information schema.

The invocation of these definitions happens in the Access Rules of the 
various clauses, which typically contain something like this (here for 
the UPDATE command):
 The current privileges for TN shall include UPDATE for each <object column>.

So what in fact happens here is that the applicable privileges of 
current user and role determine what you can do.  The enabled roles 
have no practical meaning (except in defining what you can see in the 
information schema, which is weird).

> In Postgres terms, the "pg_has_role()" function can provide the
> answer to both questions, based on what's passed in.
>
> For 'enabled' roles:
>
> pg_has_role('abc','USAGE');

What this actually gives you is both the enabled and the applicable 
roles because apparently it doesn't work to grant roles to PUBLIC, 
which would be the only difference.

> For 'applicable' roles:
>
> pg_has_role('abc','MEMBER');

What you get from this has no equivalent in the SQL standard.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/