Files
sam c54d328f0d feat(store): run services as a non-superuser role and name every connection (#1857)
Every service connected to PostgreSQL as the database owner, which is a
superuser, so anything that compromised an app process owned the cluster, and
every connection reached the server with an empty application_name, so
pg_stat_activity could not say which service a session belonged to.

Split the DB credential into two login roles and name every pool:

- archie_migrator owns public and its objects and runs goose. Migrations run
  on a short-lived pool opened from the new migration_database_url, before the
  serving pool opens. It is not a superuser.
- archie_runtime serves requests with SELECT, INSERT, UPDATE, DELETE, sequence
  USAGE/SELECT and function EXECUTE only. It has no CREATE privilege, so it
  cannot change the schema.
- Every pool sets application_name: archie-state-store, archie-gateway, and a
  -migrate / -recovery / -schema / -provision suffix on the transient
  administrative connections. Existing one-URL installs keep working: an
  absent migration_database_url falls back to database_url and the migration
  runs on the serving pool exactly as before.

Provisioning is a new idempotent `archie-state-store provision` subcommand: it
needs an admin URL the services never hold (database owner or superuser),
creates or reconciles both roles, hands every public-schema object to the
migration role, revokes PUBLIC's CREATE on public, sets row-level grants, and
sets the migration role's default privileges so tables future migrations
create are usable without a hand-run grant. It stores SCRAM verifiers, refuses
superuser roles, and verifies the boundary before committing.

install.sh now provisions the bundled path and separates the credentials: the
superuser password the Compose database is created with lives only in a
compose-only postgres.env, while the env file the units load holds the
non-superuser PGPASSWORD. A bundled install that predates the split is
upgraded in place: the roles are created, the superuser password is rotated
out of the env file, and config.toml is moved to the runtime and migration
URLs. External databases keep their operator-managed credential; the
deployments README documents the one-shot provision command.

setup writes both bundled URLs, dev:postgres provisions the dev database, the
e2e stack provisions its throwaway server, and the deployment templates point
at the two roles.
2026-11-11 03:12:54 +11:00

39 lines
1.4 KiB
Go

package statestore
import (
"context"
"github.com/samcharles93/archie-core/internal/infrastructure/postgres"
)
// openStateStorePool opens the State Store process's one process-scoped
// PostgreSQL pool, applies the schema and claims serve ownership of the
// database for the process's life, so a second State Store against the same
// database refuses to start. The schema is applied through
// migration_database_url when the configuration splits the migration role from
// the serving one. A missing
// or invalid database_url or a migration failure is a startup failure.
//
// The pool is stored on boot.pg and closed at shutdown; nothing opens a second
// pool per subsystem.
func (b *server) openStateStorePool(ctx context.Context) error {
pool, err := postgres.OpenMigrated(ctx, b.cfg.DatabaseURL, b.cfg.MigrationURL(), postgres.AppStateStore)
if err != nil {
b.log.ErrorContext(ctx, "open state store postgres pool", "err", err)
return err
}
ownership, err := postgres.AcquireOwnership(ctx, pool, postgres.OwnerStateStore)
if err != nil {
pool.Close()
b.log.ErrorContext(ctx, "claim state store ownership", "err", err)
return err
}
b.pg = pool
b.addCleanup(pool.Close)
// Registered after the pool so it runs first: the claim is dropped before
// the pool closes.
releaseCtx := context.WithoutCancel(ctx)
b.addCleanup(func() { _ = ownership.Release(releaseCtx) })
return nil
}