# How to give an AI agent schema context without customer data

> **Quick answer:** Structure keeps an agent from writing SQL your schema
> rejects: the columns and their types, the indexes, the constraints, and
> roughly how large and skewed the data is. Export that and keep the values out.
> `pg_dump --schema-only` produces the structure in one command, but read the
> file before sending it anywhere.

## What is the problem?

Two facts pull in opposite directions.

An agent without schema context invents columns. It writes `user.name` when the
table has `first_name` and `last_name`, joins on a column that is not a key, and
proposes an index that already exists. Most agent-generated SQL errors come from
not knowing the schema.

An agent with a database connection and no constraints can read anything in it.

The information that makes SQL valid and the information that makes access risky
are mostly different. Structure makes SQL valid against your schema, and
structure contains no customer rows.

The split has two limits.

**Structure does not carry meaning.** It will not tell an agent which `status`
values are live, whether `deleted_at IS NULL` is mandatory, which of two
timestamp columns is authoritative, or whether `amount` is in pounds or pence.
SQL that is valid against the schema but wrong about the business is the failure
this page does not solve. Write those conventions down separately.

**Structure is not automatically harmless.** Column names, comments, view
definitions and function bodies are all structure, and all of them can contain
facts about your business. The export is safer than rows, not safe.

## The simplest correct export

```
pg_dump --schema-only --no-owner --no-privileges --dbname=app > schema.sql
```

That is the DDL for every object the dumping role can see, generated by the
database, with no rows: tables, columns, types, defaults, constraints, indexes
and views, along with several things people may not picture as part of a
schema.

Measured on PostgreSQL 18.6, a `--schema-only` dump of a small database carried
a function body with a rate constant in it, a row-level security policy showing
how tenants are separated, and this line:

```
COMMENT ON TABLE public.secret_t IS 'acme migration 2026';
```

Read the file before sending it, as you would any configuration artefact
leaving the building:

- **Column and table names can themselves be sensitive.** A table called
  `patient_diagnosis` tells a story before anyone reads a row.
- **Comments and defaults can contain values.** A default of
  `'internal-acme-2026'` is a fact about a customer.
- **Function bodies, view definitions and policies are code**, and code contains
  constants, thresholds and business rules.
- **Foreign data wrappers name other systems.** A server definition shows what
  this database is connected to.

`--schema-only` does not carry optimizer statistics, which matters more on
PostgreSQL 18 than it used to. The statistics section covers that separately.

For something smaller, query the catalog directly. The query below returns a
column inventory, not a schema. It carries no constraints, no indexes, no
generated or identity columns, no enums or domains, no partitioning, no views,
no functions or triggers, no row-level security policies and no grants. Take the
next two queries with it, or take the dump.

```sql
SELECT c.table_name,
       c.column_name,
       c.data_type,
       c.is_nullable,
       c.column_default
FROM information_schema.columns AS c
JOIN information_schema.tables AS t
  ON t.table_schema = c.table_schema AND t.table_name = c.table_name
WHERE c.table_schema = 'public' AND t.table_type = 'BASE TABLE'
ORDER BY c.table_name, c.ordinal_position;
```

Indexes matter as much as columns. An agent cannot know that a proposed index
already exists unless it can see the existing indexes:

```sql
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;
```

Constraints tell it the relationships the database **declares**. Relationships
that live in application code, naming conventions, or other databases are
invisible here. In a schema that has deliberately left out foreign keys, that
may describe most of the relationships. Write those down separately:

```sql
SELECT conrelid::regclass AS table_name,
       conname,
       pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE connamespace = 'public'::regnamespace
ORDER BY conrelid::regclass::text, conname;
```

## The part people leave out: size and skew

This keeps an agent from giving advice that is correct on a toy table and wrong
on yours. It provides first-pass scale context rather than a tuning input: it
has no plan shape, no selectivity, no write rate, no bloat or vacuum health and
no workload.

```sql
SELECT relname,
       n_live_tup AS approx_rows,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       seq_scan,
       idx_scan
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;
```

An agent that knows `orders` has forty million rows writes a different migration
from one that does not. `n_live_tup` is an estimate maintained by the cumulative
statistics system rather than a count, and `seq_scan` and `idx_scan` are
cumulative since the last reset, so treat all three as orders of magnitude.

For distribution, choose columns carefully. `pg_stats` contains sampled literal
values:

```sql
SELECT tablename,
       attname,
       n_distinct,
       null_frac,
       correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY tablename, attname;
```

`most_common_vals` and `histogram_bounds` in the same view are **actual values
sampled from the column**, so a column of email addresses has email addresses in
its statistics. Do not export the whole view and assume it contains only
metadata.

The four columns above are derived numbers, which makes them lower risk rather
than safe. They are still an aggregate description of a population: `null_frac`
on an outcome column says how often the outcome occurs, `n_distinct` on an
identifier says how unique people are, and on a small table both can approach a
statement about individuals. Decide which to export per column rather than per
view, and leave out small populations.

### PostgreSQL 18 can dump statistics, and you have to ask for it

PostgreSQL 18 added `--statistics` and `--statistics-only` to `pg_dump`, which
changes what a schema dump can contain. Measured on 18.6:

- `pg_dump --schema-only` emits no statistics at all. Neither does a plain
  `pg_dump` with no flags: both produced zero `pg_restore_relation_stats` calls.
- `--schema-only` and `--statistics` **cannot be combined**:
  `options -s/--schema-only and --statistics cannot be used together`.
- `--statistics-only` writes the value arrays straight into the file. On a table
  of 5,000 rows the dump contained
  `'most_common_vals', '{done,pending}'::text`.

The defaults therefore exclude statistics, while the new flag needs a deliberate
decision. A statistics dump needs the same column-by-column review as `pg_stats`
itself, because it is `pg_stats` in a file.

## When the agent needs a live connection

Sometimes structure is not enough. Reviewing a slow query needs a real plan, and
a real plan needs the real statistics.

Give the agent a role that can read structure and only the tables you choose.
**Grant nothing at the schema level and add the tables one at a time**, because
the opposite approach does not work as it appears to.

The tempting approach is to grant `SELECT` on every table in `public` and then
revoke access to sensitive ones. Measured on PostgreSQL 17.11, that script leaves
every table nobody named fully readable, including one whose column is called
`secret`. A list of exceptions is only as complete as the person writing it,
and the role ends up with a privilege nobody chose.

```sql
CREATE ROLE ai_agent LOGIN PASSWORD 'use-a-generated-secret'
    NOSUPERUSER NOCREATEDB NOCREATEROLE NOBYPASSRLS CONNECTION LIMIT 5;

REVOKE ALL ON DATABASE app FROM PUBLIC;
GRANT CONNECT ON DATABASE app TO ai_agent;
GRANT USAGE ON SCHEMA public TO ai_agent;

REVOKE ALL ON TABLE users FROM ai_agent;
GRANT SELECT ON TABLE orders, products TO ai_agent;
GRANT SELECT (id, created_at, status, country_code) ON users TO ai_agent;
```

The `REVOKE` on `users` is there because column-level grants only work from a
standing start. A table-level `SELECT` covers every column, so on a database
where the table has ever been granted whole to this role, the column list adds
nothing and takes nothing away. Measured on 17.11: with a prior whole-table
grant in place, `has_column_privilege(..., 'email', 'SELECT')` returned `t`
after the column grant; with the `REVOKE` first it returned `f` while `status`
returned `t`. On a role you have just created the `REVOKE` is a no-op, which is
why it belongs in the recipe rather than in a footnote.

### PUBLIC already granted some of this

`PUBLIC` is not a group you add people to. It is every role, including the one
you are about to create, and it holds privileges by default that no script in
this article granted.

Measured on 17.11, a brand new role with nothing granted on the database could
still connect to it, because `CONNECT` is granted to `PUBLIC` on every database
at creation. So the `GRANT CONNECT` line above does not by itself decide whether
the agent can connect. The `REVOKE ALL ... FROM PUBLIC` above it does.
`EXECUTE` on functions works the same way, and on the same server the new role
could call `pg_sleep` without anything being granted to it.

A `GRANT ... TO PUBLIC` that somebody ran years ago creates the same access as
an unnamed table, but from the other direction. On 17.11 a table granted
`SELECT` to `PUBLIC` was readable by the new role immediately, and so was a
`users` table that had been granted whole to `PUBLIC` before any of the
column-level work. Check the catalog rather than assuming:

```sql
SELECT c.relname, a.privilege_type
FROM pg_class c,
     LATERAL aclexplode(c.relacl) AS a
WHERE c.relnamespace = 'public'::regnamespace
  AND c.relkind IN ('r', 'v', 'm', 'p')
  AND a.grantee = 0
ORDER BY 1, 2;
```

`grantee = 0` is `PUBLIC`. Every row is a table the agent can read regardless of
what you granted it, and the fix is a `REVOKE ... FROM PUBLIC` on each one.
Decide that one table at a time because other applications may rely on those
grants.

Check the result by asking what the role can actually do, rather than by reading
the grants back:

```sql
SELECT has_table_privilege('ai_agent', 'users', 'SELECT')            AS whole_table,
       has_column_privilege('ai_agent', 'users', 'status', 'SELECT') AS col_status,
       has_column_privilege('ai_agent', 'users', 'email',  'SELECT') AS col_email;
```

That returned `f`, `t`, `f` on 17.11, which is the result you want: no
table-wide read, the approved columns readable, and `email` not readable. It
also catches a missed `PUBLIC` grant, because `has_table_privilege` answers for
the role's effective privileges rather than the grants you wrote.

Use those functions instead of
`information_schema.column_privileges` because the view cannot distinguish the
two situations. Measured on the same server: a table granted whole showed one
row per column, exactly the shape a careful column-level grant produces. In the
view, "locked down to four columns" and "wide open and it has four columns"
look identical.

Privileges limit what the agent may read. They do not limit what it may spend,
so pair them with `default_transaction_read_only`, a `statement_timeout`, a
`lock_timeout` and an `idle_in_transaction_session_timeout` on the role, and
point it at a replica if you have one.

`EXPLAIN` without `ANALYZE` plans the query without executing it, so no rows
come back. The plan still carries information. It names the relations, shows the
predicates, and echoes the constants the query supplied:

```sql
EXPLAIN SELECT id, total FROM orders WHERE status = 'pending';
```

On 17.11 that prints `Filter: (status = 'pending'::text)`. The values came from
the query rather than from the table, and the row estimates are still a
statement about your data.

`EXPLAIN (ANALYZE)` executes the statement, which makes it a separate decision:
it takes locks, fires triggers, costs whatever the query costs, and for anything
other than a `SELECT` it leaves the side effects behind. It is the right tool on
a replica or a copy, under a timeout, and a poor default for an agent attached
to a production primary.

## If the agent needs rows

Then it needs rows that are not yours. Two options, in order of effort:

**Anonymise on export.** Replace values while keeping shape: same lengths, same
cardinality, same null rate, same date ranges. Referential integrity has to
survive or the data is useless for testing joins.

**Generate synthetic data against the real schema.** This takes longer to build,
but it never leaks because there was nothing to leak.

Either way, treat the output as production data until you have checked a sample.
Free-text columns are where real values survive an anonymisation pass, because
someone may have put a phone number in a `notes` field.

## Why does AI-generated code cause this?

The failure is a well-meant shortcut. Asked to "give the agent context about the
database", the obvious implementation is to dump some rows, because rows are
what a database contains. `SELECT * FROM users LIMIT 100` is a reasonable-looking
way to show an agent what a table holds, but it puts a hundred real people into a
context window and whatever logs sit behind it.

The second error is giving no context at all and then being surprised when the
agent invents columns. That is the failure this page exists to avoid, and it is
why "just do not connect it" is not the answer.

## How do I stop it coming back?

**Make the schema export a build artefact.** Regenerate it on every migration so
it is never stale. Stale schema context is another source of wrong SQL.

**Separate the two exports explicitly.** Keep structure and statistics in one
file that anyone can read. Treat anything containing values as production data.

**Prefer a constrained live connection to a dump** when the agent needs facts.
Privileges are auditable and revocable; a file you sent is neither.

**Re-check grants after every migration.** A new table is *not* readable by the
agent by default, measured on 17.11: a table created after
`GRANT SELECT ON ALL TABLES` returned `ERROR: permission denied for table` to
the role. That is the safer direction. The failure mode is an agent that cannot
see a new table rather than one that can see it without review. The exception is
`ALTER DEFAULT PRIVILEGES`: if somebody has set a default grant for the owning
role, new tables are readable the moment they exist. Check which of the two
applies before assuming either.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent exactly this split:
the schema and topology, table and index sizes, live and dead row counts, scan
counts, and query plans,
all without handing the agent a dump of your tables.
The workload it reports is query fingerprints with the literals stripped out,
which is the same distinction this page draws. Live activity samples are the
exception: they carry the running SQL as written, and only the developer role
sees them with literals removed. It surfaces and explains; it does
not write to your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [Why does my AI agent hallucinate columns that do not exist?](https://www.dbgorilla.com/learn/postgres/why-does-my-ai-agent-hallucinate-columns-that-dont-exist/)
- [How to set up a read-only Postgres role for an AI agent](https://www.dbgorilla.com/learn/postgres/how-to-set-up-a-read-only-postgres-role-for-an-ai-agent/)
- [Is it safe to let an AI agent access my production database?](https://www.dbgorilla.com/learn/postgres/is-it-safe-to-let-an-ai-agent-access-my-production-database/)