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.
39 lines
1.4 KiB
Go
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
|
|
}
|