This quick tutorial will show you how to get privileged information about a user available on the server.
Types of Privileges
Before we proceed to query PostgreSQL for information about the users, let us highlight the various permissions and what they allow the user assigned them to do.
The types of privileges in the PostgreSQL DBMS are:
- SELECT – The select privilege allows a user to select values from any columns of any table-like object. Table-like objects in PostgreSQL include a table, a view, materialized view, etc.
- INSERT – The insert permission allows the user to insert new rows into a table. You can also grant the insert privilege on a specific column allowing insert rows into only the set columns.
- UPDATE – This privilege type enables the set-user to update rows in any columns in a table or view. Similar to the insert permission, you can set it on a specific column to allow the user to update rows of a specific column(s) only.
- DELETE – This will allow a user to drop/delete a row from any modifiable table-like object. This requires that the user have the SELECT permission since it needs to reference table columns to verify the rows to be dropped.
- CONNECT – The connect permission allows a user to connect to the server. This type of permission is checked on connection startup by the pg_hba.conf file.
- CREATE – The create privilege enables a user to create either a new schema, a table in a set database. It can also allow a user to install extensions on a database. If this permission is revoked on a user, it does not remove all the existing objects until the termination point.
- TRUNCATE – as the name suggests, it grants the user permission to truncate a table.
- TRIGGER – This enables a user to create a trigger on table-like objects.
- TEMPORARY – Allows users to create a temporary table while connected to a set database.
- EXECUTE – execute permission enables a user to call functions or procedures. This is the only type of permission that can apply to functions/procedures.
- REFERENCES – Allows a user to create foreign key constraints that reference a table or columns.
How to Show User Privileges
Listing user privileges is simple. In psql, use the query \du+ as shown in the output below:
The above output shows the Postgres and temp users with their corresponding permissions.
Another way to do this is to use the information_schema schema and query the table_privileges table as:
The above query will show detailed information about user privileges on databases as well as tables.
To filter for a specific user, you can add the WHERE clause:
Conclusion
This short tutorial has discussed ways to fetch privileged information about the users in a PostgreSQL server.
