Postgres Permissions Concepts

At Corso, we are big fans of PostgreSQL. We are hardly alone in this, as its various advantages are well understood. If you need a general-use server-based relational database and don't have requirements that push you towards any particular technology, most experienced developers and administrators will tell you it's hard to go wrong with Postgres.

A common complaint by Postgres newcomers is that permissions are just too complicated. It's very common to add a user and then struggle with adding all the different permissions needed before that user can actually access the desired data.

It's true that there is a lot of complexity, but that complexity is the result of flexibility. The more flexible a piece of software is, the more complicated it tends to be - and Postgres is no exception to this rule of thumb.

Foundational Concepts

First, you should understand that Postgres doesn't really have distinct "users" and "groups" like other systems. Instead, you have the single concept of a role. Since roles can be members of other roles, that membership concept can allow some roles to act as permissions groups. Roles that can be logged into are typically referred to as a user, and a role that can't be logged into is often referred to as a group.

The concept of what a role can do on a server is broadly discussed as "permissions", but the Postgres-specific term for these specific permissions is a privilege. The two words are often used interchangeably, but when used in a SQL statement the term privilege is the only one you will see.

Each database object has an owner, which is the role that has full permissions on that object. Since roles can be members of other roles, other (non-owner) roles can be set up to inherit ownership of a database object and act on it like an owner.

Another term to be aware of is database object. Each entity in the database is a database object including tables, views, triggers, functions, etc. Privileges determine what a role can do with these different database objects.

You should also be aware that Postgres has a "hierarchy" of sorts with its objects. A table can't belong to database for example, it can only belong to a schema. There are various objects at each level in the hierarchy, but some of the most common are:

1. Server - While technically not an object, it's worth considering that at the top level is the server. Especially with abstractions such as cloud-based Postgres databases and multiple replicated instances of a server, this concept can get complicated - but typically when you connect to Postgres you are connecting to the "server" being discussed here. Your local development server might be available to you at localhost:5432 for example.
2. Database - Each server can have an arbitrary number of databases. It is quite typical for a server to only have one database - especially with the ease and low cost of spinning up a new cloud-based Postgres instance with a single database as needed. Each database is typically aware of itself and only itself, no matter how many databases might exist on the server. If you want to query data from one database together with data from a second database for example, you will need to install additional extensions before Postgres can do so.
3. Schema - In each database you can have an arbitrary number of schemas. These schemas are a logical grouping of tables and other objects, but they can be queried together via a join. Imagine you have an accounting system that has a builtin timecard system for employees. The financial record system and the timekeeping system might be distinct enough that it makes sense to keep the various tables for each in different schemas, for example. Among other benefits, this allows us to easily grant permissions to only one schema or the other, so as to keep data isolated and grant access only to the users and systems that need it.
4. Table (and other objects) - You must also set permissions on a table-by-table basis. Note that on the basis of permissions, Postgres considers views as a type of table. Permissions can be granted for functions, tables, sequences, and more.

Roles exist at the server level, meaning that any role can be granted permissions to any combination of databases, schemas, tables, etc. on the server. The permissions are fine-grained though, allowing us to define access database by database, schema by schema, and table by table.

Granting Access

One of the most common frustrations for anyone new to Postgres is that access has to be granted all the way down the hierarchy before the permission is usable.

A role must be able to log in (server level), it must have usage rights on the schema, and it must have the specific privilege required on a particular object. If you grant read access to a table but the user can't log in or doesn't have usage rights for the schema, that table access is worthless.

The terminology is distinct for each of these three "layers" as well, adding to the confusion. If you want to query some data from a table, you must have granted access for each of the following:

• CONNECT to the database
• USAGE on the schema
• SELECT on the table

Don't forget to validate that your computer, server, or cluster can connect to the server! Your firewall as well as your pg_hba.conf might need to be updated to allow connections from the ip address you are attempting to connect from.

Object Privileges

Privileges are set at the object level. Privileges are most commonly thought of with tables, but they can be set on functions, sequences, types, etc. in addition to the schema and the database itself.

For all the complexity around permissions, there are only 14 privileges that can be set in Postgres. Which of these privileges can be set depends on the object type; you can set USAGE on a schema, but SELECT is only available on a table, for example. The privileges available are:

SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE, SET, ALTER SYSTEM

Full detail on what these different privileges do is available from the excellent Postgres documentation on privileges. As a high-level overview though:

Privilege	Explanation
SELECT	Query data
INSERT	Insert a new row of data
UPDATE	Update existing data
DELETE	Delete row
TRUNCATE	Truncate a table ("erase" the table and remove all rows)
REFERENCES	Create foreign key constraints
TRIGGER	Create a trigger on a table
CREATE	Create objects including schemas, tables, etc.
CONNECT	Connect to a database, required before accesing any data
TEMPORARY	Create temporary tables
EXECUTE	Execute functions
USAGE	Use a particular object - including types, schemas, etc.
SET	Within the current session, set a server parameter
ALTER SYSTEM	Set a new server parameter globally

Default Privileges

The concept of default privileges allows new objects in a schema to automatically have privileges granted to pre-existing roles.

For example: suppose we have a "customer_data_writer" role, that has INSERT and UPDATE privileges on every table in a schema. Since it's a pain to explicitly set the privileges each time we create a new table, we can set default privileges so that new objects are created with those privileges already set.

If you created a new table and are surprised to see existing users unable to access the data on those tables, check your default privileges.

Administration GUIs

I'll advocate for keeping a good GUI (Graphical User Interface) database administration tool in your tool belt. It's critical to be able to administer your server via SQL commands for various reasons, but staring at a prompt like this isn't great for discoverability:

postgres=# 

There's quite a few different management GUI's out there but I recommend the open source, multiplatform pgAdmin. It won't win any awards for beauty, and as a web app it won't be blistering fast when you need to scroll through tens of thousands of records, but as an administration tool its comprehensiveness is hard to beat.

The primary argument for getting to know pgAdmin well is that is organizes everything about your server into a logical, Postgres-native hierarchy, and lets you use your mouse to do pretty much anything you can do with sql commands. This means that you can see all the different databases on your server, which schemas belong to those databases, and where different object types belong in that hierarchy.

There's very little that in the way of administration that can't be done with pgAdmin. Virtually every single object on the server can be right-clicked and managed, including privileges that can be set in the "security" tab. Best of all, before making any changes you can always see the exact SQL statements that will be run.

A Conceptual Walkthrough

Since this post attempts to be conceptual, we won't give a step-by-step walkthrough of all the commands to create roles and grant permissions for a given scenario. However, we give the general steps here with links for additional resources. If you are looking for a more detailed tutorial-like approach, Supabase has an excellent walkthrough.

The general steps to create and set up a user to access data might be as follows:

1. First create your users, with CREATE ROLE, as detailed here.
2. Ensure that they can connect to a database with GRANT CONNECT, described here.
3. GRANT USAGE on a schema as needed (using the same documentation listed in #2 above)
4. Consider whether you will need to grant CREATE privileges to the user. Typically your application will use a role that can read and write data, but can't do other operations such as TRUNCATE, DELETE, CREATE, etc. This helps limit the damage that these roles can do.
5. Next you will want to start giving specific object-level privileges as necessary. GRANT SELECT ON TABLE PRODUCT will allow you a user to select from just the "product" table. If you want to grant access to all tables in the schema, this can be done with GRANT SELECT ON ALL TABLES IN SCHEMA. If you want to grant all permissions (rather than just SELECT), that can be done with GRANT ALL.
6. Set any default privileges needed, using ALTER DEFAULT PRIVILEGES.
7. Connect as the new user, and verify that you can perform the operations that you expect.

Role Usage

Roles that serve as groups can be a very useful tool for reusing permissions, making it so that you don't have to think about the full set of permission and all the necessary grants needed when different users share the necessary privileges.

Suppose you have various applications and services accessing your database, as is relatively common. You probably want to split that access up so that each application or service has their own user, but they often all need to read or write from the same tables in the schema. In that case you can create one or two roles that represent the permissions you want to grant, and then use those roles as necessary.

Postgres has various built-in roles that may serve your needs, especially pg_read_all_data and pg_write_all_data. You can constrain the access via other objects, but a user that can CONNECT to a database and has USAGE granted to a schema will be able to read or write data depending on which privileges have been given.

Column and Row Access

Additional ways of constraining permissions are available as well. We won't go over them in detail here, but you can constrain which columns on a table can be accessed by a role, and you can even control which rows can be accessed.

It's often sufficient to allow your application logic to control which columns are available by only selecting the appropriate columns. Row-level security can usually be enforced with your where clause, often in conjunction with authorization data made available to your application such as claims in a JWT, etc. When you need an extra layer of protection though, having the database enforce access as granularly as you like can be helpful.

You can read more about column-level and row-level security here.

Summary

Permissions in Postgres are highly flexible. This flexibility can make it less straightforward to understand the full power and control available.

• Permissions are based on granting privileges to various roles on database objects.
• There are various objects in Postgres, like a database, a schema, a table, etc. The permissions available to each object is different, and the objects roughly form a hierarchy of sorts.
• Roles can be thought of as users or groups that permissions can be assigned to.
• Privileges are the various ways that a role can interact with an object. SELECT, INSERT, CREATE, etc. Permissions can be inherited from other users or groups that a role belongs to.
• Don't forget about default privileges in your process
• Don't be afraid to have a good GUI tool as an option for administering your server. It can make things like reasoning about objects and privileges a lot easier.

While far from comprehensive, hopefully this guide can help you wrap your head around some of the basic concepts of administering permissions in Postgres, and give you a foundation to research specific needs further.

Tags

• Postgres