162 lines
5.7 KiB
Go
162 lines
5.7 KiB
Go
// Package pg opens Archie's Postgres connections and applies its one ordered
|
|
// migration series.
|
|
package pg
|
|
|
|
import (
|
|
"context"
|
|
"embed"
|
|
"errors"
|
|
"fmt"
|
|
"io/fs"
|
|
"log/slog"
|
|
|
|
"github.com/jackc/pgx/v5"
|
|
// Registers the "pgx" database/sql driver that sqlx and goose use.
|
|
_ "github.com/jackc/pgx/v5/stdlib"
|
|
"github.com/jmoiron/sqlx"
|
|
"github.com/pressly/goose/v3"
|
|
)
|
|
|
|
//go:embed migrations
|
|
var migrationFiles embed.FS
|
|
|
|
// Open connects with the runtime URL and checks the connection.
|
|
func Open(ctx context.Context, url string) (*sqlx.DB, error) {
|
|
db, err := sqlx.Open("pgx", url)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("open database: %w", err)
|
|
}
|
|
if err := db.PingContext(ctx); err != nil {
|
|
_ = db.Close()
|
|
return nil, fmt.Errorf("reach database: %w", err)
|
|
}
|
|
return db, nil
|
|
}
|
|
|
|
// migrateLockName names the advisory lock that serialises migration and
|
|
// privilege grants across instances.
|
|
const migrateLockName = "migrate"
|
|
|
|
// Migrate applies every pending migration as the owner, then grants the
|
|
// runtime role DML on each module schema. Both steps run under one session
|
|
// advisory lock, so two instances starting together take turns: the second
|
|
// finds the migrations applied and the grants already in place. Granting on the
|
|
// same object from two sessions fails with "tuple concurrently updated", so the
|
|
// grants need the lock as much as the migrations do. It returns an error on any
|
|
// failure, and the caller retries.
|
|
func Migrate(ctx context.Context, log *slog.Logger, ownerURL, runtimeURL string, schemas []string) error {
|
|
db, err := Open(ctx, ownerURL)
|
|
if err != nil {
|
|
return fmt.Errorf("owner connection: %w", err)
|
|
}
|
|
defer func() { _ = db.Close() }()
|
|
|
|
release, err := lockMigrate(ctx, db)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
defer release()
|
|
|
|
files, err := fs.Sub(migrationFiles, "migrations")
|
|
if err != nil {
|
|
return fmt.Errorf("migration files: %w", err)
|
|
}
|
|
provider, err := goose.NewProvider(goose.DialectPostgres, db.DB, files)
|
|
if err != nil {
|
|
return fmt.Errorf("migration provider: %w", err)
|
|
}
|
|
results, err := provider.Up(ctx)
|
|
if err != nil {
|
|
return fmt.Errorf("apply migrations: %w", err)
|
|
}
|
|
for _, r := range results {
|
|
log.InfoContext(ctx, "migration applied", "migration", r.Source.Version, "file", r.Source.Path, "took", r.Duration)
|
|
}
|
|
return grantRuntime(ctx, db, runtimeURL, schemas)
|
|
}
|
|
|
|
// lockMigrate waits for the migration advisory lock and holds it on a dedicated
|
|
// connection until release is called. Goose's own session locker covers only
|
|
// the migrations, which leaves the grants unprotected, so the lock is taken here
|
|
// around both.
|
|
func lockMigrate(ctx context.Context, db *sqlx.DB) (release func(), err error) {
|
|
conn, err := db.Conn(ctx)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("lock migrations: %w", err)
|
|
}
|
|
if _, err := conn.ExecContext(ctx, "SELECT pg_advisory_lock(hashtextextended($1, 0))", migrateLockKey()); err != nil {
|
|
_ = conn.Close()
|
|
return nil, fmt.Errorf("lock migrations: %w", err)
|
|
}
|
|
return func() {
|
|
// Closing the connection would release the lock too, but the pool may
|
|
// keep it open.
|
|
_, _ = conn.ExecContext(context.WithoutCancel(ctx),
|
|
"SELECT pg_advisory_unlock(hashtextextended($1, 0))", migrateLockKey())
|
|
_ = conn.Close()
|
|
}, nil
|
|
}
|
|
|
|
func migrateLockKey() string { return "archie." + migrateLockName + ".owner" }
|
|
|
|
// grantRuntime gives the runtime role USAGE and DML on each schema, never DDL.
|
|
func grantRuntime(ctx context.Context, db *sqlx.DB, runtimeURL string, schemas []string) error {
|
|
cfg, err := pgx.ParseConfig(runtimeURL)
|
|
if err != nil {
|
|
return fmt.Errorf("parse runtime URL: %w", err)
|
|
}
|
|
role := pgx.Identifier{cfg.User}.Sanitize()
|
|
for _, schema := range schemas {
|
|
name := pgx.Identifier{schema}.Sanitize()
|
|
for _, stmt := range []string{
|
|
"GRANT USAGE ON SCHEMA " + name + " TO " + role,
|
|
"GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA " + name + " TO " + role,
|
|
// A table keyed by a serial draws from a sequence, which is its
|
|
// own grant: a module whose insert draws on one would fail DML it
|
|
// is otherwise allowed.
|
|
"GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA " + name + " TO " + role,
|
|
} {
|
|
if _, err := db.ExecContext(ctx, stmt); err != nil {
|
|
return fmt.Errorf("grant runtime role on %s: %w", schema, err)
|
|
}
|
|
}
|
|
}
|
|
return nil
|
|
}
|
|
|
|
// ServeLockKey is the advisory-lock key the serving processes hold in its shared
|
|
// form and a restore takes exclusively: several processes may serve at once, and
|
|
// none may serve while a restore runs. The other archie binaries lock the same
|
|
// key through internal/infrastructure/postgres, so there is one name for it.
|
|
func ServeLockKey(name string) string { return "archie." + name + ".owner" }
|
|
|
|
// ServeLockName is the one name every serving process claims and every restore
|
|
// takes exclusively. A serving process and a restore that named different locks
|
|
// would exclude nothing.
|
|
const ServeLockName = "serve"
|
|
|
|
// ClaimServe holds the serve lock shared for as long as the process serves. It
|
|
// fails when a restore holds the lock exclusively, so a process never starts on a
|
|
// database another process is restoring.
|
|
func ClaimServe(ctx context.Context, db *sqlx.DB, name string) (release func(), err error) {
|
|
conn, err := db.Conn(ctx)
|
|
if err != nil {
|
|
return nil, fmt.Errorf("claim the serve lock: %w", err)
|
|
}
|
|
var taken bool
|
|
if err := conn.QueryRowContext(ctx,
|
|
"SELECT pg_try_advisory_lock_shared(hashtextextended($1, 0))", ServeLockKey(name)).Scan(&taken); err != nil {
|
|
_ = conn.Close()
|
|
return nil, fmt.Errorf("claim the serve lock: %w", err)
|
|
}
|
|
if !taken {
|
|
_ = conn.Close()
|
|
return nil, errors.New("a restore holds this database")
|
|
}
|
|
return func() {
|
|
_, _ = conn.ExecContext(context.WithoutCancel(ctx),
|
|
"SELECT pg_advisory_unlock_shared(hashtextextended($1, 0))", ServeLockKey(name))
|
|
_ = conn.Close()
|
|
}, nil
|
|
}
|