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 user
identifier, 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 grantee is 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 applicable
privileges 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/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
