postgresqlpostgresql-9.1psqlprivileges

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


Solution

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