Run database access without a superuser role #1857

Open
opened 2026-11-10 15:26:30 +00:00 by sam · 0 comments
Owner

The application connects to Postgres as the database owner role, which is a superuser, and every connection arrives with an empty application_name. A compromise anywhere in the app therefore owns the cluster, and the activity view cannot tell which service a connection belongs to.

Wanted: a runtime role with least privilege (table and sequence grants) and a separate migration role that owns schema changes and runs the migrations; per-service application_name on every connection pool; and a login path that never needs superuser for normal work.

Done when: the services connect as a non-superuser role, migrations still apply under a role that is not the runtime role, pg_stat_activity names the service for every connection, and a fresh install provisions both roles.

The application connects to Postgres as the database owner role, which is a superuser, and every connection arrives with an empty `application_name`. A compromise anywhere in the app therefore owns the cluster, and the activity view cannot tell which service a connection belongs to. Wanted: a runtime role with least privilege (table and sequence grants) and a separate migration role that owns schema changes and runs the migrations; per-service `application_name` on every connection pool; and a login path that never needs superuser for normal work. Done when: the services connect as a non-superuser role, migrations still apply under a role that is not the runtime role, `pg_stat_activity` names the service for every connection, and a fresh install provisions both roles.
sam added the mediumtask labels 2026-11-14 14:45:41 +00:00
sam added the deferred label 2026-11-15 17:26:10 +00:00
Sign in to join this conversation.
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: sam/archie#1857