How to Configure Aurora Postgres Audit Log

Published
Length
4 min read

Luis Felipe Dias

All posts

You set up an aurora postgres audit log by turning on database logging extensions. Then you route those database event records to your central cloud log storage. Most engineering teams start by enabling statement tracking across the whole database cluster. They often pair this setup with broad group roles for their developers. When roles are too broad, audit files fill up with noisy queries fast. Worse, shared database accounts make it hard to see which person ran a command. How the aurora pos

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up an aurora postgres audit log by turning on database logging extensions. Then you route those database event records to your central cloud log storage. Most engineering teams start by enabling statement tracking across the whole database cluster. They often pair this setup with broad group roles for their developers. When roles are too broad, audit files fill up with noisy queries fast. Worse, shared database accounts make it hard to see which person ran a command.

How the aurora postgres audit log works with database roles

Database logging in Postgres tracks events based on roles and object permissions. When you set up group roles, you manage privileges for many users at once. You then grant individual users membership in those group roles. The database engine checks these role permissions every time a user runs a query. If the role allows the action, the database runs it and writes a log entry.

Good logging starts with tight permission scoping inside the database itself. For example, the SELECT privilege allows reading from any column of a table. You can also grant SELECT on only specific columns of a table or view. Notice that the SELECT privilege also allows users to run COPY TO commands. Your audit setup must watch for bulk data exports made through these read privileges.

Default permissions also shape what shows up in your database log files. The special role name PUBLIC grants a privilege to every role on the system. By default, PUBLIC gets CONNECT and TEMPORARY privileges on databases. It also gets EXECUTE privileges on functions and procedures unless the owner revokes them. In PostgreSQL 15 and later, default settings block users from creating objects in the public schema. In older or upgraded databases, an admin must revoke CREATE on the public schema from PUBLIC. Tightening these defaults stops stray object creation before it ever hits your logs.

What native logging solves and what it misses

Native database audit logs are great for recording history after a query finishes. They help security teams check past events during a security review. You can see which database role ran a statement and when it happened. You can also spot failed login attempts and schema changes across your cluster.

Still, native database logging leaves a big operational gap for engineering teams. Database roles and audit settings only record events after the statement runs. They do not control what individual statements do in flight once a user logs in. If five engineers share one read role, the log only shows that single role name. You lose track of which real person ran the query from their laptop.

Audit Log Integrity + End-to-End Encryption: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Native logs also cannot stop risky queries or hide sensitive fields in real time. If an engineer queries a table with customer emails or phone numbers, the database returns everything. Even column privileges are hard to maintain across hundreds of changing tables. Also, logging every single read query puts heavy load on your database instance. Teams often turn off read logging to save disk space and keep the database fast. That trade-off leaves security teams blind to who read sensitive card numbers or national IDs.

Best practices for scoping roles before you log

Before you turn on heavy statement logging, clean up your database roles first. Remove extra grants from the PUBLIC role so new accounts start with zero table rights. Build clear group roles that match real job tasks, like read-only support or schema admin. Grant SELECT only on the exact tables and columns each group needs to do their job.

Next, separate human users from app service accounts in your database setup. App accounts run thousands of safe, repeated queries every minute. Mixing human queries with app traffic makes your audit records hard to read and expensive to store. Give every team member a distinct path to run queries so your records stay clean. When you pair clean role design with runtime controls, you get clear audit trails.

Check every Postgres query on the wire

Database roles and network rules decide who can open a session. They do not read what each SQL statement does after login. With hoop.dev, you run an open-source proxy, a sidecar for your Postgres, inline in the Postgres wire protocol. On every query an AI agent or engineer runs against production, hoop.dev decides to pass, mask, block, or hold it for human review. Your apps and agents keep their login details and change only the host and port in the connection string their tool uses.

Before a query runs, hoop.dev guardrails refuse bad deletes or drops with a real Postgres error, or hold risky queries for approval. On the way back, hoop.dev data masking rewrites sensitive values in returned rows in memory, and every verdict goes to the audit trail. Furthermore, hoop.dev holds SOC 2 Type II, is MIT licensed, and generates evidence for HIPAA, GDPR, and PCI DSS. See the code on GitHub.

Frequently asked questions

Does the SELECT privilege allow users to export table data?

Yes, the SELECT privilege allows reading from tables and views, and it also allows COPY TO. A user with SELECT rights can export rows from any column they have permission to read.

Why can any user run functions in a new Postgres database?

The special PUBLIC role gives default privileges to every role on the database system. By default, PUBLIC receives EXECUTE rights on functions and procedures until the object owner revokes them.

Can native database audit logs rewrite sensitive data in query results?

No, native audit logs only write a record of the query after the database runs it. To rewrite fields like emails, phone numbers, or card numbers in results, you need in-flight data masking.

The query runs. The emails stay hidden.