Find out if user got permission to select/update/... a table/function/... in PostgreSQL

Question

What is the recommended way to figure out if a user got a certain right (e.g. select or execute) on a certain class (e.g. table or function) in PostgreSQL?

At the moment I got something like

aclcontains(
    someColumnWithAclitemArray,
    makeaclitem(userOid,grantorOid,someRight,false))

but it's terrible since I have to check for every grantorOid that is possible and for every userOid the user can belong to.

On a related note: what are the possible rights you can test for? I haven't found any documentation but reading the source code I guess:

INSERT
SELECT
UPDATE
DELETE
TRUNCATE
REFERENCES
TRIGGER
EXECUTE
USAGE
CREATE
CONNECT

There also seems to be a CREATE TEMP right, but I can't figure out the correct text to use in the makeaclitem-function.

Answer 1

I've found that a better approach (and I seem to remember this was taken from some queries built into psql, or maybe the information_schema views) is to use the has_*_privilege functions, and simply apply them to a set of all possible combinations of user and object. This will take account of having access to an object via some group role as well.

For example, this will show which users have which access to non-catalogue tables and views:

select usename, nspname || '.' || relname as relation,
       case relkind when 'r' then 'TABLE' when 'v' then 'VIEW' end as relation_type,
       priv
from pg_class join pg_namespace on pg_namespace.oid = pg_class.relnamespace,
     pg_user,
     (values('SELECT', 1),('INSERT', 2),('UPDATE', 3),('DELETE', 4)) privs(priv, privorder)
where relkind in ('r', 'v')
      and has_table_privilege(pg_user.usesysid, pg_class.oid, priv)
      and not (nspname ~ '^pg_' or nspname = 'information_schema')
order by 2, 1, 3, privorder;

The possible privileges are detailed in the description of the has_*_privilege functions at http://www.postgresql.org/docs/8.3/static/functions-info.html after table 9-45.

'CREATE TEMP' is a database-level privilege: it permits a user to use a pg_temp_* schema. It can be tested with has_database_privilege(useroid, datoid, 'TEMP').

Answer 2

Take a look at the "Access Privilege Inquiry Functions" and also the "GRANT" reference page.