PostgreSQL authorization

PostgreSQL Authorization Model Tutorial

This document provides a focused tutorial on PostgreSQL’s authorization model, including roles (users and groups), table- and row-level access control, views, granting and revoking privileges, and how PostgreSQL verifies authorization.

A running example is included throughout.

1. Roles: Users and Groups

PostgreSQL uses roles to model both individual users and groups. A role can:

  • Log in (“LOGIN” attribute)

  • Own objects

  • Be granted privileges

  • Contain other roles (group-like behavior)

Creating roles

CREATE ROLE app_admin LOGIN PASSWORD 'secret';
CREATE ROLE app_reader LOGIN PASSWORD 'readerpass';
CREATE ROLE reporting_group;  -- cannot log in

Assigning roles to other roles (group membership)

GRANT reporting_group TO app_reader;

This makes app_reader inherit all privileges granted to reporting_group (unless NOINHERIT).

2. Example Schema

We use a small sales schema.

CREATE TABLE customer (
    customer_id   SERIAL PRIMARY KEY,
    name          TEXT NOT NULL,
    region        TEXT NOT NULL
);

CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT NOT NULL REFERENCES customer(customer_id),
    amount        NUMERIC(12,2) NOT NULL,
    region        TEXT NOT NULL
);

Populate minimal sample data:

INSERT INTO customer(name, region) VALUES
 ('Alice', 'NA'),
 ('Bob', 'EU');

INSERT INTO orders(customer_id, amount, region) VALUES
 (1, 300, 'NA'),
 (2, 500, 'EU');

3. Table-Level Privileges

Privileges available for tables:

  • SELECT

  • INSERT

  • UPDATE (optionally per-column)

  • DELETE

  • TRUNCATE

  • REFERENCES

  • TRIGGER

Granting table access

GRANT SELECT ON customer TO reporting_group;
GRANT SELECT, INSERT ON orders TO app_admin;

Revoking

REVOKE INSERT ON orders FROM app_admin;
REVOKE SELECT ON customer FROM reporting_group;

Note: If a user inherits a privilege through a role, revoking must be done at the role where granted.

4. Row-Level Security (RLS)

Row-level security enforces tuple-level access control.
Enable it per table:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Define policies:
Allow users to see only orders from their region. Assume each login role has a setting:

ALTER ROLE app_reader SET my.region = 'NA';

Policy:

CREATE POLICY order_region_policy ON orders
    FOR SELECT
    USING (region = current_setting('my.region'));

Users now see only rows in their region.

Write policy:

CREATE POLICY order_write_policy ON orders
    FOR INSERT
    WITH CHECK (region = current_setting('my.region'));

5. Views and Access Control

Views help enforce read-only or filtered access.

Example restricted view

CREATE VIEW orders_public AS
SELECT order_id, amount
FROM orders;

Grant privileges on the view only:

GRANT SELECT ON orders_public TO reporting_group;

The underlying table permissions are not automatically exposed. Users need no direct access to orders.

Updatable view with INSTEAD OF triggers (outline)

CREATE VIEW orders_regional AS
SELECT * FROM orders WHERE region = current_setting('my.region');

Make it writable:

CREATE FUNCTION orders_regional_ins() RETURNS trigger AS $$
BEGIN
    INSERT INTO orders(customer_id, amount, region)
    VALUES(NEW.customer_id, NEW.amount, current_setting('my.region'));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_regional_ins_trg
INSTEAD OF INSERT ON orders_regional
FOR EACH ROW EXECUTE FUNCTION orders_regional_ins();

Users get controlled write access.

6. Authorization Verification Policy

PostgreSQL evaluates permissions in this order:

  1. Object ownership: Owners bypass privilege checks.

  2. Explicit GRANTs on the object.

  3. Inherited privileges from parent roles.

  4. Row-level security policies, if enabled.

  5. For views: permissions check whether user has privilege on the view, not the base table (unless security_barrier or modifiers apply).

  6. For functions: if declared SECURITY DEFINER, checks occur under the function owner’s privileges; otherwise under caller.

Checking privileges

SELECT * FROM information_schema.table_privileges
WHERE grantee = 'app_reader';

Check role memberships:

SELECT rolname, rolcanlogin, rolinherit FROM pg_roles;
SELECT * FROM pg_auth_members;

7. End-to-End Example Workflow

  1. Create users and groups.
CREATE ROLE analyst LOGIN PASSWORD 'x';
CREATE ROLE sales_group;
GRANT sales_group TO analyst;
  1. Grant table-level privileges.
GRANT SELECT ON customer TO sales_group;
  1. Enable RLS.
ALTER TABLE customer ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_region ON customer
    FOR SELECT USING (region = current_setting('my.region'));
  1. Set user session attribute.
ALTER ROLE analyst SET my.region = 'EU';
  1. Create a view for narrower reporting.
CREATE VIEW customer_names AS
SELECT customer_id, name
FROM customer;
GRANT SELECT ON customer_names TO analyst;
  1. Verify access.
SET ROLE analyst;
SELECT * FROM customer;        -- only EU rows
SELECT * FROM customer_names;  -- restricted view

Summary

  • Roles unify users and groups.

  • Privileges apply at object level; RLS applies at tuple level.

  • Views can narrow permissions and expose limited, controlled interfaces.

  • GRANT/REVOKE manage access.

  • Authorization checks follow a deterministic hierarchy including role inheritance, object ownership, and RLS.