Retrieving all object privileges for specific role

Question

Is there an easy way to enumerate all objects that a specific role has some access privilege to? I know of the set of has_*_privilege functions in pg_catalog but they don't do the job, I want to work the other way around. Effectively I want to have a view that gives oid and access privilege for anything stored in pg_class for a specific role.

Such a view would be extremely handy to check if the security of the database is correctly set up. Typically there are far fewer roles than relations so checking the roles is much less onerous IMHO. Should such an utility not be available in the standard PostgreSQL distribution?

According to the source code (acl.h) the aclitem is a struct:

typedef struct AclItem
{ Oid         ai_grantee;     /* ID that this item grants privs to */
  Oid         ai_grantor;     /* grantor of privs */
  AclMode     ai_privs;       /* privilege bits */
} AclItem;

Easy to work with. However, pg_type lists this as a user-defined, non-composite type. Why is that? The only way I see right now is to parse the aclitem[] array using string functions. Is there a better way to analyze the aclitem array?

Added information Trawling through the various PG lists, it is obvious that this issue keeps popping up in various forms at least since 1997 (did we have computers then? was tv around?), most relevant in the discussion thread "Binary in/out for aclitem" on pgsql-hackers in early 2011. As a (technically skilled) user - rather than a hacker - of PG I appreciate the concern of the developers to maintain a stable interface, but some of the concern voiced in the thread goes a little far for my tastes. What is the real reason not to have a pg_acl table in the system catalogs with definition equal to the AclItem struct in the source code? When did that struct last change? I am also aware of SE developments that will likely introduce changes to the way security is handled - when a users opts to, presumably - so I will settle for something that presents acl information in such a way that it is easy to enumerate granted privileges for a specific user, such as:

SELECT * FROM pg_privileges WHERE grantee = 16384;

Like so it can still be an abstraction of the underlying structures so any changes under the hood could then (presumably) still be translated into the exposed interface. Not too different from the information_schema approach, I would say.

Cheers, Patrick

Answer 1

There's no such view out of the box, but the data needed to create it is in the system catalogs:

http://www.postgresql.org/docs/current/static/catalogs.html

For instance, there's a relacl field in pg_class:

select oid::regclass, relacl from pg_class;

There are similar fields in other catalogs, namely typacl in pg_type and proacl in pg_proc.

You'll presumably want to use two more catalogs, namely pg_authid to know which roles are have superuser privileges, and pg_auth_members to know who has what role.

(The pg_default_acl is only used during object creation, so is not useful.)

There are a couple of aclitem-related internal functions that may come in handy in creating the view. You can list them in psql like so:

\df+ *acl*

In particular aclexplode(). The following example will hopefully be enough to get you started:

select oid::regclass,
       (aclexplode(relacl)).grantor,
       (aclexplode(relacl)).grantee,
       (aclexplode(relacl)).privilege_type,
       (aclexplode(relacl)).is_grantable
from pg_class
where relacl is not null;

It can be optimized by expanding the acl rows first, e.g.:

select oid::regclass,
       aclitem.grantee
from (select oid, aclexplode(relacl) as aclitem from pg_class) sub

It will lead you straight to the desired result.

Insofar as I'm aware, that's about as good as it'll get using the built-in tools. (Naturally, you could write your own set of operators in C if you'd like to try to optimize this further.)

With respect to your extra questions, I'm afraid they can only be answered by a handful of people in the world, aka the core devs themselves. They hang out on the pg hackers list more often than they do here.