PostgreSQL

PostgreSQL is faz's most-tested connector. It supports the full SQL surface — SELECT, INSERT, UPDATE, DELETE — gated by your permissions: block, and discovers schemas including column distribution statistics from pg_stats.

Quick example

databases:
  - name: <database>          # used as --database <database> in queries
    type: postgresql
    host: localhost
    port: 5432
    database: <db-name>       # the actual Postgres database on the host
    username: <username>
    password: <password>
    ssl: false

permissions:
  - database: <database>      # must match `name:` above
    access: R
    # access codes:
    # R    read only           — SELECT, EXPLAIN
    # W    write only          — INSERT, UPDATE, DELETE (no SELECT)
    # RW   read + write
    # RA   read + append       — SELECT, EXPLAIN, INSERT
    # RWA  read + write, no DELETE
    # A    admin (incl. DDL)

See Permissions for the full model, per-table overrides, and the operation matrix.

faz query "SELECT * FROM <table> LIMIT 5" --database <database> --table <table>

uv run faz query "SELECT * FROM <table> LIMIT 5" --database <database> --table <table>

python -m faz query "SELECT * FROM <table> LIMIT 5" --database <database> --table <table>

Configuration

faz uses psycopg2's ThreadedConnectionPool with min 2 / max 10 connections. The pool is shared across requests in the same faz serve process — you don't need to tune it for typical AI-agent traffic.

Capabilities

• All standard SQL — SELECT, INSERT, UPDATE, DELETE, JOINs, CTEs, window functions, subqueries.
• DDL (CREATE, ALTER, DROP, TRUNCATE) — only when the policy grants A.
• Schema discovery via information_schema for tables/columns plus pg_stats for distribution metrics (distinct count, null fraction, sample values, min/max).
• EXPLAIN is included in the R access level alongside SELECT.
• Connection pooling is built in; the pool spans every concurrent request.

Limitations

• Stacked statements are blocked at the connector level. A query string with more than one top-level statement separated by ; is rejected before it reaches Postgres — defense-in-depth against stacked-query injection. Even if RBAC would allow each statement individually, the connector refuses to send the combined string. Only literal ;-separated stacking counts; complex single statements pass through fine.

  Blocked — two top-level statements separated by ;:

  SELECT * FROM <table-1>; DELETE FROM <table-2>;

  Allowed — single complex statements. RBAC checks every table they touch:

  -- UNION across two SELECTs
  SELECT <col> FROM <table-1>
  UNION ALL
  SELECT <col> FROM <table-2>;
  
  -- Subquery in FROM
  SELECT * FROM (SELECT * FROM <table> WHERE <condition>) sub;
  
  -- Correlated subquery in WHERE
  SELECT * FROM <table-1> WHERE <fk> IN (SELECT id FROM <table-2>);
  
  -- CTE / WITH clause
  WITH recent AS (SELECT * FROM <table>)
  SELECT * FROM recent JOIN <other-table> USING (<key>);
  
  -- Insert-from-select — one statement, RBAC checks BOTH tables
  INSERT INTO <archive-table> SELECT * FROM <source-table>;

  To run multiple statements, submit them as multiple separate faz query calls — one statement per call.

• pg_stats requires ANALYZE. Schema discovery's distribution metrics depend on Postgres's query planner statistics. Tables that haven't been analyzed (newly populated, or track_counts = off) show null distribution.

• No streaming. Result sets are loaded into memory before being returned. The Guardrails stage caps at max_rows_per_query (default 1000), so large reads need to be paginated explicitly.

Troubleshooting

could not connect to server at startup — Postgres isn't listening on the configured host:port. Verify with psql from the same machine.

password authentication failed for user X — credentials in faz.yaml are wrong, or pg_hba.conf rejects the auth method. psql against the same connection tests both.

SSL connection required — set ssl: true in the connector config. faz then uses sslmode=require.

Schema discovery returns empty — the connecting role doesn't have USAGE on the schemas. Grant it explicitly: GRANT USAGE ON SCHEMA public TO readonly_user; and GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;.

For the broader troubleshooting flow, see Connection failed.

Related

• Databases overview — cross-connector basics.
• Permissions — how the access codes map to Postgres operations.
• faz.yaml — the full config schema.