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,
WHEN COUNT(privilege_type) = 7 THEN 'ALL'
ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
END AS 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.