Giving AI Access to Your Database? Hide the PII First

You wire up an AI assistant to your production database. You ask it a harmless question — "how many active users signed up last week?" — and it happily writes the SQL. Nice.

But here's the part nobody thinks about until it bites them: that assistant can read every column your connection can reach. users.email. customers.phone. payments.card_last4. patients.diagnosis. The moment you gave it a way to run SELECT, you also gave it a way to pull personal data into a chat window, a log file, or an LLM provider's context — often without anyone intending it.

This isn't a reason to keep AI away from your data. It's a reason to be deliberate about which data it can see. The good news: the tools to do this already live in your database, and the patterns are the same whether the query comes from an AI agent, a BI tool, or a junior analyst. Let's walk through them, from the crudest to the cleanest.

The core problem: AI inherits your connection's blast radius

An AI assistant querying your database is only as constrained as the credentials behind it. If it connects as a superuser or your app's main role, it sees everything that role sees. Security researchers reviewing the Model Context Protocol (MCP) — the standard many tools now use to connect AI to databases — repeatedly flag over-permissioning as the fastest way these integrations go wrong: the connector exposes more than the task needs, and the agent returns data well beyond what you'd ever want in a prompt.

So the first principle is boring but non-negotiable: least privilege. The AI should connect through its own dedicated, read-only role that can touch only what it genuinely needs. Everything below builds on that.

-- A dedicated, read-only role for AI/analytics access
CREATE ROLE ai_reader NOLOGIN;

-- No blanket access to the whole schema
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM ai_reader;

-- Grant only what's needed, table by table
GRANT SELECT ON analytics_events TO ai_reader;

Layer 1: Column-level GRANT

Most people know GRANT SELECT ON table. Fewer know PostgreSQL (and MySQL 8+, with slightly different syntax) lets you grant SELECT on specific columns. If a table mixes public and sensitive fields — and most do — this is the sharpest tool you have.

Say your users table looks like this: id, name, email, phone, country, plan, created_at. The AI needs country, plan, and created_at to answer product questions. It has no business reading email or phone.

-- Remove table-wide read access
REVOKE SELECT ON users FROM ai_reader;

-- Grant only the safe columns
GRANT SELECT (id, country, plan, created_at) ON users TO ai_reader;

Now if the AI writes SELECT email FROM users, the database itself rejects it with a permission error — before a single row of PII is touched. You didn't have to trust the model, the prompt, or the tool. The constraint lives where it belongs: in the database.

The trade-off is bookkeeping. As tables grow, tracking who can see which column gets fiddly. A simple column access matrix keeps you honest:

Column	ai_reader	support_role	app_role
country, plan, created_at	read	read	read/write
email, phone	—	read	read/write
card_last4	—	—	read/write

Layer 2: Masking views (keep the shape, hide the value)

Sometimes you don't want to hide a column — you want the AI to see a masked version. It's useful for the model to know an email exists and group by its domain, without ever reading the real address. This is where views shine.

Create a view that transforms sensitive columns, revoke access to the base table, and point the AI role only at the view:

CREATE OR REPLACE VIEW users_ai AS
SELECT
  id,
  country,
  plan,
  created_at,
  -- keep the domain, drop the local part
  '***@' || split_part(email, '@', 2) AS email_domain,
  -- last 2 digits only, for support triage
  'xxx-xxx-' || right(phone, 2)        AS phone_masked
FROM users;

REVOKE ALL   ON users     FROM ai_reader;
GRANT  SELECT ON users_ai  TO   ai_reader;

One important detail: add WITH (security_barrier) to the view. Without it, the planner can sometimes push a WHERE clause below your masking expression and leak the underlying value. The barrier forces your masking to run first.

CREATE VIEW users_ai WITH (security_barrier) AS
  SELECT ... FROM users;

Here are the masking styles worth knowing:

Technique	What it does	Good for
Nulling / redaction	Replaces the value with NULL or a constant	Fields AI should never read
Partial masking	Shows a fragment (domain, last 2 digits)	Triage, grouping, dedup
Substitution	Swaps in realistic fake values	Demos, shared environments
Hashing	Deterministic token instead of the value	Joins/counts without exposure

Layer 3: Add row-level security for two-dimensional control

Column controls decide which fields; row-level security (RLS) decides which rows. Combine them and you get precise, two-dimensional access — the AI sees safe columns, and only the rows it's allowed to.

This matters most for multi-tenant apps, where an AI feature answers questions for one customer and must never surface another customer's records.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
  FOR SELECT
  TO ai_reader
  USING (tenant_id = current_setting('app.tenant_id')::int);

With the tenant ID set per session, the same AI query returns only that tenant's rows — no matter how the question is phrased. Column masking hides the what; RLS hides the whose.

Layer 4: The connection layer matters too

Database grants are the backstop, but a well-designed access layer between the AI and your database adds guardrails that SQL alone can't. The MCP security literature converges on a few:

• Read-only enforcement. Reject UPDATE, DELETE, and DDL outright, so an errant (or manipulated) prompt can't modify data.
• Redaction in the data flow. Scan tool responses and store only redacted representations when PII or secrets slip through.
• Scoped exposure. Publish only approved tables and queries to the AI, not the whole schema.
• Auditability. Log every query the AI runs, so access is reviewable and revocable — not scattered across long-lived credentials in prompts and config files.

This is a big part of why managed database brokers exist. A gateway like Draxlr's MCP server, for example, connects over OAuth and is read-only by design, so the AI can explore schema and run SELECTs without ever holding raw credentials or issuing a write. Whatever tool you use, the pattern is the point: put a controllable layer between the model and the database, and enforce PII rules there and in the database itself. Defense in depth beats trusting any single boundary.

Common mistakes and gotchas

• Connecting AI as the app's main user. The single biggest error. Give it a dedicated, minimal, read-only role.
• Masking in the SELECT but leaving the base table readable. If the role can still hit the base table, your view is theater. Revoke base access.
• Forgetting security_barrier on masking views. The planner may leak the raw value through a pushed-down predicate. Set the barrier.
• Masking the value but not the WHERE. WHERE email = '[e-mail]' in an AI-written query can confirm a specific person exists even if the output is masked. Restrict filtering on sensitive columns too.
• Assuming "internal only" means safe. Data pulled into an LLM context can be logged or cached downstream. Treat every AI query as if it might leave your walls.
• No audit trail. If you can't answer "what did the AI read last Tuesday?", you can't prove compliance or catch abuse.

Key takeaways

Giving AI access to your database is not the risky part — giving it unscoped access is. Lead with least privilege and a dedicated read-only role. Use column-level GRANT to hide sensitive fields entirely, and security_barrier masking views when you want the shape without the value. Layer in row-level security for multi-tenant isolation, and put an auditable, read-only broker between the model and the database so the rules are enforced twice. Do that, and your AI assistant becomes genuinely useful and something your security team can sign off on.

Your turn

How are you handling PII when AI tools touch your database — column grants, masking views, a broker, or all three? Have you hit a gotcha I didn't list? Drop it in the comments; I'm collecting patterns and would love to hear what's working (or what blew up) in your setup.