Postgres Roles and Privileges

11 Apr 2024 · 43 minute read

By Raminder Singh, Engineering

Controlling access to data in Postgres is paramount for data security. Postgres provides a robust and flexible permissions model for users to manage access to their data. The permissions model is based on the familiar object, privilege, role model but has subtleties which must be understood by a database administrator to create airtight access.

Basic Concepts

Database Object - Any entity created in the database. Tables, foreign tables, views, materialized views, types, domains, operators, functions, triggers etc. are database objects.

Privilege - Controls what operation is allowed to be run on a database object. For example, the select privilege on a table controls the ability to read data from the table.

Role - A user or a group. A user is someone who can login to the database. A group is a collection of users to make it easier to manage privileges for users. Unlike a user, a group can't login to the database.

Owner - Every database object has an owner. The owner has complete control over the object. They can modify or delete the object or grant privileges to other users and groups.

Key Concepts

Creating and Managing Roles

Create users with login capability:

create role junior_dev login password 'password';
create role senior_dev login password 'password';

Create groups (roles without login):

create role developers nologin;

Privileges and Grants

Grant privileges on objects:

grant create on schema public to junior_dev;
grant select on table public.apps to senior_dev;

Grant privileges with the option to grant to others:

grant select on public.apps to postgres with grant option;

Revoke privileges:

revoke select on public.apps from senior_dev;

Role Membership

Add users to groups:

grant developers to junior_dev;
grant developers to senior_dev;

Users can impersonate groups they are members of:

set role developers;
reset role;

Revoke group membership:

revoke developers from senior_dev;

Grant Options

Control group membership abilities with options:

• set - ability to impersonate the group
• inherit - ability to inherit permissions from the group
• admin - ability to add/remove other users from the group

grant developers to junior_dev with admin option;

Default Access Privileges

Set default privileges for future objects:

alter default privileges in schema public grant select on tables to senior_dev;

View default privileges:

\ddp public

Role Attributes

Common role attributes:

• login - controls the role's ability to login
• superuser - controls whether the role is a superuser
• createdb - controls whether the role can create databases
• createrole - controls whether the role can create other roles
• replication - controls whether the role can initiate replication
• bypassrls - controls whether the role can bypass row level security
• connection limit - limits maximum number of connections
• inherit - controls whether the role can inherit permissions from other roles

Special Roles

Superuser - A role with the superuser attribute set. Very powerful and bypasses all privilege checks except authentication. Should be used with care. Only superusers can create other superuser roles.

Public - A group role which every other role is automatically a part of. Used to provide common privileges. Can't be deleted, but its privileges can be revoked. Provides default privileges:

• connect - ability to connect to the database
• temporary - ability to create temporary tables
• execute - ability to execute functions
• usage - ability to use objects like domains, languages or types

Permission Inheritance

A role's privileges are the union of:

1. Privileges granted to the role directly
2. Privileges inherited from roles this role is an explicit member of
3. Privileges inherited from the public role (implicit membership)

Summary

• Every database object has an owner
• Operations on database objects are controlled by privileges
• Owners can grant privileges on owned objects to other roles
• Roles can be either users or groups
• Roles can inherit permissions from roles they are members of
• public role is a role which every other role is implicitly a member of
• superuser roles bypass all privilege checks and should be used with care
• grant command only grants privileges on existing objects
• Default privileges control privileges to be granted to objects created in the future