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.

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:

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:

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.

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:

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.

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:

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:

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:

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 can help

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.

Start free

FAQ

• What does an AI agent actually need to write correct SQL?

  Table and column names with their types, the constraints, the indexes, and a sense of size and distribution. It needs to know that orders has forty million rows and that status has four distinct values, not what any particular order says. That is enough for SQL that matches your schema. It is not enough for SQL that means what you intended, which also needs the things the schema does not record: which status values are live, whether deleted_at is mandatory, how tenants are separated, whether amounts are in minor units.

• How do I export just the schema?

  pg_dump --schema-only --no-owner --no-privileges gives the DDL with no rows, for the objects the dumping role can see. It is exact, because it comes from the database rather than from a model of it. Read it before you send it: measured on 18.6, a schema-only dump carried a function body, a row-level security policy and a table comment reading 'acme migration 2026'. No rows is not the same as nothing sensitive.

• Is pg_stats safe to share?

  Mostly not as it stands. pg_stats includes most_common_vals and histogram_bounds, which are literal values sampled from your columns, so a column holding email addresses will have email addresses in its statistics. The derived numbers, n_distinct and null_frac and correlation, are lower risk rather than safe: on a small or sensitive table they still describe the population. Review them per column rather than exporting the view.

• Can I just give the agent a read-only role and let it look?

  That is the right shape when the agent needs live facts, and it is not the same as handing over a dump. Combine it with column-level grants so the sensitive columns are not readable at all, a statement timeout, and row-level security if the data is multi-tenant. Then what the agent can read is a decision the database enforces.

• Do I need synthetic data?

  Only if the agent has to run queries and see results. For writing SQL and reviewing migrations, schema plus size plus distribution is enough. Generating synthetic rows that preserve the distributions that matter is real work, so do it when you need a runnable environment, not as a default.

Related

• Why does my AI agent hallucinate columns that do not exist?
• 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?