Pgaudit Log Format
The pgaudit log format records PostgreSQL queries as structured comma-separated fields inside standard database logs. You set it up by loading the extension and choosing which statement classes to track. Most teams turn on broad session rules to catch every read and write. When database roles stay too wide, these logs flood with noise and hide risky queries. How the pgaudit log format works in PostgreSQL Standard PostgreSQL logs only show the raw text of a slow or failed query. The extension
Audit Log Integrity + Format-Preserving Encryption: the complete guide
Architecture patterns, implementation strategies, and security best practices. Delivered to your inbox.
The pgaudit log format records PostgreSQL queries as structured comma-separated fields inside standard database logs. You set it up by loading the extension and choosing which statement classes to track. Most teams turn on broad session rules to catch every read and write. When database roles stay too wide, these logs flood with noise and hide risky queries.
How the pgaudit log format works in PostgreSQL
Standard PostgreSQL logs only show the raw text of a slow or failed query. The extension hooks into the internal executor to watch statements as they run. It writes structured audit entries right into your normal server log file. Each entry starts with an audit prefix so log tools can find it fast.
After the prefix, the entry lists fields separated by commas. The first field tells you if the event came from a session or object rule. Next, you see a statement ID and a substatement ID for nested queries. The log then shows the command class, such as read, write, or role change.
It also records the exact command type, the object type, and the target table name. The final fields hold the full SQL text and any query parameters. Teams can adjust settings to hide or show parameters and statement text. This layout helps security teams parse database activity without writing complex text parsers.
Setting up session and object logging rules
Database admins control the output using two main tracking modes. Session logging tracks every command from a chosen class for a user or database. If you turn on read and write tracking globally, every single query generates a line. High-traffic apps will fill up disk space fast under global session rules.
Object logging gives you finer control over what hits the disk. It uses a dedicated audit role to mark specific tables and columns for tracking. You grant table permissions like select or update to that special audit role. When any user touches those granted columns, the extension writes an object audit entry.
This split lets you watch sensitive tables while ignoring routine app reads on public data. Still, admins must keep that audit role updated whenever developers add new tables.
Audit Log Integrity + Format-Preserving Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
What native audit logs solve and what they miss
Native audit logs give you a clear record of past database queries. They help you pass security checks and investigate incidents after an issue happens. By breaking nested statements into separate lines, the extension stops users from hiding bad commands.
Still, server log files only tell you what happened after a query finishes. Database roles decide who can log in, not what individual statements do in flight. If an engineer logs in with a shared admin account, the log only shows that shared name. You cannot tie the SQL statement back to a real person on your team.
Native logging also cannot block a dangerous delete statement or rewrite sensitive fields in results. If a user runs a broad select on customer data, raw emails and phone numbers leave the database. Worse, turning on parameter logging writes those same card numbers or national IDs into plain text log files. Teams need a way to inspect queries, require approvals, and mask data before results return.
Check database and API queries on the wire
Identity tools, roles, and network rules decide who opens a session. Setup alone cannot check what each query does or mask sensitive values in a response once a session starts. When workflows touch Postgres, MySQL, SQL Server, MongoDB, ClickHouse, Kubernetes APIs, SSH, or internal HTTP APIs, hoop.dev runs beside those services as an open-source proxy, a sidecar for your Postgres or API. It inspects requests coming in and results going back on the wire.
Your login details and client tools stay as they are while hoop.dev guardrails refuse bad deletes or drops before they run. On responses, hoop.dev data masking rewrites matched sensitive values in memory. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and writes every query and decision to an audit trail that generates evidence for HIPAA, GDPR, and PCI DSS. See the source on GitHub.
Frequently asked questions
What fields appear in a standard audit log line?
Each line shows the audit type, statement IDs, command class, object type, object name, SQL text, and query parameters.
Does turning on parameter logging expose sensitive user data?
Yes, recording parameters writes raw query values like emails, phone numbers, or card numbers directly into your server log files.
How do session audit logs differ from object audit logs?
Session rules record all queries in a chosen class for a user, while object rules only track queries on specific tables.