How to View Access Privileges in PostgreSQL

In PostgreSQL, managing access privileges is an essential part of database administration, especially in multi-user environments.

One of the most useful commands for viewing and managing access privileges for database objects (such as tables, views, sequences, etc.) is the \dp command, which is available in the psql command-line interface.

This command provides a detailed view of which roles have what kind of access to each object in the current database.

In this article, we will explain how to use the \dp command, interpret its output, and show examples to help you understand access control in PostgreSQL.

What is the \dp Command in PostgreSQL?

The \dp command in PostgreSQL is used to display the access privileges for all relations in the current database. Relations include tables, views, sequences, and other objects that can have associated permissions. When you run the \dp command, it lists the relations and their associated privileges for each role (user or group) in the database. This is particularly useful for database administrators (DBAs) who need to manage and review who has access to specific database objects and what actions they are allowed to perform.

Syntax

\dp

Simply typing \dp in the psql terminal will display the access privileges for all relations in the current schema of the database.

Example Output

Here’s an example of what the output might look like when running the \dp command:

                         Access privileges
 Schema |   Name    |   Type   |     Access privileges     | Column privileges | Policies 
--------+-----------+----------+---------------------------+-------------------+----------
 public | employees | table    | postgres=arwdDxt/postgres  |                   | 
 public | orders    | table    | postgres=arwdDxt/postgres  |                   | 
(2 rows)

Explanation of the Output

The output of the \dp command includes the following columns:

1. Schema:
   The schema in which the object (table, view, sequence, etc.) resides. Schemas help organize database objects into logical groups.
2. Name:
   The name of the object, such as the table or view. In the example above, the objects are employees and orders.
3. Type:
   The type of the object. Common object types include:
   • table: A regular table in the database.
   • view: A virtual table based on a SELECT query.
   • sequence: A special object used for auto-incrementing values, often used for primary keys.
4. Access Privileges:
   This shows the roles and their granted privileges for each object. The privileges are represented by a series of letters, each corresponding to a specific permission. For example:
   • r: SELECT (allows reading data from the table)
   • w: UPDATE (allows modifying data in the table)
   • a: INSERT (allows adding new rows to the table)
   • d: DELETE (allows deleting rows from the table)
   • D: TRUNCATE (allows truncating the table)
   • x: REFERENCES (allows creating foreign keys referencing the table)
   • t: TRIGGER (allows creating triggers on the table)

   In the example output, the postgres role has full access (arwdDxt) to both the employees and orders tables.

5. Column Privileges:
   This column displays any column-specific privileges. For example, you can give a user SELECT access to only a few columns in a table, not the entire table. If there are no column-specific privileges, this column will be empty.
6. Policies:
   This column is related to Row-Level Security (RLS) policies, which control which rows are accessible to users based on conditions. If RLS policies are configured for a table, they will appear here.

How to Use the \dp Command

1. View All Access Privileges

Running the \dp command without any arguments will display the access privileges for all relations (tables, views, etc.) in the current schema:

\dp

This is helpful for getting an overview of the access configuration across the database.

2. View Access Privileges for a Specific Object

If you want to see the access privileges for a specific table or view, use the \dp command followed by the name of the object. For example, to view the privileges for the employees table:

\dp employees

This will display the access privileges specific to that table, including which roles have which permissions.

Example Query Output:

                         Access privileges
 Schema |   Name    |   Type   |     Access privileges     | Column privileges | Policies 
--------+-----------+----------+---------------------------+-------------------+----------
 public | employees | table    | postgres=arwdDxt/postgres  |                   | 
(1 row)

3. View Privileges for Specific Roles

You can also check the access privileges for a specific role (user). For example, to see the privileges granted to the postgres role, you can execute:

\dp *.* postgres

This will show all objects to which the postgres role has any kind of access privileges.

4. Manage Permissions

The \dp command can also help DBAs understand the current permissions configuration in the database. If adjustments need to be made (for example, revoking a permission or granting additional privileges), DBAs can use SQL statements like GRANT and REVOKE to modify the access privileges. After making such changes, running the \dp command again will help verify that the changes were applied successfully.

Common Use Cases for the \dp Command

1. Verifying User Access

If you are a DBA and want to verify what kind of access a specific user has to a particular table, the \dp command is a quick way to check the privileges. For example, if you’re troubleshooting an access issue, you might use:

\dp orders

This will tell you if the user has the appropriate permissions (e.g., SELECT, INSERT) to perform the required action on the table.

2. Auditing Permissions

The \dp command can be used to audit permissions across your database, ensuring that only the intended roles have access to sensitive data. For example, you can check if a specific table, such as orders, has unnecessary permissions granted to unauthorized users.

3. Ensuring Security Best Practices

If you’re enforcing the principle of least privilege, the \dp command can help you ensure that each role only has the permissions they need. For example, you can use the command to identify tables where users may have excessive privileges and then revoke unnecessary permissions.

Conclusion

The \dp command in PostgreSQL is an invaluable tool for reviewing and managing access privileges for relations in your database. Whether you’re a DBA auditing user access or managing permissions for security purposes, \dp provides a clear and concise view of which roles have access to which database objects, and what actions they are allowed to perform. By using this command effectively, you can ensure that your database permissions are configured correctly and securely.