teyfix/postgres repository overview

⁠PostgreSQL bundle

teyfix/postgres packages Supabase's PostgreSQL 17 extension binaries with optional PgBouncer, postgres-exporter, PgBouncer exporter and SQL Exporter. The shared outer Process Compose imports/validates YAML through CUE and renders native artifacts with gomplate. The inner Process Compose owns the lifecycle: PostgreSQL → declarative provisioning → bounded migration hook → enabled companions. Container readiness checks provisioning, PostgreSQL, the pooler admin connection and the exporter's actual pg_up metric.

⁠Build and run

docker buildx bake postgres-smoke
docker buildx bake postgres --load
mkdir -p .state/postgres/{data,logs,pooler-logs}
# Supply POSTGRES_PASSWORD, APP_PASSWORD and READER_PASSWORD in your environment.
docker compose -f svc/postgres/example/compose.yaml up -d --wait

The example mounts postgres.yaml⁠. Use an explicit published build tag in consumers; dev is only a local build tag. Both amd64 and arm64 run the integration tests in CI. Versions live in docker-bake.hcl.

⁠Configuration

Mount one YAML document at /config/postgres.yaml (override POSTGRES_CONFIG). Unknown fields and invalid identifiers fail validation. Names are lowercase SQL identifiers, at most 63 characters. App role names cannot use postgres, pg_, teyfix_ or supabase_ reserved names/prefixes. Configuration changes apply on restart, including with existing data.

Passwords accept {from_env: NAME} or {from_file: /run/secrets/name}. Files may end in one newline. Resolved values must be nonempty printable ASCII; quotes, backslashes and dollar signs are supported. CUE validates identifiers and password values; gomplate renders SQL with escaped literal quotes and standard_conforming_strings enabled. Passwords are never interpolated into dollar-quoted procedural bodies. Generated files are private and are not included in the image. Application roles are always non-superuser, without database/role creation, replication or RLS bypass. Membership lists reconcile on every start.

A database's public_schema has separate create and usage booleans, both false by default; these control PUBLIC grants. Schema entries accept name, owner, and grants. Each grant has a role and lists for schema (USAGE/CREATE), tables, sequences, functions, and creators. Existing object privileges and future privileges for each listed creator are reconciled. For example:

schemas:
  - name: app
    owner: app_owner
    grants:
      - role: reader
        schema: [USAGE]
        tables: [SELECT]
        sequences: [SELECT]
        creators: [app_owner, migration_user]
search_paths:
  - role: reader
    schemas: [app, public]

Creator roles must name the actual role executing future CREATE statements. Search paths are ordered, per role and per database. Omitted privilege categories are unmanaged; explicit lists manage that category, including an empty list to revoke direct privileges. Ownership rights and privileges inherited through memberships or PUBLIC are not erased by revoking a direct grant. New database PUBLIC CONNECT is controlled explicitly; configured roles receive CONNECT through the database's connect list.

Reconciliation does not delete absent databases, schemas, roles or extensions. Explicit empty grant lists revoke the corresponding direct/default privileges for that grant entry; removing an entire entry leaves its previous grants. Configured memberships and database CONNECT for declared roles are reconciled. Use deliberate SQL migrations for destructive changes and changes outside this bounded interface. Failed provisioning prevents readiness and exits the container; prior successful changes are retained and safe to reconcile on the next start.

⁠Supabase base and extensions

The image retains Supabase's published Nix-built PostgreSQL and extension closure. It does not build Nix. It deliberately replaces the upstream entrypoint, Supabase service roles/migrations and configuration with native initdb, psql provisioning and image-owned configuration. This is a general PostgreSQL instance, not a complete Supabase deployment. Start a new cluster with this image; importing an existing Supabase data directory is not a supported migration path.

ldap2pg was evaluated for YAML roles/grants; its scope does not cover database, extension and companion provisioning. Native psql handles this bounded contract without a second reconciliation framework or separate state store.

Inspect the actual per-database catalog:

docker compose -f svc/postgres/example/compose.yaml exec postgres /usr/local/libexec/teyfix/postgres/extensions.sh app

Declare extensions: [{name: vector, schema: public}] on a database. Optional version requests a specific available version (including an explicit upgrade on existing state). A mismatched existing extension schema fails rather than moving objects implicitly. Availability does not imply activation or universal compatibility: preload requirements, fixed extension schemas, dependencies and additional services still apply. Native CREATE EXTENSION errors are surfaced. vector and pg_stat_statements activation are tested; other catalog entries are exposed for consumer-specific validation. Pgsodium/Vault key hooks store a key inside PGDATA when used; this does not claim every Supabase extension has been integration-tested.

⁠Connections and migrations

• PostgreSQL: 5432, direct connection for migrations and session features.
• PgBouncer: 6432, when enabled. Declared login roles use exact PostgreSQL SCRAM verifiers; no second password list is configured by the consumer.
• postgres-exporter: 9187, when enabled. Local peer authentication maps its separate OS user to teyfix_metrics with pg_monitor, connecting directly to PostgreSQL. The administrator is not used for metrics.

The pooler exposes only configured database mappings, without a catch-all *. Its internal admin user is generated privately on each startup. Transaction pooling does not preserve client session state; choose session mode or the direct port for clients that need it. This image uses plain TCP inside the project network; publish host ports only where needed. TLS termination is consumer-owned.

The optional migration argv runs on every startup after provisioning and before companions/readiness. It receives PGHOST=/run/postgresql, PGPORT=5432, PGUSER=postgres and the configured PGDATABASE. Mount application SQL/tools read-only or derive an image containing the application's migration binary. Keep the command idempotent or use the application's migration ledger. An exit failure or timeout fails startup. There is no built-in application schema or migration framework.

⁠Storage and logs

• /var/lib/postgresql/data: PGDATA, bind to project .state/postgres/data.
• /var/log/postgresql: native PostgreSQL JSON (*.json) and text (*.log) files, bind to .state/postgres/logs. JSON files are mode 0644 for external Vector collectors; mount this directory read-only into Vector.
• /var/log/pgbouncer: native text pooler logs; bootstrap/exporter diagnostics remain stdout/stderr. No Vector process is bundled.
• /run/teyfix-postgres and /run/pgbouncer: generated private runtime files.

PostgreSQL rotates files by age/size. Retention/deletion belongs to the consumer. SQL statement logging is disabled by default and provisioning sessions suppress it; enabling duration logging may expose application query values. Runtime readiness and logs do not collect or forward themselves.

docker compose down -v does not remove these bind-mounted directories. A scoped disposable-state reset removes this project's PostgreSQL data/log directories while the service is stopped; preserve unrelated source/cache/state.

⁠Application SQL metrics

Keep postgres-exporter for standard PostgreSQL collectors. Its old extend.query-path interface is deprecated; this image uses an optional SQL Exporter⁠ process for custom application queries instead. It starts after migration, validates collector configuration natively, and uses local peer authentication as teyfix_metrics. No administrator password or second connection secret is needed.

metrics:
  enabled: true
  sql:
    enabled: true
    database: app
    collector_files: [/config/metrics/*.collector.yaml]
    min_interval: 30s

Mount a consumer-owned collector file, for example:

collector_name: application
metrics:
  - metric_name: app_items_total
    type: gauge
    help: Current item count
    values: [count]
    query: SELECT count(*) AS count FROM app.items

In that database's schemas[].grants, explicitly grant teyfix_metrics schema USAGE and the required object privileges. Add the actual table creator roles to creators for future objects. The image grants CONNECT to the selected declared database; pg_monitor alone does not grant application-table access. SQL-only metrics do not need the standard exporter enabled. Keep query cost and scrape frequency appropriate for the application.

Scrape application metrics at :9399/metrics; standard PostgreSQL metrics remain at :9187/metrics. Readiness checks successful scrapes for enabled exporters. Legacy postgres-exporter query YAML needs conversion to SQL Exporter's native collector format; it cannot be mounted unchanged. TMDB title/person definitions remain in TMDB Embed, not in this image. See bootstrap conventions⁠.

Log directories can be overridden with paths.log in the service YAML. Native data directories remain unchanged. The pooler log directory is independently configurable with paths.pooler_log.

⁠PgBouncer metrics and tuning

Set pooler.metrics.enabled: true to start PgBouncer exporter on port 9127. This requires pooler.enabled: true and is independent of PostgreSQL and SQL metrics. The bundle creates a separate stats_users identity with a private generated pgpass file; it has no PostgreSQL role and cannot administer PgBouncer. Readiness requires pgbouncer_up 1 from an actual scrape. No consumer credential file is needed.

Pooler tuning also accepts reserve_pool_size (0), reserve_pool_timeout (5 seconds), server_idle_timeout (600 seconds), query_wait_timeout (30 seconds), and server_connect_timeout (5 seconds). Budget reserve connections alongside the main pool and PostgreSQL limits.

Maintained from the source documentation⁠. See published tags⁠ and the service integration guide⁠.