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 SELECT or 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 LOGIN and 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 SELECT on 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 GRANT and REVOKE commands.

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.

Object owners

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, app_user and 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 app_user and sre_user are the members of that role, they also have an inherited privilege to run ALTER TABLE.

There can be only one owner, but other roles can inherit from the owner.

Default privileges

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 table_owner1 and table_owner2. table_owner1 issues 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 table_owner1.

Viewing the current ACLs

With GRANT and 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:

From https://www.postgresql.org/docs/15/ddl-priv.html

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 psql‘s \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.

| Object type | Query |
| 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 r in arwdDxt is SELECT, and w is INSERT. You can see the mapping in the help document.

Summary

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:

1. 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.
2. 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.
3. TestDeck – a tool to automatically detect, take action on, and process results from flaky tests in your CI infrastructure.
4. 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.

Try it for free.

The post PostgreSQL roles and privileges explained first appeared on Aviator Blog.

The post PostgreSQL roles and privileges explained appeared first on Aviator Blog.