mathiasandClaude Opus 4.8
0c9531a9b8
Add the video_connections table (data-model VIDEO_CONNECTION) with FORCE row-level security keyed off tapir.current_user_id, identical to migration 003's per-user isolation pattern — connections are user-owned data and must be isolated at the DB layer, not only by application WHERE clauses. Store methods (UpsertConnection / ConnectionsForUser / DeleteConnection) all route through withUser so RLS scopes every access. UpsertConnection is idempotent on (user_id, provider). The OAuth refresh token never lives here; token_ref is the opaque SecretStore reference. Extend the RLS isolation proof to cover video_connections: seeded per user, included in the deny-all + scoped-read assertions, and added to the cross-user write-invisibility and survivor checks. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
31 lines
1.6 KiB
SQL
31 lines
1.6 KiB
SQL
-- Migration 005: video_connections — a user's connected video account
|
|
-- (data-model.md VIDEO_CONNECTION). The OAuth refresh token never lives here;
|
|
-- token_ref is the opaque SecretStore reference that resolves to it. Revocation
|
|
-- flips status, it does not delete the row (history is kept).
|
|
--
|
|
-- One connection per (user, provider): re-connecting the same provider upserts
|
|
-- in place (the connect flow's ON CONFLICT (user_id, provider) target).
|
|
CREATE TABLE video_connections (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
provider TEXT NOT NULL,
|
|
provider_account TEXT,
|
|
token_ref TEXT NOT NULL,
|
|
status TEXT NOT NULL,
|
|
connected_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT video_connections_user_provider_unique UNIQUE (user_id, provider)
|
|
);
|
|
|
|
CREATE INDEX idx_video_connections_user_id ON video_connections(user_id);
|
|
|
|
-- Per-user isolation, identical to migration 003's pattern: this is user-owned
|
|
-- data, so user A must never read or write user B's connections even with a wrong
|
|
-- application-level WHERE. ENABLE + FORCE so the table owner (tapir) is subject to
|
|
-- the policy too; the policy keys off the per-request GUC tapir.current_user_id
|
|
-- set by the store's withUser helper. An unset GUC yields NULL -> deny-all.
|
|
ALTER TABLE video_connections ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE video_connections FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY video_connections_isolation ON video_connections
|
|
FOR ALL
|
|
USING (user_id = current_setting('tapir.current_user_id', true)::uuid);
|