Tag Archives: database

PostgreSQL Logo

How to list database privileges in PostgreSQL

When creating a new database in PostgreSQL or when creating a new connection to an existing database from a new client, it is recommended that a dedicated database user is used.

Each database user must only be granted the permissions needed to solve it’s purpose.

The following SQL command allows you to obtain the current PostgreSQL privileges configured in the active database:

SELECT grantee, table_name, privilege_type 
FROM information_schema.role_table_grants

This command will output some like:

granteeTable_nameprivilege_type
management_usernameusersINSERT
management_usernameusersSELECT
management_usernameusersUPDATE
management_usernameusersDELETE
management_usernameusersTRUNCATE
management_usernameusersREFERENCES
management_usernameusersTRIGGER
statistics_usernamesessionsSELECT
Sample Privileges

However, you may only execute this SQL and get the PostgreSQL privileges as an admin user.