PostgreSQL roles and privileges explained
Tags: postgres
Published: 2023-06-07 19:04:59

---
title: PostgreSQL roles and privileges explained
published: true
date: 2023-05-31 14:46:54 UTC
tags: PostgreSQL
canonical_url: https://www.aviator.co/blog/postgresql-roles-and-privileges-explained/?utm_source=rss&utm_medium=rss&utm_campaign=postgresql-roles-and-privileges-explained
---

![](https://www.aviator.co/blog/wp-content/uploads/2023/05/postgreSQL-privileges-1024x574.jpg)

PostgreSQL is one of the most popular [RDBMS](https://www.oracle.com/database/what-is-a-relational-database/). 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](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`.

![](https://www.aviator.co/blog/wp-content/uploads/2023/05/Screenshot-2023-05-31-at-10.27.45-AM-1024x495.png)
_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:

![](https://www.aviator.co/blog/wp-content/uploads/2023/05/Screenshot-2023-05-31-at-10.31.55-AM-1024x799.png)
_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](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.

![](blob:https://www.aviator.co/0fca0488-be72-4b91-b6e9-15ad94aa4c0c)

### 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](https://www.aviator.co/): Automate your cumbersome merge processes

[![](https://blog.aviator.co/wp-content/uploads/2022/08/blog-cta-1024x727.png)](https://aviator.co/)

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.](http://aviator.co/)

The post [PostgreSQL roles and privileges explained](https://www.aviator.co/blog/postgresql-roles-and-privileges-explained/) first appeared on [Aviator Blog](https://www.aviator.co/blog).

The post [PostgreSQL roles and privileges explained](https://www.aviator.co/blog/postgresql-roles-and-privileges-explained/) appeared first on [Aviator Blog](https://www.aviator.co/blog).