How to Configure LlamaIndex Postgres Audit Log

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up a llamaindex postgres audit log by combining database logging with agent event handlers. First, give the agent a restricted database role and turn on statement logs in Postgres. Many teams connect their AI apps to databases using broad admin accounts. When a role has too many rights, a bad prompt can read or change private tables. Good logs and strict roles work together to show what the agent did. How to set up a llamaindex postgres audit log LlamaIndex connects to Postgres throug

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 a llamaindex postgres audit log by combining database logging with agent event handlers. First, give the agent a restricted database role and turn on statement logs in Postgres. Many teams connect their AI apps to databases using broad admin accounts. When a role has too many rights, a bad prompt can read or change private tables. Good logs and strict roles work together to show what the agent did.

How to set up a llamaindex postgres audit log

LlamaIndex connects to Postgres through a light wrapper around SQLAlchemy. Its query engine turns plain questions into SQL statements and runs them against your tables. If you do not pass a specific list of tables, the engine pulls all schema details. This behavior makes role setup your first step before turning on logs.

In Postgres, you should create a dedicated role for the AI app. When many apps or users need the same rules, you can set up group roles. You then grant the agent role membership in that group role. By default, the special PUBLIC role gets rights to connect and run functions. The database owner can revoke these default PUBLIC privileges to keep the setup tight.

In PostgreSQL 15 and newer, regular users cannot create objects in the public schema by default. On older versions, an admin must revoke create rights on the public schema from PUBLIC. Next, grant the select privilege on only the specific tables or columns the agent needs. Note that the select privilege also allows copying data out of those tables. Once the role has its own name, every query in the database log points to the agent.

On the app side, you can attach callback handlers inside the framework. These handlers record the user question, the generated SQL statement, and the raw response. Pairing app logs with database logs gives your team two views of every query run.

What native roles and logs solve and where they fall short

LlamaIndex docs warns that running arbitrary SQL queries from text-to-SQL tools brings real security risks. The docs advise teams to use restricted roles, read-only databases, and sandboxing. Native Postgres roles and logs solve the first part of this problem. They control which database account can log in and which tables that account can read. Native database logs also record the SQL text after the database runs the statement.

Still, native database setup leaves a wide gap during live agent runs. Postgres only sees the database role name, not which end user or prompt triggered the query. If ten services share one read-only role, the database log mixes all their queries together. Correlating a slow or strange SQL query back to one chat session takes manual work.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

Static column grants are also hard to maintain as schemas change each week. If an agent needs a table for general totals, it might still pull sensitive columns. For example, a query result might return user emails, phone numbers, card numbers, or national IDs. Database logs only show that a query ran after the data has already left the server. They do not rewrite sensitive fields in results before those rows reach the language model.

Best practices for reviewing agent database activity

Start by naming every database role after the specific agent or workflow that uses it. Never share human admin credentials with an automated query engine. Always pass an explicit table list to the query engine in your app code. This stops the engine from reading every table schema in your database on startup.

Review your database logs and app traces together on a set schedule. Look for failed queries, permission errors, and unexpected table joins. Failed queries often show where a user tried to trick the model into reading restricted data. Regular log checks help you tighten column grants and improve your system prompts over time.

Run guardrails and masking on the Postgres connection

Role grants and firewall rules are needed setup. Setup alone cannot check single SQL statements or mask sensitive rows in a response. By design, hoop.dev is an open-source proxy that sits beside Postgres, RDS, or Aurora as a sidecar for your Postgres. Clients and agent tools keep their credentials and point their connection string at the listener.

On each query, hoop.dev guardrails refuse disallowed drops, deletes, or table reads before they run. Next, hoop.dev data masking rewrites sensitive fields in returned rows that match your rules. Furthermore, hoop.dev holds SOC 2 Type II and logs every statement and verdict per resource. Check out the project on GitHub.

Frequently asked questions

Why does LlamaIndex pull my entire Postgres schema by default?

When you do not list specific tables, the query engine loads all table schemas for context. Always pass a strict list of allowed tables when you set up the database wrapper.

Can Postgres grant read access to only a few columns for an agent?

Yes, Postgres lets you grant the select privilege on specific columns of a table or view. The agent role will get a permission error if its generated SQL asks for other columns.

Do native Postgres logs show which user prompt created a SQL query?

No, native database logs only see the SQL statement and the Postgres role that ran it. You must join app logs with database logs or add query metadata to link prompts.

The query runs. The emails stay hidden.