Files
zeekay c7ffc5e7d2 reconcile: roles, databases, DCS and pg_hba from a declared file
bin/reconcile runs beside sqlha in a sql pod and acts only on the leader,
over the local socket. From a declared file, pinned by the SHA-256 the process
is started with (--sha256, in the pod spec), and with quoted identifiers, it
creates roles LOGIN NOSUPERUSER NOREPLICATION NOBYPASSRLS with no password and
synchronous_commit=on, adds OAuth roles to sql_iam, creates databases with
their owner, revokes CONNECT from PUBLIC on declared databases, adopts an
existing database by ALTER OWNER on each object (never REASSIGN OWNED BY) and
reports what the old owner still holds, reports any sql_iam member with a
password, PATCHes the declared DCS block through Patroni's local REST API, and
reloads pg_hba when its hash changes. Every statement is logged.

Every connection sets lock_timeout and statement_timeout, so a session holding
a lock fails one step with a reported error and the next pass retries, instead
of queueing the table's users behind an ALTER. The file refuses the superuser,
the Patroni replication user (SQL_REPLICATION_USER), sql_iam and pg_*, so a
declaration cannot strip replicator of REPLICATION; a pass alters no role the
file does not name.

bin/test_reconcile.py runs it against a two-member Patroni cluster, on
PostgreSQL 16.15 and 18.6.

managed/LLM.md and managed/deploy/sql.yaml no longer say the shared Postgres
is being retired.

Claude-Session: https://claude.ai/code/session_01SCPMGSQsLNF2YtHFL4bwyp
2026-10-09 04:47:33 -07:00

57 lines
2.3 KiB
YAML

# Hanzo SQL — managed pg-wire endpoint (OPTION-A follow-up path).
#
# Declarative provisioning via the existing `SQL` datastore Kind (operator
# facade over DatastoreSpec — same StatefulSet + ClusterIP Service + headless
# `<name>-hs` + per-replica PVC machinery that backs `docdb`). This reuse is the
# one-way: no new CRD. The only change from the hanzo-sql `crs/sql.yaml` is the
# image — swap `ghcr.io/hanzoai/sql:18` (hanzo-sql, the one shared PostgreSQL,
# which stays and is not this product) for the embedded-Postgres pg-wire endpoint.
#
# A tenant that needs an isolated `postgres://` = one such CR (or a
# `ManagedDatabase` CR, the paid isolated-tenant facade). Consumer DNS =
# `<name>.<ns>.svc.cluster.local:5432`. The DATABASE_URL is synced to the
# consumer via a KMSSecret CR → k8s Secret → env `DATABASE_URL` valueFrom
# secretKeyRef (same path docdb uses for MONGO_URI).
apiVersion: hanzo.ai/v1
kind: SQL
metadata:
name: hanzo-sql
namespace: hanzo
spec:
type: postgresql # pg-wire; endpoint speaks the Postgres protocol
image:
repository: ghcr.io/hanzoai/sql-managed
tag: "v0.1.0" # semver only — never :latest, never sha
pullPolicy: IfNotPresent
replicas: 1 # pglite is single-writer per data dir; do not scale >1
storage:
storageClassName: do-block-storage
size: 10Gi
retentionPolicy: Retain
ports:
- name: data
containerPort: 5432
protocol: TCP
env:
- name: HANZO_SQL_DATA
value: /data/db
# The operator creates the `data` PVC (volumeClaimTemplate) but does NOT
# auto-mount it — the CR must mount it explicitly.
volumeMounts:
- name: data
mountPath: /data
serviceAliases:
- postgres
imagePullSecrets:
- name: ghcr-secret
partOf: data
resources:
requests: { cpu: 50m, memory: 128Mi }
limits: { cpu: "1", memory: 1Gi }
# KNOWN GAP (blocker for this follow-up): DatastoreSpec has no `fsGroup`
# (only the `Service` Kind does). The sql-managed image runs nonroot (65532)
# and cannot write a root-owned DO-block PVC without an fsGroup. Fix = add
# `fsGroup` to DatastoreSpec in operator/src/crd.rs + set it to 65532 here.
# Until then, host the endpoint on the `Service` Kind (has fsGroup+persistence)
# or run the image as root.