Repository navigation
Define least-privilege role design for Postgres #997
Description
ateapi currently connects to PostgreSQL as the postgres superuser. The HBA rule accepts any database and requested role from any client holding a certificate issued by the pod-identity CA. ateapi also applies its embedded schema during startup, coupling normal runtime access with DDL privileges.
We should move to least-privilege application roles, but the correct role split depends on the PostgreSQL migration design (tracked separately in #901) which has not yet been established. For example, migrations may need a separate owner / migrator role while the running API server only receives DML privileges. HBA rules also should be restricted appropriately.
Activity
iplay88keys commentedon Sep 18, 2026
Bowei Du (@bowei), Joe Betz (@jpbetz), Julian Gutierrez Oschmann (@juli4n), Here's my proposal for review. I will work on an implementation:
Problem
ateapicurrently uses one PostgreSQL identity to create the schema, apply Goose migrations, serve requests, and maintain outbox partitions. The reference PostgreSQL manifest also accepts any database and requested role from a client with a trusted certificate. This makes every database operation run as the schema owner.Startup migrations are only part of the problem: the running server also creates, truncates, and drops outbox partitions. Separating migrations without addressing that maintenance would not produce a DML-only runtime.
Proposed design
Give
ateapitwo PostgreSQL connection strings and role classes:Keep the existing startup lifecycle. Schema creation and migrations use the DDL connection; request handling and watch polling use DML connections; outbox partition creation, stray cleanup, and retention drops use the DDL connection. The current advisory locks continue to coordinate migrations and maintenance across replicas.
The existing PostgreSQL connection remains the DML input. A new DDL connection input defaults to the DML connection, allowing simpler single-role deployments. For two-role deployments, deployment tooling supplies the credentials and provisions a clean runtime role without ownership or DDL privileges.
Update the reference PostgreSQL manifest to provision both roles and restrict
pg_hba.confto the Substrate database and those roles, requiring both a trusted client certificate and role credentials.Known risk
Both credentials and the live DDL pool remain inside the serving
ateapiprocess. This design prevents accidental DDL through normal runtime paths and provides database-level permission separation and auditing, but it is not a security boundary against compromise of the process itself.The stronger alternative is to run migrations outside the serving process and either move partition maintenance to a separate privileged worker or expose only narrowly scoped
SECURITY DEFINERmaintenance routines. That would letateapireceive only the DML credential, but requires a new migration lifecycle and more deployment or database machinery. It is deferred from this change.References
Introduce separate PostgreSQL roles for schema ownership and normal a…