App Backends Postgres Adapter

postgres-url is the reference adapter for a Postgres server you operate. Give it a URL and an admin role and it runs each backend as a separate database on that server. It implements all 25 operations of the Adapter SPI and is exported from @hellmai/lumenflow-packs-app-backends/adapters/postgres-url. It is generic Postgres: it uses no feature specific to a hosting provider.

It promises only what the server provides. See What it guarantees and what it does not.

Prerequisites

• Postgres 16 or 17. The adapter uses role-membership options introduced in 16, and by default accepts major versions 16 and 17 (engineVersions). On a server outside that set the first call fails with unsupported_capability.
• An admin role with CREATEDB and CREATEROLE. SUPERUSER is not required. The adapter uses this role to create databases and per-backend roles, and never hands it to an application.
• If anon and authenticated already exist, the admin role needs ADMIN OPTION on both (GRANT anon TO <admin> WITH ADMIN OPTION). When the adapter creates them itself, it already has it. An existing role with login, superuser or bypass-RLS rights is refused.
• The pg package, version 8.11 or later, installed next to the pack. It is an optional peer dependency and the adapter loads it on first use. Without it the first call fails with an instruction to install it.
• A sealing port and an object store port. See Sealing and custody.

Caution

The adapter creates and drops databases and roles on the server it is pointed at. Do not point it, or the conformance suite, at a server whose other databases you cannot afford to lose track of. It also creates, once, a registry database (default app_backends_operator) and the shared anon and authenticated roles, and never drops them.

Configuration

import {
  createPostgresUrlAdapter,
  parsePostgresUrl,
} from '@hellmai/lumenflow-packs-app-backends/adapters/postgres-url';

const admin = parsePostgresUrl(process.env.APP_BACKENDS_ADMIN_URL ?? ''); // postgres://user:password@host:5432/postgres

const adapter = createPostgresUrlAdapter({
  host: admin.host,
  port: admin.port,
  ...(admin.tls === undefined ? {} : { tls: admin.tls }),
  admin: { user: admin.user, password: admin.password }, // a Secret
  sealing, // your SealingPort
  objectStore, // your ObjectStorePort
});

parsePostgresUrl splits a postgres:// or postgresql:// URL into host, port, user, password (as a Secret), database and TLS settings (sslmode). The URL is consumed there and never stored. ConfigError lists every problem in a configuration at once.

PostgresUrlAdapterConfig:

Field	Meaning
host, port	The server. Port defaults to 5432.
tls	true, false or an object with ca, rejectUnauthorized and servername, passed to pg.
admin	{ user, password }. password must be a Secret (new Secret('...')), which renders as a placeholder when logged.
sealing	Required. A SealingPort.
objectStore	Required. An ObjectStorePort, which resolves sealed export sinks and import sources.
connectionSource	Optional. A ConnectionSourcePort that resolves the sealed reference adopt takes into a login on an existing database. Without it adopt answers unsupported_capability.
registryDatabase	Created on first use. Default app_backends_operator.
maintenanceDatabase	An existing database to connect to before the registry exists. Default postgres.
engineVersions	Accepted major versions, declared in the capability document. Default ['16', '17'].
region	The region triple the adapter declares. Default { residency: 'unspecified', provider: 'external-postgres', location: 'external' }.
restorePointBeforeMigration	Take a restore point before applying a migration. Default true.
restorePointRetention	Restore points kept per backend; older ones are dropped. Default 5.
limits	maxConnectionsPerBackend (20), statementTimeoutMs (30 000), idleInTransactionTimeoutMs (60 000), lockTimeoutMs (5 000), maxDatabaseSizeGb (50), maxBackends (null: derived from the server’s max_connections), capacityHighWater (0.85).
connectionRefTtlMs	How long a sealed connection reference is valid. Default one hour.
exportRetentionMs	How long an export manifest says its objects are kept. Default seven days.
adapterId	Default postgres-url.
clock, logger	For tests and log routing. createRedactingLogger writes JSON lines with known secrets removed.
executor	A SqlExecutor. Default: the pg-backed one, createPgSqlExecutor().

The adapter never imports a database driver itself. It talks to the server through the SqlExecutor port, so the driver is replaceable. createPgSqlExecutor(), also at @hellmai/lumenflow-packs-app-backends/adapters/postgres-url/pg, loads pg by dynamic import, opens one client per session (no pool), sets statement and connection timeouts, and turns every failure into a SqlError with passwords and connection strings removed.

Adopting an existing database

A database that existed before the pack can be registered with adopt and then exported and verified. Give the adapter a connectionSource, which holds the logins and resolves a sealed reference for the caller:

const connectionSource = {
  // Resolve the sealed reference for this caller or throw. Return a Secret password.
  async resolveAdoptable(ref, isolation) {
    return await vault.connectionFor(ref.custodyRef, isolation); // { host, port, database, user, password, tls? }
  },
};
const adapter = createPostgresUrlAdapter({ host, admin, sealing, objectStore, connectionSource });

const adopted = await adapter.adopt(
  { connectionRef, mode: 'read_only', environment: 'production' },
  ctx,
);
const sink = /* a sealed export sink */;
await adapter.exportSnapshot(adopted.value, 'neutral-jsonl-v1', sink, exportCtx);
await adapter.verify(adopted.value, adopted.value, verifyCtx);

The adapter proves the login works before it registers anything, seals the password, and stores the server, port, database and user in its registry with ownership adopted. It creates no schema, table or role in the adopted database, so the idempotency store and catalog lock of a provisioned backend are not needed there. Export and verify read the database in one read-only, repeatable-read transaction. The adopted login needs only the privileges to read what it should export.

A read_only adoption refuses migrations, restore points, restore, credential rotation, access-mode and limit changes, import, connection references, usage and deprovision with policy_violation, and never drops the database. To move the data onto a provisioned backend, export the adopted backend, import the export into a provisioned one and verify the two. See the guarantees in Adopting an existing database.

The isolation model

• A database per backend. Each backend is its own database on the server. There is no schema-per-tenant and no shared tables.
• Opaque names. Database names, role names and every handle reference are random. None is derived from a workspace, tenant or app identifier, because catalog names in a shared server are readable by every connected role. The mapping from a handle to its database lives in the registry database.
• Per-backend login roles. Each backend has three login roles, for the data API, the auth server and the migrator. They have no SUPERUSER, CREATEDB, CREATEROLE, BYPASSRLS or REPLICATION, do not inherit, have connection limits, and each backend’s roles can connect only to that backend’s database. Passwords are random, are set on the server as SCRAM verifiers (never in clear) and are held sealed.
• Shared NOLOGIN roles. anon and authenticated are created once per server with no login. The data API role can SET ROLE to them without inheriting their privileges. They are what a request runs as, with the verified claims available to policies.
• Deny by default. CONNECT is revoked from everyone but the backend’s own three roles. The public schema is revoked from PUBLIC and usable only by anon and authenticated. A table gets no access for anon or authenticated until a migration grants it and defines policies, so a table without policies is unreadable and unwritable by the end-user roles.
• Migrations are constrained. They run as the migrator role, never the admin, inside one transaction. An admission lint refuses, with a typed finding that names the statement, anything that would escape the backend: extensions outside the allowlist (pgcrypto, uuid-ossp, citext, pg_trgm, btree_gist), COPY ... PROGRAM and server-side file COPY, lo_import and lo_export, server file functions and roles, role management, SET ROLE and SET SESSION AUTHORIZATION, ALTER SYSTEM, untrusted procedural languages, foreign data wrappers, CREATE, DROP and ALTER DATABASE, the adapter’s own lf_meta schema, FORCE ROW LEVEL SECURITY, transaction control, and statements that cannot run in a transaction (VACUUM, CREATE INDEX CONCURRENTLY). The lint is a second line of defence; the first is that the migrator role holds no such privilege.
• A ledger inside the backend. The migration ledger lives in the backend’s own lf_meta schema, so a restore carries it with the data it describes.

What it guarantees and what it does not

The adapter declares this capability document, built by buildCapabilities(config):

Capability	Value
engineFamily	postgres, versions from engineVersions
rowLevelPolicies	true
transactionalMigrations	true
pitr	supported: false, window and granularity 0
restorePoints	true
branching	false (no branch method)
exportFormats	['neutral-jsonl-v1']
crossRegionBackups	false
encryptionAtRest	false
scaleToZero	false, coldStartP95Ms is null
placement	['external']
regions	The single configured region
extensions	The allowlist above

What follows from that, in plain terms:

• No point-in-time recovery. The adapter cannot prove that the server archives its write-ahead log, so it declares pitr.supported false. recoveryWindow and a point_in_time restore return unsupported_capability. If you operate PITR on your server, that is yours; the adapter does not claim it.
• Restore points are logical clones, not backups. A restore point is a CREATE DATABASE ... TEMPLATE copy on the same server. Postgres will not copy a database with sessions, so creating one briefly disconnects the backend: new connections are blocked, existing sessions are ended, the copy is made, and connections are allowed again. Plan restore points, including the one taken before each migration, with that interruption in mind. Losing the server loses its restore points. Back up the server separately.
• Which sessions a deprovision or restore point ends. Before it drops or copies a database, the adapter ends the client sessions it is allowed to signal: those of the backend’s own login roles and any other role the admin holds the privileges of. It does not signal superuser sessions, sessions of roles the admin is not a member of, or server processes such as autovacuum workers. Postgres cancels autovacuum workers by itself when a database is dropped or copied, so they never block. A superuser or otherwise unsignalable client session that stays connected makes the call return provider_unavailable (retryable, outcome none, or partial when earlier steps already took effect) and name that role class. End that session, then repeat the call, or run reconcile() for an interrupted deprovision. The adapter does not report this as internal, and it does not need SUPERUSER or the Postgres 18 pg_signal_autovacuum_worker role. A deprovision blocked by such a session leaves the database refusing connections (state deprovisioning) until a repeated call or reconcile() completes it; there is no way to undo a deprovision. Before it ends sessions, deprovision stops new connections to the database, so a client that keeps reconnecting cannot hold the drop open. swap and setAccessMode (freeze) do not end a superuser session: such a session stays connected, and one that began before the database default changed can still write. Freeze fails with provider_unavailable (outcome partial) when a session of the backend’s own login roles could not be ended.
• Restore is never in place. restore clones a restore point into a new candidate backend that shares the original’s login roles, so no new credentials are needed. swap promotes it and keeps the previous database until discard.
• One export format. Only neutral-jsonl-v1 is declared and implemented. postgres-custom-dump is a valid value in the contract that this adapter does not offer, so an export in it returns unsupported_capability. Export writes a manifest.json, one tables/<name>.jsonl per table and a sequences.jsonl. It carries tables, primary keys, sequences, grants to the end-user roles and policies. It does not carry indexes other than the primary key, other constraints, views, functions, triggers or user-defined types; those come back through migrations, and the manifest lists what was not carried. Export reads inside one read-only, repeatable-read transaction and changes nothing. The format is specified in full in Export format, together with a standalone verifier and an importer into plain Postgres (app-backends-export verify and import) that need no adapter, control plane or LumenFlow configuration.
• No branching, no cross-region backups, no scale to zero.
• Encryption at rest is not claimed. The adapter cannot know how the server’s storage is protected.
• Credential rotation works for data_api, auth and migrator. operator and signing_key return unsupported_capability: the admin credential belongs to your host configuration and signing keys belong to the auth server in the data plane.
• Usage reports storage only. requests, connectionSeconds and egressGb are 0 because the adapter does not see data-plane traffic.
• Capacity. cellCapacity (operator only) reports the fraction of maxBackends in use when you set it, and otherwise the fraction of the server’s max_connections the backends could use at their per-backend connection cap. acceptingNew is false once utilization reaches capacityHighWater.

Sealing and custody

The adapter never keeps a credential in clear and never returns one. Two ports are yours to supply.

SealingPort wraps credentials for storage in the registry. It has two methods:

interface SealingPort {
  seal(context: SealContext, plaintext: string): Promise<string>;
  unseal(context: SealContext, sealed: string): Promise<string>;
}

SealContext is { workspaceId, tenantSubject, appId, backendId, purpose }. Unsealing under another context must fail. Production custody adapters (a file key, a vault, a key management service) are supplied separately; this pack depends only on the interface. createInMemorySealing() is a test double that does not encrypt, only binds a value to its context. Do not use it outside tests.

ObjectStorePort resolves a sealed sink or source into an object you can write to or read from: resolveSink(ref) returns { put(name, text) } and resolveSource(ref) returns { get(name), list() }. The adapter never sees the location or its credentials.

connectionFor(handle, role) returns a SealedConnectionRef. The adapter also exposes two helpers for the data plane, which is the only code that should call them: resolveConnection(ref, access) and resolveConnectionAsOperator(handle, role) return a ResolvedConnection whose password is a Secret, pointing at the backend’s current database (a swap changes which database that is, without new credentials). A reference that is not the caller’s own returns not_found.

Running the conformance suite against a disposable Postgres

The conformance harness for this adapter is exported from @hellmai/lumenflow-packs-app-backends/adapters/postgres-url/conformance. It builds the adapter itself, so the fault injector, the clock, log capture and the object store are wired in, and it implements the data, policy, SQL, fault and object-store probes against a real server.

1. Start a throwaway server and an admin role. For example, with a container runtime:

   docker run -d --rm --name lf-conformance -p 127.0.0.1:55432:5432 \
     -e POSTGRES_PASSWORD="$(openssl rand -base64 24)" postgres:17-alpine \
     -c max_connections=300

   Then connect as the superuser of that container and create the admin role the adapter will use:

   CREATE ROLE conformance_admin LOGIN CREATEDB CREATEROLE NOSUPERUSER PASSWORD '<strong random password>';

2. Run the suite:

   import {
     assertConformant,
     formatReport,
     runConformance,
   } from '@hellmai/lumenflow-packs-app-backends/conformance';
   import { createPostgresUrlConformanceHarness } from '@hellmai/lumenflow-packs-app-backends/adapters/postgres-url/conformance';
   
   const harness = createPostgresUrlConformanceHarness({
     adminUrl: 'postgres://conformance_admin:<password>@127.0.0.1:55432/postgres',
   });
   
   const report = await runConformance({ adapter: harness.adapter, harness });
   console.log(formatReport(report));
   assertConformant(report);

3. Stop the container.

Notes:

• The harness uses the admin password as the secret canary: no result, error, log line or export object may contain it. Use a strong random password. A weak one such as postgres also appears in ordinary output and would be reported as a leak.
• createPostgresUrlConformanceHarness takes adminUrl, an optional region and optional adapterConfig (limits, retention and similar). The harness owns the sealing, object store, executor, logger and clock.
• The harness’s sealing and object store are in-memory test doubles. A passing run says the adapter honours the SPI against a real Postgres server; it does not say anything about your production custody or object storage.
• Leave the run to finish. Each case deprovisions what it created. The registry database and the shared roles remain on the server.
• If you run the repository’s own real-server tests, the disposable container they start is labelled with its owner and removed on stop, on SIGINT, SIGTERM and SIGHUP and on exit. A run killed with SIGKILL cannot clean up, so the next start removes labelled containers whose owner on this host is proven dead. Live, unlabelled and other-host containers are never touched.
• A run is only evidence if report.conformant is true over the whole catalogue. Do not use only for the run you rely on.