Module 8 · Security

Roles, Privileges, and Default Permissions

Lesson 22 of 30 10 min read Updated

PostgreSQL authorization is built from roles, object ownership, explicit privileges, and default privilege templates. The outcome of this lesson is practical: you should be able to create login roles for applications, group roles for capabilities, grants for existing objects, and default privileges for future objects without accidentally making a schema writable or a table readable by the wrong identity.

This chapter connects directly to the Security section of the PostgreSQL course. Authentication answers who connected. Authorization answers what that connected identity, plus any roles it can use, may do inside the database.

How PostgreSQL Decides Access

A PostgreSQL role is both a user-like identity and a group-like container. A role with LOGIN can start a session. A role without LOGIN is commonly used as a capability bundle, such as app_read, app_write, or reporting_read. Role membership lets one role inherit or activate the privileges of another.

Every database object has an owner. The owner can usually do anything meaningful to that object and can grant privileges on it to other roles. Ordinary privileges are object-specific: CONNECT applies to databases, USAGE and CREATE apply to schemas, SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, and TRIGGER apply to tables, and USAGE, SELECT, and UPDATE apply to sequences. A role needs the right combination. For example, selecting from sales.orders requires visibility into the schema through USAGE and SELECT on the table.

When a statement runs, PostgreSQL checks the current role, inherited memberships, object ownership, and any explicit grants. If the role lacks a required privilege, the executor stops before returning or changing protected data. This is why authorization belongs in database design, not only in application code.

Syntax Anatomy

CREATE ROLE defines an identity or capability group. GRANT role_a TO role_b gives membership. GRANT SELECT ON TABLE x TO role_y gives object access. REVOKE removes a grant. ALTER DEFAULT PRIVILEGES changes what privileges will be applied to objects created later by a specific creator role. That last detail is critical: default privileges are not retroactive, and they are scoped by the role that creates future objects.

Separate ownership from runtime access. A migration or owner role should create and own objects. Application login roles should normally receive only membership in non-login capability roles. This keeps passwords, object ownership, and day-to-day application permissions from collapsing into one powerful identity.

Example 1: Read-Only Access to One Schema

This example creates an owner, a read capability role, a login role, and one table. The app can read rows but cannot insert them.

CREATE ROLE shop_owner NOLOGIN;
CREATE ROLE shop_read NOLOGIN;
CREATE ROLE shop_app LOGIN PASSWORD 'replace-in-real-systems';

CREATE SCHEMA shop AUTHORIZATION shop_owner;
CREATE TABLE shop.products (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  price_cents integer NOT NULL CHECK (price_cents >= 0)
);
ALTER TABLE shop.products OWNER TO shop_owner;

INSERT INTO shop.products (name, price_cents)
VALUES ('Notebook', 700), ('Pen', 150);

GRANT USAGE ON SCHEMA shop TO shop_read;
GRANT SELECT ON TABLE shop.products TO shop_read;
GRANT shop_read TO shop_app;

With SET ROLE shop_app, SELECT name FROM shop.products ORDER BY id returns Notebook and Pen. An INSERT fails with a permission error because shop_read has no table write privilege. The schema grant alone is not enough; it lets the role resolve object names inside shop, but the table grant controls row access.

Example 2: Write Access Requires Tables and Sequences

Adding write access is more than granting INSERT. If a table uses an identity or serial-style sequence, the inserting role may also need sequence privileges, depending on how the value is generated and referenced.

CREATE ROLE shop_write NOLOGIN;
GRANT USAGE ON SCHEMA shop TO shop_write;
GRANT SELECT, INSERT, UPDATE ON TABLE shop.products TO shop_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA shop TO shop_write;
GRANT shop_write TO shop_app;

SET ROLE shop_app;
INSERT INTO shop.products (name, price_cents)
VALUES ('Pencil', 100)
RETURNING id, name;

The deterministic behavior is that the insert succeeds and returns a generated id with Pencil. The exact numeric id depends on previous inserts. If the table grant exists but the needed sequence privilege is missing, the symptom is usually a permission error mentioning the sequence rather than the table. That message is a clue that the table privilege check passed and the generated value path failed later.

Example 3: Default Privileges for Future Tables

Grants on existing objects do not cover future objects. Default privileges solve that by recording a template for objects created later by a particular creator role.

SET ROLE shop_owner;
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA shop
GRANT SELECT ON TABLES TO shop_read;

CREATE TABLE shop.categories (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  label text NOT NULL UNIQUE
);

RESET ROLE;
SET ROLE shop_app;
SELECT label FROM shop.categories;

The final SELECT is allowed because shop_owner created shop.categories after the default privilege rule existed. If a different migration role creates a future table, this default rule does not apply. If shop.categories had existed before the ALTER DEFAULT PRIVILEGES statement, it would still need an ordinary GRANT SELECT ON TABLE shop.categories.

Design Choices and Trade-Offs

Small roles are easier to reason about but create more grants to manage. Broad roles reduce administration but increase blast radius when credentials are leaked or a query path is compromised. A common compromise is to create capability roles by workload: read, write, migration, reporting, and maintenance. Login roles then receive one or more capability roles.

Using inheritance makes permissions available immediately through membership. Using NOINHERIT requires SET ROLE before the member can use another role’s privileges, which can be useful for administrative workflows that should be explicit. For application roles, inherited capability roles are simpler, provided the membership is narrow.

Schema permissions are often misunderstood. Denying table access while allowing schema USAGE can be valid; the role can resolve names but cannot read or write protected relations. Granting schema CREATE is much stronger because it lets a role create objects in that namespace. Avoid giving application roles CREATE in shared schemas unless that is the intended product behavior.

Failure Modes and Troubleshooting

Symptom: permission denied for schema shop. Cause: the role has a table grant but lacks USAGE on the schema. Diagnose: inspect schema privileges with catalog queries or has_schema_privilege. Correction: grant USAGE on the schema to the capability role, not directly to every application login.

Symptom: permission denied for sequence products_id_seq during insert. Cause: table insert privilege was granted, but the generated identifier path also touches a sequence. Diagnose: list sequences in the schema and test has_sequence_privilege for the application role. Correction: grant the required sequence privileges to the write capability role.

Symptom: a newly migrated table is invisible to the reporting user. Cause: default privileges were configured for the wrong creator role, or after the table had already been created. Diagnose: check the table owner and compare it with the role named in ALTER DEFAULT PRIVILEGES FOR ROLE. Correction: apply an ordinary grant to the existing table and fix the default privilege rule for future migrations.

SELECT has_schema_privilege('shop_app', 'shop', 'USAGE') AS can_use_shop_schema;
SELECT has_table_privilege('shop_app', 'shop.products', 'SELECT') AS can_select_products;
SELECT has_table_privilege('shop_app', 'shop.products', 'INSERT') AS can_insert_products;

SELECT n.nspname AS schema_name,
       c.relname AS object_name,
       c.relkind AS object_kind,
       pg_get_userbyid(c.relowner) AS owner_name
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'shop'
ORDER BY c.relkind, c.relname;

The first three queries return booleans that isolate whether the missing privilege is schema, read, or write access. The catalog query shows object owners, which is essential when default privileges appear not to work.

Security and Reliability Implications

Least privilege reduces damage from application defects. A read-only connection string cannot update prices even if an injection flaw reaches the database. A write role without schema CREATE cannot add functions or shadow objects in a shared namespace. A migration role separated from the application role prevents normal request traffic from owning the schema.

Privileges also affect reliability. Emergency revocation is cleaner when applications use capability roles: revoke membership or one object grant, then verify with has_*_privilege functions. Audits are clearer when role names describe workload purpose instead of people or hosts. Performance impact from privilege checks is usually not the limiting factor; operational complexity and accidental over-granting are the bigger costs.

Hands-On Lab

Prerequisites: a disposable PostgreSQL database where you can create roles, schemas, and tables. Do not run this lab in a shared production database because it creates login roles and drops them during cleanup.

  1. Connect as an administrative role in the disposable database.
  2. Run the setup and grants from Examples 1 and 2.
  3. Verify read access with SET ROLE shop_app and SELECT count(*) FROM shop.products.
  4. Verify write access with the INSERT ... RETURNING statement from Example 2.
  5. Revoke shop_write from shop_app, retry the insert, and confirm that it fails while reads still work.
  6. Grant shop_write back, configure default privileges from Example 3, create shop.categories, and verify that shop_app can select from it.
  7. Clean up with the rollback script.
RESET ROLE;
REVOKE shop_write FROM shop_app;
SET ROLE shop_app;
SELECT count(*) FROM shop.products;
INSERT INTO shop.products (name, price_cents) VALUES ('Marker', 250);
RESET ROLE;
GRANT shop_write TO shop_app;

The SELECT count(*) should still succeed after revoking shop_write because shop_app remains a member of shop_read. The insert should fail until shop_write is granted again.

RESET ROLE;
DROP SCHEMA IF EXISTS shop CASCADE;
DROP ROLE IF EXISTS shop_app;
DROP ROLE IF EXISTS shop_write;
DROP ROLE IF EXISTS shop_read;
DROP ROLE IF EXISTS shop_owner;

The cleanup removes the schema and lab roles. In a real rollback, review dependencies first because CASCADE removes dependent objects.

Assessment Exercises

  1. A role has SELECT on analytics.events but receives permission denied for schema analytics. What exact grant is missing, and why is the table grant insufficient?
  2. You add ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA app GRANT SELECT ON TABLES TO app_read, but a new table created by admin is not readable. Explain the cause and two corrections.
  3. Design roles for an application with public browsing, customer checkout writes, and schema migrations. Which roles should have LOGIN, which should own objects, and which privileges should each capability role receive?
  4. An insert fails with a sequence permission error. Describe the diagnostic query you would run and the smallest grant that should fix the failure.
  5. Why is granting CREATE on a shared schema more sensitive than granting USAGE?

Summary

PostgreSQL roles are authorization building blocks. Use login roles for sessions, non-login roles for capabilities, owners for schema control, explicit grants for existing objects, and default privileges for future objects. The important mechanics are membership, schema visibility, object privileges, sequence access, ownership, and the creator-specific nature of default privileges. A secure design is one you can verify with deterministic privilege checks and troubleshoot from the exact permission error PostgreSQL returns.

Progress is saved in this browser.