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, "'", "''") + "'" }