Users, Roles & Permissions

Database permissions answer one production question: "Which identity is allowed to do this?" In PostgreSQL, that starts with roles, continues through GRANT and REVOKE, and becomes useful only when your app role, migration role, and admin role are deliberately different.

You already learned the first security layer in SQL injection: keep user input from becoming SQL. This lesson is the second layer: if a bug still reaches the database, the connected role should be too limited to turn that bug into a disaster.

Roles Are Database Identities

A role is a PostgreSQL identity. It can own objects, receive permissions, belong to other roles, and, on a real server, optionally log in. The browser lab does not connect over the network, so we use SET ROLE to switch identities inside one live session.

Here is a small production-shaped setup: a migration role owns the schema and table; app roles get only the actions they need.

CREATE ROLE migration_runner;
CREATE ROLE app_reader;
CREATE ROLE app_writer;
 
CREATE SCHEMA app AUTHORIZATION migration_runner;
 
SET ROLE migration_runner;
 
CREATE TABLE app.support_tickets (
  ticket_id integer PRIMARY KEY,
  customer_email text NOT NULL,
  subject text NOT NULL,
  status text NOT NULL
);
 
INSERT INTO app.support_tickets (ticket_id, customer_email, subject, status)
VALUES
  (1, 'ada@example.com', 'Cannot reset password', 'open'),
  (2, 'ken@example.com', 'Invoice copy needed', 'open'),
  (3, 'priya@example.com', 'Feature request', 'closed');
 
RESET ROLE;
 
GRANT USAGE ON SCHEMA app TO app_reader, app_writer;
GRANT SELECT ON app.support_tickets TO app_reader;
GRANT SELECT, INSERT ON app.support_tickets TO app_writer;
 
SELECT n.nspname AS schema_name,
       c.relname AS table_name,
       r.rolname AS owner_role
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_roles r ON r.oid = c.relowner
WHERE n.nspname = 'app'
  AND c.relname = 'support_tickets'
ORDER BY n.nspname, c.relname;

CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 3
RESET
GRANT
GRANT
GRANT
 schema_name |   table_name    |    owner_role
-------------+-----------------+------------------
 app         | support_tickets | migration_runner
(1 row)

The important row is the owner: migration_runner, not the app. Ownership is stronger than ordinary permission. The owner can alter, drop, and grant privileges on the object. Your web application should not be the owner of its tables, because an injected or buggy app query should not be able to reshape the database.

GRANT Allows, REVOKE Takes Back

GRANT adds a privilege. REVOKE removes a privilege. A privilege is specific: SELECT is not INSERT, INSERT is not DELETE, and table privileges are separate from schema privileges.

The reader role can read tickets, but it cannot create one:

SET ROLE app_reader;
 
INSERT INTO app.support_tickets (ticket_id, customer_email, subject, status)
VALUES (4, 'tomas@example.com', 'Login loop', 'open');

That error is good news. The database understood the SQL and refused it because the current role did not have the right privilege.

When the business decision is "this role may create tickets," grant exactly that action on exactly that table:

RESET ROLE;
 
GRANT INSERT ON app.support_tickets TO app_reader;
 
SET ROLE app_reader;
 
INSERT INTO app.support_tickets (ticket_id, customer_email, subject, status)
VALUES (4, 'tomas@example.com', 'Login loop', 'open');
 
SELECT t.ticket_id,
       t.customer_email,
       t.status
FROM app.support_tickets t
ORDER BY t.ticket_id;

RESET
GRANT
SET
INSERT 0 1
 ticket_id |  customer_email   | status
-----------+-------------------+--------
         1 | ada@example.com   | open
         2 | ken@example.com   | open
         3 | priya@example.com | closed
         4 | tomas@example.com | open
(4 rows)

The grant allowed only the intended write. It did not quietly make app_reader a table owner, schema owner, or admin. You can prove that by rehearsing a delete target, then trying the delete:

SELECT t.ticket_id,
       t.customer_email,
       t.status
FROM app.support_tickets t
WHERE t.ticket_id = 4
ORDER BY t.ticket_id;
 
DELETE FROM app.support_tickets t
WHERE t.ticket_id = 4;

The rehearsal SELECT matters even in a lesson where the DELETE is supposed to fail. It keeps the habit from Section 8.2 intact: before any destructive statement, prove the target rows.

If that insert permission was temporary, take it back:

RESET ROLE;
 
REVOKE INSERT ON app.support_tickets FROM app_reader;
 
SELECT has_table_privilege('app_reader', 'app.support_tickets', 'SELECT') AS can_select,
       has_table_privilege('app_reader', 'app.support_tickets', 'INSERT') AS can_insert,
       has_table_privilege('app_reader', 'app.support_tickets', 'DELETE') AS can_delete;

RESET
REVOKE
 can_select | can_insert | can_delete
------------+------------+------------
 t          | f          | f
(1 row)

This is the shape of permission review: name the role, name the object, name the action. Avoid "just make it work" grants such as table ownership, CREATE everywhere, or superuser-like roles for app code. They solve today's error by deleting tomorrow's safety rail.

Least Privilege In Practice

Least privilege means each role gets the smallest set of privileges needed for its job.

In a typical app, that means at least three different database identities:

• app_user: used by application requests; can run the ordinary reads and writes the app needs.
• migration_user: used by migration tooling; owns or changes schema objects during reviewed deploys.
• admin_user: used rarely by operators; can investigate incidents, repair data, and manage roles.

Those names are examples, not magic. The point is separation. If the web app connects as the migration role, an application bug may be able to ALTER TABLE. If migrations run as the admin role, every migration file has more power than it needs. If everyone shares one password, audit trails tell you almost nothing.

Schemas Are Permission Boundaries

A schema is a namespace inside a database: app.support_tickets means table support_tickets inside schema app. Schemas are also permission gates.

This is easy to miss: granting SELECT on a table is not enough if the role cannot use the schema that contains it.

RESET ROLE;
 
CREATE SCHEMA billing AUTHORIZATION migration_runner;
 
SET ROLE migration_runner;
 
CREATE TABLE billing.payment_notes (
  note_id integer PRIMARY KEY,
  customer_email text NOT NULL,
  note text NOT NULL
);
 
INSERT INTO billing.payment_notes (note_id, customer_email, note)
VALUES
  (1, 'ada@example.com', 'Card expires next month'),
  (2, 'ken@example.com', 'Manual invoice approved');
 
RESET ROLE;
 
GRANT SELECT ON billing.payment_notes TO app_reader;
 
SET ROLE app_reader;
 
SELECT p.note_id,
       p.customer_email
FROM billing.payment_notes p
ORDER BY p.note_id;

That denial is the boundary doing its job. app_reader has table-level SELECT, but it does not have USAGE on the billing schema, so it cannot reach objects inside that namespace. In a real app, you might expose a safe reporting schema or view instead of opening the raw billing schema to ordinary app code.

Row-Level Security Is The Next Layer

Row-level security (RLS) is PostgreSQL's way to apply permissions inside a table, row by row. Table grants answer "may this role select from the table at all?" RLS policies answer "which rows may this role see or change?"

This is only a peek, not a full policy design lesson:

RESET ROLE;
 
ALTER TABLE app.support_tickets ENABLE ROW LEVEL SECURITY;
 
SELECT c.relname AS table_name,
       c.relrowsecurity AS rls_enabled
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
  AND c.relname = 'support_tickets'
ORDER BY c.relname;

RESET
ALTER TABLE
   table_name    | rls_enabled
-----------------+-------------
 support_tickets | t
(1 row)

Do not reach for RLS as a shortcut around basic grants. Start with roles, schema boundaries, and table privileges. Add RLS when multiple users or tenants share a table and the database itself must enforce row visibility.

Try the whole pattern below. The starter code succeeds; the break-it line proves the reader still cannot delete rows.

Permissions are how the database participates in application security instead of trusting the application to get every query right forever. Next, you will move from limiting blast radius to surviving real failure: backups, recovery, and the restore drills that prove they work.