Skip to content

Guides

Connect PostgreSQL (pgaudit)

Observe PostgreSQL access with pgAudit: configure logs, stream events, preserve attribution and produce read/write access-map edges.

Last updated:

The pgAudit connector tails PostgreSQL’s structured audit log and emits one access-map edge per audited data access. The read/write mode is taken verbatim from pgAudit’s class (READ, WRITE, DDL) — never inferred from the SQL text. The connector is read-only over the log file and never opens a connection to the database.

For the general source model and config structure, start with Connect a source. This page covers PostgreSQL-side prerequisites, the full config surface, attribution mechanics, and operational considerations for production estates.

PostgreSQL prerequisites

The connector reads pgAudit output, so PostgreSQL must be configured to produce it. Install the extension (apt install postgresql-<version>-pgaudit on Debian/Ubuntu), load it via shared_preload_libraries, restart, then CREATE EXTENSION pgaudit in each database you want to observe.

The minimum postgresql.conf settings for useful output:

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'read, write'       # READ and WRITE cover data access
log_destination = 'jsonlog'        # recommended for streaming; csvlog also works
logging_collector = on
log_directory = '/var/log/postgresql'
log_filename = 'postgresql.json'

Restart after shared_preload_libraries; reload (SELECT pg_reload_conf()) after the rest. Verify by running a query and confirming a pgAudit entry appears in the log — the entry’s message field contains the pgAudit structured line (class, command, object name). If only standard PostgreSQL messages appear, the extension is not loaded or not configured.

Configuration

The connector is selected by kind: "pgaudit" in the OLIVARES_SOURCES_CONFIG JSON file. A complete example:

{
  "sources": [
    {
      "name": "prod-postgres",
      "kind": "pgaudit",
      "tenant": "acme",
      "config": {
        "log_path": "/var/log/postgresql/postgresql.json",
        "format": "jsonlog",
        "follow": "true",
        "shared_accounts": "app_pool,reporting"
      }
    }
  ]
}

All config values are strings. The keys are owned by the pgAudit connector:

  • log_path (required) — absolute path to the PostgreSQL log file. The connector reads this file; it does not discover log files by convention.
  • format — csvlog or jsonlog. Defaults to csvlog. Controls the parser the connector uses; it must match the log_destination PostgreSQL is actually writing.
  • follow — when "true", tail the file continuously (streaming mode). This applies to jsonlog only. A csvlog file is read as a batch because CSV records can span newlines, making a streaming tail unreliable. See Batch vs streaming below.
  • shared_accounts — comma-separated list of PostgreSQL roles or application_name values that are pooled or shared across multiple callers. Access attributed to any of these is marked approximate deliberately. See Attribution and shared accounts.

Do not invent config keys beyond these four. The connector’s config surface is intentionally narrow — it reads a log file, it does not manage PostgreSQL.

Attribution and shared accounts

The access map needs to answer “which agent did this.” pgAudit log entries carry the PostgreSQL role (user) and, when set, the session’s application_name. The connector uses both to build the edge’s origin.

Per-agent identity earns a firm edge. When each agent sets a distinct application_name on its connection (or uses a dedicated role), the connector attributes each access unambiguously and the edge is firm.

Shared roles collapse to approximate. When multiple agents share a connection pool, a common service account, or a generic application_name, the connector cannot separate callers. Every access through that identity is marked approximate, and the product says so openly rather than pretending it can distinguish agents it cannot. This is why shared_accounts exists: declaring pooled identities up front tells the connector to mark them approximate immediately.

The governance implication is direct: an approximate edge cannot support a firm least-privilege finding. If you need per-agent governance over database access, issue per-agent credentials or application_name values. See Fidelity for the full attribution model.

Coverage tier

pgAudit is clean coverage. The READ, WRITE, and DDL classes are authoritative — PostgreSQL’s audit subsystem classifies the statement, and the connector takes that classification verbatim. There is no inference, no SQL parsing, and no heuristic.

An edge’s mode is R (read), RW (readwrite, for WRITE and DDL classes), or left as the class dictates. The connector never upgrades or downgrades what pgAudit reported. See Fidelity — coverage for how clean compares to lossy and opaque sources.

Batch vs streaming

  • csvlog — batch. CSV records can span newlines (a query with embedded newlines produces a multiline row), so a streaming tail cannot reliably split records. The connector reads the file as a complete batch; set poll_seconds on the source entry to re-run on an interval.
  • jsonlog with follow: "true" — streaming. Each JSON entry is a single line, so a tail is safe. The connector holds the file open and emits edges as PostgreSQL writes them.

For near-real-time edges, jsonlog with follow is the recommended configuration. For periodic audits or lower-volume databases, csvlog batch is sufficient.

What the edges look like

Each pgAudit log entry that the connector parses produces one access-map edge with these fields:

  • Origin — the PostgreSQL role, qualified by application_name when present. Example: reporting_agent or app_pool/invoice-service.
  • Resource — the fully qualified object: database.schema.table. Example: prod.public.orders.
  • Mode — R (from pgAudit class READ) or RW (from pgAudit class WRITE or DDL).
  • Attribution — firm if the origin resolves to a single agent identity, approximate if it matches a shared_accounts entry or cannot be disambiguated.
  • Source — identifies this connector instance by name.

The edge carries no SQL text, no row data, and no query parameters. It records only the structural fact that an identity accessed an object in a given mode.

Operational notes

Log rotation. When PostgreSQL rotates its log file (via log_rotation_age or log_rotation_size), the connector must be pointed at the current file. If your rotation renames the active file, the follow tail will continue reading the old file descriptor until it is exhausted and then stop. Point log_path at a stable symlink or use a naming convention that the connector can follow. The connector does not manage rotation itself.

High-volume estates. pgAudit can produce substantial log volume on busy databases, especially with pgaudit.log = 'all'. Scope pgaudit.log to the classes you need (typically read, write) and consider per-database or per-role filtering via pgaudit.log_relation to reduce noise without losing coverage.

Multiple PostgreSQL instances. Each instance needs its own source entry in OLIVARES_SOURCES_CONFIG with a distinct name and its own log_path. There is no multi-instance discovery — each source watches exactly one log file.

Permissions. The collector process needs read access to the PostgreSQL log file. It does not need a database connection, a PostgreSQL role, or any network access to the database server. File-system read permission is the only requirement.

Search documentation