41 lines
1.8 KiB
SQL
41 lines
1.8 KiB
SQL
SET search_path TO atlas, public;
|
|
|
|
CREATE TABLE extension_authorization_codes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
client_id TEXT NOT NULL,
|
|
redirect_uri TEXT NOT NULL,
|
|
code_hash TEXT NOT NULL UNIQUE,
|
|
code_challenge TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
consumed_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CHECK (expires_at > created_at),
|
|
UNIQUE (workspace_id, id)
|
|
);
|
|
|
|
CREATE TABLE extension_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE RESTRICT,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
|
client_id TEXT NOT NULL,
|
|
refresh_token_hash TEXT NOT NULL UNIQUE,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
revoked_at TIMESTAMPTZ,
|
|
last_used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (workspace_id, id)
|
|
);
|
|
CREATE INDEX ix_extension_sessions_active ON extension_sessions (workspace_id, user_id, client_id, expires_at)
|
|
WHERE revoked_at IS NULL;
|
|
|
|
ALTER TABLE extension_authorization_codes ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE extension_authorization_codes FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY workspace_isolation ON extension_authorization_codes
|
|
USING (workspace_id = app_current_workspace_id()) WITH CHECK (workspace_id = app_current_workspace_id());
|
|
ALTER TABLE extension_sessions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE extension_sessions FORCE ROW LEVEL SECURITY;
|
|
CREATE POLICY workspace_isolation ON extension_sessions
|
|
USING (workspace_id = app_current_workspace_id()) WITH CHECK (workspace_id = app_current_workspace_id());
|