How to Configure RDS Postgres Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up an rds postgres audit log by turning on database logging and role rules. This setup writes user actions and queries to your database log files. Teams often start by turning on full statement logs for all users. That approach creates huge log files and slows down the database. Worse, when engineers share one database role, the log only shows the shared role name. You still do not know which person ran a bad query. How the native rds postgres audit log and roles work A good audit tra

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 rds postgres audit log by turning on database logging and role rules. This setup writes user actions and queries to your database log files. Teams often start by turning on full statement logs for all users. That approach creates huge log files and slows down the database. Worse, when engineers share one database role, the log only shows the shared role name. You still do not know which person ran a bad query.

How the native rds postgres audit log and roles work

A good audit trail starts with clear database roles and tight rules. In PostgreSQL, you can set up group roles to manage rules for many users. You then add each user to the right group role. This beats giving rules to one person at a time. When each person logs in with their own role, the log records their role name.

You also need to lock down default rules on the database. A special role called PUBLIC gives rules to every role on the system. By default, PUBLIC can connect to databases and make temp tables. It can also run functions and procedures. The owner of an object can take away these default rules at any time.

Older database versions also let every user create objects in the public schema. In version 15 and later, the default setup blocks this behavior. If you upgraded from an older version, you must take away that create rule from PUBLIC. Once roles are clean, the database engine writes matched queries to the log storage.

What native logging solves and what it misses

Native database logs help you check what happened after an incident. They record which database role ran a statement and when it ran. You can pair these logs with column rules to limit read actions. For example, the SELECT rule lets a role read specific columns of a table or view. You can leave out sensitive columns when you grant read rules to a group.

Still, native roles and logs leave big gaps during daily work. First, the SELECT rule also lets a user export data out of the table. Second, maintaining column rules across hundreds of tables takes too much time. Most teams give up and grant broad read rules to whole tables. When an engineer queries that table, raw emails, phone numbers, and card numbers return in full.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Third, database logs only write down events after the query finishes. They cannot stop a bad write query before it runs. They also cannot ask a manager to approve a risky command in real time. Finally, apps and engineers often use shared roles from a secret store. When that happens, your audit log cannot tie a query to a real person.

Closing the gap for live database queries

To fix this, teams must link every database session to a real user identity. You should not hand out static database passwords to engineers. Instead, let users log in with their work identity provider. Then every single query ties back to a real person in the log. You can also apply data masking in flight. Masking rewrites matched fields like national IDs or card numbers in query results. This keeps raw data safe while engineers check production issues.

Add query and result controls beside Postgres

Grants and pools govern who logs into PostgreSQL. Yet every query in an open session still runs with that role's full scope. In practice, hoop.dev sits inline in the wire protocol beside your database as a sidecar for your Postgres. For every plain or prepared query an agent or human runs against production, hoop.dev decides to pass, mask, block, or escalate it for review.

When a tool or agent sends SQL, hoop.dev Postgres controls block banned statements before execution or hold risky ones for human approval via an AI Analyzer you configure. Matched sensitive columns in result rows are masked in memory before they reach the caller. Each query and decision is logged per listener, which generates evidence for SOC 2, HIPAA, and GDPR audits. View the binary on GitHub.

Frequently asked questions

Why does my RDS Postgres audit log show the same user for every query?

Your team is likely logging in with a shared database role. When multiple people use one password, the database only sees that single role name. Give each user an individual login or tie sessions to your identity provider.

Can native PostgreSQL rules hide sensitive columns from audit and read queries?

Yes, you can grant the SELECT rule on specific columns of a table or view. Still, managing column rules across many tables is hard to keep up over time. Many teams use dynamic data masking to rewrite emails and phone numbers in results instead.

How do I stop all users from making new tables in the public schema?

In PostgreSQL 15 and newer, the default setup already stops this. On older versions, an admin must revoke the CREATE rule on the public schema from PUBLIC. This keeps regular users from adding unauthorized tables that clutter your logs.

The query runs. The emails stay hidden.