sc-idp/lib/schema.js

// Idempotent DDL for the saltcorn-idp plugin tables. Portable subset: TEXT for
// strings/JSON/timestamps, INTEGER for booleans; no JSONB. Sealed key/secret
// material is stored as hex TEXT (see crypto.sealText). Table names come from
// constants so there is one source of truth.
//
// MULTI-TENANCY: raw CREATE statements must be schema-qualified with
// db.getTenantSchemaPrefix() (e.g. "t1".) so tables land in the current tenant's
// Postgres schema -- otherwise unqualified DDL hits the wrong schema while
// Saltcorn's db.select reads the tenant-qualified one. On SQLite the prefix is
// "" (single schema), so the dev MAIN/TEST instances are unaffected.

const db = require("@saltcorn/data/db");

const { TABLE_ENV, TABLE_KEYS, TABLE_OIDC_STORE, TABLE_GROUPS, TABLE_GROUP_MEMBERS, TABLE_CLIENTS, TABLE_SAML, TABLE_SAML_SPS, TABLE_LDAP_SERVICE } = require("./constants");


const createIdpEnv = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_ENV} (
            env_id          TEXT PRIMARY KEY,
            env_label       TEXT,
            created_at      TEXT NOT NULL,
            bootstrapped_at TEXT
        )
    `);
};


const createIdpKeys = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_KEYS} (
            kid                TEXT PRIMARY KEY,
            alg                TEXT NOT NULL,
            public_jwk         TEXT NOT NULL,
            private_ciphertext TEXT NOT NULL,
            private_iv         TEXT NOT NULL,
            private_tag        TEXT NOT NULL,
            status             TEXT NOT NULL DEFAULT 'active',
            created_at         TEXT NOT NULL,
            retire_after       TEXT
        )
    `);
    await db.query(`CREATE INDEX IF NOT EXISTS ${TABLE_KEYS}_status ON ${pfx}${TABLE_KEYS} (status)`);
};


// Single table backing the oidc-provider storage adapter (all model types).
const createIdpOidcStore = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_OIDC_STORE} (
            model      TEXT NOT NULL,
            id         TEXT NOT NULL,
            payload    TEXT NOT NULL,
            uid        TEXT,
            grant_id   TEXT,
            user_code  TEXT,
            expires_at INTEGER,
            PRIMARY KEY (model, id)
        )
    `);
    await db.query(`CREATE INDEX IF NOT EXISTS ${TABLE_OIDC_STORE}_uid      ON ${pfx}${TABLE_OIDC_STORE} (uid)`);
    await db.query(`CREATE INDEX IF NOT EXISTS ${TABLE_OIDC_STORE}_grant    ON ${pfx}${TABLE_OIDC_STORE} (grant_id)`);
    await db.query(`CREATE INDEX IF NOT EXISTS ${TABLE_OIDC_STORE}_usercode ON ${pfx}${TABLE_OIDC_STORE} (user_code)`);
};


// Custom groups + the user<->group junction ("meet me" table).
const createIdpGroups = async () => {
    const pfx = db.getTenantSchemaPrefix();
    // Portable auto-increment PK: sqlite "integer primary key" auto-assigns
    // rowids; postgres uses "serial". (AUTOINCREMENT is sqlite-only syntax.)
    const serial = db.isSQLite ? "integer" : "serial";
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_GROUPS} (
            id          ${serial} PRIMARY KEY,
            name        TEXT NOT NULL UNIQUE,
            description TEXT,
            created_at  TEXT NOT NULL
        )
    `);
};


const createIdpGroupMembers = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_GROUP_MEMBERS} (
            group_id INTEGER NOT NULL,
            user_id  INTEGER NOT NULL,
            PRIMARY KEY (group_id, user_id)
        )
    `);
    await db.query(`CREATE INDEX IF NOT EXISTS ${TABLE_GROUP_MEMBERS}_user ON ${pfx}${TABLE_GROUP_MEMBERS} (user_id)`);
};


// Registered relying parties. Confidential clients' secrets are sealed at rest
// (hex columns); public clients (token_auth_method='none') have none.
const createIdpClients = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_CLIENTS} (
            client_id         TEXT PRIMARY KEY,
            label             TEXT,
            token_auth_method TEXT NOT NULL DEFAULT 'none',
            redirect_uris     TEXT NOT NULL,
            grant_types       TEXT NOT NULL,
            response_types    TEXT NOT NULL,
            scope             TEXT,
            secret_ciphertext TEXT,
            secret_iv         TEXT,
            secret_tag        TEXT,
            created_at        TEXT NOT NULL
        )
    `);
};


// Per-tenant SAML signing material: a self-signed X.509 cert (advertised in IdP
// metadata) + its sealed private key (used to sign assertions). Singleton row.
const createIdpSaml = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_SAML} (
            id                 TEXT PRIMARY KEY,
            cert               TEXT NOT NULL,
            private_ciphertext TEXT NOT NULL,
            private_iv         TEXT NOT NULL,
            private_tag        TEXT NOT NULL,
            created_at         TEXT NOT NULL
        )
    `);
};


// Registered SAML relying parties (service providers). The IdP only issues an
// assertion to a registered SP and only to one of its allow-listed ACS URLs, so
// a forged AuthnRequest cannot redirect a signed assertion to an attacker. The
// optional signing_cert (public, not sealed) enables AuthnRequest signature
// verification; want_authn_requests_signed (INTEGER 0/1) gates enforcement.
const createIdpSamlSps = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_SAML_SPS} (
            entity_id                  TEXT PRIMARY KEY,
            label                      TEXT,
            acs_urls                   TEXT NOT NULL,
            signing_cert               TEXT,
            want_authn_requests_signed INTEGER NOT NULL DEFAULT 0,
            created_at                 TEXT NOT NULL
        )
    `);
};


// LDAP service-account credentials (search-then-bind binder). Single row; the
// password is sealed at rest (hex columns), like client secrets.
const createIdpLdapService = async () => {
    const pfx = db.getTenantSchemaPrefix();
    await db.query(`
        CREATE TABLE IF NOT EXISTS ${pfx}${TABLE_LDAP_SERVICE} (
            dn                TEXT,
            secret_ciphertext TEXT,
            secret_iv         TEXT,
            secret_tag        TEXT,
            created_at        TEXT NOT NULL
        )
    `);
};


const createAllTables = async () => {
    await createIdpEnv();
    await createIdpKeys();
    await createIdpOidcStore();
    await createIdpGroups();
    await createIdpGroupMembers();
    await createIdpClients();
    await createIdpSaml();
    await createIdpSamlSps();
    await createIdpLdapService();
};


module.exports = {
    createAllTables
};