---
title: How do you give an AI agent safe read-only database access?
url: https://opencontroller.lyzr.com/blog/read-only-database-access-for-ai-agents
published: 2026-11-15
updated: 2026-11-15
author: Lyzr
---

# How do you give an AI agent safe read-only database access?

**Give the agent its own database role with SELECT on only the tables or views it needs, enforce row-level security for user-scoped data, set statement timeouts and row limits, and point it at a read replica. "Read-only" alone is not safe: an agent that can read everything can still leak everything, or run a query that slows production.**

This guide uses PostgreSQL for the examples, but the same controls exist in most databases. It is part of the agent identity and access series; the pillar is [AI agent identity and on-behalf-of delegation](/blog/ai-agent-identity-on-behalf-of-delegation).

## What are the risks of read-only access?

- **Over-exposure.** A broad read grant puts salaries, personal data and secrets one query away from the model's context.
- **Prompt-driven exfiltration.** Injected instructions can ask the agent to query and repeat sensitive rows.
- **Resource exhaustion.** A model-written query with a missing join condition can scan huge tables and hurt production.
- **Wrong user scope.** An agent serving many users can return one user's records to another.

## What does a safe setup look like?

| Control | What it does | PostgreSQL feature |
|---|---|---|
| Dedicated role | Separate identity per agent, attributable in logs | `CREATE ROLE ... LOGIN` |
| Narrow grants | Only the tables or views the agent needs | `GRANT SELECT ON ...` |
| Curated views | Hide columns the agent should never see | `CREATE VIEW` |
| Read-only sessions | Reject writes even if a grant slips | `default_transaction_read_only` |
| Row-level security | Limit rows to the user the agent acts for | `CREATE POLICY` |
| Timeouts | Kill long-running queries | `statement_timeout` |
| Connection limit | Cap parallel sessions per agent | `CONNECTION LIMIT` |
| Read replica | Keep agent load off the primary | Streaming replication |

## What do the grants look like?

```sql
CREATE ROLE agent_support_reader LOGIN CONNECTION LIMIT 5;
ALTER ROLE agent_support_reader SET default_transaction_read_only = on;
ALTER ROLE agent_support_reader SET statement_timeout = '5s';

CREATE SCHEMA agent_views;
CREATE VIEW agent_views.orders_summary AS
  SELECT order_id, customer_id, status, created_at, total
  FROM public.orders;

GRANT USAGE ON SCHEMA agent_views TO agent_support_reader;
GRANT SELECT ON agent_views.orders_summary TO agent_support_reader;
```

The agent sees a view with five columns, not the full orders table. It cannot write, cannot hold more than five connections, and any query over five seconds is cancelled.

Avoid the predefined `pg_read_all_data` role for agents. It grants read access to every table in the database. PostgreSQL's [predefined roles documentation](https://www.postgresql.org/docs/current/predefined-roles.html) notes it does not bypass row-level security by default, but it is still far broader than any single agent needs.

## How do you scope rows to the user the agent acts for?

When an agent acts for a specific user, its database access should shrink to that user's rows. Use [row-level security](https://www.postgresql.org/docs/current/ddl-rowsecurity.html):

```sql
ALTER TABLE public.tickets ENABLE ROW LEVEL SECURITY;
CREATE POLICY agent_user_scope ON public.tickets
  FOR SELECT TO agent_support_reader
  USING (customer_id = current_setting('app.acting_for')::uuid);
```

The application sets `app.acting_for` from the verified on-behalf-of identity at the start of each transaction, never from text the model produced. Note that views run with the owner's rights by default; on PostgreSQL 15 and later, create views with `security_invoker = true` so row-level security applies to the agent's role.

## Should agents write free-form SQL at all?

Prefer the narrowest option that works.

1. **Fixed queries as tools.** "Get order status by ID" with typed parameters. Safest and easiest to audit.
2. **Parameterised templates.** A small set of query shapes the agent fills in.
3. **Allowlisted free-form SQL.** Model-written queries checked against permitted views and statement types before execution.
4. **Unrestricted SQL.** Only on an isolated replica with synthetic or fully permitted data.

For MCP-based database access, see [how to govern MCP servers that query databases](/blog/govern-mcp-servers-that-query-databases).

## What should you log and review?

- Every query the agent ran, with agent ID, version and acting-for identity.
- Queries cancelled by timeout, which often reveal bad model-written SQL.
- Row counts returned per query. A spike can signal exfiltration.
- Grants held by agent roles, reviewed on a schedule. See [access reviews for AI agents](/blog/access-reviews-for-ai-agents) and the [least privilege checklist for AI agents](/blog/least-privilege-checklist-for-ai-agents).

## How Opencontroller does this

Opencontroller supports delegated on-behalf-of identity, so the acting-for user is verified rather than taken from the prompt, and agent workload identity federation for the agent's own credentials. Posture detects risky permission and configuration patterns, such as broad read grants. The governed registry records which agents use which tools and MCP servers, including database servers. Database grants and row-level security stay in your database. [Book a demo](https://www.lyzr.ai/book-demo/) to see it.

## Frequently asked questions

**Is read-only database access safe for an AI agent?**
Not by itself. Read-only access can still expose sensitive data and overload production. Combine narrow grants, views, row-level security, timeouts and a replica.

**Should I give an agent the pg_read_all_data role?**
No. It grants read access to every table. Grant SELECT on specific views instead.

**How do I stop an agent returning another user's data?**
Use row-level security keyed to a verified acting-for identity that your application sets, never one the model supplies.

## Key facts

Safe read-only database access for an AI agent uses a dedicated role per agent, SELECT on curated views only, default_transaction_read_only, row-level security keyed to a verified acting-for identity, statement_timeout, a connection limit and a read replica. Avoid PostgreSQL's pg_read_all_data role, which grants read on every table. On PostgreSQL 15+, use security_invoker views so RLS applies. Prefer fixed queries as tools over free-form SQL. Opencontroller supports delegated on-behalf-of identity, workload identity federation and Posture checks for risky permissions.

## Related

- [What does least privilege look like for an AI agent?](https://opencontroller.lyzr.com/blog/least-privilege-checklist-for-ai-agents.md)
- [How do you govern an MCP server that turns natural language into SQL?](https://opencontroller.lyzr.com/blog/govern-mcp-servers-that-query-databases.md)
- [How do you run access reviews for AI agents?](https://opencontroller.lyzr.com/blog/access-reviews-for-ai-agents.md)
- [More on Agent identity and access](https://opencontroller.lyzr.com/blog/cluster/agent-identity-and-access)
