Re: Role incompatibilities

* Peter Eisentraut (peter_e(at)gmx(dot)net) wrote:
> 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.

Alright, now you're trying to confuse me. :P

> 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.

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).

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

Hang on, you're confusing things again. They're not talking about
'enabled privileges', they're talking about 'enabled authorization
identifiers'. You're right that the 'applicable privileges' are the
privileges granted to the 'enabled authorization identifiers', plus
PUBLIC.

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

If those are the view names then I think they might add to the
confusion, and thank the spec for that. ;)

> 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).

This sounds more-or-less right... I think the reason for this is that
what's in information_schema is sometimes supposed to be filtered down
to only what you 'own'. Ownership isn't an 'applicable privilege' but
is instead an attribute of each object. Ownership is granted when roles
are granted though which is where the 'enabled authorization
identifiers' comes in: You're considered to be the 'owner' of everything
which any of your 'enabled authorization identifiers' own.

Perhaps an example here would help:

current user: user1

'enabled authorization identifiers':
user1
role1 (current role)
role2 (granted to role1)
role3 (granted to role1)

'applicable privileges':
select,insert,update,delete on table xyz

owner of xyz: role1 (thus, 'user1' is also considered an 'owner')

I could see a reason to want to know what 'enabled authorization
identifiers' you've currently got, though I'm not sure right off that we
expose this in an easy way to get to the full list in Postgres
(pg_auth_members has this information but you have to handle the
recursion). I'm not sure you actually need the full list though, you
just need to use pg_has_role() to do the check on each of the objects.

> > 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.

It tells you if you have the rights of 'abc' currently or not. If you
want to know if you have a certain privilege on a certain table
currently or not you can just use the regular 'has_table_privilege'
type functions...

> > 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, but I'm pretty sure
there's something about roles which you can 'set role' to but which you
don't currently have the rights of in the SQL spec...

I hope this helps? If not then I'm probably going to have to go reread
the spec again some more myself. :)

Thanks,

Stephen