Skip site navigation (1) Skip section navigation (2)

Re: Role incompatibilities

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 (view raw or flat)
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

In response to

pgsql-hackers by date

Next:From: Jim C. NasbyDate: 2006-07-28 17:11:48
Subject: Re: [HACKERS] Resurrecting per-page cleaner for btree
Previous:From: Jim C. NasbyDate: 2006-07-28 16:59:57
Subject: Re: GUC with units, details

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group