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 theadmin_user
role has the following permissions:a
: INSERT (append)r
: SELECT (read)w
: UPDATE (write)d
: DELETED
: TRUNCATEx
: REFERENCESt
: TRIGGER
reader_user=r/admin_user
: This indicates that thereader
role has theSELECT
(read) permission, granted byadmin_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.