Skip to content

Modern Docker Management, Designed for Everyone

2.7 KB
Raw Blame
-- +goose Up
CREATE TABLE IF NOT EXISTS federated_credentials (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    enabled BOOLEAN NOT NULL DEFAULT 0,
    issuer_url TEXT NOT NULL,
    audiences TEXT NOT NULL,
    subject_claim TEXT NOT NULL DEFAULT 'sub',
    subject_match TEXT NOT NULL,
    match_type TEXT NOT NULL DEFAULT 'exact',
    role_id TEXT NOT NULL,
    environment_id TEXT,
    identity_user_id TEXT NOT NULL,
    token_ttl_seconds INTEGER NOT NULL DEFAULT 900,
    last_used_at DATETIME,
    expires_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (environment_id) REFERENCES environments(id) ON DELETE SET NULL,
    FOREIGN KEY (identity_user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_federated_credentials_issuer_url ON federated_credentials(issuer_url);
CREATE INDEX IF NOT EXISTS idx_federated_credentials_enabled ON federated_credentials(enabled);
CREATE INDEX IF NOT EXISTS idx_federated_credentials_identity_user_id ON federated_credentials(identity_user_id);
CREATE INDEX IF NOT EXISTS idx_federated_credentials_role_id ON federated_credentials(role_id);

CREATE TABLE IF NOT EXISTS federated_token_replays (
    id TEXT PRIMARY KEY,
    token_hash TEXT NOT NULL UNIQUE,
    issuer_url TEXT NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME
);

CREATE INDEX IF NOT EXISTS idx_federated_token_replays_issuer_url ON federated_token_replays(issuer_url);
CREATE INDEX IF NOT EXISTS idx_federated_token_replays_expires_at ON federated_token_replays(expires_at);

ALTER TABLE users ADD COLUMN is_service_account BOOLEAN NOT NULL DEFAULT 0;
ALTER TABLE user_sessions ADD COLUMN source TEXT;
ALTER TABLE user_sessions ADD COLUMN federated_credential_id TEXT REFERENCES federated_credentials(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS idx_user_sessions_federated_credential_id ON user_sessions(federated_credential_id);

-- +goose Down
DROP INDEX IF EXISTS idx_user_sessions_federated_credential_id;
ALTER TABLE user_sessions DROP COLUMN federated_credential_id;
ALTER TABLE user_sessions DROP COLUMN source;
ALTER TABLE users DROP COLUMN is_service_account;

DROP INDEX IF EXISTS idx_federated_credentials_role_id;
DROP INDEX IF EXISTS idx_federated_credentials_identity_user_id;
DROP INDEX IF EXISTS idx_federated_credentials_enabled;
DROP INDEX IF EXISTS idx_federated_credentials_issuer_url;
DROP INDEX IF EXISTS idx_federated_token_replays_expires_at;
DROP INDEX IF EXISTS idx_federated_token_replays_issuer_url;
DROP TABLE IF EXISTS federated_token_replays;
DROP TABLE IF EXISTS federated_credentials;