Home » Programming » Databases » How to List All Users, Permissions, and Roles in PostgreSQL

How to List All Users, Permissions, and Roles in PostgreSQL

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:


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:


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:


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.

Photo of author
I'm Brad, and I'm nearing 20 years of experience with Linux. I've worked in just about every IT role there is before taking the leap into software development. Currently, I'm building desktop and web-based solutions with NodeJS and PHP hosted on Linux infrastructure. Visit my blog or find me on Twitter to see what I'm up to.

Leave a Comment