Re: Role incompatibilities - Mailing list pgsql-hackers

* Clark C. Evans (cce@clarkevans.com) wrote:
> Sorry to ressurect this thread.  However, I've been playing with the new
> role system and I'd prefer to keep CURRENT_USER as the login user, and
> not making it a synonymn for CURRENT_ROLE.  In my application, I love the
> ability to "shed" privleges by "SET ROLE dataentry;".  However, I need
> CURRENT_USER to remain as 'clark' for audit trail triggers (recording
> that 'dataentry' changed a particular order is kinda useless).

This sounds like a reasonable point.  I'm not sure it's something we can
actually do something about but I believe it's something worth thinking
about.

> I have a related information_schema question.  Tom said that I could
> probably use "login" or "inherit" to determine which 'roles' are users,
> and which are really roles.  Is this still the advice?  That said,

Yes, this there isn't really any real difference between the two...

> shouldn't PostgreSQL just call this mixed-thingy an 'authority' to
> reduce confusion.  Then role-is-authority and user-is-authority.
> Probably too late, but, just in case it is still changable...

I'm not really sure this would buy us all that much...

> My deeper question is... from the information_schema, is it possible
> (both in theory via definition, and in pratice via implementation) to
> obtain two things:
>
>   (a) the roles to which I can do "SET ROLE" with, I guess this is
>       my granted roles?
>
>   (b) the roles to which I currently am using for my permission(s),
>       or simply, the role inherit graph and my current role

These should be 'applicable_roles' and 'enabled_roles', respectively.
One possible issue I just noticed was that they both seem to follow
through 'noinherit' roles (even though actual permissions do not).  Only
'applicable_roles' should follow through 'noinherit' roles,
'enabled_roles' shouldn't.  They do work correctly otherwise, from what
I can tell:

abc=> select * from applicable_roles;grantee |      role_name      | is_grantable
---------+---------------------+--------------admin   | postgres            | NOsfrost  | admin               |
NOsfrost | app1_admin          | NO 
(3 rows)

abc=> select * from enabled_roles ;     role_name
---------------------sfrostpostgresadminapp1_admin
(4 rows)

abc=> set role app1_admin;
SET
abc=> select * from enabled_roles ;     role_name
---------------------app1_admin
(1 row)

abc=> select * from applicable_roles ;grantee | role_name | is_grantable
---------+-----------+--------------
(0 rows)

> P.S.  There isn't a way to list "all roles" from the information_schema,
>       except via DISTINCT on a table that refers to them?

I'm not sure a way is defined by the SQL spec, which we try to follow in
information_schema.  pg_authid will give you the list but you need extra
permissisons to view that.  I don't think it'd be out of the question to
add a 'pg_roles' view that provided the full list if there was enough
demand for it...
Thanks,
    Stephen