Securing your PostgreSQL DB with Roles & Privileges

PostgreSQL authorization has always been a black box for me. I’ve never “needed” different roles to manage who/what has access to my DB. I’ve always relied on postgres superuser for all my web applications, and it has worked so far!

I recently needed to avoid postgres superuser. To become SOC2 compliant, we needed to remove global access and fine-tune who has access to what schemas and tables. This is where my journey starts in the PostgreSQL authorization realm.

Let’s dive in.

Table of contents

Authorization concepts

Let’s start laying the foundation with the different concepts we will use.

Users

There are no more users! Starting with PostgreSQL 8.1, the concepts of users and groups were superseded by roles.

Roles

Roles are the main concept when managing access permissions. Each role has different privileges. Privileges can be USAGE, SELECT, INSERT, UPDATE, etc., and enables the role access to granted objects.

It’s possible to grant a role to be a member of another role as we’ll see in the GRANT command section. The granted role inherits all the privileges of the said role.

Example of role membership:

• Creating a read_access role.
• Creating a role metabase and granting membership to the read_access role. The metabase role inherits all the privileges from read_access.

Official docs.

Privileges

The role that creates the object is the owner of the said object. Only the owner and a superuser can do any operation on the object. We must grant privileges to allow other roles to use the object.

The most common DB objects are databases, schemas, tables, views, tablespaces, functions, operators, casts, sequences, and extensions.

The right to modify or destroy an object is only the owner’s privilege. For example, there is no way to grant a privilege to alter a table; only the owner can modify it.

Granting privileges indicates to PostgreSQL what actions a role can execute on what DB objects. We can specify that a role can only read (SELECT) specific tables, or a role can only create (INSERT) rows in one table but not other tables, etc.

The privileges that we can grant depend on the object itself. You can see the complete list of privileges and objects.

Official docs.

Authentication and authorization commands

These are the commands I used to set up the different roles I needed. There are more variations, but this sufficed for my use case. I’ll link each command’s official documentation.

CREATE ROLE

When you create a role, it can have different attributes. Attributes allow the role to:

• Create a DB
• Connect to the DB
• Be a superuser
• Create other roles
• Set a password
• Limit the number of connections

You can specify the attributes like this:

CREATE ROLE name [ [ WITH ] attribute [ attribute_args ] ]

For example, to specify that a role backend_app can LOGIN and have a PASSWORD “strong-password”:

-- LOGIN allows the role connect to the DB.
CREATE ROLE backend_app WITH LOGIN PASSWORD 'strong-password';

Official docs.

CREATE USER

As we mentioned earlier, there are no more users in PostgreSQL. The command exists as a wrapper of the CREATE ROLE command.

-- CREATE USER includes LOGIN attribute by default.
CREATE USER backend_app WITH PASSWORD 'strong-password';
-- is equivalent to:
CREATE ROLE backend_app WITH LOGIN PASSWORD 'strong-password';

Official docs.

GRANT

The GRANT command has two purposes:

• Give DB objects access to roles through privileges.
• Give a role membership to other roles.

It’s best to show this GRANT command with the following examples; the documentation is quite extensive.

Granting read privilege to existing tables to read_access role

GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_access;

I stress existing tables because this doesn’t grant read access to future tables created. If you want to grant access to future tables, you need to alter the default privileges of the read_access role and specify the tables’ creator role.

Granting all privileges to role ops to the operations schema and its tables

-- Privileges on schema: be able to create more tables.
GRANT ALL PRIVILEGES ON SCHEMA operations TO ops;
-- Privileges on tables: be able to create rows.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA operations TO ops;
-- Privileges on sequences: using serial primary keys.
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA operations TO ops;
-- Privileges on routines: function, aggregate function, window function, or procedure.
GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA operations TO ops;

Note on being able to ALTER a table (add, remove, or change columns). You must own the table to use ALTER TABLE. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the table’s schema. This blog post doesn’t cover how to change schema, table, or sequences ownership. Official docs.

Giving metabase role membership to read_access

-- Create metabase role.
CREATE ROLE metabase WITH LOGIN PASSWORD 'strong-password';
-- Grant membership.
GRANT read_access TO metabase;

Official docs.

ALTER DEFAULT PRIVILEGES

This command allows you to set privileges that will be applied to objects created in the future.

This command alters the default privileges when a new object is created. Every object has an owner, so you must specify the owner’s role as part of the command.

Giving read tables permission to read_access role to future tables created by role postgres

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT SELECT ON TABLES TO read_access;

This means that the metabase user (created in the previous step) can read tables created in the future by the postgres role. If a role other than postgres creates tables, read_access won’t have access unless default privileges are modified for the said role.

Official docs.

REASSIGN OWNED

This command is a shortcut to change all DB objects ownership a specific role owns. I haven’t tested it, but knowing that this exists is useful.

Changing all objects created by postgres role to other_db_role role

REASSIGN OWNED BY postgres TO other_db_role;

Be sure to read the NOTES section of the official docs, as this command has some caveats.

Official docs.

Scoping the roles you need

Before creating different roles, we need to determine what roles we need and what each role will have access to.

Questions that can help you figure out what roles you need for your use case:

• What are the different services that use the DB?
  • Do they need read and write access to the DB?
  • Only read access?
• What are the various teams in your company that need access?
  • Do all they need to read and write, or just read access?
  • To what schemas and tables do they need access?
• What other roles would make your app more secure?

Real-world example

Current state

We’ve been using the postgres role in the past year. We use this single role/password combination in all our different systems connecting to the database.

Desired state

We want to become SOC2 compliant. As part of the spec, we must minimize access following the least privilege access control. We must define different roles according to their needs and remove all access to the postgres role. We’ll ensure access removal to postgres by rotating its password when all the systems use specific roles tailored to their needs.

• Engineering team: read and write access to all schemas and tables.
• DB Migrations: backend app that creates schemas, create and update tables. DB migrations run when releasing the application (new deployment).
• Metabase: read access to all schemas and tables.

Let’s do it!

Let’s start with db_migrations role.

CREATE ROLE db_migrations WITH LOGIN PASSWORD 'strong-password';
-- Reassign all db objects owned by `postgres`.
REASSIGN OWNED BY postgres TO db_migrations;
-- Revoke creating tables in public schema for all roles.
REVOKE CREATE ON SCHEMA public FROM public;

The object’s owner (db_migrations) has complete control over them, and it doesn’t need extra privileges to access them. Current applications that use postgres role won’t be affected because postgres is a SUPERUSER.

Following up with the engineering role to read and write access to all schemas and tables

Let’s create two roles: read_access and write_access. These roles will act on existing and future DB objects. This allows us to create multiple roles <> passwords per application granting membership to either read_access, write_access, or both.

Read access:

-- Read access role without login attribute.
CREATE ROLE read_access;
-- Alter default privileges (future tables created by db_migrations role).
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrations GRANT USAGE ON SCHEMAS TO read_access;
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrations GRANT SELECT ON TABLES TO read_access;

-- Grant privileges to existing DB objects.
-- This generates all the required statements, copy/paste them in your sql console and execute them.
SELECT FORMAT('GRANT USAGE ON SCHEMA %I TO %I;', schema_name, 'read_access')
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

SELECT FORMAT('GRANT SELECT ON ALL TABLES IN SCHEMA %I TO %I;', schema_name, 'read_access')
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

Write access:

-- Write access role without login attribute.
CREATE ROLE write_access;
-- Alter default privileges (future tables created by db_migrations role).
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrations GRANT USAGE ON SCHEMAS TO write_access;
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrations GRANT ALL PRIVILEGES ON TABLES TO write_access;
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrations GRANT ALL PRIVILEGES ON SEQUENCES TO write_access;
ALTER DEFAULT PRIVILEGES FOR ROLE db_migrations GRANT ALL PRIVILEGES ON ROUTINES TO write_access;

-- Grant privileges to existing DB objects.
-- This generates all the required statements, copy/paste them in your sql console and execute them.
SELECT FORMAT('GRANT USAGE ON SCHEMA %I TO %I;', schema_name, 'write_access')
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

SELECT FORMAT('GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I TO %I;', schema_name, 'write_access')
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

SELECT FORMAT('GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I TO %I;', schema_name, 'write_access')
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

SELECT FORMAT('GRANT ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA %I TO %I;', schema_name, 'write_access')
FROM information_schema.schemata
WHERE schema_name NOT IN ('pg_toast', 'pg_catalog', 'information_schema');

This write_access role doesn’t allow the creation of new schemas. The only role that can create new schemas is db_migrations by having database ownership. If you want to allow write_access role to create schemas change GRANT USAGE ON SCHEMAS to GRANT ALL PRIVILEGES ON SCHEMAS.

Let’s grant role membership to the engineering role to these newly created roles: read_access and write_access.

-- Engineering role with login attribute and password.
CREATE ROLE engineering WITH LOGIN PASSWORD 'strong-password';
-- Grant read and write access membership to `engineering`.
GRANT read_access, write_access TO engineering;

The metabase role. Read access.

As we’ve created a read_access role, it simplified creating a new role and giving it read access.

-- Metabase role with login attribute and password.
CREATE ROLE metabase WITH LOGIN PASSWORD 'strong-password';
-- Grant read access membership to `metabase`.
GRANT read_access TO metabase;

Rotate the postgres role password

Now that our different applications are using the new roles/password, we can rotate the postgres role password.

ALTER ROLE postgres WITH PASSWORD 'super-strong-password';

ALTER ROLE official docs.