This post will demonstrate how to list all users, permissions, and roles on a PostgreSQL database server.
Maintaining data security is paramount in any application or database deployment, and making sure users only have access to the data they need is the cornerstone of this security. PostgreSQL has robust user, role, and permission management features, particularly with role based access that allows you to assign permissions to roles, and roles to users, making management tasks more straightforward and reducing the chance of accidentally assigning the wrong permissions to users.
As your database deployments grow, the number of users and their roles and permissions will also – increasing in not just in number and complexity. It is important to regularly review permissions, and revoke those that are no longer required, and to effectively do so you need to be able to take an overview of your database security by inspecting all assigned permissions.
Connect to PostgreSQL as an Administrative Users
The default PostgreSQL user, named postgres has administrative rights for all databases on a PostgreSQl cluster. You can log in as that user and connect to the local PostgreSQL server by running:
sudo -u postgres psql
If you are connecting remotely, you will need to specify your credentials.
Listing all PostgreSQL Users
Once connected to a PostgreSQl server with psql, you can list all users in PostgreSQL, issue the following command:
\du
This will output all users, including their login username, roles, and whether they have administrative rights.
List all PostgreSQL Roles
List all roles in PostgreSQL with the following command:
\dg
This will output a list of all roles, including the name of the role and the names of the users who have that role.
List all Assigned Permissions in PostgreSQL
To list all granted permissions in PostgreSQL, execute the following:
\dp
This will list all assigned permissions including:
- The name of the table or view the permission affects
- The type of permission (SELECT, INSERT, UPDATE, or DELETE)
- The role or user who has been assigned the permission
Once you have a list of all users, roles, and permissions in your PostgreSQL deployment, you can check that each user has only the permissions required on the specific databases, tables, columns, or views they need access to.
