PostgREST JWT Management

Manage the signing and refreshing of tokens used to access PostgREST, inside PostgREST itself.

Important

Be sure to Enable JWT Authentication in PostgREST before continuing.

Postgres

Extensions

pgcrypto provides the gen_random_uuid() and hmac() functions used to generate tokens.

db/postgres/migrations/01-extensions.sql

-- pgcrypto adds public.crypt and public.hmac, used by auth
create extension pgcrypto;

Functions

Add to the auth schema that was created in the JWT Authentication page.

This migration adds SQL functions for generating and refreshing JWTs used by PostgREST.

db/postgres/migrations/02-auth_schema.sql

begin;

-- Create auth schema
create schema if not exists auth;
set search_path to auth;

/*
refresh_tokens - Stores opaque refresh tokens issued to users, clients,
or identities. The "subject" column identifies the owner (e.g. user_id,
UUID, etc.).
*/
create table refresh_token (
  id bigserial primary key,
  subject text not null,
  token text not null,
  created_at timestamptz not null default now()
);

-- url_encode(): base64url encoding used in JWT header/payload.
create or replace function url_encode(data bytea)
returns text language sql as $$
  select translate(encode(data, 'base64'), E'+/=\n', '-_');
$$ immutable;

-- sign_raw(): manually signs a JWT payload using HS256.
create or replace function sign_raw(
  payload json,
  secret_base64 text,
  algorithm text default 'HS256'
) returns text
language plpgsql immutable as $$
declare
  alg text;
  clean_secret bytea;
begin
  alg := case algorithm
    when 'HS256' then 'sha256'
    else 'sha256'
  end;
  clean_secret := decode(secret_base64, 'base64');
  return (
    with
      header as (select url_encode(convert_to('{"alg":"' || algorithm || '","typ":"JWT"}','utf8')) as data),
      payload_enc as (select url_encode(convert_to(payload::text,'utf8')) as data),
      signables as (select header.data || '.' || payload_enc.data as data from header, payload_enc)
    select
      signables.data || '.' ||
      url_encode(public.hmac(convert_to(signables.data,'utf8'), clean_secret, alg))
    from signables
  );
end;
$$;

-- generate_user_jwt(): builds a short-lived access token (default 10 min)
create or replace function generate_user_jwt(
  subject text,
  secret text,
  role text default 'authenticated'
) returns text
language plpgsql as $$
declare
  jwt text;
begin
  select sign_raw(row_to_json(r), secret) into jwt
  from (
    select
      role as role,
      subject as sub,
      extract(epoch from now())::integer + 600 as exp
  ) r;
  return jwt;
end;
$$;

-- Issue access and refresh tokens as HTTP cookies
create or replace function issue_tokens(
  subject text,
  role text default 'authenticated'
)
returns void
language plpgsql
security definer
as $$
declare
  access_token text;
  refresh_token text;
  headers text;
begin
  select generate_user_jwt(subject, current_setting('pgrst.jwt_secret'), role)
  into access_token;

  refresh_token := public.gen_random_uuid();

  insert into refresh_token(subject, token)
  values (subject, refresh_token);

  headers := json_build_array(
    json_build_object('Set-Cookie',
      'access_token=' || access_token ||
      '; Path=/; HttpOnly; SameSite=Lax; Secure; Max-Age=600'
    ),
    json_build_object('Set-Cookie',
      'refresh_token=' || refresh_token ||
      '; Path=/; HttpOnly; SameSite=Lax; Secure; Max-Age=2592000'
    )
  )::text;

  perform set_config('response.headers', headers, true);
end;
$$;

-- Refresh expired access tokens using the stored refresh token
create or replace function refresh_tokens()
returns void
language plpgsql security definer as $$
declare
  subject text;
  access_token text;
  refresh_cookie text;
  headers text;
begin
  refresh_cookie := current_setting('request.cookies', true)::json->>'refresh_token';

  select subject into subject
  from refresh_token
  where token = refresh_cookie
    and created_at > now() - interval '30 days';

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

  select generate_user_jwt(subject, current_setting('pgrst.jwt_secret'))
  into access_token;

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

commit;

Grants

db/postgres/migrations/05-grants.sql

-- Allow anon to refresh tokens
grant usage on schema auth to anon;
grant execute on function auth.refresh_tokens() to anon;

-- If you have a service role
grant usage on schema auth to service;
grant execute on function auth.issue_tokens(text) to service;
grant all on all tables in schema api to service;
grant usage, select on all sequences in schema api to service;

▶️ Run the Migrations

cd db
bin/postgres migrate

PostgREST

Add the auth schema to PostgREST.

app/compose.yaml

postgrest:
  environment:
    PGRST_DB_SCHEMAS: api,auth # Add auth to your list of schemas

Note

Since auth is not the first schema listed in PGRST_DB_SCHEMAS, auth requests must include the HTTP header Content-Profile: auth.

Caddy

Make the refresh_tokens function public:

app/caddy/Caddyfile

# --- Public routes ---

# Public PostgREST RPC endpoints
@auth {
  path /rpc/refresh_tokens
}
handle @auth {
  reverse_proxy http://postgrest:3000
}