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.
146 lines
4.8 KiB
Go
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
|
|
}
|