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

146 lines
4.8 KiB
Go

// Package postgres opens archie's PostgreSQL database and applies its schema
// migrations.
package postgres
import (
"context"
"embed"
"errors"
"fmt"
"io/fs"
"github.com/jackc/pgx/v5/pgxpool"
"github.com/jackc/pgx/v5/stdlib"
"github.com/pressly/goose/v3"
"github.com/pressly/goose/v3/lock"
)
// minServerVersion is PostgreSQL 18 in server_version_num form.
const minServerVersion = 180000
// The application_name each service sets on its connections, so
// pg_stat_activity says which service a connection belongs to. PostgreSQL
// truncates the value at 63 bytes, which every name below is inside.
const (
AppStateStore = "archie-state-store"
AppGateway = "archie-gateway"
)
// migrationSuffix marks the transient pool that applies migrations, so the
// activity view distinguishes it from the pool that serves traffic.
const migrationSuffix = "-migrate"
//go:embed migrations/*.sql
var migrations embed.FS
// Migrations is the State Store schema, rooted at the migration files.
func Migrations() fs.FS {
sub, err := fs.Sub(migrations, "migrations")
if err != nil {
// Unreachable: the pattern above embeds the directory.
panic(err)
}
return sub
}
// ErrServerTooOld is returned for a server older than PostgreSQL 18.
var ErrServerTooOld = errors.New("postgres: server is older than PostgreSQL 18")
func checkServerVersion(num int) error {
if num < minServerVersion {
return fmt.Errorf("%w (server_version_num %d)", ErrServerTooOld, num)
}
return nil
}
// Open connects a pool to url and refuses a server older than PostgreSQL 18.
// applicationName is the service name every connection reports to
// pg_stat_activity; it overrides any name the URL carries, so no connection
// arrives unnamed because a deployment template forgot one.
func Open(ctx context.Context, url, applicationName string) (*pgxpool.Pool, error) {
poolConfig, err := pgxpool.ParseConfig(url)
if err != nil {
return nil, fmt.Errorf("postgres: open: %w", err)
}
if applicationName != "" {
if poolConfig.ConnConfig.RuntimeParams == nil {
poolConfig.ConnConfig.RuntimeParams = map[string]string{}
}
poolConfig.ConnConfig.RuntimeParams["application_name"] = applicationName
}
pool, err := pgxpool.NewWithConfig(ctx, poolConfig)
if err != nil {
return nil, fmt.Errorf("postgres: open: %w", err)
}
var num int
if err := pool.QueryRow(ctx, "SELECT current_setting('server_version_num')::int").Scan(&num); err != nil {
pool.Close()
return nil, fmt.Errorf("postgres: read server version: %w", err)
}
if err := checkServerVersion(num); err != nil {
pool.Close()
return nil, err
}
return pool, nil
}
// OpenMigrated opens and migrates the pool a service owns its tables in.
// applicationName names the service in pg_stat_activity.
//
// runtimeURL is the pool the service serves from; migrationURL is the role
// that owns the schema and applies migrations. An empty migrationURL falls
// back to runtimeURL: an install that has not split the roles keeps working
// exactly as before, applying the schema through its one role.
func OpenMigrated(ctx context.Context, runtimeURL, migrationURL, applicationName string) (*pgxpool.Pool, error) {
if runtimeURL == "" {
return nil, fmt.Errorf("database_url is required: %s is PostgreSQL-only", applicationName)
}
if migrationURL == "" {
migrationURL = runtimeURL
}
if migrationURL == runtimeURL {
// One role for both jobs: migrate on the serving pool, as before.
pool, err := Open(ctx, runtimeURL, applicationName)
if err != nil {
return nil, err
}
if err := Migrate(ctx, pool, Migrations()); err != nil {
pool.Close()
return nil, err
}
return pool, nil
}
// The migration role is not the serving role: migrate on a short-lived
// pool of its own, then open the serving pool. The two do not overlap, so a
// runtime role with no schema privileges never carries goose's work.
migrationPool, err := Open(ctx, migrationURL, applicationName+migrationSuffix)
if err != nil {
return nil, err
}
migrateErr := Migrate(ctx, migrationPool, Migrations())
migrationPool.Close()
if migrateErr != nil {
return nil, migrateErr
}
return Open(ctx, runtimeURL, applicationName)
}
// Migrate applies the goose migrations in fsys. A session-level advisory lock
// makes processes that start together apply each migration once.
func Migrate(ctx context.Context, pool *pgxpool.Pool, fsys fs.FS) error {
locker, err := lock.NewPostgresSessionLocker()
if err != nil {
return fmt.Errorf("postgres: migration lock: %w", err)
}
db := stdlib.OpenDBFromPool(pool)
defer func() { _ = db.Close() }()
provider, err := goose.NewProvider(goose.DialectPostgres, db, fsys, goose.WithSessionLocker(locker))
if err != nil {
return fmt.Errorf("postgres: migrations: %w", err)
}
if _, err := provider.Up(ctx); err != nil {
return fmt.Errorf("postgres: migrate: %w", err)
}
return nil
}