Authentication

This page shows to implement secure, JWT-based authentication using PostgreSQL functions.

📦 1. Install pgjwt

To enable JWT signing inside Postgres, you’ll need to install the pgjwt extension.

Clone the Repository

git clone https://github.com/michelp/pgjwt postgres/pgjwt

Update Your Dockerfile

Edit postgres/Dockerfile to build and install the extension:

RUN apt-get update && apt-get install -y \
 build-essential \
 postgresql-server-dev-17

# pgjwt - used by auth schema
COPY ./pgjwt /pgjwt
WORKDIR /pgjwt
RUN make && make install

WORKDIR /var/lib/postgresql

Then rebuild:

docker compose build postgres

➡️ 2. Add Migrations

📚 Load Required Extensions

Add this to a migration file like 01-extensions.sql:

-- pgcrypto adds public.crypt used in auth.encrypt_pass
create extension pgcrypto;

-- pgjwt adds public.sign used in auth.generate_access_token
create extension pgjwt;

⚠️ Do not wrap this file in a BEGIN/COMMIT block — create extension is non-transactional.

🏗 Create the Auth Schema

Create a new migration file, e.g. 02-create_auth_schema.sql.

-- 02-create_auth_schema.sql
begin;

-- Create auth schema and tables
create schema auth;

create table auth.user (
  username text primary key check (length(username) >= 3),
  password text not null check (length(password) < 512),
  role name not null check (length(role) < 512)
);

create table auth.refresh_token (
  id bigint generated always as identity primary key,
  created_at timestamp not null default now(),
  token text,
  username text
);

-- Enforce that roles exist in pg_roles
create function auth.check_role_exists() returns trigger
language plpgsql as $$
begin
  if not exists (select 1 from pg_roles where rolname = new.role) then
    raise foreign_key_violation using message = 'unknown database role: ' || new.role;
    return null;
  end if;
  return new;
end
$$;

create constraint trigger ensure_user_role_exists
after insert or update on auth.user
for each row execute procedure auth.check_role_exists();

-- Encrypt passwords on insert/update
create function auth.encrypt_pass() returns trigger
language plpgsql as $$
begin
  if tg_op = 'INSERT' or new.password <> old.password then
    new.password := crypt(new.password, gen_salt('bf'));
  end if;
  return new;
end
$$;

create trigger encrypt_pass
before insert or update on auth.user
for each row execute procedure auth.encrypt_pass();

-- Generate JWT access tokens
create function auth.generate_access_token(
  role_ text, user_ text, secret text
) returns text
language plpgsql as $$
declare
  access_token text;
begin
  select public.sign(row_to_json(r), secret) into access_token from (
    select role_ as role, user_ as username,
      extract(epoch from now())::integer + 600 as exp
  ) r;
  return access_token;
end;
$$;

-- Login endpoint
create function auth.login(user_ text, pass text) returns void
language plpgsql security definer as $$
declare
  access_token text;
  headers text;
  refresh_token text;
  role_ name;
begin
  select role into role_
  from auth.user
  where username = user_
    and password = public.crypt(pass, password);

  if role_ is null then
    raise sqlstate 'PT401' using message = 'Invalid user or password';
  end if;

  select auth.generate_access_token(role_, user_, current_setting('pgrst.jwt_secret')) into access_token;

  refresh_token := public.gen_random_uuid();
  insert into auth.refresh_token (token, username) values (refresh_token, user_);

  headers := '[' ||
    '{"Set-Cookie": "access_token=' || access_token || '; Path=/; HttpOnly;"},' ||
    '{"Set-Cookie": "refresh_token=' || refresh_token || '; Path=/rpc/refresh_token; HttpOnly;"}' ||
  ']';
  perform set_config('response.headers', headers, true);
end;
$$;

-- Logout endpoint
create function auth.logout() returns void
language plpgsql security definer as $$
declare headers text;
begin
  headers := '[' ||
    '{"Set-Cookie": "access_token=; path=/; expires=Thu, 01 Jan 1970 00:00:00 GMT;"},' ||
    '{"Set-Cookie": "refresh_token=; path=/; expires=Thu, 01 Jan 1970 00:00:00 GMT;"}' ||
  ']';
  perform set_config('response.headers', headers, true);
end;
$$;

-- Refresh token endpoint
create function auth.refresh_token() returns void
language plpgsql security definer as $$
declare
  user_ text;
  access_token text;
  headers text;
  refresh_token_ text;
  role_ text;
begin
  refresh_token_ := current_setting('request.cookies', true)::json->>'refresh_token';

  select username into user_
  from auth.refresh_token
  where token = refresh_token_
    and created_at > now() - interval '30 days';

  if user_ is null then
    raise sqlstate 'PT401' using message = 'Invalid or expired refresh token';
  end if;

  select role into role_ from auth.user where username = user_;
  if role_ is null then
    raise sqlstate 'PT401' using message = 'Unknown user';
  end if;

  select auth.generate_access_token(role_, user_, current_setting('pgrst.jwt_secret')) into access_token;

  headers := '[{"Set-Cookie": "access_token=' || access_token || '; Path=/; HttpOnly;"}]';
  perform set_config('response.headers', headers, true);
end;
$$;

commit;

👮 Grant Permissions

Add a migration script such as 98-roles.sql:

create role basic_subscriber;

Add another migration script such as 99-grants.sql:

begin;

-- Anon can access the auth functions
grant usage on schema auth to anon;
grant execute on function auth.login(text, text) to anon;
grant execute on function auth.logout() to anon;
grant execute on function auth.refresh_token() to anon;

-- Setup initial permissions for basic_subscriber
grant basic_subscriber to authenticator;
grant usage on schema api to basic_subscriber;
-- Grant more privileges here
-- grant select, insert, update on api.customer to basic_subscriber;

commit;

▶️ 3. Run the Migrations

bin/postgres migrate

4. Add the Auth Schema to PostgREST

Add the auth schema to Postgres in compose.yaml:

PGRST_DB_SCHEMAS: api,auth

📝 If auth is not the first schema in your PGRST_DB_SCHEMAS, requests must be sent with the header Content-Profile: auth.

Restart PostgREST to load the new schema.

5. Add an Authorization header in PostgREST endpoints

The Authorization header is set to the access_token cookie value (if present).

Add to caddy/Caddyfile:

  # Set the Authorization header if 'access_token' cookie is non-empty
  @hasCookie {
    header_regexp Cookie access_token=.+
  }
  route @hasCookie {
    header Authorization "Bearer {cookie.access_token}"
  }

✅ Usage

Now we'll test the endpoints, using curl.

Create a user

Create a postgres/seed/seed_demo.sql:

insert into auth.user (username, password, role) values (
  'demo', 'demo', 'basic_subscriber'
);

Run the script:

bin/postgres psql < postgres/seed/seed_demo.sql

Login

curl --show-headers -X POST \
  -H 'Content-Profile: auth' \
  -H 'Content-Type: application/json' \
  --data '{"user_": "demo", "pass": "demo"}' \
  http://localhost:8000/rpc/login

--show-headers makes the response headers appear in the curl output, which should include Set-Cookie headers access_token and refresh_token:

HTTP/1.1 204 No Content
Content-Range: 0-0/*
Date: Fri, 04 Jul 2025 23:58:10 GMT
Server: postgrest/12.2.8
Set-Cookie: access_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYmFzaWNfc3Vic2NyaWJlciIsInVzZXJuYW1lIjoiZGVtbyIsImV4cCI6MTc1MTY3NDA5MX0.6SRT0g1BlqMAkNWxk5VuAIuCHuk03EtaOnjO5hoVtpM; Path=/; HttpOnly;
Set-Cookie: refresh_token=028caa10-d087-41d8-8d8c-62d60bb419b5; Path=/rpc/refresh_token; HttpOnly;

Use the access_token in subsequent requests, in the Authorization header:

curl -H "Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYmFzaWNfc3Vic2NyaWJlciIsInVzZXJuYW1lIjoiZGVtbyIsImV4cCI6MTc1MTY3NDA5MX0.6SRT0g1BlqMAkNWxk5VuAIuCHuk03EtaOnjO5hoVtpM" http://localhost:8000/rest/task

Logout

Logout clears the cookies:

$ curl --show-headers -X POST -H 'Content-Profile: auth' -H "Content-Type: application/json" http://localhost:8000/rpc/logout
HTTP/1.1 204 No Content
Content-Range: 0-0/*
Date: Sat, 05 Jul 2025 00:03:44 GMT
Server: postgrest/12.2.8
Set-Cookie: access_token=; path=/; expires=Thu, 01 Jan 1970 00:00:00 GMT;
Set-Cookie: refresh_token=; path=/; expires=Thu, 01 Jan 1970 00:00:00 GMT;
Via: 1.1 Caddy

Refresh token

Pass the refresh token to re-authenticate:

$ curl --show-headers -X POST -H 'Content-Profile: auth' -H 'Cookie: refresh_token='c1d54797-ecfa-4ecb-a6dc-bb4ff2ef803a'; HttpOnly' http://localhost:8000/rpc/refresh_token
HTTP/1.1 204 No Content
Content-Range: 0-0/*
Date: Sat, 05 Jul 2025 00:13:51 GMT
Server: postgrest/12.2.8
Set-Cookie: access_token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYmFzaWNfc3Vic2NyaWJlciIsInVzZXJuYW1lIjoiZGVtbyIsImV4cCI6MTc1MTY3NTAzMX0.kPvJXJNiTo1TZEvShhRFWS6qLfMUqd_AyKrjk7Gs5Io; Path=/; HttpOnly;
Via: 1.1 Caddy

References:

• Postgrest: Authentication
• Postgrest: SQL User Management