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

535 lines
21 KiB
Go

package postgres
import (
"context"
"crypto/hmac"
"crypto/rand"
"crypto/sha256"
"encoding/base64"
"errors"
"fmt"
"net/url"
"os"
"strings"
"github.com/jackc/pgx/v5"
"golang.org/x/crypto/pbkdf2"
)
// The roles the bundled installer provisions. An install pointed at its own
// database may name different roles in its URLs; these are the names setup
// writes into the bundled configuration.
const (
RuntimeRole = "archie_runtime"
MigrationRole = "archie_migrator"
)
// provisionAdminSuffix names the provisioning connection in pg_stat_activity.
const provisionAdminSuffix = "-provision"
// ProvisionOptions names the three roles one provision run reconciles.
type ProvisionOptions struct {
// AdminURL is a role allowed to create roles and take ownership of the
// schema: the database owner or a superuser. It is a bootstrap credential,
// used once into the database and never stored by the services, and it
// must not be one of the two roles being provisioned.
AdminURL string
// RuntimeURL is the least-privilege role the services connect as.
RuntimeURL string
// MigrationURL is the role that owns the schema and runs migrations.
MigrationURL string
// NewAdminPassword, when set, replaces the admin role's password at the end
// of a successful run. The installer uses it to move the superuser
// credential out of the env file the services load.
NewAdminPassword string
}
// ProvisionResult reports what one provision run reconciled.
type ProvisionResult struct {
RuntimeRole string
MigrationRole string
AdminRole string
// RolesCreated lists the roles this run created; a re-run creates none.
RolesCreated []string
// Tables, Sequences and Functions count the public objects this run took
// ownership of. A fresh database has none yet; the migration role's default
// privileges cover the objects its first migration creates.
Tables int
Sequences int
Functions int
}
// provisionRoles is the role set a run reconciles, with the password each
// login must have afterwards.
type provisionRoles struct {
admin provisionRole
runtime provisionRole
migration provisionRole
}
type provisionRole struct {
name string
password string
}
// Provision makes the database safe to serve from: it creates the migration
// role that owns the schema and the runtime role with only row access, then
// hands every existing object to the migration role and sets the default
// privileges that keep objects created by future migrations usable by the
// runtime. It is idempotent: running it on a provisioned database reconciles
// it back to that state.
//
// The connection URLs also carry each role's password (or PGPASSWORD when they
// do not). Provisioning is the only place a superuser password is needed; it
// is not stored anywhere the services read.
func Provision(ctx context.Context, opts ProvisionOptions) (ProvisionResult, error) {
roles, err := parseProvisionURLs(opts, os.Getenv)
if err != nil {
return ProvisionResult{}, err
}
pool, err := Open(ctx, opts.AdminURL, AppStateStore+provisionAdminSuffix)
if err != nil {
return ProvisionResult{}, err
}
defer pool.Close()
tx, err := pool.Begin(ctx)
if err != nil {
return ProvisionResult{}, fmt.Errorf("postgres: provision: begin: %w", err)
}
defer func() { _ = tx.Rollback(ctx) }()
result, err := provisionTx(ctx, tx, roles, opts.NewAdminPassword)
if err != nil {
return ProvisionResult{}, err
}
if err := tx.Commit(ctx); err != nil {
return ProvisionResult{}, fmt.Errorf("postgres: provision: commit: %w", err)
}
return result, nil
}
// provisionTx performs one provisioning run inside tx, so a failure at any
// step leaves the database as it was.
func provisionTx(ctx context.Context, tx pgx.Tx, roles provisionRoles, newAdminPassword string) (ProvisionResult, error) {
result := ProvisionResult{
RuntimeRole: roles.runtime.name,
MigrationRole: roles.migration.name,
AdminRole: roles.admin.name,
}
for _, role := range []provisionRole{roles.runtime, roles.migration} {
created, err := ensureProvisionRole(ctx, tx, role)
if err != nil {
return ProvisionResult{}, err
}
if created {
result.RolesCreated = append(result.RolesCreated, role.name)
}
}
// The schema owner must change before the objects move: PostgreSQL only
// lets an object's owner (or a superuser) hand it to the migration role.
for _, statement := range schemaStatements(roles) {
if _, err := tx.Exec(ctx, statement); err != nil {
return ProvisionResult{}, fmt.Errorf("postgres: provision %s: %w", statement, err)
}
}
objects, err := schemaObjects(ctx, tx, roles.migration.name)
if err != nil {
return ProvisionResult{}, err
}
for _, object := range objects {
statement := object.ownerStatement(roles.migration.name)
if _, err := tx.Exec(ctx, statement); err != nil {
return ProvisionResult{}, fmt.Errorf("postgres: provision %s: %w", statement, err)
}
switch object.kind {
case objectTable, objectForeignTable, objectPartitionedTable:
result.Tables++
case objectSequence:
result.Sequences++
default:
result.Functions++
}
}
if newAdminPassword != "" {
if err := setRolePassword(ctx, tx, roles.admin.name, newAdminPassword); err != nil {
return ProvisionResult{}, err
}
}
if err := verifyProvision(ctx, tx, roles); err != nil {
return ProvisionResult{}, err
}
return result, nil
}
// ensureProvisionRole creates role as a non-superuser login when it is missing,
// and reconciles an existing role back to that shape. It refuses a role that
// is still a superuser: demoting a superuser is the operator's decision, not a
// side effect of an install.
func ensureProvisionRole(ctx context.Context, tx pgx.Tx, role provisionRole) (bool, error) {
verifier, err := scramVerifier(role.password)
if err != nil {
return false, err
}
var superuser bool
err = tx.QueryRow(ctx, "SELECT rolsuper FROM pg_roles WHERE rolname = $1", role.name).Scan(&superuser)
switch {
case errors.Is(err, pgx.ErrNoRows):
statement := "CREATE ROLE " + quoteIdent(role.name) + " LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE PASSWORD " + quoteLiteral(verifier)
if _, err := tx.Exec(ctx, statement); err != nil {
return false, fmt.Errorf("postgres: provision create role %s: %w", role.name, err)
}
return true, nil
case err != nil:
return false, fmt.Errorf("postgres: provision read role %s: %w", role.name, err)
case superuser:
return false, fmt.Errorf("postgres: provision refuses role %q: it is a superuser; name a least-privilege role instead", role.name)
}
// An existing role may have been created by hand with more than it needs.
statement := "ALTER ROLE " + quoteIdent(role.name) + " LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE PASSWORD " + quoteLiteral(verifier)
if _, err := tx.Exec(ctx, statement); err != nil {
return false, fmt.Errorf("postgres: provision update role %s: %w", role.name, err)
}
return false, nil
}
// setRolePassword stores a new password for role without sending the plaintext
// to the server as SQL.
func setRolePassword(ctx context.Context, tx pgx.Tx, role, password string) error {
verifier, err := scramVerifier(password)
if err != nil {
return err
}
statement := "ALTER ROLE " + quoteIdent(role) + " PASSWORD " + quoteLiteral(verifier)
if _, err := tx.Exec(ctx, statement); err != nil {
return fmt.Errorf("postgres: provision rotate %s password: %w", role, err)
}
return nil
}
// schemaStatements is the fixed part of a provision run: who owns the schema
// and what the runtime role may do in it. Every statement is idempotent, so a
// re-run is a reconciliation rather than a second install.
func schemaStatements(roles provisionRoles) []string {
runtime := quoteIdent(roles.runtime.name)
migration := quoteIdent(roles.migration.name)
return []string{
// The migration role owns the namespace it manufactures the schema in.
"ALTER SCHEMA public OWNER TO " + migration,
// PostgreSQL 15 made this the default for a new database; an install
// created before that may still let every role create objects here.
"REVOKE CREATE ON SCHEMA public FROM PUBLIC",
"GRANT USAGE ON SCHEMA public TO " + runtime,
"GRANT USAGE, CREATE ON SCHEMA public TO " + migration,
// The runtime role reads and writes rows, and nothing else: no TRUNCATE,
// no ownership, no CREATE. The app's queries never ask for more.
"GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO " + runtime,
"GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO " + runtime,
"GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO " + runtime,
// Tables and sequences a future migration creates are owned by the
// migration role, so its default privileges are what keeps the runtime
// role able to use them without a later grant.
"ALTER DEFAULT PRIVILEGES FOR ROLE " + migration + " IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO " + runtime,
"ALTER DEFAULT PRIVILEGES FOR ROLE " + migration + " IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO " + runtime,
"ALTER DEFAULT PRIVILEGES FOR ROLE " + migration + " IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO " + runtime,
}
}
// objectKind is a public-schema object class the migration role must own.
type objectKind string
const (
objectTable objectKind = "table"
objectPartitionedTable objectKind = "partitioned table"
objectSequence objectKind = "sequence"
objectView objectKind = "view"
objectMaterializedView objectKind = "materialized view"
objectForeignTable objectKind = "foreign table"
objectFunction objectKind = "function"
objectProcedure objectKind = "procedure"
)
// schemaObject is one public-schema object whose ownership moves to the
// migration role.
type schemaObject struct {
kind objectKind
// reference is the object's name for a relation, or its regprocedure text
// for a function; both are engine-generated, never operator input.
reference string
}
// ownerStatement renders the ALTER that hands object to role. Relations are
// quoted; a function reference is already the regprocedure form PostgreSQL
// renders, argument types included.
func (o schemaObject) ownerStatement(role string) string {
if o.kind == objectFunction || o.kind == objectProcedure {
return "ALTER " + o.kind.alterKeyword() + " " + o.reference + " OWNER TO " + quoteIdent(role)
}
return "ALTER " + o.kind.alterKeyword() + " " + pgx.Identifier{"public", o.reference}.Sanitize() + " OWNER TO " + quoteIdent(role)
}
// alterKeyword is the object class's ALTER target. A partitioned table is
// altered as a table: PostgreSQL has no ALTER PARTITIONED TABLE.
func (k objectKind) alterKeyword() string {
switch k {
case objectTable, objectPartitionedTable:
return "TABLE"
case objectSequence:
return "SEQUENCE"
case objectView:
return "VIEW"
case objectMaterializedView:
return "MATERIALIZED VIEW"
case objectForeignTable:
return "FOREIGN TABLE"
case objectProcedure:
return "PROCEDURE"
default:
return "FUNCTION"
}
}
// schemaObjects lists the public-schema objects someone other than the
// migration role still owns. Objects an extension owns are left alone, and
// indexes, row types and sequences owned by a table column follow the table's
// owner, so the relation loop does not repeat them. A partition has its own
// dependency on the partitioned table, so it is listed and transferred too.
func schemaObjects(ctx context.Context, tx pgx.Tx, migrationRole string) ([]schemaObject, error) {
rows, err := tx.Query(ctx, `
SELECT c.relkind, c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND c.relkind IN ('r', 'p', 'S', 'v', 'm', 'f')
AND pg_get_userbyid(c.relowner) <> $1
AND NOT EXISTS (
SELECT 1 FROM pg_depend d
WHERE d.classid = 'pg_class'::regclass
AND d.objid = c.oid
AND d.deptype = 'e')
AND (c.relkind <> 'S' OR NOT EXISTS (
SELECT 1 FROM pg_depend d
WHERE d.classid = 'pg_class'::regclass
AND d.objid = c.oid
AND d.refclassid = 'pg_class'::regclass
AND d.deptype IN ('a', 'i')))
ORDER BY c.relkind, c.relname`, migrationRole)
if err != nil {
return nil, fmt.Errorf("postgres: provision list schema objects: %w", err)
}
var objects []schemaObject
for rows.Next() {
var relkind, name string
if err := rows.Scan(&relkind, &name); err != nil {
rows.Close()
return nil, fmt.Errorf("postgres: provision list schema objects: %w", err)
}
kind, ok := relationKinds[relkind]
if !ok {
// An object class this provisioner does not know how to hand over;
// leaving it behind is visible in verifyProvision's counts.
continue
}
objects = append(objects, schemaObject{kind: kind, reference: name})
}
rows.Close()
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("postgres: provision list schema objects: %w", err)
}
rows, err = tx.Query(ctx, `
SELECT p.prokind, p.oid::regprocedure::text
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
AND p.prokind IN ('f', 'p')
AND pg_get_userbyid(p.proowner) <> $1
AND NOT EXISTS (
SELECT 1 FROM pg_depend d
WHERE d.classid = 'pg_proc'::regclass
AND d.objid = p.oid
AND d.deptype = 'e')
ORDER BY p.oid::regprocedure::text`, migrationRole)
if err != nil {
return nil, fmt.Errorf("postgres: provision list schema functions: %w", err)
}
defer rows.Close()
for rows.Next() {
var prokind, reference string
if err := rows.Scan(&prokind, &reference); err != nil {
return nil, fmt.Errorf("postgres: provision list schema functions: %w", err)
}
kind := objectFunction
if prokind == "p" {
kind = objectProcedure
}
objects = append(objects, schemaObject{kind: kind, reference: reference})
}
if err := rows.Err(); err != nil {
return nil, fmt.Errorf("postgres: provision list schema functions: %w", err)
}
return objects, nil
}
// relationKinds maps pg_class.relkind to the object class an ALTER names.
var relationKinds = map[string]objectKind{
"r": objectTable,
"p": objectPartitionedTable,
"S": objectSequence,
"v": objectView,
"m": objectMaterializedView,
"f": objectForeignTable,
}
// verifyProvision refuses a state that would leave the runtime role able to
// change the schema, or either role able to reach superuser powers through a
// role membership.
func verifyProvision(ctx context.Context, tx pgx.Tx, roles provisionRoles) error {
for _, role := range []provisionRole{roles.runtime, roles.migration} {
var superuser bool
if err := tx.QueryRow(ctx, "SELECT rolsuper FROM pg_roles WHERE rolname = $1", role.name).Scan(&superuser); err != nil {
return fmt.Errorf("postgres: provision verify role %s: %w", role.name, err)
}
if superuser {
return fmt.Errorf("postgres: provision left role %q a superuser", role.name)
}
if err := verifyNoSuperuserMembership(ctx, tx, role.name); err != nil {
return err
}
}
var canCreate bool
if err := tx.QueryRow(ctx, "SELECT has_schema_privilege($1, 'public', 'CREATE')", roles.runtime.name).Scan(&canCreate); err != nil {
return fmt.Errorf("postgres: provision verify runtime schema privileges: %w", err)
}
if canCreate {
return fmt.Errorf("postgres: provision left %q able to create schema objects; migrations would not be the migration role's alone", roles.runtime.name)
}
var migrationCanCreate bool
if err := tx.QueryRow(ctx, "SELECT has_schema_privilege($1, 'public', 'CREATE')", roles.migration.name).Scan(&migrationCanCreate); err != nil {
return fmt.Errorf("postgres: provision verify migration schema privileges: %w", err)
}
if !migrationCanCreate {
return fmt.Errorf("postgres: provision left %q unable to create schema objects", roles.migration.name)
}
return nil
}
// verifyNoSuperuserMembership refuses a role that inherits a superuser's
// powers through role membership, which rolsuper alone does not show.
func verifyNoSuperuserMembership(ctx context.Context, tx pgx.Tx, role string) error {
rows, err := tx.Query(ctx, `
SELECT parent.rolname
FROM pg_auth_members m
JOIN pg_roles parent ON parent.oid = m.roleid
JOIN pg_roles member ON member.oid = m.member
WHERE member.rolname = $1 AND parent.rolsuper`, role)
if err != nil {
return fmt.Errorf("postgres: provision verify role memberships: %w", err)
}
defer rows.Close()
if rows.Next() {
var parent string
if err := rows.Scan(&parent); err != nil {
return fmt.Errorf("postgres: provision verify role memberships: %w", err)
}
return fmt.Errorf("postgres: provision refuses role %q: it is a member of superuser role %q", role, parent)
}
return rows.Err()
}
// parseProvisionURLs reads the three roles out of the URLs the installer
// passes, taking a password from the URL's userinfo or, for a passwordless
// URL, from PGPASSWORD. It refuses a plan that would leave the runtime role
// with powers the services never need.
func parseProvisionURLs(opts ProvisionOptions, getenv func(string) string) (provisionRoles, error) {
admin, err := roleFromURL(opts.AdminURL, "admin_url", getenv, false)
if err != nil {
return provisionRoles{}, err
}
runtime, err := roleFromURL(opts.RuntimeURL, "runtime_url", getenv, true)
if err != nil {
return provisionRoles{}, err
}
migration, err := roleFromURL(opts.MigrationURL, "migration_url", getenv, true)
if err != nil {
return provisionRoles{}, err
}
if runtime.name == migration.name {
return provisionRoles{}, fmt.Errorf("postgres: provision: runtime_url and migration_url name the same role %q; they must differ", runtime.name)
}
for _, role := range []provisionRole{runtime, migration} {
if role.name == admin.name {
return provisionRoles{}, fmt.Errorf("postgres: provision: %q is the admin role; runtime_url and migration_url must name the roles being provisioned", role.name)
}
}
return provisionRoles{admin: admin, runtime: runtime, migration: migration}, nil
}
// roleFromURL extracts one login role from a PostgreSQL URL. requirePassword
// is set for the two provisioned roles, whose password must be known so the
// run can set it; the admin role may authenticate through the environment.
func roleFromURL(raw, label string, getenv func(string) string, requirePassword bool) (provisionRole, error) {
if strings.TrimSpace(raw) == "" {
return provisionRole{}, fmt.Errorf("postgres: provision: %s is required", label)
}
parsed, err := url.Parse(raw)
if err != nil || (parsed.Scheme != "postgres" && parsed.Scheme != "postgresql") {
return provisionRole{}, fmt.Errorf("postgres: provision: %s must be a postgres:// URL naming a login role", label)
}
if parsed.User == nil || parsed.User.Username() == "" {
return provisionRole{}, fmt.Errorf("postgres: provision: %s must name the role in its URL userinfo", label)
}
role := provisionRole{name: parsed.User.Username()}
if password, ok := parsed.User.Password(); ok && password != "" {
role.password = password
} else if password := getenv(databasePasswordEnv); password != "" {
role.password = password
}
if requirePassword && role.password == "" {
return provisionRole{}, fmt.Errorf("postgres: provision: %s carries no password and %s is unset", label, databasePasswordEnv)
}
return role, nil
}
// databasePasswordEnv is the variable pgx and the PostgreSQL client tools read
// a connection password from when a URL does not carry one.
const databasePasswordEnv = "PGPASSWORD"
// scramVerifier returns the SCRAM-SHA-256 verifier PostgreSQL stores for
// password. ALTER ROLE accepts a pre-computed verifier, so the plaintext never
// travels in a statement another session's statement log can capture.
func scramVerifier(password string) (string, error) {
if password == "" {
return "", errors.New("postgres: provision: a role password is required")
}
const iterations = 4096
salt := make([]byte, 16)
if _, err := rand.Read(salt); err != nil {
return "", fmt.Errorf("postgres: provision: salt: %w", err)
}
salted := pbkdf2.Key([]byte(password), salt, iterations, sha256.Size, sha256.New)
storedKey := sha256.Sum256(hmacSHA256(salted, []byte("Client Key")))
serverKey := hmacSHA256(salted, []byte("Server Key"))
return fmt.Sprintf("SCRAM-SHA-256$%d:%s$%s:%s",
iterations,
base64.StdEncoding.EncodeToString(salt),
base64.StdEncoding.EncodeToString(storedKey[:]),
base64.StdEncoding.EncodeToString(serverKey),
), nil
}
func hmacSHA256(key, message []byte) []byte {
mac := hmac.New(sha256.New, key)
mac.Write(message)
return mac.Sum(nil)
}
// quoteIdent quotes an SQL identifier such as a role or table name.
func quoteIdent(name string) string { return pgx.Identifier{name}.Sanitize() }
// quoteLiteral quotes an SQL string literal. scramVerifier's output is
// base64 and separators only, so it can never contain the quote being escaped;
// the escaping is here so a future caller cannot inject through the value.
func quoteLiteral(value string) string {
return "'" + strings.ReplaceAll(value, "'", "''") + "'"
}