32 lines
1.4 KiB
SQL
32 lines
1.4 KiB
SQL
-- Short-lived, tenant-owned TOTP replacement enrollments.
|
|
--
|
|
-- The current TOTP secret remains authoritative until the replacement secret
|
|
-- has produced a valid code. Raw enrollment tokens are never persisted, and
|
|
-- pending secrets use the same authenticated encryption boundary as account
|
|
-- TOTP secrets.
|
|
|
|
CREATE TABLE IF NOT EXISTS pending_totp_resets (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
enrollment_token_digest BYTEA NOT NULL UNIQUE
|
|
CHECK (octet_length(enrollment_token_digest) = 32),
|
|
totp_secret_ciphertext BYTEA NOT NULL
|
|
CHECK (octet_length(totp_secret_ciphertext) >= 16),
|
|
totp_secret_nonce BYTEA NOT NULL
|
|
CHECK (octet_length(totp_secret_nonce) = 24),
|
|
failed_attempts INTEGER NOT NULL DEFAULT 0 CHECK (failed_attempts >= 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
UNIQUE (user_id, id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS pending_totp_resets_expiry_idx
|
|
ON pending_totp_resets(expires_at);
|
|
|
|
ALTER TABLE pending_totp_resets ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE pending_totp_resets FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS pending_totp_resets_owner ON pending_totp_resets;
|
|
CREATE POLICY pending_totp_resets_owner ON pending_totp_resets
|
|
USING (user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|