PostgreSQL is one of the most popular RDBMS. When you use a locally running PostgreSQL, you would just use a single superuser for convenience. When it comes to the production environment, you will want to set up users and permissions properly.
However, while there are many articles on how to insert and query data in PostgreSQL, its access control mechanism is not well explained. This article summarizes how it works as the start guide for PostgreSQL’s access control.
Roles, objects, and privileges
Like other access control mechanisms, PostgreSQL’s access control can be explained like “Role X is allowed to do Y on object Z”. Here, roles are users and groups, objects are databases, tables, etc., and privileges are actions like
INSERT. Conceptually, PostgreSQL’s ACL entry can be explained as a tuple of
(role, object, privilege).
Roles are basically users and groups. It acts like both; you can log in as a role, and a role can belong to another role. Each role has an attribute like
INHERIT that indicate whether you can log in as that role and whether the role inherits privileges from the roles it belongs to. You can add a role to a member of another role by using
GRANT ROLE ... command.
Objects in PostgreSQL are databases, tables, etc.. There is a tree structure in PostgreSQL objects. A PostgreSQL instance can have multiple databases. A database can have multiple schemas. A schema can have multiple tables.
Privileges are permissions defined over PostgreSQL objects. For example, there is a
SELECT privilege on tables, which is a permission to run
SELECT queries on them. Every kind of object has a different set of privileges.
With these elements, you can express access control configuration like “Role
readonly_user is allowed to run
accounts table”. You can see the valid combinations of object types and privileges in https://www.postgresql.org/docs/15/ddl-priv.html. You can add or remove the
(role, object, privilege) tuples with
Inheritance happens only between roles, not between objects. Since PostgreSQL objects have a tree structure, you might want to give
SELECT privilege at the database level, hoping that it gives the
SELECT privilege to all the tables in the database. PostgreSQL privilege doesn’t work in such a way.
Each PostgreSQL object has a special role called “Owner”. Certain actions like
ALTER TABLE can be done only by owners, and you cannot
GRANT such privileges to non-owners.
Sometimes you want to assign more than two owners for an object. Let’s say, you have two roles,
sre_user, and you want both users to be able run
ALTER TABLE, which only the owner can do. Since there can be only one owner per object, you cannot directly make both users to be the owner. At the same time,
ALTER TABLE is not something you can
GRANT to roles.
You can use role inheritance to solve this problem. Create
table_owner role and
GRANT table_owner TO app_user, sre_user, then transfer the owner role like
ALTER TABLE my_table OWNER TO table_owner. Now the table owner is
table_owner, but because
sre_user are the members of that role, they also have an inherited privilege to run
When an object is created, initially only the owner can access that object. For example, if you create a new table, only you can access that table. You will need to grant privileges to other roles separately. This is cumbersome since you need to do this every time you create a new table. PostgreSQL has a feature that allows you to configure the default privileges that are given when an object is newly created.
Let’s say you want to assign read-only privileges by default to a read-only role for all new tables under a database and schema. In PostgreSQL v14 or later, there is a predefined role
pg_read_all_data role that allows its members to read all data in all databases, but if you want to restrict it to a certain database, you cannot use this role. We are going to give read-only access to
ro_user role by using default privileges.
For the existing tables, we can run
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ro_user. This gives
SELECT privileges to the existing ones. However, we want to give this privilege to the tables created in the future. To do that,
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO ro_user. This changes the default privileges.
Note that these default privileges are applied only when the grantor creates a new table. For example, assume that we have two owner roles
ALTER DEFAULT PRIVILEGES ... and the other one doesn’t have a default privilege. In this case, the issued
ALTER DEFAULT PRIVILEGES is tied only to
table_owner1 and it’s applied only when
table_owner1 creates a new table. Even if
table_owner2 creates a new table, it won’t have default privileges defined by
Viewing the current ACLs
REVOKE commands, you can modify the ACLs explained above, but how can we see the current ACLs? If you use
psql CLI, there are CLI internal commands that show them:
However, you might not have easy access to
psql; you might have a way to run a read-only query via Redash, Retool, Grafana, etc., but not with
psql. Even in that case, you can run a
SELECT query on PostgreSQL internal tables to see the same information as
psql commands. The
psql commands also query those PostgreSQL internal tables under the hood, and show them nicely. You can find the
\dt (Show tables) implementation at https://github.com/postgres/postgres/blob/f4a9422c0c37ba638adbab853b8badb98a53ce04/src/bin/psql/describe.c#L3850 and there is a
SELECT statement there. Here are some examples of privilege queries.
|DATABASE||SELECT datname, pg_catalog.pg_get_userbyid(datdba), datacl FROM pg_database;|
|SCHEMA||SELECT nspname, pg_catalog.pg_get_userbyid(nspowner), nspacl FROM pg_namespace;|
|TABLE||SELECT relname, relacl FROM pg_class WHERE relacl IS NOT NULL AND relname NOT LIKE ‘pg_%’;|
|Default Privileges||SELECT pg_catalog.pg_get_userbyid(defaclrole), defaclobjtype, defaultacl FROM pg_default_acl;|
Each ACL entry is shown in an abbreviated form, and it looks like
arwdDxt. Each privilege is shortened to one character. For example
INSERT. You can see the mapping in the help document.
PostgreSQL access control mechanism is built based on roles, objects, and privileges. There is a way to automatically set the privileges for the new objects. In order to see the current configuration, you can use
psql CLI or directly query PostgreSQL internal tables.
Aviator: Automate your cumbersome merge processes
Aviator automates tedious developer workflows by managing git Pull Requests (PRs) and continuous integration test (CI) runs to help your team avoid broken builds, streamline cumbersome merge processes, manage cross-PR dependencies, and handle flaky tests while maintaining their security compliance.
There are 4 key components to Aviator:
- MergeQueue – an automated queue that manages the merging workflow for your GitHub repository to help protect important branches from broken builds. The Aviator bot uses GitHub Labels to identify Pull Requests (PRs) that are ready to be merged, validates CI checks, processes semantic conflicts, and merges the PRs automatically.
- ChangeSets – workflows to synchronize validating and merging multiple PRs within the same repository or multiple repositories. Useful when your team often sees groups of related PRs that need to be merged together, or otherwise treated as a single broader unit of change.
- TestDeck – a tool to automatically detect, take action on, and process results from flaky tests in your CI infrastructure.
- Stacked PRs CLI – a command line tool that helps developers manage cross-PR dependencies. This tool also automates syncing and merging of stacked PRs. Useful when your team wants to promote a culture of smaller, incremental PRs instead of large changes, or when your workflows involve keeping multiple, dependent PRs in sync.