'PostgreSQL: Show all the privileges for a concrete user

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

postgresqlpostgresql-9.1psqlprivileges


Solution 1:[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:[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

Solution 3:[3]

This is what worked for me the best. short and clean.

\du lists all user accounts and roles and \du+ is the extended version which shows even more information.

# \du
                                        List of roles
     Role name      |                         Attributes                         | Member of
--------------------+------------------------------------------------------------+-----------
 padmin             | Superuser, Create role, Create DB                          | {}
 test               |                                                            | {}
 postgres           | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root               | Superuser, Create role, Create DB                          | {}

# \du+
                                               List of roles
     Role name      |                         Attributes                         | Member of | Description
--------------------+------------------------------------------------------------+-----------+-------------
 padmin             | Superuser, Create role, Create DB                          | {}        |
 test               |                                                            | {}        |
 postgres           | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 root               | Superuser, Create role, Create DB                          | {}        |

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vao Tsun
Solution 2
Solution 3 Mr. Crowley

Related Questions