PostgREST: an instant REST API from a Postgres schema

Install

# Single binary
PR_VER=12.2.0
curl -L -o /tmp/postgrest.tar.xz \
    "https://github.com/PostgREST/postgrest/releases/download/v${PR_VER}/postgrest-v${PR_VER}-linux-static-x86-64.tar.xz"
tar -xJf /tmp/postgrest.tar.xz -C /tmp
sudo mv /tmp/postgrest /usr/local/bin/

# Or via Nix / Brew
nix-env -iA nixpkgs.postgrest
brew install postgrest

# Or as a container
docker run --rm postgrest/postgrest:v12.2.0

Configuration

Two-line config:

# /etc/postgrest.conf
db-uri = "postgresql://authenticator:pw@127.0.0.1:5432/mydb"
db-schemas = "api"
db-anon-role = "web_anon"

# JWT for authenticated requests
jwt-secret = "<a-long-random-key>"
server-port = 3000

Two database roles matter:

• authenticator — the role PostgREST itself logs in as. NO PRIVILEGES on data tables — only the right to switch into the per-request role via SET ROLE.
• web_anon — the role PostgREST switches into for unauthenticated requests.

-- In the database
CREATE ROLE web_anon NOLOGIN;
CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD 'pw';
GRANT web_anon TO authenticator;

The first table

CREATE SCHEMA api;

CREATE TABLE api.todos (
    id          bigserial PRIMARY KEY,
    title       text NOT NULL,
    done        boolean NOT NULL DEFAULT false,
    created_at  timestamptz NOT NULL DEFAULT now()
);

GRANT USAGE  ON SCHEMA api          TO web_anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON api.todos TO web_anon;
GRANT USAGE, SELECT ON SEQUENCE api.todos_id_seq TO web_anon;

Start PostgREST:

postgrest /etc/postgrest.conf

The API is now live:

# List
curl http://localhost:3000/todos

# Filter
curl 'http://localhost:3000/todos?done=eq.false&order=created_at.desc&limit=10'

# Insert
curl -X POST http://localhost:3000/todos \
    -H 'Content-Type: application/json' \
    -d '{"title": "Buy milk"}'

# Update
curl -X PATCH 'http://localhost:3000/todos?id=eq.42' \
    -H 'Content-Type: application/json' \
    -d '{"done": true}'

# Delete
curl -X DELETE 'http://localhost:3000/todos?id=eq.42'

Filtering, ordering, pagination, partial responses, and resource embedding are all in the query string. No routes were written.

The query operators

?status=eq.active           # equals
?price=gt.10&price=lt.100   # greater/less than
?status=in.(active,pending) # IN
?title=ilike.*book*         # case-insensitive LIKE
?tags=cs.{tag1,tag2}        # array contains
?metadata->>type=eq.foo     # JSONB field

?select=id,title,user(name) # column selection + foreign-table embed
?order=created_at.desc
?limit=20&offset=40

# Range header style pagination too:
curl -H 'Range: 0-19' -H 'Prefer: count=exact' .../todos

Embedding related rows

Foreign keys in the schema become embeddable resources:

CREATE TABLE api.users (id bigserial PRIMARY KEY, name text);
ALTER TABLE api.todos ADD COLUMN user_id bigint REFERENCES api.users(id);

curl 'http://localhost:3000/todos?select=id,title,user:users(id,name)&limit=5'

# Or top-down: every user with their open todos
curl 'http://localhost:3000/users?select=id,name,todos(*)&todos.done=is.false'

Single round-trip, joined at the database, returned as nested JSON.

Functions as RPC

Pure SQL or PL/pgSQL functions in the exposed schema become POST endpoints:

CREATE FUNCTION api.complete_all_for_user(p_user bigint)
    RETURNS bigint LANGUAGE sql AS $$
    UPDATE api.todos SET done = true WHERE user_id = p_user RETURNING count(*);
$$;
GRANT EXECUTE ON FUNCTION api.complete_all_for_user(bigint) TO web_anon;

curl -X POST http://localhost:3000/rpc/complete_all_for_user \
    -H 'Content-Type: application/json' \
    -d '{"p_user": 42}'

Authentication: JWT

PostgREST doesn't issue JWTs — it validates whatever signed tokens you give it. Pair it with an OIDC provider (Authentik in that tutorial works perfectly) that signs JWTs with the shared secret.

A signed JWT carrying a role claim switches the database role per request:

// JWT payload
{
  "role": "web_user",
  "user_id": 42,
  "exp": 1727900000
}

curl http://localhost:3000/todos \
    -H 'Authorization: Bearer <jwt>'
# Now executed as web_user instead of web_anon

Authorization: row-level security

Postgres RLS plus a per-request role gives database-enforced per-row permissions. Users can only see their own todos:

CREATE ROLE web_user;
GRANT web_user TO authenticator;
GRANT USAGE ON SCHEMA api TO web_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON api.todos TO web_user;

ALTER TABLE api.todos ENABLE ROW LEVEL SECURITY;

CREATE POLICY todos_isolation ON api.todos
    USING (user_id = current_setting('request.jwt.claims', true)::json->>'user_id' = id::text)
    WITH CHECK (user_id = current_setting('request.jwt.claims', true)::json->>'user_id' = id::text);

(Adjust the cast/expression as needed; the canonical helper auth.uid() in Supabase wraps that pattern.)

Now a request with role web_user and JWT user_id=42 can only see/edit rows where user_id = 42. No matter how the API is queried — via PostgREST, psql with that role, or another tool — the database enforces the isolation. Authorization moves from "be careful in every API route" to "one CREATE POLICY, applied to everyone."

Worth knowing

• PostgREST is stateless. Scale horizontally by running multiple instances behind a load balancer.
• OpenAPI / Swagger is exposed at / by default — the API documents itself from the schema.
• For complex business logic, write SQL functions; for write-side validation, use CHECK constraints + RAISE EXCEPTION in PL/pgSQL.
• For long-running operations, use database triggers + LISTEN/NOTIFY, or pair PostgREST with a separate worker that reads a job table.
• Supabase is essentially "PostgREST + GoTrue auth + a hosted UI" — the architectural ideas are the same; the convenience layer is what they charge for.

When PostgREST is the wrong tool

• APIs that are mostly orchestration of external services — PostgREST exposes one Postgres database, not a workflow engine.
• APIs that need responses shaped fundamentally differently from the database structure — you can paper over it with views and functions, but at some point a hand-written API is more honest.
• Anywhere the team is not comfortable writing SQL — the leverage all comes from the database; teams uncomfortable with relational schemas will not enjoy this.

For the use cases it fits — internal tools, admin panels, mobile-app backends with relational data, the typical SaaS CRUD layer — PostgREST collapses a class of code that's usually rewritten twice into a 50-line SQL schema.