How do teams manage access control and row-level security behind a PostgreSQL API?

Should access control live in the database or in the API layer?

If the API is not the only thing that reads the database, put the rules in the database. Rules held by the API protect only requests that pass through it.

There are two established models. In the first, used by PostgREST and Postrust, the API connects as a role with no privileges of its own and becomes a different PostgreSQL role for each request; PostgreSQL’s GRANTs and row security policies then decide what that request may see. In the second, used by Hasura, permissions are metadata kept by the API server and compiled into the SQL it generates. Both are reasonable; they put the rules in different places.

Postrust supports both on its GraphQL surface: Hasura’s permission model read from a JSON document, or PostgreSQL roles and RLS (see the Hasura comparison). The rest of this guide is about the database-enforced model, which is the one that covers REST and GraphQL alike.

How does a JWT become a PostgreSQL role for each request?

The server verifies the token, opens a transaction, sets the role the token’s role claim names, and stores the claims as transaction-local settings. The query then runs as that role.

In Postrust each request runs in one transaction. Before the query, the role and the request’s settings are applied with SET LOCAL ROLE and set_config(name, value, true), both scoped to that transaction, so nothing leaks to the next request on the same pooled connection. The claims are available two ways:

• request.jwt.claims — the whole verified claims object as JSON.
• request.jwt.claims.<name> — each claim whose name is a valid setting name, as text, so a policy need not parse JSON.

REST requests also get request.method, request.path, request.headers and request.cookies. The claim that names the role is role unless PGRST_JWT_ROLE_CLAIM_KEY says otherwise, and a request with no token runs as PGRST_DB_ANON_ROLE. The authentication docs cover how exp, nbf and aud are checked, and configuration lists every setting.

DATABASE_URL=postgres://authenticator:change-me@db:5432/app
PGRST_DB_ANON_ROLE=web_anon
PGRST_JWT_SECRET=a-secret-of-at-least-32-characters-long

Which roles and GRANTs does a generated API need?

Three kinds: a login role the server connects as, an anonymous role, and one or more roles for signed-in callers. GRANTs decide which tables each may touch; policies come after.

-- The server logs in as this. It can do nothing but become the others.
CREATE ROLE authenticator LOGIN NOINHERIT PASSWORD 'change-me';

CREATE ROLE web_anon NOLOGIN;
CREATE ROLE app_user NOLOGIN;
GRANT web_anon, app_user TO authenticator;

GRANT USAGE ON SCHEMA public TO web_anon, app_user;
GRANT SELECT ON public.articles TO web_anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.projects TO app_user;

A role the token names but the database does not know fails at the SET ROLE, so a forged or mistyped role cannot fall through to broader access. Keep web_anon’s grants small: anything granted to it is public.

How do you write RLS policies for users, tenants and teams?

Enable RLS on the table, then write policies that compare row columns with claims from the token. With RLS enabled and no policy, the role sees no rows at all.

Small helper functions keep policies readable. The nullif matters: a custom setting a pooled connection has seen before reads back as an empty string, not NULL, when it is not set.

CREATE SCHEMA auth;
GRANT USAGE ON SCHEMA auth TO web_anon, app_user;

CREATE FUNCTION auth.user_id() RETURNS text
  LANGUAGE sql STABLE
  AS $$ SELECT nullif(current_setting('request.jwt.claims.sub', true), '') $$;

CREATE FUNCTION auth.tenant_id() RETURNS uuid
  LANGUAGE sql STABLE
  AS $$ SELECT nullif(current_setting('request.jwt.claims.tenant_id', true), '')::uuid $$;

Per-user rows

ALTER TABLE notes ENABLE ROW LEVEL SECURITY;

CREATE POLICY own_notes ON notes
  FOR ALL TO app_user
  USING (owner_id = (SELECT auth.user_id()))
  WITH CHECK (owner_id = (SELECT auth.user_id()));

USING filters the rows a statement can see or change; WITH CHECK rejects rows an insert or update would write. PostgreSQL reuses USING as the check when it is omitted from a FOR ALL policy, but spelling it out keeps the write rule visible in review. The scalar subquery around the function lets PostgreSQL evaluate it once per statement rather than once per row.

Tenant isolation

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  FOR ALL TO app_user
  USING (tenant_id = (SELECT auth.tenant_id()))
  WITH CHECK (tenant_id = (SELECT auth.tenant_id()));

CREATE INDEX ON invoices (tenant_id);

The tenant comes from a signed claim, never from a header or query parameter the caller chooses. A token without the claim yields NULL, which matches no row.

Team or organisation membership

CREATE POLICY team_projects ON projects
  FOR SELECT TO app_user
  USING (team_id IN (
    SELECT team_id FROM memberships WHERE user_id = (SELECT auth.user_id())
  ));

CREATE INDEX ON memberships (user_id, team_id);
CREATE INDEX ON projects (team_id);

The subquery runs as the caller, so app_user needs SELECT on memberships, and any policy on memberships applies inside it. If memberships’s own policy refers back to projects, PostgreSQL reports infinite recursion; the usual fix is a SECURITY DEFINER function that returns the caller’s team ids, written with the care described below.

Does the same policy protect REST and GraphQL in Postrust?

Yes. Both surfaces run each query in a transaction that has done SET LOCAL ROLE and set request.jwt.claims, so one policy answers both.

curl 'http://localhost:3000/projects?select=id,name' \
  -H "Authorization: Bearer $TOKEN"

curl http://localhost:3000/v1/graphql \
  -H "Authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -d '{"query":"{ projects { id name } }"}'

That includes GraphQL subscriptions, which authenticate once from the WebSocket’s connection_init payload. The GraphQL surface additionally exposes Hasura session variables, so a policy written for Hasura can read current_setting('hasura.user_id'). Without an admin secret configured, Postrust ignores x-hasura-* headers and takes session variables only from a verified token, so a caller cannot choose the value a policy compares against. The GraphQL docs cover the rest, and the Apollo Federation walkthrough shows a policy deciding what each caller sees through a router.

For a check that is not about rows — a suspended account, say — PGRST_DB_PRE_REQUEST names a function Postrust calls before each REST request’s query, in the same transaction; if it raises, the request is refused.

What mistakes break row-level security behind an API?

Most failures are silent: the query succeeds against the wrong rows. These are the ones to look for in review.

• Policies without ENABLE ROW LEVEL SECURITY. CREATE POLICY on a table without RLS enabled does nothing. Query pg_class.relrowsecurity to list exposed tables that lack it.
• Testing as the owner. The table owner bypasses RLS unless the table is set to FORCE ROW LEVEL SECURITY; superusers and BYPASSRLS roles always do. Never let the API’s login role own tables.
• SECURITY DEFINER functions. They run as their owner, usually past RLS. Give them a fixed search_path, filter by the caller explicitly, and revoke the default EXECUTE from PUBLIC (see CREATE FUNCTION).
• Views. A view checks permissions as its owner unless created with security_invoker (PostgreSQL 15 and later), so an exposed view can step around the policies on its tables.
• Unindexed policy columns. The policy predicate is part of every query; without an index on tenant_id or owner_id each request scans the table.
• Read rules that also govern writes. A FOR ALL policy written with reads in mind, such as USING (published), also decides which rows a role may insert and update. Give writes their own FOR INSERT and FOR UPDATE policies when the rules differ.

How do you test RLS policies before they ship?

Reproduce what the API does — set the claims, switch role — inside a transaction you roll back, and assert on what comes back.

BEGIN;
SELECT set_config('request.jwt.claims.sub', 'user-1', true),
       set_config('request.jwt.claims.tenant_id',
                  '6f1c2e9a-0000-4000-8000-000000000001', true);
SET LOCAL ROLE app_user;

SELECT count(*) FROM invoices;              -- only tenant ...0001
SELECT count(*) FROM notes WHERE owner_id <> 'user-1';  -- expect 0
ROLLBACK;

Run tests like these in CI against a real PostgreSQL — tools such as pgTAP make the assertions tidy — and include the negative cases: another tenant’s id, a token with no tenant claim, and the anonymous role. PostgreSQL’s CREATE POLICY reference explains how multiple permissive and restrictive policies combine, which is worth reading before a table gets its second policy.