Letting an AI agent query your production database? Do these 6 things first

"Just ask the database in plain English" is one of the most useful things an AI agent can do. A sales lead types "Which 10 customers spent the most this quarter?" and gets an answer in seconds, instead of waiting two days for an analyst.

It's also one of the scariest things to set up. You're giving software that writes its own SQL a connection to your data.

We build AI Agent Studio, a no-code platform where agents can answer questions from a live database, so we think about this a lot. Here's the setup we recommend before connecting any AI tool to a database, ours included.

The examples use PostgreSQL, and we ran every snippet on PostgreSQL 16 before publishing. The ideas carry over to MySQL, SQL Server and the rest.

The short version

1. Create a dedicated read-only role

Never reuse your app's database user. Give the agent its own role that can only read, and only from one schema:

-- A separate schema for everything the agent may see (more in step 2)
CREATE SCHEMA reporting;

CREATE ROLE ai_agent LOGIN PASSWORD 'use-a-long-random-password' CONNECTION LIMIT 5;

GRANT CONNECT ON DATABASE shop TO ai_agent;
GRANT USAGE ON SCHEMA reporting TO ai_agent;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO ai_agent;

-- Make views and tables created later in this schema readable too
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting
  GRANT SELECT ON TABLES TO ai_agent;

-- Extra safety: every transaction starts read-only
ALTER ROLE ai_agent SET default_transaction_read_only = on;

The SELECT-only grants are the real protection. default_transaction_read_only is a second layer, but a session can switch it off with one SET, so don't rely on it alone. CONNECTION LIMIT stops a runaway agent from opening hundreds of connections.

Two details that catch people out:

• ALTER DEFAULT PRIVILEGES only applies to objects created by the role that ran it. Run it as the same user that will create your views.
• Store the password in your secrets manager, not in the agent's prompt or config file.

2. Expose views, not raw tables

The role above can only see the reporting schema. Put views there that contain only what the agent needs, and leave out personal data:

CREATE VIEW reporting.orders_summary AS
SELECT
  o.id,
  o.created_at,
  o.status,
  o.total_amount,
  c.country            -- no names, emails or addresses
FROM public.orders o
JOIN public.customers c ON c.id = o.customer_id;

By default a view runs with its owner's privileges, so ai_agent can read reporting.orders_summary without any access to public.orders or public.customers. (If you create views with security_invoker = true, that changes, and the agent would need access to the underlying tables.)

Then describe the data. Many AI tools read the database's comments when they work out what to query, and plain-English descriptions make their SQL much more accurate:

COMMENT ON VIEW reporting.orders_summary IS
  'One row per order. total_amount is in USD, including tax.';
COMMENT ON COLUMN reporting.orders_summary.status IS
  'pending, paid, refunded or cancelled';

Clear names help just as much. total_amount_usd gets fewer wrong answers than amt2.

3. Set timeouts

An agent will eventually write a query that joins three big tables with no filter. Make sure it can't run for an hour:

ALTER ROLE ai_agent SET statement_timeout = '5s';
ALTER ROLE ai_agent SET idle_in_transaction_session_timeout = '30s';

Five seconds is plenty for most business questions. If a question needs more, that's a sign it belongs in a proper report or a pre-computed view, not a live query.

Role settings apply to new sessions, so reconnect after changing them.

4. Limit rows in the tool layer

Nobody needs 2 million rows in a chat answer. Wrap the agent's query before running it:

import psycopg2

MAX_ROWS = 500

def run_agent_query(conn, sql: str):
    sql = sql.strip().rstrip(";")
    if ";" in sql:
        raise ValueError("Only one statement is allowed")

    with conn.cursor() as cur:
        cur.execute(f"SELECT * FROM ({sql}) AS agent_q LIMIT {MAX_ROWS}")
        columns = [d.name for d in cur.description]
        return columns, cur.fetchall()

conn = psycopg2.connect(dsn)  # credentials for the ai_agent role
conn.set_session(readonly=True, autocommit=True)

Wrapping the query in a subquery also means anything that isn't a SELECT fails with a syntax error before it runs. A DELETE inside SELECT * FROM (...) simply isn't valid SQL.

This is a guardrail, not a security boundary. The read-only role from step 1 is what actually protects your data. The wrapper keeps answers small and stops the agent sending several statements at once. (The semicolon check will also reject a query with a ; inside a string, which is a trade-off we're happy with.)

5. Point it at a replica

If you have a read replica, connect the agent there instead of your primary database. Even a well-behaved query can be heavy, and you don't want a curious question about last year's revenue slowing down checkout.

A replica is also naturally read-only, which gives you one more layer on top of step 1.

No replica? Lean harder on steps 3 and 4, and consider a lower CONNECTION LIMIT.

6. Log every question, query and answer

When the agent gives a wrong number, you need to know why. Log three things together, with a timestamp and the user who asked:

1. The question the person asked
2. The SQL the agent generated
3. The answer it returned

On the database side, a superuser can log every statement from the agent's role:

ALTER ROLE ai_agent SET log_statement = 'all';

And show the SQL to the person, next to the answer. Anyone who knows the data can then sanity-check it. "Revenue: $1.2M" is easy to trust blindly; "Revenue: $1.2M, from SUM(total_amount) WHERE status = 'paid'" invites someone to say "that should include refunds".

Test it before you trust it

Before you connect an agent, log in as ai_agent yourself and check the guardrails actually hold:

-- Should work
SELECT country, SUM(total_amount) FROM reporting.orders_summary GROUP BY country;

-- Should all fail
SELECT * FROM public.customers;   -- ERROR: permission denied for table customers
SET default_transaction_read_only = off;
DELETE FROM public.orders;        -- ERROR: permission denied for table orders
SELECT pg_sleep(6);               -- ERROR: canceling statement due to statement timeout

That's exactly what we saw when we ran this setup on PostgreSQL 16: the agent can read the view, and nothing else.

Then test the answers. Ask the agent 10 questions you already know the answers to, such as last month's revenue, the number of active customers or your top product. Compare. Wrong answers usually point to a confusing column name or a missing view, which you fix in step 2.

Where AI Agent Studio fits

AI Agent Studio lets you connect a database and ask questions in plain English, alongside document search (with sources) and agents that act in 100+ apps. When you connect it, or any similar tool, give it the ai_agent role and the reporting schema from this post, not your app's credentials. The setup is the same whichever tool you use.

How are you handling this? If you've connected an AI tool to a real database, what guardrails did you add, and what went wrong first? Tell us in the comments.