340 lines
14 KiB
PL/PgSQL
340 lines
14 KiB
PL/PgSQL
-- Identity, tenant ownership and component foundations.
|
|
--
|
|
-- Raw invitation, enrollment, session, recovery and component access tokens
|
|
-- must never be stored in PostgreSQL. Their SHA-256 digests are the only
|
|
-- persisted representation. TOTP and CookieCloud secrets are encrypted by the
|
|
-- application with XChaCha20-Poly1305 before they reach this schema.
|
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY,
|
|
username TEXT NOT NULL,
|
|
username_normalized TEXT NOT NULL UNIQUE,
|
|
room_id TEXT NOT NULL UNIQUE CHECK (room_id ~ '^[1-9][0-9]*$'),
|
|
role TEXT NOT NULL CHECK (role IN ('system_admin', 'user')),
|
|
status TEXT NOT NULL CHECK (status IN ('active', 'disabled')),
|
|
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),
|
|
last_totp_step BIGINT,
|
|
totp_enrolled_at TIMESTAMPTZ NOT NULL,
|
|
auth_version BIGINT NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
disabled_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- A room is an account invariant rather than editable profile data.
|
|
CREATE OR REPLACE FUNCTION prevent_user_room_id_change()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.room_id IS DISTINCT FROM OLD.room_id THEN
|
|
RAISE EXCEPTION 'a user room_id is immutable';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS users_room_id_immutable ON users;
|
|
CREATE TRIGGER users_room_id_immutable
|
|
BEFORE UPDATE OF room_id ON users
|
|
FOR EACH ROW EXECUTE FUNCTION prevent_user_room_id_change();
|
|
|
|
CREATE TABLE IF NOT EXISTS invitations (
|
|
id UUID PRIMARY KEY,
|
|
code_digest BYTEA NOT NULL UNIQUE CHECK (octet_length(code_digest) = 32),
|
|
code_prefix TEXT NOT NULL,
|
|
room_id TEXT NOT NULL CHECK (room_id ~ '^[1-9][0-9]*$'),
|
|
grant_role TEXT NOT NULL DEFAULT 'user' CHECK (grant_role IN ('system_admin', 'user')),
|
|
created_by UUID REFERENCES users(id) ON DELETE RESTRICT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
consumed_by UUID UNIQUE REFERENCES users(id) ON DELETE RESTRICT,
|
|
consumed_at TIMESTAMPTZ,
|
|
revoked_at TIMESTAMPTZ,
|
|
CHECK ((consumed_by IS NULL) = (consumed_at IS NULL)),
|
|
-- Only the one-time bootstrap path may mint the first system administrator.
|
|
CHECK (grant_role <> 'system_admin' OR created_by IS NULL)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS invitations_created_by_idx
|
|
ON invitations(created_by, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS invitations_room_idx
|
|
ON invitations(room_id, expires_at DESC);
|
|
|
|
-- Pending enrollment is deliberately separate from users. An account does not
|
|
-- exist until a valid TOTP has been confirmed. Rows are short lived and are
|
|
-- pruned opportunistically by registration calls.
|
|
CREATE TABLE IF NOT EXISTS pending_registrations (
|
|
id UUID PRIMARY KEY,
|
|
enrollment_token_digest BYTEA NOT NULL UNIQUE
|
|
CHECK (octet_length(enrollment_token_digest) = 32),
|
|
invitation_id UUID NOT NULL UNIQUE REFERENCES invitations(id) ON DELETE CASCADE,
|
|
username TEXT NOT NULL,
|
|
username_normalized TEXT NOT NULL UNIQUE,
|
|
room_id TEXT NOT NULL UNIQUE CHECK (room_id ~ '^[1-9][0-9]*$'),
|
|
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
|
|
);
|
|
CREATE INDEX IF NOT EXISTS pending_registrations_expiry_idx
|
|
ON pending_registrations(expires_at);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_sessions (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
token_digest BYTEA NOT NULL UNIQUE CHECK (octet_length(token_digest) = 32),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
last_seen_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
revoked_at TIMESTAMPTZ,
|
|
user_agent_hash BYTEA,
|
|
ip_prefix TEXT
|
|
);
|
|
CREATE INDEX IF NOT EXISTS user_sessions_active_user_idx
|
|
ON user_sessions(user_id, expires_at DESC)
|
|
WHERE revoked_at IS NULL;
|
|
CREATE INDEX IF NOT EXISTS user_sessions_expiry_idx
|
|
ON user_sessions(expires_at)
|
|
WHERE revoked_at IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS recovery_codes (
|
|
id UUID PRIMARY KEY,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
code_digest BYTEA NOT NULL CHECK (octet_length(code_digest) = 32),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
consumed_at TIMESTAMPTZ,
|
|
UNIQUE (user_id, code_digest)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS recovery_codes_available_idx
|
|
ON recovery_codes(user_id)
|
|
WHERE consumed_at IS NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS cookiecloud_credentials (
|
|
user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
|
|
host TEXT NOT NULL,
|
|
secrets_ciphertext BYTEA NOT NULL CHECK (octet_length(secrets_ciphertext) >= 16),
|
|
secrets_nonce BYTEA NOT NULL CHECK (octet_length(secrets_nonce) = 24),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- The current product assigns exactly one immutable Bilibili source to an
|
|
-- account. Keeping it as an explicit entity gives component routing a stable
|
|
-- source_id while preserving the one-account/one-room product rule.
|
|
CREATE TABLE IF NOT EXISTS live_sources (
|
|
id UUID PRIMARY KEY,
|
|
owner_user_id UUID NOT NULL UNIQUE REFERENCES users(id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL DEFAULT 'bilibili' CHECK (provider = 'bilibili'),
|
|
room_id TEXT NOT NULL UNIQUE CHECK (room_id ~ '^[1-9][0-9]*$'),
|
|
enabled BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (owner_user_id, id)
|
|
);
|
|
|
|
CREATE OR REPLACE FUNCTION enforce_live_source_account_room()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SET search_path = pg_catalog, public
|
|
AS $$
|
|
BEGIN
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM public.users
|
|
WHERE id = NEW.owner_user_id AND room_id = NEW.room_id
|
|
) THEN
|
|
RAISE EXCEPTION 'live source room_id must equal its owning account room_id';
|
|
END IF;
|
|
IF TG_OP = 'UPDATE'
|
|
AND (NEW.owner_user_id IS DISTINCT FROM OLD.owner_user_id
|
|
OR NEW.room_id IS DISTINCT FROM OLD.room_id) THEN
|
|
RAISE EXCEPTION 'live source ownership and room_id are immutable';
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS live_source_account_room ON live_sources;
|
|
CREATE TRIGGER live_source_account_room
|
|
BEFORE INSERT OR UPDATE OF owner_user_id,room_id ON live_sources
|
|
FOR EACH ROW EXECUTE FUNCTION enforce_live_source_account_room();
|
|
|
|
-- Every future OBS feature is a component instance. Component-specific state
|
|
-- belongs in dedicated tables when it becomes relational; settings remain JSON
|
|
-- so a new renderer does not require a core schema rewrite.
|
|
CREATE TABLE IF NOT EXISTS component_instances (
|
|
id UUID PRIMARY KEY,
|
|
owner_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
source_id UUID NOT NULL,
|
|
kind TEXT NOT NULL CHECK (kind ~ '^[a-z][a-z0-9_.-]{1,63}$'),
|
|
name TEXT NOT NULL,
|
|
settings JSONB NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(settings) = 'object'),
|
|
settings_version INTEGER NOT NULL DEFAULT 1 CHECK (settings_version > 0),
|
|
enabled BOOLEAN NOT NULL DEFAULT true,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (owner_user_id, id),
|
|
FOREIGN KEY (owner_user_id, source_id)
|
|
REFERENCES live_sources(owner_user_id, id) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS component_instances_owner_kind_idx
|
|
ON component_instances(owner_user_id, kind, created_at);
|
|
CREATE INDEX IF NOT EXISTS component_instances_source_idx
|
|
ON component_instances(owner_user_id, source_id, enabled);
|
|
|
|
CREATE TABLE IF NOT EXISTS component_access_tokens (
|
|
id UUID PRIMARY KEY,
|
|
owner_user_id UUID NOT NULL,
|
|
component_instance_id UUID NOT NULL,
|
|
label TEXT NOT NULL,
|
|
token_prefix TEXT NOT NULL,
|
|
token_digest BYTEA NOT NULL UNIQUE CHECK (octet_length(token_digest) = 32),
|
|
scopes TEXT[] NOT NULL DEFAULT ARRAY['events:subscribe']::TEXT[],
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
expires_at TIMESTAMPTZ,
|
|
last_used_at TIMESTAMPTZ,
|
|
revoked_at TIMESTAMPTZ,
|
|
FOREIGN KEY (owner_user_id, component_instance_id)
|
|
REFERENCES component_instances(owner_user_id, id) ON DELETE CASCADE
|
|
);
|
|
CREATE INDEX IF NOT EXISTS component_access_tokens_component_idx
|
|
ON component_access_tokens(component_instance_id, created_at DESC);
|
|
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
|
|
action TEXT NOT NULL,
|
|
target_type TEXT NOT NULL,
|
|
target_id TEXT,
|
|
metadata JSONB NOT NULL DEFAULT '{}'::jsonb CHECK (jsonb_typeof(metadata) = 'object'),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
CREATE INDEX IF NOT EXISTS audit_log_actor_time_idx
|
|
ON audit_log(actor_user_id, created_at DESC);
|
|
|
|
-- Legacy settings remain readable during the staged migration. main.rs can
|
|
-- associate and copy each row into the owner's initial danmaku component, then
|
|
-- stop writing this table without a destructive migration.
|
|
ALTER TABLE overlay_settings
|
|
ADD COLUMN IF NOT EXISTS owner_user_id UUID REFERENCES users(id) ON DELETE SET NULL;
|
|
ALTER TABLE overlay_settings
|
|
ADD COLUMN IF NOT EXISTS component_instance_id UUID REFERENCES component_instances(id)
|
|
ON DELETE SET NULL;
|
|
CREATE INDEX IF NOT EXISTS overlay_settings_owner_idx
|
|
ON overlay_settings(owner_user_id);
|
|
|
|
-- The old outbox is currently unused. Adding a nullable owner makes old rows
|
|
-- valid while ensuring any newly adopted outbox workflow can be tenant-aware.
|
|
ALTER TABLE live_session_outbox
|
|
ADD COLUMN IF NOT EXISTS owner_user_id UUID REFERENCES users(id) ON DELETE CASCADE;
|
|
|
|
-- Database-enforced tenant isolation for tables that are always accessed in a
|
|
-- known user's context. Call `set_config('app.user_id', <uuid>, true)` inside a
|
|
-- transaction before touching them. FORCE also protects against accidental
|
|
-- table-owner bypass by the runtime role.
|
|
ALTER TABLE cookiecloud_credentials ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE cookiecloud_credentials FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS cookiecloud_credentials_owner ON cookiecloud_credentials;
|
|
CREATE POLICY cookiecloud_credentials_owner ON cookiecloud_credentials
|
|
USING (user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|
|
|
|
ALTER TABLE component_instances ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE component_instances FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS component_instances_owner ON component_instances;
|
|
CREATE POLICY component_instances_owner ON component_instances
|
|
USING (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|
|
|
|
ALTER TABLE live_sources ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE live_sources FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS live_sources_owner ON live_sources;
|
|
CREATE POLICY live_sources_owner ON live_sources
|
|
USING (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|
|
|
|
ALTER TABLE component_access_tokens ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE component_access_tokens FORCE ROW LEVEL SECURITY;
|
|
DROP POLICY IF EXISTS component_access_tokens_owner ON component_access_tokens;
|
|
CREATE POLICY component_access_tokens_owner ON component_access_tokens
|
|
USING (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID)
|
|
WITH CHECK (owner_user_id = NULLIF(current_setting('app.user_id', true), '')::UUID);
|
|
|
|
-- A presented component token is the one case where the owner is not known
|
|
-- before lookup. This narrowly scoped function crosses RLS using only a
|
|
-- full-entropy SHA-256 digest, returns no secret material, and pins search_path
|
|
-- to prevent object-shadowing attacks. Once the owner is known, all component
|
|
-- reads/writes continue in a normal tenant transaction.
|
|
CREATE OR REPLACE FUNCTION lookup_component_access_token(p_token_digest BYTEA)
|
|
RETURNS TABLE (
|
|
token_id UUID,
|
|
owner_user_id UUID,
|
|
component_instance_id UUID,
|
|
scopes TEXT[]
|
|
)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
VOLATILE
|
|
SET search_path = pg_catalog, public
|
|
AS $$
|
|
DECLARE
|
|
account_id UUID;
|
|
BEGIN
|
|
-- FORCE RLS intentionally remains enabled. Enter each active account's
|
|
-- context before checking the digest instead of granting a broad bypass.
|
|
FOR account_id IN
|
|
SELECT account.id FROM public.users AS account WHERE account.status='active'
|
|
LOOP
|
|
PERFORM pg_catalog.set_config('app.user_id', account_id::TEXT, true);
|
|
RETURN QUERY
|
|
SELECT token.id, token.owner_user_id, token.component_instance_id, token.scopes
|
|
FROM public.component_access_tokens AS token
|
|
JOIN public.component_instances AS component
|
|
ON component.id = token.component_instance_id
|
|
AND component.owner_user_id = token.owner_user_id
|
|
WHERE token.owner_user_id = account_id
|
|
AND token.token_digest = p_token_digest
|
|
AND token.revoked_at IS NULL
|
|
AND (token.expires_at IS NULL OR token.expires_at > pg_catalog.now())
|
|
AND component.enabled
|
|
LIMIT 1;
|
|
IF FOUND THEN
|
|
RETURN;
|
|
END IF;
|
|
END LOOP;
|
|
END
|
|
$$;
|
|
|
|
-- Startup source enumeration is another service operation whose tenant is not
|
|
-- known in advance. This function returns only routing identifiers (never
|
|
-- CookieCloud or TOTP material) and enters each account's RLS context in turn.
|
|
CREATE OR REPLACE FUNCTION list_active_live_sources()
|
|
RETURNS TABLE (
|
|
owner_user_id UUID,
|
|
source_id UUID,
|
|
room_id TEXT
|
|
)
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
VOLATILE
|
|
SET search_path = pg_catalog, public
|
|
AS $$
|
|
DECLARE
|
|
account RECORD;
|
|
BEGIN
|
|
FOR account IN
|
|
SELECT users.id,users.room_id
|
|
FROM public.users
|
|
WHERE users.status='active'
|
|
ORDER BY users.created_at
|
|
LOOP
|
|
PERFORM pg_catalog.set_config('app.user_id', account.id::TEXT, true);
|
|
RETURN QUERY
|
|
SELECT account.id,source.id,account.room_id
|
|
FROM public.live_sources AS source
|
|
WHERE source.owner_user_id=account.id AND source.enabled;
|
|
END LOOP;
|
|
END
|
|
$$;
|