PostgreSQL Store

The PostgreSQL store is the recommended backend for production deployments. It uses pgxpool from the pgx/v5 driver for high-performance, pooled connections and ships with embedded SQL migrations that create all required tables automatically.

Installation

go get github.com/xraph/vault
go get github.com/jackc/pgx/v5

Creating a store

From a connection string

import "github.com/xraph/vault/store/postgres"

store, err := postgres.New(ctx, "postgres://user:pass@localhost:5432/vault?sslmode=disable")
if err != nil {
    log.Fatal(err)
}
defer store.Close()

postgres.New creates a *pgxpool.Pool internally, connects to the database, and returns a ready-to-use store.

From an existing pool

If you already have a *pgxpool.Pool (for example, shared with other parts of your application), use NewFromPool:

import (
    "github.com/jackc/pgx/v5/pgxpool"
    "github.com/xraph/vault/store/postgres"
)

pool, err := pgxpool.New(ctx, connString)
if err != nil {
    log.Fatal(err)
}

store := postgres.NewFromPool(pool)

NewFromPool does not take ownership of the pool -- it does not close the pool when store.Close() is called. You are responsible for closing the pool separately if you created it yourself.

Options

Option	Signature	Description
`WithLogger`	`WithLogger(l *slog.Logger) StoreOption`	Sets the structured logger for migration progress and internal errors. Defaults to `slog.Default()`.

store, err := postgres.New(ctx, connString,
    postgres.WithLogger(slog.New(slog.NewJSONHandler(os.Stdout, nil))),
)

Migrations

The store ships with five embedded SQL migration files (//go:embed migrations/*.sql) that are applied in order by Migrate:

File	Tables created
`001_secrets.sql`	`vault_secrets`, `vault_secret_versions`
`002_flags.sql`	`vault_flags`, `vault_flag_rules`, `vault_flag_overrides`
`003_config.sql`	`vault_config`, `vault_config_versions`, `vault_overrides`
`004_rotation.sql`	`vault_rotation_policies`, `vault_rotation_records`
`005_audit.sql`	`vault_audit`

Total: 11 tables created across the five migration files.

Call Migrate once at application startup:

if err := store.Migrate(ctx); err != nil {
    log.Fatal("migration failed:", err)
}

Migration tracking

Migrations are tracked in a vault_migrations table that is created automatically. Each migration file name is recorded with an applied_at timestamp. On subsequent calls to Migrate, already-applied migrations are skipped. Applied migrations are logged at the Info level through the configured logger.

Table schemas

vault_secrets

Column	Type	Notes
`id`	`TEXT PRIMARY KEY`	TypeID (e.g. `sec_01h...`)
`key`	`TEXT NOT NULL`	Secret key name
`app_id`	`TEXT NOT NULL`	Application scope
`encrypted_value`	`BYTEA NOT NULL`	AES-256-GCM ciphertext
`encryption_alg`	`TEXT`	Algorithm identifier (e.g. `AES-256-GCM`)
`encryption_key_id`	`TEXT`	Key identifier for rotation tracking
`version`	`BIGINT NOT NULL`	Auto-incremented version number
`metadata`	`JSONB`	User-defined key-value metadata
`expires_at`	`TIMESTAMPTZ`	Optional expiration timestamp
`created_at`	`TIMESTAMPTZ NOT NULL`	Creation timestamp
`updated_at`	`TIMESTAMPTZ NOT NULL`	Last update timestamp

Unique constraint: (key, app_id)

vault_secret_versions

Column	Type	Notes
`id`	`TEXT PRIMARY KEY`	TypeID (e.g. `ver_01h...`)
`secret_key`	`TEXT NOT NULL`	References the secret key
`app_id`	`TEXT NOT NULL`	Application scope
`version`	`BIGINT NOT NULL`	Version number
`encrypted_value`	`BYTEA NOT NULL`	Ciphertext at this version
`created_by`	`TEXT`	Who created this version
`created_at`	`TIMESTAMPTZ NOT NULL`	Version creation timestamp

Unique constraint: (secret_key, app_id, version). Indexed on (secret_key, app_id).

vault_flags

Column	Type	Notes
`id`	`TEXT PRIMARY KEY`	TypeID (e.g. `flag_01h...`)
`key`	`TEXT NOT NULL`	Flag key
`type`	`TEXT NOT NULL`	Value type: `bool`, `string`, `int`, `float`, `json`
`default_value`	`JSONB NOT NULL`	Default when no rule matches
`description`	`TEXT`	Human-readable description
`tags`	`JSONB`	Tag array for categorization
`variants`	`JSONB`	Named variant definitions
`enabled`	`BOOLEAN NOT NULL`	Kill switch (disabled = always return default)
`app_id`	`TEXT NOT NULL`	Application scope
`metadata`	`JSONB`	User-defined metadata
`created_at`	`TIMESTAMPTZ NOT NULL`	Creation timestamp
`updated_at`	`TIMESTAMPTZ NOT NULL`	Last update timestamp

Unique constraint: (key, app_id)

vault_flag_rules

Stores targeting rules ordered by priority. Indexed on (flag_key, app_id, priority). Each rule has a type (e.g. when_tenant, rollout, schedule), a config JSONB column with rule parameters, and a return_value JSONB column.

vault_flag_overrides

Per-tenant flag value overrides. Unique constraint: (flag_key, app_id, tenant_id).

vault_config and vault_config_versions

Mirror the secrets pattern with versioned config entries. vault_config has a unique constraint on (key, app_id). vault_config_versions has a unique constraint on (config_key, app_id, version) and an index on (config_key, app_id).

vault_overrides

Per-tenant config overrides. Unique constraint: (key, app_id, tenant_id).

vault_rotation_policies

Rotation schedules with interval_ns (stored as BIGINT nanoseconds), enabled flag, and last_rotated_at / next_rotation_at timestamps. Unique constraint: (secret_key, app_id).

vault_rotation_records

Append-only rotation history. Indexed on (secret_key, app_id, rotated_at DESC).

vault_audit

Append-only audit log. Indexed on (app_id, created_at DESC) and (key, app_id, created_at DESC) for efficient time-range queries.

Lifecycle methods

Method	Behaviour
`Migrate(ctx)`	Runs all pending embedded SQL migrations in lexicographic order
`Ping(ctx)`	Calls `pool.Ping(ctx)` to verify database connectivity
`Close()`	Calls `pool.Close()` to release all pooled connections

UPSERT patterns

The PostgreSQL store uses INSERT ... ON CONFLICT DO UPDATE (UPSERT) patterns for write operations. This ensures idempotent writes -- calling SetSecret or SetConfig with the same key either creates or updates the entry atomically.

When to use

Production deployments -- Durable, ACID-compliant storage with connection pooling.
Staging environments -- Mirror production behaviour exactly.
Multi-instance deployments -- All application instances share the same database.
Compliance requirements -- Audit trail persisted in PostgreSQL with indexed queries.

Complete example

package main

import (
    "context"
    "fmt"
    "log"
    "log/slog"
    "os"

    "github.com/xraph/vault/config"
    "github.com/xraph/vault/crypto"
    "github.com/xraph/vault/flag"
    "github.com/xraph/vault/secret"
    "github.com/xraph/vault/store/postgres"
)

func main() {
    ctx := context.Background()

    connString := os.Getenv("DATABASE_URL")
    if connString == "" {
        connString = "postgres://localhost:5432/vault?sslmode=disable"
    }

    // Create the PostgreSQL store with a logger.
    store, err := postgres.New(ctx, connString,
        postgres.WithLogger(slog.Default()),
    )
    if err != nil {
        log.Fatal("connect:", err)
    }
    defer store.Close()

    // Run migrations (idempotent -- safe to call on every startup).
    if err := store.Migrate(ctx); err != nil {
        log.Fatal("migrate:", err)
    }

    // Verify connectivity.
    if err := store.Ping(ctx); err != nil {
        log.Fatal("ping:", err)
    }

    // Create an encryptor with a 32-byte key.
    key := make([]byte, 32)
    copy(key, "my-secret-encryption-key-32byte")
    enc, _ := crypto.NewEncryptor(key)

    // Wire up services using the shared store.
    secretSvc := secret.NewService(store, enc, secret.WithAppID("myapp"))
    cfgSvc := config.NewService(store, config.WithAppID("myapp"))

    // Store and retrieve a secret.
    meta, err := secretSvc.Set(ctx, "api_key", []byte("sk-live-abc123"), "")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Secret stored: key=%s version=%d\n", meta.Key, meta.Version)

    sec, _ := secretSvc.Get(ctx, "api_key", "")
    fmt.Printf("Decrypted: %s\n", string(sec.Value))

    // Store and read config.
    _ = cfgSvc.Set(ctx, "max_retries", 3, "")
    fmt.Printf("Max retries: %d\n", cfgSvc.Int(ctx, "max_retries", 1))

    // Define and evaluate a feature flag.
    _ = store.DefineFlag(ctx, &flag.Definition{
        Key:          "dark_mode",
        Type:         flag.TypeBool,
        DefaultValue: false,
        Enabled:      true,
        AppID:        "myapp",
    })

    engine := flag.NewEngine(store)
    val, _ := engine.Evaluate(ctx, "dark_mode", "myapp")
    fmt.Printf("dark_mode = %v\n", val)
}

PostgreSQL Store

On this page