Skip to main content

Users, Roles, and Privileges

Learning Focus

Use this lesson to create PostgreSQL roles, grant access safely (database, schema, tables, sequences), configure default privileges, and apply row-level security (RLS) when you need database-enforced tenant isolation.

Concept Overview​

PostgreSQL uses roles for both "users" and "groups".

A role can:

  • own objects
  • log in (LOGIN)
  • be granted privileges
  • contain other roles (membership)

In practice, you usually create:

  • an owner role that owns schemas/tables (used by migrations)
  • one or more runtime roles for applications (reader/writer)
  • optional human roles for admins, developers, and support

Why is it important?​

  • Security: overprivileged roles turn a small bug into a full data breach.
  • Safety: least privilege prevents accidental DROP, TRUNCATE, or schema changes.
  • Auditability: separate roles per service/person makes “who did what” traceable.
  • Operational stability: correct grants (schemas, sequences, defaults) prevent production outages.

Where does it fit?​

This is foundational for:

  • production deployments (secure auth + least privilege)
  • multi-tenant applications (RLS policies)
  • safe migrations (owner role) vs app runtime roles
  • security hardening (pg_hba.conf, SCRAM, TLS) in the next lesson

Syntax & Rules​

Core syntax​

Create roles (group roles):

CREATE ROLE app_reader;
CREATE ROLE app_writer;
CREATE ROLE app_owner;

Create a login role (a "user") and attach group roles:

CREATE ROLE app_user LOGIN PASSWORD 'change-me';
GRANT app_writer TO app_user;
GRANT app_reader TO app_user;

Grant database + schema access:

GRANT CONNECT ON DATABASE pg_lab TO app_user;
GRANT USAGE ON SCHEMA app TO app_reader, app_writer;

Grant table privileges (existing objects):

GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_writer;

Sequences (identity/serial inserts):

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_writer;

Available options / parameters​

Role attributes you will see often:

AttributeMeaningWhen to use
LOGINrole can authenticatefor users/services
NOLOGINrole cannot authenticategroup roles (reader/writer)
INHERIT / NOINHERITautomatically use privileges of granted rolesINHERIT is typical; NOINHERIT requires SET ROLE
CREATEDBcan create databasesadmin-only
CREATEROLEcan create/manage rolesadmin-only
SUPERUSERbypasses checks (do everything)avoid for apps; restrict to DBAs
BYPASSRLSbypass row-level securityrare; security/admin only

Privilege scopes you should remember:

ScopeExampleNotes
DatabaseGRANT CONNECT ON DATABASE pg_lab TO app_user;connects only; does not grant schema/table access
SchemaGRANT USAGE ON SCHEMA app TO app_reader;required to access objects in schema
TablesGRANT SELECT ON app.orders TO app_reader;table-level privileges
SequencesGRANT USAGE ON SEQUENCE app.orders_order_id_seq TO app_writer;required for identity/serial inserts

Key rules and considerations​

  • In PostgreSQL, roles are the unified concept; "users" are simply roles with LOGIN.
  • Granting table privileges is not enough: schema USAGE is often the missing piece.
  • Inserts into identity/serial columns often require sequence privileges.
  • ALTER DEFAULT PRIVILEGES affects future objects created by a specific owner role.
  • Consider revoking broad defaults from PUBLIC (see the hardening lesson) to reduce blast radius.

Step-by-Step Examples​

These examples use a dedicated schema app and a separate owner role.

Example 1: Application backend roles (owner + reader + writer)​

Create roles and a schema owned by app_owner:

-- Run as a privileged admin role
DROP SCHEMA IF EXISTS app CASCADE;

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_owner') THEN
    CREATE ROLE app_owner NOLOGIN;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_reader') THEN
    CREATE ROLE app_reader NOLOGIN;
  END IF;
  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'app_writer') THEN
    CREATE ROLE app_writer NOLOGIN;
  END IF;
END$$;

CREATE SCHEMA app AUTHORIZATION app_owner;

Expected output:

DROP SCHEMA
DO
CREATE SCHEMA

Create two tables as the owner:

SET ROLE app_owner;

CREATE TABLE app.orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_email text NOT NULL,
  status text NOT NULL DEFAULT 'new',
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE app.order_items (
  order_id bigint NOT NULL REFERENCES app.orders(order_id),
  sku text NOT NULL,
  qty integer NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, sku)
);

RESET ROLE;

Expected output:

SET
CREATE TABLE
CREATE TABLE
RESET

Grant access:

-- Schema visibility
GRANT USAGE ON SCHEMA app TO app_reader, app_writer;

-- Existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_reader;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_writer;

-- Identity/serial sequences for inserts
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_writer;

Expected output:

GRANT
GRANT
GRANT
GRANT

Verify with SET ROLE (simulate what your app runtime role can do):

SET ROLE app_writer;

INSERT INTO app.orders (customer_email)
VALUES ('ava@example.com')
RETURNING order_id, status;

RESET ROLE;

Expected output:

SET
 order_id | status
----------+--------
        1 | new
(1 row)

RESET

Explanation:

  • app_owner owns schema/tables (migrations run as owner).
  • app_reader can read data.
  • app_writer can modify data and needs sequence privileges for identity inserts.

Example 2: Read-only dashboard user (grant on views, not base tables)​

Create a view owned by app_owner:

SET ROLE app_owner;

CREATE OR REPLACE VIEW app.v_order_summary AS
SELECT
  status,
  count(*) AS orders,
  min(created_at) AS first_order_at,
  max(created_at) AS last_order_at
FROM app.orders
GROUP BY status;

RESET ROLE;

Expected output:

SET
CREATE VIEW
RESET

Grant access only to the view:

GRANT USAGE ON SCHEMA app TO app_reader;
GRANT SELECT ON app.v_order_summary TO app_reader;
REVOKE ALL ON app.orders FROM app_reader;

Expected output:

GRANT
GRANT
REVOKE

Test:

SET ROLE app_reader;

SELECT * FROM app.v_order_summary ORDER BY status;

-- Base table should fail
SELECT * FROM app.orders;

Expected output:

SET
 status | orders | first_order_at | last_order_at
--------+--------+----------------+--------------
 new    |      1 | ...            | ...
(1 row)

ERROR:  permission denied for table orders

Explanation:

  • Views can provide a controlled interface.
  • Revoke base table access to prevent raw data access.

Example 3: Default privileges (so future tables inherit grants)​

Without default privileges, new tables created by app_owner will not automatically be accessible to runtime roles.

Configure defaults as the owner:

SET ROLE app_owner;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT ON TABLES TO app_reader;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

CREATE TABLE app.audit_log (
  audit_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  happened_at timestamptz NOT NULL DEFAULT now(),
  event text NOT NULL
);

RESET ROLE;

Expected output:

SET
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES
CREATE TABLE
RESET

Verify the new table is readable by app_reader:

SET ROLE app_reader;
SELECT count(*) FROM app.audit_log;
RESET ROLE;

Expected output:

SET
 count
-------
     0
(1 row)

RESET

Explanation:

  • Default privileges apply to objects created by the role that ran ALTER DEFAULT PRIVILEGES.
  • Put these statements in migrations run as the owner role.

Example 4: Row-level security (RLS) for tenant isolation​

RLS enforces per-tenant access in the database.

Setup a tenant table:

SET ROLE app_owner;

DROP TABLE IF EXISTS app.invoices;
CREATE TABLE app.invoices (
  invoice_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  amount_cents bigint NOT NULL CHECK (amount_cents >= 0)
);

INSERT INTO app.invoices (tenant_id, amount_cents)
VALUES (42, 1000), (42, 2500), (7, 999);

ALTER TABLE app.invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation
ON app.invoices
USING (
  tenant_id = current_setting('app.tenant_id', true)::bigint
);

GRANT SELECT ON app.invoices TO app_reader;

RESET ROLE;

Expected output:

SET
DROP TABLE
CREATE TABLE
INSERT 0 3
ALTER TABLE
CREATE POLICY
GRANT
RESET

Query as app_reader with different tenant settings:

SET ROLE app_reader;

SET app.tenant_id = '42';
SELECT invoice_id, tenant_id, amount_cents FROM app.invoices ORDER BY invoice_id;

SET app.tenant_id = '7';
SELECT invoice_id, tenant_id, amount_cents FROM app.invoices ORDER BY invoice_id;

RESET ROLE;

Expected output:

SET
SET
 invoice_id | tenant_id | amount_cents
------------+-----------+-------------
          1 |        42 |        1000
          2 |        42 |        2500
(2 rows)

SET
 invoice_id | tenant_id | amount_cents
------------+-----------+-------------
          3 |         7 |         999
(1 row)

RESET

Explanation:

  • RLS filters rows based on a session setting.
  • Your application must set app.tenant_id per connection.

Practical Use Cases​

1) Microservices and least privilege​

Give each service its own login role and only the grants it needs (often via group roles).

CREATE ROLE billing_service LOGIN PASSWORD 'change-me';
GRANT app_writer TO billing_service;

2) Production read-only reporting​

Grant SELECT on views, not base tables.

GRANT SELECT ON app.v_order_summary TO app_reader;

3) Safer migrations​

Run migrations as an owner role; run apps as runtime roles.

-- migrations
SET ROLE app_owner;
-- DDL here
RESET ROLE;

4) Multi-tenant enforcement​

Use RLS for hard isolation when you can’t trust application logic alone.

ALTER TABLE app.invoices ENABLE ROW LEVEL SECURITY;

5) Delegated access via memberships​

Use role membership to simplify access management.

GRANT app_reader TO analyst_user;

Common Mistakes & Troubleshooting​

Mistake 1: Forgetting schema USAGE​

Wrong SQL:

GRANT SELECT ON app.orders TO app_reader;
-- but no schema USAGE

Bad outcome:

ERROR:  permission denied for schema app

Fix:

GRANT USAGE ON SCHEMA app TO app_reader;

Mistake 2: Missing sequence privileges for identity/serial inserts​

Wrong setup:

GRANT INSERT ON app.orders TO app_writer;
-- but no sequence grants

Bad outcome:

ERROR:  permission denied for sequence orders_order_id_seq

Fix:

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_writer;

Mistake 3: Default privileges don’t apply (wrong owner)​

Bad outcome:

  • you run ALTER DEFAULT PRIVILEGES as an admin
  • tables are created by app_owner
  • runtime roles still can’t access new tables

Fix:

  • run ALTER DEFAULT PRIVILEGES as the same role that creates objects (usually app_owner).

Mistake 4: Confusing SET ROLE with authentication​

Wrong assumption:

  • SET ROLE app_reader changes identity like logging in as that role

Reality:

  • SET ROLE changes the active role for privilege checking within a session.

Fix:

  • For testing, use SET ROLE to simulate privileges.
  • For true authentication testing, connect as that login role.

Debugging tips (numbered)​

  1. Check role membership:
SELECT r.rolname AS role, m.rolname AS member
FROM pg_auth_members am
JOIN pg_roles r ON r.oid = am.roleid
JOIN pg_roles m ON m.oid = am.member
ORDER BY role, member;
  1. Inspect table privileges:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app' AND table_name = 'orders'
ORDER BY grantee, privilege_type;
  1. Verify schema privileges:
SELECT nspname, nspacl
FROM pg_namespace
WHERE nspname = 'app';
  1. If inserts fail, check sequence privileges in the schema.

Best Practices​

✅ Use separate roles: owner (DDL) vs runtime (DML)
❌ Don’t run applications as superuser or as the schema/table owner

✅ Grant schema USAGE + table privileges together
❌ Don’t assume table GRANTs work without schema access

✅ Grant sequence privileges for identity/serial inserts
❌ Don’t forget sequences when you add INSERT

✅ Put grants and default privileges into migrations
❌ Don’t manage production grants manually in ad-hoc sessions

✅ Use views/procedures to constrain access surface
❌ Don’t grant broad table access to third parties

✅ Test RLS with realistic connection behavior
❌ Don’t rely on RLS without ensuring the app sets tenant context

Hands-On Practice​

Exercise 1 (Easy): Create reader and writer roles​

Task: create app_reader and app_writer roles, and grant reader SELECT and writer SELECT/INSERT on app.orders.

Solution:

CREATE ROLE app_reader NOLOGIN;
CREATE ROLE app_writer NOLOGIN;

GRANT USAGE ON SCHEMA app TO app_reader, app_writer;
GRANT SELECT ON app.orders TO app_reader;
GRANT SELECT, INSERT ON app.orders TO app_writer;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_writer;

Exercise 2 (Medium): Configure default privileges​

Task: ensure any new tables created in schema app by app_owner grant SELECT to app_reader automatically.

Solution:

SET ROLE app_owner;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT ON TABLES TO app_reader;
RESET ROLE;

Exercise 3 (Advanced): Add RLS tenant isolation​

Task: enable RLS on a table app.documents(tenant_id, body) so each tenant only sees its rows based on app.tenant_id.

Solution:

SET ROLE app_owner;

CREATE TABLE app.documents (
  doc_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  body text NOT NULL
);

ALTER TABLE app.documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_docs
ON app.documents
USING (tenant_id = current_setting('app.tenant_id', true)::bigint);

GRANT SELECT ON app.documents TO app_reader;

RESET ROLE;

Connection to Other Concepts​

Related conceptWhy it matters
Security HardeningRoles and grants are necessary, but authentication/network hardening completes the security story.
ViewsViews help you expose controlled projections and avoid base table access.
Stored procedures and functionsYou can grant EXECUTE on safe procedures instead of broad table privileges.
TriggersAudit triggers can record current_user/session_user for traceability.
Backup and recoveryBackup roles need specific privileges; security is part of recoverability.

Visual Learning Diagram​

flowchart TD
  A["Login role\napp_user"] --> B["Member of\napp_writer"]
  A --> C["Member of\napp_reader"]
  B --> D["Privileges\nINSERT/UPDATE/DELETE"]
  C --> E["Privileges\nSELECT"]
  D --> F["Schema app\n+ tables + sequences"]
  E --> F
  F --> G["RLS policies\n(optional)"]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G allNodes
  class G highlight

Common Pitfalls​

PitfallConsequencePrevention
Grant tables but not schema USAGEpermission denied errorsgrant USAGE ON SCHEMA alongside table grants
Forget sequence privilegesinserts fail for identity/serialgrant USAGE, SELECT on sequences
Default privileges set by wrong rolenew tables inaccessiblerun defaults as the object owner role
App connects as owner/superuserhuge blast radiusdedicated least-privilege runtime roles
RLS enabled but context not setempty results or errorsensure app sets app.tenant_id per connection

Quick Reference​

CREATE ROLE app_reader NOLOGIN;
CREATE ROLE app_user LOGIN PASSWORD '...';
GRANT app_reader TO app_user;
GRANT USAGE ON SCHEMA app TO app_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_reader;

What's Next​