How to Configure Codex Postgres Audit Log
To set up a codex postgres audit log, give the agent a dedicated database role. Then turn on statement logging in your database to record every query that role runs. Many teams let OpenAI Codex use a shared developer login to query tables. Shared logins make it impossible to tell agent queries apart from human queries. Broad permissions on that role also let the agent change or delete data by mistake. How a codex postgres audit log works in the database A clean audit trail starts with identit
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.
To set up a codex postgres audit log, give the agent a dedicated database role. Then turn on statement logging in your database to record every query that role runs. Many teams let OpenAI Codex use a shared developer login to query tables. Shared logins make it impossible to tell agent queries apart from human queries. Broad permissions on that role also let the agent change or delete data by mistake.
How a codex postgres audit log works in the database
A clean audit trail starts with identity separation inside PostgreSQL. You create a specific role just for the AI coding agent. When you have many users or agents, you can set up group roles. You then grant individual roles membership in the group role to manage privileges. By default, PostgreSQL gives a special role called PUBLIC basic rights on the system. PUBLIC gets the right to connect to databases and create temporary tables. It also gets the right to run functions and procedures. The database owner can revoke these default rights from PUBLIC to lock things down.
In PostgreSQL 15 and newer, regular users cannot create new objects in the public schema. In older versions, or upgraded databases, an admin must revoke that right from PUBLIC. Once the agent has its own role, you grant it read rights on needed tables. The SELECT privilege lets the role read from a whole table or specific columns. Note that the SELECT privilege also allows the role to export data with COPY TO. After you set up the role, the database log records every statement tied to that user name.
What native database logging solves and what it misses
Native database roles and logs solve the first step of tracking agent work. They verify who can log in and write down the SQL statements that ran. If an agent runs a bad query, your team can find it in the server log later. You can also see which tables the agent read during its session.
Still, native database setup leaves a big operational gap during live sessions. Database roles only check permissions when the session starts or when a table is read. They do not inspect what individual commands or queries do in flight before they finish. A role with table read rights can still pull thousands of customer emails or phone numbers. It can also read card numbers or national IDs and send them to the model. Native logs only tell you about a data leak after the query is already done. Also, putting database keys directly into agent settings exposes those keys on local machines. Teams need a way to pause risky queries and mask sensitive fields in results.
Audit Log Integrity + End-to-End Encryption: architecture patterns and best practices
Continue reading? Get the full guide.
Free. No spam. Unsubscribe anytime.
Best practices for restricting agent database roles
Start by removing all write privileges from the database role that the agent uses. Grant the SELECT privilege only on the exact columns the agent needs for its task. Do not grant table-wide read access if the table holds private user records. Revoke default execute rights on functions from PUBLIC so the agent cannot trigger stored routines. Review your database logs each week to spot slow queries or unexpected table scans. Keep the agent role separate from the roles that your web apps or engineers use. This separation keeps your audit log clean and easy to search during a security review.
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 should OpenAI Codex use its own PostgreSQL role?
A separate role tags every agent query with a unique user name in the database log. Shared logins mix human work and AI queries together, which breaks your audit trail.
Can a read-only PostgreSQL role still export table data?
Yes, the standard SELECT privilege in PostgreSQL also allows the role to run COPY TO. Still, you can restrict SELECT to specific columns so the role only reads safe fields.
Do native PostgreSQL logs hide sensitive data from AI agents?
No, native logs only write down the queries that ran on the database server. To hide emails, phone numbers, or card numbers, you need data masking that rewrites matched fields in results.
Related guides
In this post