LLM SQL Agent PII Safety: How to Stop Data Leaks

In this article

1. Before you start
2. Why interviewers ask this
3. LLM SQL agent PII safety from first principles
4. Worked example
5. Trade-offs and edge cases
6. How to answer it in an interview
7. Common mistakes
8. Follow-up questions
9. Sources

Before you start

This page belongs to the generative AI system design learning path, and it assumes only three ideas.

Why interviewers ask this

A design that lets a model query customer data fails the round if one injected sentence makes it return another customer's email address. Interviewers ask about LLM SQL agent PII safety to see whether a candidate puts the control in the database or trusts the prompt.

A weak answer says "the system prompt tells the model to run SELECT only." A strong answer names the threat, a least-privilege role, limits on rows and columns, and what each layer still misses. A Staff-level answer adds the end user's identity on every request, query limits, audit logs and an honest statement that no single layer is perfect, as LangChain's security policy says. The topic fits generative AI design rounds on assistants over business data, next to permission-aware retrieval and agentic retrieval.

LLM SQL agent PII safety from first principles

SQL agent

A SQL agent is a program in which a language model writes SQL queries, runs them on a database through stored credentials and reads the results back to answer a user's question, so the model's text decides which query executes.

Row-level security

Row-level security (RLS) is a PostgreSQL feature that attaches a rule to a table so that each database role sees only the rows for which the rule is true.

Confused deputy

A confused deputy is a program that holds more privileges than the person asking it for help and can be talked into using them on that person's behalf, so anyone who can shape its input can borrow its access.

In plain words

Picture a temporary worker handed a master key to the records room. The worker is told, politely, to fetch only what the visitor may see. The worker may mean well. A clever visitor can still phrase a request that changes the worker's mind. The analogy breaks in one place: a model has no memory of rules apart from the text it is given, so a rule written in its instructions is only more text.

The same idea in one precise sentence: the safe design assumes the agent will try every permission its credentials carry, so the credentials must carry only what any user of the agent may see.

Step 1: A read-only role stops writes but still reads every row

A read-only database role stops the agent from changing data, but it still lets the agent read every row and column that role can reach. A vendor write-up puts it plainly: a user with select access can read every record and column in a table.

The threat is the confused deputy: the agent holds database credentials the end user does not, so anyone who can shape the agent's context can borrow those privileges. Prompt injection is text that reaches the model, from a user message or from data the model reads, and changes what the model does.

LangChain's guidance is to assume that any LLM able to use those credentials may in fact delete data if the credentials allow it, and its SQL agent reference advises least-privilege roles, ideally read-only and schema-limited. Least privilege is the rule that a role gets only the permissions its job needs. A read-only role blocks writes and deletes, yet it still reads every row and column. You now know the starting point; the next step limits which rows.

Step 2: Row-level security ties rows to the end user

Row-level security makes the database return only the rows a rule allows, so a model that writes a query for every customer still receives one user's rows. In PostgreSQL a policy is a boolean expression on a table, and rows for which the expression returns true will be visible. If row security is enabled and no policy applies, a default deny policy is assumed, so no rows are visible.

The PostgreSQL documentation's own example ties each row to the database user:

CREATE TABLE accounts (manager text, company text, contact_email text);
ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
CREATE POLICY account_managers ON accounts TO managers
  USING (manager = current_user);

This is the example from the row security chapter. A policy without a role applies to all users. Row security has exemptions: according to the row security chapter, superusers and roles with the bypass-row-security attribute always bypass it, and table owners normally do too unless the table is set to force row security for its owner. So the agent's role must not own the table, must not be a superuser and must not have the bypass-row-security attribute.

How the end user's identity reaches the policy

The application must pass the end user's identity to the database on each request so the policy can use it; otherwise every user shares the agent's view. A common pattern keeps the user id in a session setting:

CREATE POLICY own_rows ON customers
  USING (account_manager = current_setting('app.user_id', true));
-- set by the application, inside the same transaction as the agent's query
SELECT set_config('app.user_id', 'u123', true);

With true as the second argument of current_setting (missing_ok), an unset identity returns null and the policy matches no rows, not an error. The application sets the value with set_config and is_local = true, so it lasts only for that transaction, as the PostgreSQL system administration functions page describes. An agent that runs arbitrary SQL could overwrite the setting and defeat the policy. The query tool should accept only a single SELECT statement on listed tables or views, and reject session-setting commands, set_config and any function call, because by default PostgreSQL grants execute permission on functions and procedures to the public role. The Render article makes the same point: grants and validation enforce safety, not the system prompt.

Step 3: A safe view keeps PII columns out of reach

A safe view keeps PII columns out of the agent's reach by exposing only the columns the question needs. Row-level security decides which rows come back; it says nothing about which columns. A view that lists id, region and plan, with name, email and the last digits of a social security number left out, means the agent cannot select what the role cannot see.

OWASP is the Open Worldwide Application Security Project. Its 2025 list of risks for LLM applications describes sensitive information as including PII, financial details, health records, confidential business data and credentials, and it advises that you define sensitive categories and construct rules for identifying and handling such content. The National Institute of Standards and Technology (NIST) defines PII as any information about an individual that can be used to distinguish or trace that person's identity, or that is linked or linkable to the person (NIST's guide to protecting personally identifiable information). Because linkable data counts, this page treats partial identifiers, such as the last digits of a social security number, as PII by design judgement.

Why the view must run as the querying role

A view can silently undo the row policy. By default a PostgreSQL view reads its base table with the privileges of the view's owner, so row security is checked against that owner, and if the owner is the table owner it is skipped and the agent sees every row. The remedy, described in the undefined documentation, is to create the view WITH (security_invoker = true), available since PostgreSQL 15, so the base table's policies are checked against the querying role.

That has a cost: the same page says the user of a security invoker view must have the relevant permissions on the view and its underlying base relations. So grant the agent only the non-PII columns of the base table, keep the view as its named surface, and let the query tool's allowlist reject direct queries on customers. For a view that is meant to filter rows, the same page also describes the security_barrier option, which stops leaky functions in a user's query from seeing rows before the filter applies.

GRANT SELECT (id, region, plan) ON customers TO sql_agent;
CREATE VIEW customers_safe WITH (security_invoker = true) AS
  SELECT id, region, plan FROM customers;
GRANT SELECT ON customers_safe TO sql_agent;

Step 4: Limits, masking and audit bound what remains

Limits, masking and audit logs bound the damage that the earlier layers leave. The SQL agent reference warns that the agent can generate expensive or dangerous queries, such as long scans, large joins or locking queries, and advises statement timeouts and resource limits at the role or session level. It adds that client-side timeouts may not cancel a statement that is already running on the database server, so set the limit on the server.

A row cap limits volume, not exposure: the worked example shows that a cap still lets PII through. PostgreSQL has no role-level row cap, so the query tool enforces one, for example by wrapping each generated query as a subquery with a fixed row limit, or by fetching at most a fixed number of rows. Timeouts, by contrast, can be set on the role.

Masking and audit logs keep the model's context clean

Mask inside the view, not afterwards, because data masked in application code has already left the database and may already sit in the model's context. Log which end user triggered each query, since a shared agent login makes the log useless for tracing who asked.

1 · Read-only role2 · Row policy3 · Safe view4 · Limits and logReplay

Step-by-step description

1. Writes stop; every row still readable.
2. Each user sees only allowed rows.
3. PII columns are left out.
4. Timeouts, caps and audit bound the rest.

• A read-only role blocks writes but still reads every row and column.
• Row-level security shows each end user only the rows a policy allows.
• A view that omits PII columns keeps them out of the model's context.
• Timeouts, row caps and per-user audit logs bound the damage that remains.
• The query tool should accept only a single select statement on listed tables or views.

A read-only role blocks writes but still reads every row and column. Row-level security shows each end user only the rows a policy allows. A view that omits PII columns keeps them out of the model's context. Timeouts, row caps and per-user audit logs bound the damage that remains.

Worked example

The same question, "list customers who churned", behaves very differently under four database setups. The table below is illustrative: a table of 1,000 rows, six columns (id, name, email, last four digits of a social security number, region, plan), five regions of equal size and one churned customer in every four.¹ The end user manages one region. Three columns count as PII: name, email and the partial social security number.¹

Under an owner role and under a read-only role without a row policy, the agent receives 250 churned customers with six columns, which is 750 PII values in the model's context.¹ Adding a row policy on region cuts the rows to 50 and the PII values to 150.¹ Adding a safe view with three non-PII columns cuts the PII values to 0.¹ A row cap of 100 rows applied by the query tool (illustrative) returns 300 PII values, so a cap limits volume, not exposure.¹

In the owner row, a row policy would not reduce the 250 rows, because table owners normally bypass row security unless the table is set to force row security for its owner, as the row security chapter explains.

Text description of the diagram

• In the toy example the owner role and a read-only role each send 750 PII values to the model.
• Row-level security cuts that to 150, and adding a safe view cuts it to 0.

In the toy example the owner role and a read-only role each send 750 PII values to the model. Row-level security cuts that to 150, and adding a safe view cuts it to 0.¹

Trade-offs and edge cases

Each safeguard has a gap, so the design combines layers, as LangChain advises: no security technique is perfect, and layers beat any single defense.

Text description of the diagram

• A read-only role does not limit which rows or columns the agent reads.
• Row-level security does not stop the agent reading PII columns in the rows it can see.

• Silent filtering. A read-only role does not limit which rows or columns the agent reads, and row-level security does not stop the agent reading PII columns in the rows it can see. The PostgreSQL documentation says rows are typically silently suppressed with no error reported, so an agent asked for a total may return a smaller count with no warning. Tell the agent, and the user, what scope the answer covers.
• Prompt rules. OWASP says prompt restrictions may not always be honored and could be bypassed via prompt injection. Use them as a courtesy, never as access control.
• Identity plumbing. Row policies only help if the end user's identity reaches the database on each request and the agent cannot change it. One shared login for all users gives every user the same rows. If identity travels as a session setting or a role switch, an agent that runs arbitrary SQL could overwrite it, so the query tool must block session-setting commands and set_config.
• Framework state. As of October 2026, the create_sql_agent reference marks its AgentExecutor approach as legacy, so check the currently recommended agent interface before building. The database controls on this page apply either way.
• Regulation. Which data counts as protected, and for how long a hosted model may keep it, depends on the law and the provider's terms; this page does not state either. A design that must satisfy a regulator is covered in retrieval-augmented generation (RAG) in a regulated bank.
• Detection accuracy. The sources read for this page give no accuracy figures for tools that detect and mask PII, so none are claimed.

How to answer it in an interview

The spoken structure: say the database enforces it, then roles, rows, columns and limits, and end on why one layer is never enough.

Common mistakes

• "The system prompt says SELECT only, so it is safe." (wrong) Prompt rules can be bypassed. (right) "The database role enforces read-only; the prompt is only guidance."
• "Read-only means no PII risk." (wrong) It blocks writes only. (right) "Read-only stops changes; views and row policies limit what is read."
• "I redact PII after the query returns." (wrong) The data has already left the database. (right) "I mask inside a view so the model never receives it."
• "A client-side timeout protects the database." (wrong) The statement may keep running on the server. (right) "I set the timeout on the role or session in the database."
• "PII means a name or a social security number." (wrong) NIST's definition covers information linked or linkable to a person, so partial identifiers are safest treated as PII. (right) "I treat email, partial numbers and linkable fields as PII."
• "I wrapped the table in a view, so row-level security still applies." (wrong) By default the view reads the table as the view's owner, so the policy may be skipped. (right) "I create the view with security_invoker = true so the caller's policies apply, and I grant the agent only the non-PII columns of the base table."
• "The agent connects as the table owner, and row security protects the rows." (wrong) Table owners normally bypass row security. (right) "The agent's role does not own the table and has no bypass-row-security attribute."

Follow-up questions

Should the agent see the full schema?

No. Expose only the views the question needs, and have the query tool reject any other table. A smaller schema leaves less to misuse and keeps PII column names out of the model's context.

Is a read-only database role enough for an LLM SQL agent?

No. A read-only role stops writes, but a SELECT grant on a table still lets the role read every row and column of it. Add row limits with row-level security, and column limits with a view or a column-level privilege.

Does row-level security work with an LLM agent?

Yes, if the agent's role does not bypass row security and the end user's identity reaches the database on each request in a way the agent cannot overwrite. Rows that fail the policy are silently dropped, so the agent may give a smaller total without any error.

Can a system prompt protect PII?

Only partly. OWASP says prompt restrictions may not always be honored and can be bypassed through prompt injection, so enforcement belongs in the database.

Should you mask PII before or after the model sees it?

Before. Mask inside a view or at the database edge, so the model's context never holds the raw values. A gateway can add a second filter; see the LLM gateway interview questions.

How do you stop runaway queries from an agent?

Set statement timeouts and resource limits on the database role or session, because a client-side timeout may not cancel the running statement.

Sources

1. PostgreSQL documentation: CREATE POLICY — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
2. PostgreSQL documentation: 5.9 Row Security Policies — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
3. PostgreSQL documentation: CREATE VIEW — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
4. PostgreSQL documentation: System Administration Functions — PostgreSQL Global Development Group (undated page, accessed 2026-10-26), 26 Oct 2026
5. LangChain security policy — LangChain documentation (accessed 2026-10-26), 26 Oct 2026
6. create_sql_agent reference — LangChain reference documentation (accessed 2026-10-26), 26 Oct 2026
7. OWASP Top 10 for LLM Applications 2025 — OWASP GenAI Security Project, 14 Nov 2024
8. Guide to Protecting the Confidentiality of Personally Identifiable Information (PII) — NIST, 1 Apr 2010
9. Connect an AI agent to a SQL database with LangChain tools — Render (practitioner article, accessed 2026-10-26), 26 Oct 2026
10. LangChain SQL agent on PostgreSQL: limiting what it can execute — hoop.dev (vendor blog, accessed 2026-10-26), 26 Oct 2026