Profile PostgreSQL¶

English · Русский

Database profiling is available in stable 1.6.0 and requires explicit read-only configuration, allowlists and resource budgets.

Install the optional driver:

pip install "agent-paranoid-android[postgres]"

Configure one explicit read-only scope. The password setting names another environment variable; it is not the password itself.

export POSTGRES_SOURCE_ID=warehouse
export POSTGRES_HOST=db.example.internal
export POSTGRES_DATABASE=analytics
export POSTGRES_USER=test_data_agent
export POSTGRES_PASSWORD_ENV=WAREHOUSE_PASSWORD
export WAREHOUSE_PASSWORD='replace-in-your-shell'
export POSTGRES_ALLOWED_SCHEMAS=public
export POSTGRES_ALLOWED_TABLES=public.customers,public.orders
export POSTGRES_ALLOWED_COLUMNS=public.customers.customer_id,public.customers.status,public.orders.order_id,public.orders.customer_id,public.orders.state,public.orders.amount

For an intentionally table-wide aggregate profile, an entry may instead use the exact schema.table.* form:

export POSTGRES_ALLOWED_COLUMNS='public.customers.*,public.orders.*'

The parent tables must still appear exactly in POSTGRES_ALLOWED_TABLES. Before any aggregate runs, bounded PostgreSQL catalog metadata expands each wildcard into a frozen, sorted explicit-column snapshot. Profiling queries enumerate quoted column identifiers; they never execute SELECT *. Bare, schema-wide, table-name, embedded, or over-budget wildcards fail closed.

Declared single-column integer and string primary keys are recognized regardless of their name (for example, ticket_no or record_code). Generation uses fresh synthetic identifiers; it does not preserve source keys. Sensitive key fields retain their sensitivity classification. Reprofile saved artifacts to obtain this metadata behavior.

Composite primary keys are currently unsupported: profiling rejects them before aggregate scans, including when the column allowlist exposes only part of the key. It never treats one component as a complete unique key. Other primary-key types require an independently reviewed generation specification. Review the complete relational constraints explicitly when authoring that specification; automatic inference does not support composite uniqueness.

If a platform portal supplies a JDBC endpoint, replace only the separate host, port, database, and TLS settings with a credential-free JDBC-style URL:

export POSTGRES_JDBC_URL='jdbc:postgresql://db.example.internal:5432/analytics?sslmode=verify-full'

Keep POSTGRES_USER, POSTGRES_PASSWORD_ENV, every allowlist, and every budget separate. Userinfo, passwords, unknown query properties, and session-changing options in the URL fail before a connection is opened. This syntax is parsed into the existing Psycopg adapter; Java and JDBC drivers are not used. If both URL and component settings are present, explicitly supplied values must match.

PostgreSQL connections now default to verify-full, which verifies the server certificate and configured hostname. Configure a trusted CA certificate through the Psycopg/libpq trust-root configuration (for example PGSSLROOTCERT) and use a hostname covered by the certificate. Existing installations using the former require default must configure trust before upgrading. Connection failure does not fall back to a weaker mode. Explicit require, verify-ca, or disable requires POSTGRES_ALLOW_INSECURE=true and is intended only for isolated local testing; these modes do not provide full server identity verification.

The database role must already be read-only. The client also requests a read-only transaction, TLS, statement and lock timeouts, and bounded aggregate results. It accepts no arbitrary SQL and never profiles source rows.

Table profiling retains observed minimum/maximum bounds for non-sensitive date and timestamp columns in the existing column-summary query. Inferred generation uses those ranges; timestamp offsets are retained. Sensitive-name columns do not expose date bounds, and all-null columns have none. Invalid or inconsistently typed bounds fail profiling rather than silently inventing a period. Missing bounds elsewhere still require review; this table-profile behavior does not imply date-bound support for profile-query.

Create a safe profile, review a generation specification, generate, and validate. Output paths must not already exist unless the command explicitly supports --overwrite:

test-data-agent profile-postgres --output out/postgres-profile.json
test-data-agent infer-spec out/postgres-profile.json --output out/dataset-spec.yaml
test-data-agent generate out/dataset-spec.yaml --seed 12345 --output out/generated
test-data-agent validate out/dataset-spec.yaml out/generated
test-data-agent export-postgres-sql out/dataset-spec.yaml --seed 12345 --output out/generated.sql

Run the SQL only in the intended local or disposable target database:

psql --set ON_ERROR_STOP=1 --dbname synthetic_target --file out/generated.sql

The SQL file contains one transaction, deterministic quoted CREATE TABLE, foreign-key, and INSERT statements, PostgreSQL scalar literals, and NULL. It is built from validated generated records, not profile query rows. Render or validation failure leaves no partial output file. Repeating export with the same reviewed spec, seed, package, and recorded environment produces the same logical SQL; compare files directly when verifying one environment:

test-data-agent export-postgres-sql out/dataset-spec.yaml \
  --seed 12345 --output out/generated-second.sql
cmp out/generated.sql out/generated-second.sql

To profile a reviewed relation without creating a database view, put one fully qualified single-table query in a local file:

SELECT o.order_id, o.state, o.amount AS amount
FROM public.orders AS o
WHERE o.order_id < 999999

Keep the same physical schema/table/column allowlists and run:

test-data-agent profile-query query.sql \
  --adapter postgres \
  --source-id warehouse \
  --entity orders_query \
  --output out/query-profile.json
test-data-agent infer-spec out/query-profile.json \
  --output out/query-spec.yaml
test-data-agent generate out/query-spec.yaml \
  --seed 12345 --output out/query-generated
test-data-agent validate out/query-spec.yaml out/query-generated

This generation walkthrough uses direct columns and aliases, all explicitly allowed above. A derived expression such as amount * 2 can be profiled under the SQL policy, but automatic infer-spec rejects profiles with unsupported expression dependencies. Do not infer that the expression is translated into a generation formula; review a separate supported specification instead.

The query is parsed locally and must stay inside the documented scalar, projection, and filter subset. A no-row schema probe and bounded aggregate wrappers execute in the forced read-only session. The profile contains a query fingerprint but not the SQL text, its literal, backend messages, or query rows.

The equivalent typed Python entry point keeps the query path and database configuration explicit:

from pathlib import Path

import psycopg

from test_data_agent import (
    SqlQueryAdapter,
    SqlQueryProfileRequest,
    profile_postgres_query_source,
)
from test_data_agent.postgres_config import PostgresConfig

request = SqlQueryProfileRequest(
    adapter=SqlQueryAdapter.POSTGRES,
    source_id="warehouse",
    entity="orders_query",
    query_file=Path("query.sql"),
)
profile = profile_postgres_query_source(
    request,
    config=PostgresConfig.from_env(),
    driver=psycopg,
)

Exact values remain disabled by default. A bounded, reviewed, non-sensitive business enum can be kept locally with its source-qualified identity:

test-data-agent profile-postgres \
  --local-category warehouse.public.orders.state \
  --output out/postgres-profile.json

PII, secrets, identifiers, quasi-identifiers, free text, excessive cardinality, and long values fail closed. Original literals are not sent to providers, MCP, logs, or errors. A column wildcard is not a local-value policy: exact values are considered only when the separate, fully qualified --local-category selector is present and its content checks pass.

The selector authorizes only the bounded value domain and aggregate counts. It does not retain source row order or a mapping that reconstructs source rows. The generated SQL may contain approved values because its input is the validated synthetic dataset.

The same profiling boundary is available from Python:

import psycopg

from test_data_agent.postgres_client import PostgresClient
from test_data_agent.postgres_config import PostgresConfig
from test_data_agent import generate_dataset, infer_dataset_spec, validate_dataset
from test_data_agent.postgres_profiler import dataset_profile_from_postgres

config = PostgresConfig.from_env()
profile = dataset_profile_from_postgres(PostgresClient(config, psycopg))
spec = infer_dataset_spec(profile)
# Review or edit spec here before generation.
rows = generate_dataset(spec, seed=12345)
report = validate_dataset(rows, spec)
assert report.valid

Keep profile and generated artifacts local unless their destination policy explicitly permits otherwise.

For a complete synthetic end-to-end check against a temporary local PostgreSQL cluster, run the examples/local_postgres example. It creates a SELECT-only role, proves that writes are denied, profiles two related tables, validates deterministic generation, executes the exported SQL in an empty target database, and removes the cluster. Run examples/local_postgres/run-jdbc.sh OUTPUT for the same workflow configured by a placeholder JDBC-style URL, or examples/local_postgres/run-wildcard.sh OUTPUT for bounded table-qualified wildcard expansion. Run examples/local_postgres/run-query.sh OUTPUT for the reviewed query-file path.