Understanding Postgres Table Permissions

Postgres has an extensive user and permission system that provides robust access control mechanisms. Access control mechanisms are helpful for security - they minimize the risk of unauthorized access to data.

This article covers commands to check existing permissions, and explains roles, permission strings, and modifying permissions.

We start by answering the question - How can you find table privileges and interpret their meanings?

Checking Permissions with \dp and \z Commands

In Postgres, you can check table permissions using either the \dp or \z commands in psql (the two are equivalent).

To view the permissions of all tables starting with post, you can run:

\dp post*

This command will show the access privileges for tables whose names match the pattern post*. Here is an example output:

Access privileges
 Schema |      Name       |   Type   |            Access privileges             | Column privileges | Policies
--------+-----------------+----------+------------------------------------------+-------------------+----------
 public | posts           | table    |                                          |                   |
 public | posts_seq       | sequence |                                          |                   |
 public | posts_analytics | table    | admin_user=arwdDxt/admin_user+           |                   |
        |                 |          | reader_user=r/admin_user                 |                   |
(3 rows)

Below is a visual representation of the permission structure:

Let's break down what each part of this means.

Roles

In this example, there are two roles: admin_user and reader_user.

Roles have access permissions to database objects like tables, and can assign permissions to other roles.

For example, the admin_user may have created a table post_analytics, and granted access to the reader_user. However, there might be another table, let's say user_data, that should have more restricted access. The admin_user may not want to grant access to the reader_user for this table.

Reading the Permissions

The Access privileges column lists the permissions granted to each role. The format is grantee_role=privileges/grantor_role.

• admin_user=arwdDxt/admin_user: This indicates that the admin_user role has the following permissions:
  • a: INSERT (append)
  • r: SELECT (read)
  • w: UPDATE (write)
  • d: DELETE
  • D: TRUNCATE
  • x: REFERENCES
  • t: TRIGGER
• reader_user=r/admin_user: This indicates that the reader role has the SELECT (read) permission, granted by admin_user.

Changing Permissions

To modify the permissions of a table, you can use the GRANT and REVOKE commands. Here are some examples:

• Granting Permissions:

-- Grant SELECT and INSERT permissions to a user
GRANT SELECT, INSERT ON posts TO some_user;

• Revoking Permissions:

-- Revoke DELETE permission from a user
REVOKE DELETE ON posts FROM some_other_user;

• Granting All Permissions:

-- Grant all possible permissions to a user
GRANT ALL PRIVILEGES ON posts TO admin_user;

For more detailed information, you can reference the latest documentation on Postgres privileges here.