Skip to content

Data platform: Postgres, RLS, blobs and per-tenant encryption

Server-side persistence (T11). The local library and the extractit CLI never use it: they run in process without a database, and .importlinter forbids core, parse, extract, ground, rules, llm, cli and testing from importing extractit.store or extractit.security.

Piece Module
Settings (DX_*) extractit.store.config
Engines, tenant_session, atenant_session, system_session extractit.store.db, store.tenant
Base, mixins, models extractit.store.models
Migrations and apply_rls extractit.store.migrations (rls.py, versions/)
RLS coverage audit extractit.store.rls_audit
Object store (FS, S3) extractit.store.objects
Encrypted content-addressed blobs extractit.store.blobs
DXE1 format, KEK providers, keyring extractit.security.crypto, security.keys
Administration python -m extractit.store.admin

Tenancy model

An organization is the tenant; projects live inside it. Every tenant table carries tenant_id (FK to organizations.id); project-scoped tables also carry project_id, with a composite FK (tenant_id, project_id) → projects(tenant_id, id) so a row's project always belongs to the row's tenant. Ids are UUIDv7 in the database and TypeIDs (org_…, prj_…) at the boundary (store.ids.encode/decode).

Database roles

Role Attributes Used by
dx_migrator owns the schema objects, NOBYPASSRLS Alembic only (DX_MIGRATOR_DATABASE_URL)
dx_app NOBYPASSRLS, owns nothing, DML granted per table the service (DX_DATABASE_URL)
dx_system BYPASSRLS, minimal grants provisioning, maintenance, lookups before the tenant is known (DX_SYSTEM_DATABASE_URL)
dx_dbos NOBYPASSRLS, owns schema dbos the durable runtime (T14): DBOS system tables, plus narrow policies on runs/run_events (DX_DBOS_DATABASE_URL, see runtime)

deploy/compose/postgres/init-roles.sql creates them (passwords from DX_MIGRATOR_DB_PASSWORD, DX_APP_DB_PASSWORD, DX_SYSTEM_DB_PASSWORD; development defaults otherwise). The development Compose stack runs it on first start. On managed Postgres the operator creates the roles with the same attributes; the initial migration only checks that they exist.

Row-level security and the tenant context

Every tenant table has RLS enabled and forced and one policy tenant_isolation for dx_app with identical USING and WITH CHECK:

tenant_id = (SELECT nullif(current_setting('app.tenant_id', true), '')::uuid)
-- project-scoped tables, in addition:
AND ((SELECT nullif(current_setting('app.project_id', true), '')) IS NULL
     OR project_id = (SELECT nullif(current_setting('app.project_id', true), '')::uuid))
  • The scalar sub-selects run once per statement (initPlan); nullif turns the empty string a GUC keeps after its transaction into NULL, so a query without context returns 0 rows instead of failing.
  • organizations compares id (only dx_system inserts); projects filters the project on id.
  • tenant_session(tenant_id, project_id=None) stores the context in session.info; an after_begin listener runs SELECT set_config('app.tenant_id', :t, true), set_config('app.project_id', :p, true) at the start of every transaction of the session. set_config(..., true) is SET LOCAL with bound parameters: it vanishes at commit or rollback, so pooled connections (and PgBouncer in transaction mode) never carry a tenant into the next transaction.
  • A dx_app session that begins a transaction without context raises TenantContextMissing.
  • Repositories add the tenant predicate explicitly as well (defense in depth).

Measured cost (Postgres 16, Apple M-series, 10 000 rows per tenant): the latency the application sees changes by −8 % (noise) for a key lookup and +2.4 % for a 10 000-row listing. Server-side, the plan is identical except for one Result node: Postgres folds the policy into a one-time filter ($0 = <tenant>), which costs a few microseconds per statement. DX_LOAD_TESTS=1 runs the measurement (tests/tenancy/test_tenancy_overhead.py).

Static guards

tests/architecture fails when create_engine, create_async_engine, sessionmaker, set_config or SET LOCAL appear outside extractit/store/, when system_session is used outside store/ and its allowlist, or when a SQLAlchemy text( call lacks # dx:raw-sql-ok <reason>. python -m extractit.store.admin check-rls (and test_rls_policy_coverage) read pg_class, pg_policies and pg_roles: every table with tenant_id must have RLS enabled and forced and a dx_app policy using app.tenant_id; a table without tenant_id must be listed in GLOBAL_TABLES with a reason; the dbos schema (T14) is exempt; dx_app must have no BYPASSRLS and own no table.

Declaring a new table

  1. Model: inherit the mixins and Base.

    class Thing(IdMixin, TenantMixin, ProjectMixin, TimestampMixin, VersionMixin, Base):
        __tablename__ = "things"
        name: Mapped[str] = mapped_column(String(200))
    

    VersionMixin adds row_version as SQLAlchemy version_id_col (optimistic locking; the ETag of T12). A model with more __table_args__ extends ProjectMixin.project_table_args("things"). 2. Migration: create the table on top of the current head and call apply_rls("things") (project_scoped=False for tables without project_id). It enables and forces RLS, creates the policy and grants dx_app its DML (and dx_system SELECT by default). 3. Leak battery: register a factory in tests/tenancy/factories.py:

    @factory("things")
    def thing(tenant_id, project_id):
        return {"tenant_id": tenant_id, "project_id": project_id, "name": "x", "row_version": 1}
    

    test_every_tenant_table_has_a_factory fails until it exists. The battery then checks, for the new table, SELECT/INSERT/UPDATE/DELETE from tenant A against B's rows, the context-less case and the project context. 4. Run alembic check (the migration tests do) so models and migrations stay identical.

Objects and blobs

ObjectStore (put, open, head, delete, presign_get, presign_put, capabilities) has two backends behind the same contract suite:

  • FilesystemObjectStore (DX_BLOB_ROOT): atomic writes (temporary file, fsync, os.replace, directory fsync), directories 0700 and files 0600, keys with .., absolute paths or unusual characters rejected; presigned URLs are HMAC-signed dxfs:// URLs checked by verify_presigned.
  • S3ObjectStore (DX_BLOB_BACKEND=s3, DX_S3_BUCKET, DX_S3_ENDPOINT_URL, DX_S3_ADDRESSING=path|virtual, standard AWS credential chain): HEAD before PUT, ChecksumAlgorithm=SHA256, multipart above 16 MiB, presigned URLs of at most 900 s. Tests use moto; no MinIO.

BlobStore is content addressed per tenant: the key is t/{tenant_id}/blobs/aa/bb/<sha256> (no deduplication across tenants). put spools and hashes the input, skips the write if the object exists, otherwise encrypts it in streaming; open decrypts in streaming and raises BlobIntegrityError on any mismatch. Reading a 100 MiB blob used 7 MiB of extra resident memory.

Encryption boundary

Plaintext exists only inside the server process. Objects at rest are DXE1:

header  = "DXE1" | fmt (1) | dek_version (4) | salt (16) | chunk_log2 (1)
subkey  = HKDF-SHA256(DEK, salt, info = "dx-blob-v1")        one key per object
chunk_i = AES-256-GCM(subkey, nonce = counter_i (64 bits, zero padded to 12 bytes),
                      aad = header | tenant_id | sha256(plaintext) | last flag)

Chunks are 1 MiB. The AAD stops truncation, reordering and moving an object to another tenant. Encryption ran at about 6.9 GB/s on Apple M-series (threshold 150 MB/s on CI runners).

Key hierarchy:

  • DEK per tenant, versioned (tenant_keys: version, wrapped_dek, provider, kek_ref, kek_version, status = active|retired|destroyed; at most one active). rotate_dek adds a new active version; old blobs stay readable with their version (named in the header).
  • KEK per tenant wraps the DEKs:
    • LocalKeyProvider: HKDF from a 32-byte master key in DX_KEK_FILE (created by admin init-kek, mode 0600; any other mode, or a missing file, refuses to start; in DX_ENV=production a warning recommends Transit).
    • OpenBaoTransitProvider (DX_KEY_PROVIDER=openbao, DX_OPENBAO_ADDR, DX_OPENBAO_TOKEN, DX_OPENBAO_TRANSIT_MOUNT): one Transit key per tenant dx-t-<tenant_id> with derived=true and the tenant id as context, so a DEK wrapped for tenant A never unwraps with B's key or context.
  • rewrap_all / admin rewrap-kek --tenant X rotates the KEK and rewraps every DEK: kek_version changes, objects are not touched.
  • Unwrapped DEKs are cached for 5 minutes (1 024 entries): reads survive a short Transit outage; after that, KeyProviderUnavailable.
  • TenantKeyring.destroy(tenant) sets every DEK to destroyed, erases wrapped_dek, deletes the Transit key and empties the cache: reads raise KeyDestroyedError; other tenants are unaffected. With the local provider a backup of tenant_keys plus the master file can still recover "destroyed" data: real crypto shredding needs Transit and backup control (T50, T53).

Administration

python -m extractit.store.admin init-kek                 # DX_KEK_FILE, mode 0600
python -m extractit.store.admin migrate                  # alembic upgrade head (dx_migrator)
python -m extractit.store.admin provision-tenant --slug acme --name Acme
python -m extractit.store.admin check-rls
python -m extractit.store.admin rewrap-kek --tenant org_01…

provision-tenant creates the organization, its default project and the first DEK in one dx_system transaction; if the key cannot be created, no organization remains.

Tests

  • tests/security: DXE1, providers (respx for Transit), cache, keyring; OpenBao for real with the requires_openbao marker.
  • tests/store: contract suites of ObjectStore and BlobStore (FS and S3 on moto, PUTs counted with a botocore hook), Alembic round trip and alembic check, provisioning, repositories, key destruction and rewrap, load (DX_LOAD_TESTS=1).
  • tests/tenancy: leak battery, RLS coverage, context lifecycle on a pool of 2.
  • tests/architecture: static rules above.

Postgres comes from DX_TEST_DATABASE_URL (a superuser URL) or a postgres:16 container started by testcontainers when Docker is available; otherwise these tests skip. The data-platform CI job runs them on a Postgres 16 service with OpenBao in dev mode.