Show all the privileges for a concrete user

Python241820 picture Python241820 · Nov 23, 2016 · Viewed 16.5k times · Source

How to make a query to the Postgres data dictionary to find out all the privileges that a particular user has.

I've been looking for a solution and I can not find anything. Thanks and good day

Answer

Vao Tsun picture Vao Tsun · Nov 23, 2016

table permissions:

select 
 * 
from information_schema.role_table_grants 
where grantee='YOUR_USER'
;

ownership:

select 
   * 
from pg_tables 
where tableowner = 'YOUR_USER'
;

schema permissions:

select  
  r.usename as grantor, e.usename as grantee, nspname, privilege_type, is_grantable
from pg_namespace
join lateral (
  SELECT
    *
  from
    aclexplode(nspacl) as x
) a on true
join pg_user e on a.grantee = e.usesysid
join pg_user r on a.grantor = r.usesysid 
 where e.usename = 'YOUR_USER'
;