Advanced Template: API Key Authentication#

Scope: advanced template only. This subsystem is scaffolded by pgmi init --template advanced (membership/08-api-keys.sql). It is not part of pgmi core and is not included in the basic template.
These API keys authenticate callers of the APIs generated by the advanced template. They are unrelated to PostgreSQL connection credentials or to any authentication used by the pgmi CLI itself.

Machine-to-machine authentication for agents, MCP clients, and CI pipelines calling your deployed application’s REST/RPC/MCP endpoints.

Key format#

{prefix}_{key_id}_{secret}

prefix — short tenant-configurable label (default pgmi), sourced from the pgmi.api_key_prefix GUC. Set via ALTER DATABASE mydb SET pgmi.api_key_prefix = 'myapp' or --param api_key_prefix=myapp on deploy.
key_id — 8-char alphanumeric identifier stored unhashed for O(1) lookup. Never secret on its own.
secret — 32 bytes of random material encoded as URL-safe base64.

Only SHA-256(full_key) is persisted. The raw key is returned exactly once, at creation, and never recoverable.

Lifecycle functions#

All mutations flow through SECURITY DEFINER functions so the membership and activity checks cannot be bypassed by direct DML (even from the admin role, which holds SELECT only on membership.api_key).

Function	Purpose
`membership.create_api_key(user_id, organization_id, display_name, expires_at?, activated_at?)`	Issue a new key. Returns `out_api_key` (show to the user exactly once), `out_key_id`, `out_object_id`. Also inserts a `membership.user_identity` row with `provider='apikey'` so the existing JWT/OIDC auth pipeline resolves the key to the owning user.
`membership.validate_api_key(raw_key)`	Validate at request time. Returns `is_valid`, `user_id`, `organization_id`, `key_id`, `reason`. Updates `last_used_at` on success. Hash-safe compare (no short-circuit on partial matches).
`membership.disable_api_key(key_id)` / `enable_api_key(key_id)`	Temporarily block / restore. Reversible.
`membership.revoke_api_key(key_id)`	Permanent. Deletes the matching `user_identity` row so the key cannot be re-enabled into a working identity.

Caller authorization for issuing keys#

create_api_key is SECURITY DEFINER and returns a working credential for the target user, so the caller — not only the target — must be authorized. membership.can_create_api_key(user_id, organization_id) gates it first: the current identity must be a platform superuser, minting for itself in an org it actively belongs to, or an admin or owner of the organization provisioning a key for one of its members. A plain reader/contributor member cannot mint another member’s key (that would hand over an impersonation credential), and a non-member cannot mint anywhere. Unauthorized callers get the same P0404 “Organization not found” as a missing org, so the function is not a cross-tenant existence oracle. Like the lifecycle guard, it fails closed for identity-less sessions.

Tenant scoping of the lifecycle functions#

disable_api_key, enable_api_key, and revoke_api_key are SECURITY DEFINER, so they run as the table owner and row-level security does not constrain them. Tenant isolation comes from membership.can_manage_api_key(key_id), which every one of them calls first: the current identity must be a platform superuser, or an active member of the key’s organization. Anything else raises P0404 — the same “not found” a nonexistent key raises, so the functions cannot be used as a cross-tenant key-existence oracle.

The guard fails closed: a session with no resolvable identity (no auth.idp_subject) manages no keys. Operator scripts that run as the database owner should either adopt an identity first —

SELECT set_config('auth.idp_subject', 'google|alice-001', true);
SELECT membership.revoke_api_key('a1b2c3d4e5f6');

— or update membership.api_key directly, which the owner may do regardless of grants.

Rejection reasons returned by `validate_api_key`#

`reason`	Meaning
`malformed key`	Wrong prefix, missing parts, or NULL input.
`unknown key`	`key_id` not in the table (or already soft-deleted).
`invalid secret`	Key material does not match the stored hash.
`key is disabled` / `key is revoked`	Status enforcement.
`key not yet active`	`activated_at` is in the future.
`key expired`	`expires_at` has passed.
`user is inactive`	`membership."user".is_active = false`.
`organization is inactive`	`membership.organization.is_active = false`.

Deactivating a user or organization invalidates every key they own immediately — no per-key revoke required.

Integrating with the auth pipeline#

membership.create_api_key inserts a user_identity row with idp_provider='apikey' and idp_subject_id = key_id. The auth gateway extracts {provider}|{subject_id} from the validated key and sets the session GUC auth.idp_subject:

-- Gateway / transport layer
SELECT is_valid, user_id, key_id
INTO v_valid, v_user, v_key_id
FROM membership.validate_api_key(:authorization_header);

IF v_valid THEN
    PERFORM set_config('auth.idp_subject', 'apikey|' || v_key_id, true);
    -- api.current_user_id() now resolves to v_user for the rest of the session.
END IF;

This means RLS policies keyed on api.current_user_id() or auth.idp_subject work identically for API-key sessions and interactive JWT/OIDC sessions.

Security posture#

Hash-safe compare — membership.eq_hash_safe(text, text) XOR-folds byte-wise so the comparison does not short-circuit on the first differing byte. PL/pgSQL cannot guarantee true constant time, but because it compares SHA-256 hashes, any residual timing leak reveals at most hash-prefix similarity, never raw key bytes. A known key_id (public) does not help an attacker binary-search the hash.
No admin write path — INSERT, UPDATE, DELETE, TRUNCATE revoked on membership.api_key from the admin role. All mutations route through the SECURITY DEFINER functions above.
Inactive principal rejection — checked on every validation, not just at issue time.
RLS on membership.api_key — customers see their own organization’s keys; admin role has read access for ops triage.
No PUBLIC grant in membership — PostgreSQL grants EXECUTE to PUBLIC on every function it creates, so deploy.sql revokes it across the schema once every file has run. What each role can reach is an explicit grant: the customer role gets create_api_key, disable_api_key, enable_api_key and revoke_api_key for self-service, and nothing else. validate_api_key resolves a raw key to its owner and org, and generate_api_key_material mints secret material — both are gateway and admin surface only.

Operational concerns#

Rotation: the only supported path is revoke + create. There is no rotate_api_key helper; issuing a fresh key gives the caller full control of the transition window.
Audit: last_used_at is updated on every successful validation. Sort descending to find stale keys. Exchange tables (rest_exchange, rpc_exchange, mcp_exchange) log every authenticated request if autoLog=true on the handler.
Secrets in logs: pgmi does not log raw keys. Exception paths store sqlstate=<code> detail=<LEFT(SQLERRM,200)> in the exchange tables, not raw SQLERRM, so keys embedded in error messages by a misbehaving handler do not leak.

docs/SECURITY.md — broader authentication model, RLS, and trust-boundary notes.
MCP gateway — MCP tool authentication via p_context->>'user_id'.
internal/scaffold/templates/advanced/membership/08-api-keys.sql — source of truth.
internal/scaffold/templates/advanced/membership/__test__/test_api_keys.sql — lifecycle, edge-case, expiry, and inactive-principal tests.