PostgreSQL: Show all the privileges for a concrete user

Question

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

Solution 1

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'
;

Solution 2

This command was helpful for me:

\l

Here's how I used it:

postgres=# \l

                        List of databases
 Name   | Owner    | Encoding | Collate | Ctype |          Access privileges          
------------------------------+-----------------+----------+---------+-------+-------------------------------------
 mydb1  | postgres | UTF8     | en_NG   | en_NG | =Tc/postgres                       +
        |          |          |         |       | postgres=CTc/postgres              +
        |          |          |         |       | myuser=CTc/postgres
 mydb2  | postgres | UTF8     | en_NG   | en_NG | =Tc/postgres                       +
        |          |          |         |       | postgres=CTc/postgres              +
        |          |          |         |       | my_user=CTc/postgres

Resources: PostgreSQL: List the database privileges using psql

That's all.

I hope this helps

Comments

[Jeff]

Great solution(s) but for schema permission, you don't need a join lateral. Simply ... FROM pg_namespace, aclexplode(nspacl) AS a ...

[ygoe]

Not sure what this does, but all query results are empty. Yet my user can access their own databases, tables and even peek into foreign databases and create/delete new tables there! This answer doesn't help.

[a_horse_with_no_name]

@ygoe: if a user owns a table (or database), no special grant exists as the owner of an object always has full access to that object (without grants)

[Gergely M]

Note: JOIN LATERAL is available in Postgres from version 9.3 thus the schema permissions query above will not work in AWS Redshift.