Auth Schema

Use this Postgres auth schema to enable flexible external authentication with JWT claims, typically coming from providers such as Keycloak. The schema expects your application to push verified claims JSON into the row_level_security.jwt setting before running queries. Start with this base migration, then layer Teams and RBAC in their respective chapters.

Full schema

Copy/paste the DBMate-friendly migration below to create the base schema, users table, and helper functions. It includes both migrate:up and migrate:down sections so you can roll forward or back cleanly.

-- migrate:up

-- ===========================================
-- SCHEMA
-- ===========================================
CREATE SCHEMA IF NOT EXISTS auth;

-- ===========================================
-- TABLE: auth.users
-- ===========================================
CREATE TABLE IF NOT EXISTS auth.users (
  id           int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- tight internal ID
  external_id  text UNIQUE NOT NULL,                             -- Keycloak sub
  email        text UNIQUE,
  first_name   text,
  last_name    text,
  created_at   timestamptz NOT NULL DEFAULT now(),
  updated_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_auth_users_external_id ON auth.users (external_id);
CREATE INDEX IF NOT EXISTS idx_auth_users_email       ON auth.users (email);

-- ===========================================
-- FUNCTION: auth.jwt()
-- ===========================================
CREATE OR REPLACE FUNCTION auth.jwt()
RETURNS jsonb
LANGUAGE sql
STABLE
AS $$
  SELECT
    CASE
      WHEN current_setting('row_level_security.jwt', true) IS NULL
        THEN '{}'::jsonb
      ELSE current_setting('row_level_security.jwt', true)::jsonb
    END;
$$;

-- ===========================================
-- FUNCTION: auth.me()
-- ===========================================
CREATE OR REPLACE FUNCTION auth.me()
RETURNS auth.users
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  _claims     jsonb := auth.jwt();
  _sub        text;
  _email      text;
  _first_name text;
  _last_name  text;
  _user       auth.users;
BEGIN
  _sub := _claims->>'sub';
  IF _sub IS NULL THEN
    RAISE EXCEPTION 'JWT missing sub claim';
  END IF;

  _email      := _claims->>'email';
  _first_name := COALESCE(_claims->>'given_name', _claims->>'first_name');
  _last_name  := COALESCE(_claims->>'family_name', _claims->>'last_name');

  INSERT INTO auth.users (external_id, email, first_name, last_name)
  VALUES (
    _sub,
    _email,
    _first_name,
    _last_name
  )
  ON CONFLICT (external_id) DO UPDATE
    SET email      = COALESCE(EXCLUDED.email, auth.users.email),
        first_name = COALESCE(EXCLUDED.first_name, auth.users.first_name),
        last_name  = COALESCE(EXCLUDED.last_name, auth.users.last_name),
        updated_at = now()
  RETURNING * INTO _user;

  RETURN _user;
END;
$$;

-- ===========================================
-- FUNCTION: auth.id()
-- ===========================================
CREATE OR REPLACE FUNCTION auth.id()
RETURNS int
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  _sub text;
  _id  int;
BEGIN
  _sub := auth.jwt() ->> 'sub';
  IF _sub IS NULL THEN
    RAISE EXCEPTION 'JWT missing sub claim';
  END IF;

  SELECT u.id
  INTO _id
  FROM auth.users u
  WHERE u.external_id = _sub;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'No auth.users row for sub=%, call auth.me() first', _sub;
  END IF;

  RETURN _id;
END;
$$;

-- migrate:down
DROP FUNCTION IF EXISTS auth.id();
DROP FUNCTION IF EXISTS auth.me();
DROP FUNCTION IF EXISTS auth.jwt();
DROP TABLE IF EXISTS auth.users;
DROP SCHEMA IF EXISTS auth;

The rest of this chapter calls out the important pieces if you want to tweak or extend the schema.

• auth.users stores one row per Keycloak subject (sub). We keep both the opaque external_id and a dense id for joins inside the app schema.
• The optional indexes make typical lookup paths explicit (Keycloak subjects or email).

Example usage

Once the schema is installed, start each request or job by setting the JWT and calling the helpers:

BEGIN;

SET LOCAL row_level_security.jwt = $${
  "sub": "1234567890abcdef",
  "email": "[email protected]",
  "given_name": "Daniel",
  "family_name": "Purton"
}$$;

SELECT auth.jwt();
SELECT * FROM auth.me();                       -- upsert user row
SELECT auth.id();                              -- dense internal id for joins

COMMIT;

Helper functions

auth.jwt()

CREATE OR REPLACE FUNCTION auth.jwt()
RETURNS jsonb
LANGUAGE sql    
STABLE
AS $$
  SELECT
    CASE
      WHEN current_setting('row_level_security.jwt', true) IS NULL
        THEN '{}'::jsonb
      ELSE current_setting('row_level_security.jwt', true)::jsonb
    END;
$$;

auth.jwt() reads the JSON claims payload injected with SET LOCAL row_level_security.jwt = '<claims>'. Returning {} instead of NULL lets calling functions safely destructure the result with ->> accessors.

auth.me()

CREATE OR REPLACE FUNCTION auth.me()
RETURNS auth.users
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
  _claims     jsonb := auth.jwt();
  _sub        text;
  _email      text;
  _first_name text;
  _last_name  text;
  _user       auth.users;
BEGIN
  _sub := _claims->>'sub';
  IF _sub IS NULL THEN
    RAISE EXCEPTION 'JWT missing sub claim';
  END IF;

  _email      := _claims->>'email';
  _first_name := COALESCE(_claims->>'given_name', _claims->>'first_name');
  _last_name  := COALESCE(_claims->>'family_name', _claims->>'last_name');

  INSERT INTO auth.users (external_id, email, first_name, last_name)
  VALUES (
    _sub,
    _email,
    _first_name,
    _last_name
  )
  ON CONFLICT (external_id) DO UPDATE
    SET email      = COALESCE(EXCLUDED.email, auth.users.email),
        first_name = COALESCE(EXCLUDED.first_name, auth.users.first_name),
        last_name  = COALESCE(EXCLUDED.last_name, auth.users.last_name),
        updated_at = now()
  RETURNING * INTO _user;

  RETURN _user;
END;
$$;

auth.me() upserts the user row based on the current JWT claims. When you add the Teams schema, replace this helper with the extended version from that chapter so you can join or create teams. The RBAC chapter then layers roles and permissions on top.

auth.id()

CREATE OR REPLACE FUNCTION auth.id()
RETURNS int
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  _sub text;
  _id  int;
BEGIN
  _sub := auth.jwt() ->> 'sub';
  IF _sub IS NULL THEN
    RAISE EXCEPTION 'JWT missing sub claim';
  END IF;

  SELECT u.id
  INTO _id
  FROM auth.users u
  WHERE u.external_id = _sub;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'No auth.users row for sub=%, call auth.me() first', _sub;
  END IF;

  RETURN _id;
END;
$$;

auth.id() bridges your request context with database‑side RLS policies. After you call auth.me() once, auth.id() gives you the dense bigint to use inside policies (current_setting('row_level_security.user_id') := auth.id()), auditing tables, or other helper functions.

Usage notes

• Set row_level_security.jwt for every transaction; leaving it NULL makes helper functions throw, which protects you from running queries without an authenticated user.
• When you add more profile fields, extend both the table and the auth.me() upsert. The conflict handler is already wired to COALESCE so optional attributes remain untouched.
• This schema is intentionally slim: keep organization or permission data in separate modules and join using auth.id() when building row‑level policies.

Example transaction

Call auth.me() inside a transaction after setting the JWT claims:

BEGIN;

SET LOCAL row_level_security.jwt = $${
  "sub": "1234567890abcdef",
  "email": "[email protected]",
  "given_name": "Daniel",
  "family_name": "Purton"
}$$;

SELECT * FROM auth.me();
SELECT auth.id();

COMMIT;