Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 21 · 6 min read

PostgreSQL Roles and Permissions (GRANT)

On this page
  1. The access requirements
  2. Roles: users and groups
  3. GRANT at the database, schema and table level
  4. Column-level privileges
  5. ALTER DEFAULT PRIVILEGES for future tables
  6. Testing permissions with SET ROLE
  7. Check yourself

The access requirements

Your company's salesdb database is moving to production. Today everyone connects as postgres, the superuser. That means any bug, leaked password or mistyped command can do anything at all. You've been asked to set up proper access:

  • A dashboard service that can read the sales schema and nothing else.
  • An order service that can read and write sales, but can't change the schema.
  • Analysts who can read everything except customers' email addresses.
  • Migrations that run as their own role and create tables nobody has to remember to grant access to.

This lesson builds that setup and explains each piece along the way.

Roles: users and groups

PostgreSQL has one concept: the role. A role with the LOGIN attribute can connect, so it behaves like a user. A role without LOGIN can't connect, but other roles can be members of it, so it behaves like a group. CREATE USER is just CREATE ROLE ... LOGIN.

The design that ages well:

  1. Create group roles (no LOGIN) for each kind of access.
  2. Grant privileges to the group roles only.
  3. Create login roles for people and services, and make them members of the right groups.

Onboarding or offboarding someone then changes one membership, not dozens of grants.

SQL
CREATE SCHEMA sales;
CREATE TABLE sales.customers (id int PRIMARY KEY, name text, email text);
CREATE TABLE sales.orders (id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id int, total numeric);

CREATE ROLE sales_read NOLOGIN;
CREATE ROLE sales_write NOLOGIN;
GRANT sales_read TO sales_write;     -- writers can read too

CREATE ROLE dashboard_svc LOGIN PASSWORD 'change-me' CONNECTION LIMIT 10;
CREATE ROLE order_svc     LOGIN PASSWORD 'change-me';
GRANT sales_read  TO dashboard_svc;
GRANT sales_write TO order_svc;

CONNECTION LIMIT stops one runaway service from using up every connection on the server. Use a real secret instead of change-me, and keep application roles away from SUPERUSER, CREATEDB and CREATEROLE.

GRANT at the database, schema and table level

Reaching a table takes three separate permissions, and missing any one of them blocks access:

LayerPrivilegeWithout it
DatabaseCONNECT"permission denied for database"
SchemaUSAGE"permission denied for schema", even with table grants
TableSELECT, INSERT, UPDATE, DELETE"permission denied for table"
SQL
GRANT CONNECT ON DATABASE salesdb TO sales_read;
GRANT USAGE ON SCHEMA sales TO sales_read;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO sales_read;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA sales TO sales_write;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA sales TO sales_write;

The sequence grant matters for serial columns. Inserting into one calls nextval, which needs USAGE on its sequence. Identity columns (Day 20) don't need it.

Lock down the defaults too. Every role is automatically a member of a pseudo-role called PUBLIC, and by default PUBLIC can connect to every database. Remove that so access is opt-in:

Code
REVOKE ALL ON DATABASE salesdb FROM PUBLIC;

Column-level privileges

Privileges can be granted per column. Analysts get every customer column except email:

SQL
CREATE ROLE analyst NOLOGIN;
GRANT CONNECT ON DATABASE salesdb TO analyst;
GRANT USAGE ON SCHEMA sales TO analyst;
GRANT SELECT ON sales.orders TO analyst;
GRANT SELECT (id, name) ON sales.customers TO analyst;

An analyst who runs SELECT * FROM sales.customers gets "permission denied", because * includes email. They have to list the columns they're allowed to see. For hiding rows rather than columns, such as letting each tenant see only its own data, use row-level security (Day 29).

ALTER DEFAULT PRIVILEGES for future tables

GRANT ... ON ALL TABLES only covers tables that exist right now. Next week a migration adds sales.refunds, and the dashboard can't read it. Default privileges fix this. They say "whenever this role creates a table here, grant this":

SQL
CREATE ROLE migrator LOGIN PASSWORD 'change-me';
GRANT USAGE, CREATE ON SCHEMA sales TO migrator;

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales
  GRANT SELECT ON TABLES TO sales_read;
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA sales
  GRANT INSERT, UPDATE, DELETE ON TABLES TO sales_write;

The FOR ROLE migrator part is the most common source of confusion. Default privileges apply to objects created by that role. Set them up for your own admin role while migrations run as migrator, and they never fire. In psql, \ddp shows what's configured.

Testing permissions with SET ROLE

Don't assume the setup works. Become each role and try. SET ROLE switches your session's privileges to another role you're a member of, which is how you test as a superuser:

SQL
SET ROLE dashboard_svc;
SELECT count(*) FROM sales.orders;                       -- works
INSERT INTO sales.orders (customer_id, total) VALUES (1, 10);
-- ERROR: permission denied for table orders
RESET ROLE;

A few more tools worth knowing:

  • Predefined roles. PostgreSQL ships roles like pg_read_all_data (read every table) and pg_monitor (read monitoring views). They're handy for backup tools and monitoring agents.
  • NOINHERIT for break-glass access. Membership normally gives a role's privileges automatically. Grant an admin role with GRANT db_admin TO bob WITH INHERIT FALSE (PostgreSQL 16+), and Bob must run SET ROLE db_admin on purpose before using it.
  • Audits. information_schema.role_table_grants lists table grants. Review them now and then for privileges that crept in.

Check yourself

The role also needs GRANT USAGE ON SCHEMA billing. Schema usage is a separate privilege that controls whether a role can even look up objects in the schema, so table grants have no effect until it's granted. Grant both to a group role, then add the service's login role to that group.

The default privileges were probably created without FOR ROLE deployer, so they apply to a different creator role. Recreate them with FOR ROLE deployer, and grant on the tables that are already missing it.

Very little: revoke their membership in the group roles, or drop their login role after reassigning anything they own. Because every privilege was granted to group roles rather than to individuals, no table-level grants need to change, which is the main reason to design permissions around groups.

Use SET ROLE reporting_ro; in a session as a member of that role, try the queries, then RESET ROLE;. Functions like has_table_privilege('reporting_ro', 'billing.invoices', 'SELECT') also answer specific questions directly.

The owner can do anything to its tables, including DROP, ALTER and bypassing row-level security by default. If the application is compromised, so is the schema. Connect as a role that has only the SELECT, INSERT, UPDATE and DELETE it needs, and run migrations as a separate owner role.