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);
nullifturns the empty string a GUC keeps after its transaction into NULL, so a query without context returns 0 rows instead of failing. organizationscomparesid(onlydx_systeminserts);projectsfilters the project onid.tenant_session(tenant_id, project_id=None)stores the context insession.info; anafter_beginlistener runsSELECT 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)isSET LOCALwith 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_appsession that begins a transaction without context raisesTenantContextMissing. - 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
-
Model: inherit the mixins and
Base.class Thing(IdMixin, TenantMixin, ProjectMixin, TimestampMixin, VersionMixin, Base): __tablename__ = "things" name: Mapped[str] = mapped_column(String(200))VersionMixinaddsrow_versionas SQLAlchemyversion_id_col(optimistic locking; the ETag of T12). A model with more__table_args__extendsProjectMixin.project_table_args("things"). 2. Migration: create the table on top of the current head and callapply_rls("things")(project_scoped=Falsefor tables withoutproject_id). It enables and forces RLS, creates the policy and grantsdx_appits DML (anddx_systemSELECTby default). 3. Leak battery: register a factory intests/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_factoryfails 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. Runalembic 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, directoryfsync), directories 0700 and files 0600, keys with.., absolute paths or unusual characters rejected; presigned URLs are HMAC-signeddxfs://URLs checked byverify_presigned.S3ObjectStore(DX_BLOB_BACKEND=s3,DX_S3_BUCKET,DX_S3_ENDPOINT_URL,DX_S3_ADDRESSING=path|virtual, standard AWS credential chain):HEADbeforePUT,ChecksumAlgorithm=SHA256, multipart above 16 MiB, presigned URLs of at most 900 s. Tests usemoto; 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_dekadds 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 inDX_KEK_FILE(created byadmin init-kek, mode 0600; any other mode, or a missing file, refuses to start; inDX_ENV=productiona warning recommends Transit).OpenBaoTransitProvider(DX_KEY_PROVIDER=openbao,DX_OPENBAO_ADDR,DX_OPENBAO_TOKEN,DX_OPENBAO_TRANSIT_MOUNT): one Transit key per tenantdx-t-<tenant_id>withderived=trueand 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 Xrotates the KEK and rewraps every DEK:kek_versionchanges, 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 todestroyed, eraseswrapped_dek, deletes the Transit key and empties the cache: reads raiseKeyDestroyedError; other tenants are unaffected. With the local provider a backup oftenant_keysplus 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 (respxfor Transit), cache, keyring; OpenBao for real with therequires_openbaomarker.tests/store: contract suites ofObjectStoreandBlobStore(FS and S3 on moto, PUTs counted with a botocore hook), Alembic round trip andalembic 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.