Local Postgres & PostgREST
This page assumes nothing: not that you have Postgres installed, not that you've used PostgREST before, not that Docker is already running. By the end you'll have a real database and a real PostgREST API on your machine, and you'll have queried it with curl before ever touching Nuxt.
The opinionated take: don't install Postgres natively, and don't install PostgREST as a binary. Run both in Docker. It's disposable — if you break it, docker compose down -v wipes it and you start clean in seconds. This is also exactly what this module's own tests and playground do, so it's a setup that's continuously verified to work.
What these two things actually are
- Postgres is the database. It stores your data in tables and enforces rules about who can read or write which rows (that last part — row-level security — is what makes this whole stack interesting for auth).
- PostgREST is a standalone web server that sits in front of Postgres and turns your database schema into a REST API automatically. You don't write API endpoints — PostgREST inspects your tables and generates
GET /todos,POST /todos, filtering, pagination, etc. for you. Your app never connects to Postgres directly; it talks to PostgREST over HTTP.
They're kept as two separate Compose files below, on purpose: one Postgres instance is meant to back any number of databases, and each database gets its own PostgREST container. That's not a hypothetical — it's the second half of this guide.
Prerequisites
Install Docker Desktop (or OrbStack on macOS) and make sure it's actually running:
docker --version
If that prints a version instead of "command not found," you're set.
Create the Postgres Compose file
Make a project folder with these two files. This one is the shared, long-lived instance — you won't touch it again when you add more databases later.
services:
db:
image: postgres:18
environment:
POSTGRES_PASSWORD: postgres
ports: ['5432:5432']
volumes:
- ./init.sql:/docker-entrypoint-initdb.d/init.sql:ro
healthcheck:
test: ['CMD-SHELL', 'pg_isready -U postgres']
interval: 2s
retries: 20
init.sql runs once, the first time the db volume is created — it's where the roles PostgREST needs come from, plus a table to try it out on:
-- Roles PostgREST switches between per-request, based on the JWT it's given.
-- Roles are cluster-wide in Postgres, not per-database, so this file only
-- needs to run once, ever — every database you add later reuses these.
create role anon nologin; -- no token / logged out
create role authenticated nologin; -- valid user JWT
create role service_role nologin bypassrls; -- your backend, bypasses RLS
create role authenticator noinherit login password 'authenticator'; -- who PGRST_DB_URI connects as
grant anon, authenticated, service_role to authenticator;
grant usage on schema public to anon, authenticated, service_role;
-- A table to actually query
create table todos (
id bigint generated always as identity primary key,
title text not null
);
grant select, insert on todos to anon;
insert into todos (title) values ('Buy milk');
authenticator is a technical detail, not a "real" user: it's the only role PostgREST itself logs into Postgres as, and it can only ever act as one of the three roles above via set role, on Postgres's authority — never a role of PostgREST's own choosing.
Create the PostgREST Compose file
This is the piece that's per-database. Keep it in a separate file from Postgres — you'll add another one just like it for every database you want exposed.
services:
postgrest:
image: postgrest/postgrest:v16.3
depends_on:
db:
condition: service_healthy
environment:
PGRST_DB_URI: postgres://authenticator:authenticator@db:5432/postgres
PGRST_DB_SCHEMAS: public
PGRST_DB_ANON_ROLE: anon
PGRST_JWT_SECRET: replace-with-a-secret-of-at-least-32-characters
# Needed for the healthcheck below: --ready checks the admin server, which only
# starts if given a port, and only accepts --ready's probe on a literal host
# (not the default "all interfaces"). It isn't published, so this is internal-only.
PGRST_ADMIN_SERVER_PORT: 3002
PGRST_ADMIN_SERVER_HOST: localhost
ports: ['3001:3000']
healthcheck:
test: ['CMD', 'postgrest', '--ready']
interval: 2s
retries: 20
Start both together
docker compose -f docker-compose.yml -f docker-compose.postgrest.yml up -d --wait
Passing both files with -f merges them into one Compose project, so depends_on: db resolves and --wait blocks until both healthchecks pass — no "connection refused" races. They're still separate files on disk; Compose is just running them together for convenience.
Check Postgres is really up
docker compose exec db psql -U postgres -c 'select * from todos;'
You should see the one seeded row (Buy milk). If this fails, PostgREST will fail too — fix this first before going further.
Check PostgREST is really up
curl http://localhost:3001/
This returns PostgREST's auto-generated OpenAPI description as JSON — a wall of schema text. That's fine; it means PostgREST connected to Postgres and introspected it successfully. Now hit the actual table:
curl http://localhost:3001/todos
[{"id":1,"title":"Buy milk"}]
That request had no Authorization header, so PostgREST ran it as anon — which is exactly why the grant select ... to anon line in init.sql mattered. Remove that grant and this same request returns [].
One Postgres, many databases
This is the actual point of keeping the two files separate: one Postgres instance can back any number of databases, and each database gets its own PostgREST container — its own port, its own JWT secret if you want one, and (once you put a reverse proxy in front of it) its own domain. You don't stand up a second Postgres per project or per customer; you add a database to the one you already have.
Add a second database on the same instance — roles already exist cluster-wide, so this is all it takes:
docker compose exec db psql -U postgres -c "create database acme;"
docker compose exec db psql -U postgres -d acme -c "grant usage on schema public to anon, authenticated, service_role;"
docker compose exec db psql -U postgres -d acme -c "create table todos (id bigint generated always as identity primary key, title text not null); grant select, insert on todos to anon; insert into todos (title) values ('Acme first task');"
Then a second PostgREST file pointing at it — same shape as before, different database name, different port:
services:
postgrest-acme:
image: postgrest/postgrest:v16.3
depends_on:
db:
condition: service_healthy
environment:
PGRST_DB_URI: postgres://authenticator:authenticator@db:5432/acme
PGRST_DB_SCHEMAS: public
PGRST_DB_ANON_ROLE: anon
PGRST_JWT_SECRET: replace-with-a-different-secret-of-32-plus-chars
PGRST_ADMIN_SERVER_PORT: 3004
PGRST_ADMIN_SERVER_HOST: localhost
ports: ['3003:3000']
healthcheck:
test: ['CMD', 'postgrest', '--ready']
interval: 2s
retries: 20
Bring it up alongside the other two files:
docker compose -f docker-compose.yml -f docker-compose.postgrest.yml -f docker-compose.acme.yml up -d --wait
Both APIs are now live, each scoped to its own database:
curl http://localhost:3001/todos # [{"id":1,"title":"Buy milk"}]
curl http://localhost:3003/todos # [{"id":1,"title":"Acme first task"}]
Repeat that pattern — one create database, one Compose file, one port — for every database you add. In production, the last step is a reverse proxy (Caddy, nginx, Traefik) mapping a domain to each PostgREST container's port; that's ordinary HTTP routing and outside PostgREST's own concerns, so it isn't covered here.
Where JWTs fit in
PostgREST never sees "users" — it only sees a JWT's role claim, which picks the Postgres role for that one request, and everything else in the payload is fair game for row-level-security policies to read. There's no session, no cookie store, no login endpoint inside PostgREST itself; minting and verifying JWTs is entirely your app's job (see Authentication for how this module wires that up with nuxt-auth-utils).
You can mint a throwaway token to try authenticated locally with plain Node — no dependencies:
node -e "
const c=require('crypto'),b=s=>Buffer.from(s).toString('base64url'),
h=b(JSON.stringify({alg:'HS256',typ:'JWT'})),
p=b(JSON.stringify({role:'authenticated'})),
sig=c.createHmac('sha256','replace-with-a-secret-of-at-least-32-characters').update(h+'.'+p).digest('base64url');
console.log(h+'.'+p+'.'+sig)
"
curl http://localhost:3001/todos -H "Authorization: Bearer <paste the token>"
Troubleshooting
password authentication failed for user "authenticator"—init.sqlonly runs the first time thedbvolume is created. If you edited it after the firstdocker compose up, rundocker compose down -v(the-vdrops the volume) and start again.JWT secret must be at least 32 characters— PostgREST refuses to boot with a shortPGRST_JWT_SECRET. Not a real security boundary locally, but PostgREST enforces it anyway; pad the string out.bind: address already in useon port 5432 — you already have a Postgres running natively (common on macOS via Homebrew) or a previous Compose project still up. Either stop that service, or remap the port (e.g.'5433:5432') and updatePGRST_DB_URI's port to match.relation "todos" does not exist— same root cause as the first bullet:init.sqldidn't run against a fresh volume.docker compose down -v && docker compose up -d --wait.curl: (7) Failed to connect— checkdocker compose ps; ifpostgrestshows as unhealthy, rundocker compose logs postgrest— it almost always means it couldn't reachdbyet,PGRST_DB_URIis wrong, or (for a database you just added) the database doesn't exist yet.
Resetting
docker compose -f docker-compose.yml -f docker-compose.postgrest.yml down -v # stop and wipe everything, including the Postgres volume
docker compose -f docker-compose.yml -f docker-compose.postgrest.yml up -d --wait
docker-compose.yml (Postgres) and docker-compose.postgrest.yml (PostgREST), seeded by db/seed.sql. It's a good base for bug reproductions, and it's what pnpm db:up / pnpm test run against.Once curl http://localhost:3001/todos works, move on to Installation and point postgrest.url at http://localhost:3001.