Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 | 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.
