How To List Database Permissions In PostgreSQL

Faruk Erdem

3 years ago

In today’s article, we will talk about How To List Database Permissions In PostgreSQL.

In some cases, we may need to create a different user in a database and give select, insert, update and delete privileges to only some tables.

You have a large number of tables and if you want to see which table you have authorized, you can use the plpgsql script below.

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table, 
    CASE 
        WHEN COUNT(privilege_type) = 7 THEN 'ALL'
        ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
    END AS grants
FROM information_schema.role_table_grants
where CONCAT(table_schema, '.', table_name) not like'%information_schema%' and grantee not like'%postgres%'  and grantee not like'%PUBLIC%' 
GROUP BY table_name, table_schema, grantee;

SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,

CASE

WHEN COUNT(privilege_type) = 7 THEN 'ALL'

ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')

END AS grants

FROM information_schema.role_table_grants

where CONCAT(table_schema, '.', table_name) not like'%information_schema%' and grantee not like'%postgres%' and grantee not like'%PUBLIC%'

GROUP BY table_name, table_schema, grantee;

When you run the above command, it will show the permissions in the tables and views as follows.