From: | Stephen Frost <sfrost(at)snowman(dot)net> |
---|---|
To: | "Clark C(dot) Evans" <cce(at)clarkevans(dot)com> |
Cc: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>, Peter Eisentraut <peter_e(at)gmx(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Role incompatibilities |
Date: | 2006-07-28 17:06:15 |
Message-ID: | 20060728170615.GY20016@kenobi.snowman.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
* 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim C. Nasby | 2006-07-28 17:11:48 | Re: [HACKERS] Resurrecting per-page cleaner for btree |
Previous Message | Jim C. Nasby | 2006-07-28 16:59:57 | Re: GUC with units, details |