PostgreSQL

Read a config or secret value from a Postgres table, with native watch via LISTEN/NOTIFY. Built on pgx/v5.

Install

go get github.com/xavidop/mamori/providers/postgres

import _ "github.com/xavidop/mamori/providers/postgres"

Using the ref

A postgres:// ref points at one row of a table (a key/value lookup), optionally selecting a field from a JSON value.

postgres://<table>/<key>[#json-field][?key_col=<c>&val_col=<c>]

Examples

• postgres://settings/greeting - runs SELECT value FROM settings WHERE key = $1 with $1 = 'greeting'.
• postgres://settings/http?val_col=int_value - reads the same row but from the int_value column instead of value.
• postgres://settings/db#host - reads the JSON object at key db and returns its host field.
• postgres://public.settings/feature_x?key_col=name&val_col=data - runs SELECT data FROM public.settings WHERE name = $1.

type Config struct {
	Greeting string `source:"postgres://settings/greeting"`
	Timeout  int    `source:"postgres://settings/http?val_col=int_value"`
	DBPass   secret.String `source:"postgres://secrets/db_password"`
}

The row key is always bound as the $1 parameter, while the table and column names are validated against a strict identifier allowlist (^[A-Za-z_][A-Za-z0-9_]*$, with one optional schema dot) before any query is built, so a ref can never be used for SQL injection. Value.Version is a content hash of the value by default, or the column named by WithVersionColumn (cast to text) for exact native change detection. Values are non-sensitive unless you set WithSensitive(true) or wrap the field in secret.String.

Watch

Watch issues LISTEN <channel> (default mamori_config) and re-queries on each NOTIFY. Have your writes signal the channel, for example with a trigger:

CREATE OR REPLACE FUNCTION mamori_notify() RETURNS trigger AS $$
BEGIN PERFORM pg_notify('mamori_config', NEW.key); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER settings_notify AFTER INSERT OR UPDATE ON settings
FOR EACH ROW EXECUTE FUNCTION mamori_notify();

Configuration

import pgprov "github.com/xavidop/mamori/providers/postgres"

mamori.WithProvider(pgprov.New(pgprov.WithDSN(os.Getenv("DATABASE_URL"))))

Close() is idempotent and terminal: after it returns, every Resolve, and any Watch started after Close, report errors.Is(err, mamori.ErrUnavailable) locally, without contacting the database. It closes the pgxpool.Pool this provider opened lazily. A pool injected with WithPool belongs to the caller and is left open; New followed by Close with no prior Resolve never dials, so there is nothing to close.

Close does not stop a Watch that is already running. On a pool this provider opened, the watch starts failing, but it carries the pool’s own closed-pool error rather than mamori.ErrUnavailable, so errors.Is(err, mamori.ErrUnavailable) is false for it. On a pool injected with WithPool, Close leaves the pool alone and the watch keeps serving live values. Cancel the watch’s own context to stop it. Close does not stop a Watch compares every provider.

Error classification

Beyond the not-found case, query failures are classified by SQLSTATE so mamori.ErrorKind can distinguish them:

PostgreSQL has no rate-limit SQLSTATE class, so nothing maps to rate_limited. Codes not listed above report unknown rather than being guessed at, and the original *pgconn.PgError stays reachable with errors.As. Because the pool is lazy, this is what turns a bad password or a refused connection, which only surface at query time, into a diagnosable kind instead of a bare unknown.

Verified with an in-memory fake (including a test that the identifier allowlist rejects malicious names); live behavior is covered by //go:build integration tests.