Re: Role incompatibilities

* Clark C. Evans (cce(at)clarkevans(dot)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 | NO
sfrost | admin | NO
sfrost | app1_admin | NO
(3 rows)

abc=> select * from enabled_roles ;
role_name
---------------------
sfrost
postgres
admin
app1_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