In PostgreSQL, privileges decide what operations a database object can perform. The privileges can be granted or revoked to a database object using the GRANT or REVOKE commands. To perform a specific database operation, Postgres users may need to check the privileges assigned to a specific database object. To do that, different commands or meta-commands can be used in Postgres.

This article will explain how to get the list of assigned privileges to a specific Postgres table.

How to Get/Check the List of Privileges Granted to a Postgres Table?

In PostgreSQL, the “\z” command and “information_schema” can be used to check the list of assigned privileges to a certain Postgres table.

Example 1: Check Table Privileges Using “\z” Command

Type the “\z” command followed by the table’s name to check the privileges assigned to a particular table:

\z emp_info;

Here “emp_info” represents the table name:

The above snippet shows that the “postgres”, “sample_user”, and “example_user” users have access to the “emp_info” table. The stated users are granted various privileges, such as append, read, write, delete, truncate, REFERENCES, and TRIGGER.

Note: in the above snippet, the “arwdDxt” is the abbreviation for different privileges that are explained in the official Postgres documentation.

Example 2: Check Table Privileges Using “information_schema”

In PostgreSQL, the “information_schema.role_table_grants” is used to get the list of all privileges assigned to a particular table. Here is the example code that demonstrates the working of the information schema and the “role_table_grants” view:

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_name='emp_bio';

In the above code:

- The “grantee” and “privilege_type” are the columns to be fetched.
- The “information schema” is used to get all the information regarding the database objects, in our case, the object represents a table.
- The “role_table_grants” view is used to determine the assigned privileges on the selected tables or views.

The output snippet shows that the stated command retrieves detailed information regarding the privileges assigned to the “emp_info” table.

That’s it! All the necessary aspects of getting the list of assigned privileges to a Postgres table have been explained.

Conclusion

In PostgreSQL, the “\z” command and “information_schema” can be used to check the list of assigned privileges to a certain Postgres table. Users can utilize the “\z” command followed by the table’s name to check the privileges assigned to a particular table. Alternatively, the “information_schema” can be executed along with the “role_table_grants” view to determine the assigned privileges on the selected tables or views. This article has covered a couple of methods to fetch the list of privileges allocated to a Postgres table.