db

Overview ¶

Package db provides PostgreSQL connection pooling, tenant-scoped Row-Level Security (RLS), schema migrations, and extension verification for the CrossCodex platform.

Startup Lifecycle ¶

Schema changes and the default tenant are provisioned out of band by `crosscodexd migrate`, which connects as the schema owner (database.migration_dsn). A service then connects only as the restricted roles, in three stages:

1. (Once, as the owner, in `crosscodexd migrate`) Run migrations:

   migrator, err := db.NewMigrator(ownerDSN) if err != nil { return err } defer migrator.Close() if err := migrator.Up(ctx); err != nil { return err }

2. Open the application pool, check the role and schema version, and verify extensions:

   pool, err := db.NewPool(db.PoolConfig{ DSN: appUserDSN, MaxOpenConns: 20, Extensions: []string{"age", "vector"}, }) if err != nil { return err } defer pool.Close() if err := db.CheckRestrictedRole(ctx, appDB, "database.dsn"); err != nil { return err } if err := db.CheckSchemaVersion(ctx, appDB); err != nil { return err } if err := pool.VerifyExtensions(ctx); err != nil { return err }

   CheckRestrictedRole refuses a superuser, BYPASSRLS, table-owner or member-of-one connection. CheckSchemaVersion refuses a schema that is not at LatestMigrationVersion or is dirty. appDB is a *sql.DB on the same DSN (anything satisfying RowQueryer).

3. Wrap the pool for tenant-scoped access:

   tenantConn := db.NewTenantPool(pool)

Tenant-Scoped Queries ¶

Every tenant-scoped operation must go through a transaction. The TenantPool sets app.current_tenant via public.enter_tenant (which refuses deleted tenants) and optionally app.current_user via SET LOCAL. PostgreSQL reverts both on rollback or commit. Direct Query/Exec calls on TenantPool return ErrTenantRequired because SET LOCAL has no effect outside a transaction.

ctx, err := tenant.WithTenant(ctx, tenantID)
ctx  = tenant.WithUser(ctx, userID)  // optional: limits to user's own jobs

tx, err := tenantConn.Begin(ctx)
if err != nil { return err }
defer tx.Rollback()

rows, err := tx.Query(ctx, "SELECT job_id, status FROM jobs")
// ... process rows ...
return tx.Commit()

Security Model — Three Roles ¶

Three PostgreSQL roles enforce defense-in-depth at the database level. Each role has the minimum privileges needed for its purpose, and none can escalate to another's capabilities.

• postgres (superuser): Owns all tables and extensions. Used only by `crosscodexd migrate` (database.migration_dsn) for schema migrations, role passwords and default_tenant provisioning (EnsureTenant), and as the owner the tenant_admin functions run as. Never used by application code at runtime; the daemon refuses to start on this role (CheckRestrictedRole).

• app_user: SELECT/INSERT/UPDATE/DELETE on public-schema tables, with Row-Level Security (RLS) policies enforcing tenant isolation per transaction. Has NO access to graph schemas or ag_catalog. Cannot perform DDL. This is the role used by the relational connection pool (configured via database.dsn).

• graph_user: Owns per-tenant graph schemas (crosscodex_{tenant_id}) created by the tenant provisioning trigger. AGE cypher commands internally perform DDL (creating label tables, updating sequences), which requires ownership — GRANT INSERT/UPDATE/DELETE is not sufficient. graph_user has USAGE/SELECT/EXECUTE on ag_catalog for graph metadata queries, but has NO access to public-schema relational tables. This is the role used by the graph connection pool (configured via database.graph_dsn).

• tenant_admin: no table, sequence or schema grants. It may only EXECUTE the SECURITY DEFINER functions provision_tenant, set_tenant_status and list_tenants, so operators can provision and suspend tenants (crosscodexd admin tenant, via TenantAdmin) without the owner credential, and cannot read tenant data or bulk-modify tenants. app_user may EXECUTE only tenant_is_active, which the gateway calls (TenantActive) to refuse suspended tenants despite the tenants RLS policy.

This separation means a bug in graph-handling code cannot read or modify relational data (jobs, classifications, vote summaries), and a bug in relational code cannot modify graph structure. Even if application code is compromised, PostgreSQL itself blocks cross-boundary access.

Privilege Matrix ¶

The matrix below summarizes what each role can and cannot do. The integration tests in integration_test.go ("Role Isolation" section) verify every cell marked "no" and every cell marked "yes" — making this matrix an executable specification, not just documentation.

Capability                     postgres  app_user  graph_user
─────────────────────────────  ────────  ────────  ──────────
DDL (CREATE/ALTER/DROP)        yes       no        graph schemas only
Relational DML (public)        yes       yes+RLS   no
ag_catalog read                yes       no        yes
ag_catalog execute (cypher)    yes       no        yes
Graph schema DML               yes       no        yes (owner)
LOAD shared library            yes       no        no
Tenant provisioning (INSERT)   yes       via RLS   no
TRUNCATE relational tables     yes       no        no
Disable RLS / triggers         yes       no        no

The AGE shared library is loaded at server startup via shared_preload_libraries=age in postgresql.conf. Application code must NOT use the LOAD command — PostgreSQL restricts LOAD to superusers. shared_preload_libraries makes the library available to all sessions without per-session LOAD calls.