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, so 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, so there are 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, so 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, with 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, with the 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 helps with mintJwt() and setPostgrestToken()).
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 set NUXT_PUBLIC_POSTGREST_URL to http://localhost:3001.