Signing keys

-- better-supabase module: signing-keys
-- @bs-module signing-keys@1 managed
-- Ed25519 or ES256 signing keys per owner: the private JWK in Supabase Vault, one active key per owner, rotation that keeps a retired kid in the JWKS until the latest exp a signer noted, and a purge. Only the service role calls it; createSigningKeys() in better-supabase/blocks/signing-keys generates keys and signs with WebCrypto.
-- Managed by `better-supabase sql add`; re-running it overwrites this file.
-- Change it through `blocks` in better-supabase.config.ts and the module's SQL hooks.

create schema if not exists better_supabase;
grant usage on schema better_supabase to anon, authenticated, service_role;
-- Asymmetric signing keys per owner (a tenant, an app, a stream): the public
-- JWK here, the private JWK in Vault. One key per owner is active; a retired
-- key stays in the JWKS until publish_until, the latest exp a signer noted.
create table if not exists "better_supabase"."signing_keys" (
  "id" uuid primary key default gen_random_uuid(),
  "owner_type" text not null check ("owner_type" ~ '^[a-z][a-z0-9_-]{0,39}$'),
  "owner_id" text not null check (length("owner_id") between 1 and 200),
  "kid" text not null unique check (length("kid") between 1 and 200),
  "alg" text not null check ("alg" in ('Ed25519', 'ES256')),
  "public_jwk" jsonb not null check (not ("public_jwk" ? 'd')),
  "vault_secret_id" uuid not null,
  "state" text not null default 'active' check ("state" in ('active', 'retired')),
  "publish_until" timestamptz,
  "created_at" timestamptz not null default now(),
  "retired_at" timestamptz
);
create unique index if not exists signing_keys_active_idx on "better_supabase"."signing_keys" ("owner_type", "owner_id") where "state" = 'active';
create index if not exists signing_keys_owner_idx on "better_supabase"."signing_keys" ("owner_type", "owner_id", "created_at" desc);
alter table "better_supabase"."signing_keys" enable row level security;
revoke all on "better_supabase"."signing_keys" from anon, authenticated;
grant all on "better_supabase"."signing_keys" to service_role;

-- Adds a key and makes it the owner's active one; the key it replaces is
-- retired and stays published until its last token expires.
create or replace function "better_supabase"."add_signing_key"(owner_type text, owner_id text, kid text, alg text, public_jwk jsonb, private_jwk text)
returns jsonb
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_old "better_supabase"."signing_keys";
  v_row "better_supabase"."signing_keys";
  v_vault uuid;
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  if add_signing_key.private_jwk is null or length(add_signing_key.private_jwk) = 0 then
    raise exception 'a signing key needs its private JWK' using errcode = '22023', hint = 'SIGNING_KEY_INVALID';
  end if;
  perform pg_catalog.pg_advisory_xact_lock(pg_catalog.hashtextextended(
    'bs:signing:' || add_signing_key.owner_type || ':' || add_signing_key.owner_id, 0));
  update "better_supabase"."signing_keys" k set "state" = 'retired', "retired_at" = now()
  where k."owner_type" = add_signing_key.owner_type
    and k."owner_id" = add_signing_key.owner_id
    and k."state" = 'active'
  returning k.* into v_old;
  v_vault := vault.create_secret(
    add_signing_key.private_jwk,
    'bs:signing:' || add_signing_key.kid,
    'better-supabase signing key'
  );
  insert into "better_supabase"."signing_keys" ("owner_type", "owner_id", "kid", "alg", "public_jwk", "vault_secret_id")
  values (add_signing_key.owner_type, add_signing_key.owner_id, add_signing_key.kid, add_signing_key.alg,
    add_signing_key.public_jwk, v_vault)
  returning * into v_row;
  if v_old."kid" is not null then
    null;
  end if;
  null;
  return jsonb_build_object('kid', v_row."kid", 'alg', v_row."alg", 'retired', v_old."kid");
end;
$$;
revoke execute on function "better_supabase"."add_signing_key"(text, text, text, text, jsonb, text) from public, anon, authenticated;
grant execute on function "better_supabase"."add_signing_key"(text, text, text, text, jsonb, text) to service_role;

-- The owner's active key with its private JWK, or null.
create or replace function "better_supabase"."signing_key"(owner_type text, owner_id text)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  return (
    select jsonb_build_object('kid', k."kid", 'alg', k."alg", 'privateKey', ds.decrypted_secret)
    from "better_supabase"."signing_keys" k
    join vault.decrypted_secrets ds on ds.id = k."vault_secret_id"
    where k."owner_type" = signing_key.owner_type
      and k."owner_id" = signing_key.owner_id
      and k."state" = 'active'
  );
end;
$$;
revoke execute on function "better_supabase"."signing_key"(text, text) from public, anon, authenticated;
grant execute on function "better_supabase"."signing_key"(text, text) to service_role;

-- Keeps the key published until at least exp; false for an unknown kid.
create or replace function "better_supabase"."note_signing_key_exp"(kid text, exp timestamptz)
returns boolean
language plpgsql
security definer
set search_path = ''
as $$
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  update "better_supabase"."signing_keys" k set "publish_until" = note_signing_key_exp.exp
  where k."kid" = note_signing_key_exp.kid
    and (k."publish_until" is null or k."publish_until" < note_signing_key_exp.exp);
  return exists (select 1 from "better_supabase"."signing_keys" k where k."kid" = note_signing_key_exp.kid);
end;
$$;
revoke execute on function "better_supabase"."note_signing_key_exp"(text, timestamptz) from public, anon, authenticated;
grant execute on function "better_supabase"."note_signing_key_exp"(text, timestamptz) to service_role;

-- Retires a key without a replacement; false when it was not active.
create or replace function "better_supabase"."retire_signing_key"(kid text)
returns boolean
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_row "better_supabase"."signing_keys";
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  update "better_supabase"."signing_keys" k set "state" = 'retired', "retired_at" = now()
  where k."kid" = retire_signing_key.kid and k."state" = 'active'
  returning k.* into v_row;
  if v_row."kid" is null then
    return false;
  end if;
  null;
  return true;
end;
$$;
revoke execute on function "better_supabase"."retire_signing_key"(text) from public, anon, authenticated;
grant execute on function "better_supabase"."retire_signing_key"(text) to service_role;

-- The owner's JWK Set: the active key and every retired key still in use.
create or replace function "better_supabase"."signing_jwks"(owner_type text, owner_id text, grace interval default null)
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  return jsonb_build_object('keys', coalesce((
    select jsonb_agg(k."public_jwk" || jsonb_build_object('kid', k."kid", 'use', 'sig')
      order by k."created_at" desc)
    from "better_supabase"."signing_keys" k
    where k."owner_type" = signing_jwks.owner_type
      and k."owner_id" = signing_jwks.owner_id
      and (k."state" = 'active' or k."publish_until" + coalesce(grace, '5 minutes'::interval) > now())
  ), '[]'::jsonb));
end;
$$;
revoke execute on function "better_supabase"."signing_jwks"(text, text, interval) from public, anon, authenticated;
grant execute on function "better_supabase"."signing_jwks"(text, text, interval) to service_role;

-- Deletes every key of an owner, for example from on_organization_purge.
create or replace function "better_supabase"."delete_signing_keys"(owner_type text, owner_id text)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_count integer;
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  delete from "better_supabase"."signing_keys" k
  where k."owner_type" = delete_signing_keys.owner_type
    and k."owner_id" = delete_signing_keys.owner_id;
  get diagnostics v_count = row_count;
  return v_count;
end;
$$;
revoke execute on function "better_supabase"."delete_signing_keys"(text, text) from public, anon, authenticated;
grant execute on function "better_supabase"."delete_signing_keys"(text, text) to service_role;

-- Deletes retired keys no token can still need.
create or replace function "better_supabase"."purge_signing_keys"(grace interval default null)
returns integer
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_count integer;
begin
  if not (coalesce(nullif((select auth.jwt()) ->> 'role', ''), session_user::text) in ('service_role', 'postgres', 'supabase_admin')) then
    raise exception 'only the service role manages signing keys' using errcode = '42501', hint = 'SIGNING_KEYS_FORBIDDEN';
  end if;
  delete from "better_supabase"."signing_keys" k
  where k."state" = 'retired' and not (k."state" = 'active' or k."publish_until" + coalesce(grace, '5 minutes'::interval) > now());
  get diagnostics v_count = row_count;
  return v_count;
end;
$$;
revoke execute on function "better_supabase"."purge_signing_keys"(interval) from public, anon, authenticated;
grant execute on function "better_supabase"."purge_signing_keys"(interval) to service_role;

-- Deleting a key deletes its Vault secret.
create or replace function "better_supabase"."drop_signing_key_vault_secret"()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  delete from vault.secrets vs where vs.id = old."vault_secret_id";
  return null;
end;
$$;
revoke execute on function "better_supabase"."drop_signing_key_vault_secret"() from public, anon, authenticated;
drop trigger if exists "bs_signing_key_vault" on "better_supabase"."signing_keys";
create trigger "bs_signing_key_vault" after delete on "better_supabase"."signing_keys"
  for each row execute function "better_supabase"."drop_signing_key_vault_secret"();

create schema if not exists better_supabase;
create table if not exists better_supabase.modules (
  name text primary key,
  version integer not null,
  mode text not null,
  installed_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);
alter table better_supabase.modules enable row level security;
revoke all on better_supabase.modules from anon, authenticated;
grant select on better_supabase.modules to service_role;

-- better-supabase module: signing-keys
-- @bs-module-data signing-keys
-- Rows and settings a schema diff doesn't capture. Run `better-supabase sql data`
-- after the schema migration to put them in a migration.
-- Managed by `better-supabase sql add`; re-running it overwrites this file.
-- Change it through `blocks` in better-supabase.config.ts and the module's SQL hooks.

insert into better_supabase.modules (name, version, mode)
values ('signing-keys', 1, 'managed')
on conflict (name) do update
  set version = excluded.version, mode = excluded.mode, updated_at = now();