PostgreSQL database access for AI app builders

An AI app builder can connect to an existing PostgreSQL database without owning its schema, but only if you make that boundary real in PostgreSQL. A prompt that says "do not change production" is not a control. A separate role, transaction defaults, explicit migration review, and schema checks are controls.

The safe model splits database work into three lanes. Discovery reads metadata and samples permitted data. The application reads and writes only the tables and operations it needs. Schema change runs through a separate migration identity after a human approves exact SQL. I have watched teams collapse those lanes into one convenient owner credential, then discover that an agent treated a plausible column name as permission to redesign a live table. Convenience lasted an afternoon; cleanup lasted much longer.

Discovery should be read only by construction

A discovery connection needs enough access to understand the allowed schema, not enough access to improve it. Create a login role that cannot create databases or roles, cannot bypass row security, and cannot inherit surprise privileges from a broad group. PostgreSQL starts new roles without those powers, but explicit declarations make the intent reviewable.

CREATE ROLE app_discovery
  LOGIN
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOINHERIT
  NOBYPASSRLS
  CONNECTION LIMIT 3
  PASSWORD 'replace-through-secret-manager';

ALTER ROLE app_discovery SET default_transaction_read_only = on;
GRANT CONNECT ON DATABASE customer_portal TO app_discovery;
GRANT USAGE ON SCHEMA app TO app_discovery;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_discovery;

default_transaction_read_only blocks ordinary writes in sessions that keep the default. It is a useful belt, not the suspenders. The lack of INSERT, UPDATE, DELETE, TRUNCATE, CREATE, and ownership is what keeps the role contained if a client changes its transaction setting. Do not grant the role membership in an application owner group, and do not make it the owner of a schema.

Existing grants deserve inspection before the builder connects. The following query produces one row per table privilege, so a reviewer can spot anything beyond SELECT:

SELECT table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'app_discovery'
ORDER BY table_schema, table_name, privilege_type;

A healthy result has a shape like app | invoices | SELECT. An empty result may mean discovery cannot see a required table; a row ending in UPDATE means the role is too powerful. Also check schema privileges with has_schema_privilege and database privileges with has_database_privilege, because table grants do not reveal whether the role can create objects elsewhere.

Do not use a production snapshot as an excuse to share an owner credential. A copy can still contain customer data, and an agent with ownership can alter it so thoroughly that later comparisons become useless. Give discovery a dedicated identity on every environment.

Catalog inspection must stay inside an allowlist

The builder should discover only approved schemas and record what PostgreSQL actually reports. information_schema offers portable views for tables, columns, constraints, and privileges. pg_catalog exposes PostgreSQL details such as indexes, types, generated expressions, and row security. Both are better sources than an LLM's memory of a typical customer table.

Start with an allowlist such as app and reporting. Reject pg_catalog, information_schema, temporary schemas, extension schemas, and every unlisted tenant schema as application targets. The query should filter at the database, role, and SQL levels; a prompt level allowlist alone can disappear during a later chat.

SELECT
  c.table_schema,
  c.table_name,
  c.ordinal_position,
  c.column_name,
  c.data_type,
  c.is_nullable,
  c.column_default
FROM information_schema.columns AS c
WHERE c.table_schema IN ('app', 'reporting')
ORDER BY c.table_schema, c.table_name, c.ordinal_position;

Store the result as a schema snapshot with a retrieval time and a database identifier. The snapshot is evidence of what the generator saw. It is not permanent truth. PostgreSQL can change between discovery and code generation, so compare a fresh fingerprint before deployment. A practical fingerprint can hash ordered table, column, type, nullability, default, constraint, and index descriptions. If the fingerprint differs, stop and rediscover instead of guessing which change is harmless.

Sampling rows is a separate permission decision. Column metadata rarely contains personal data, while row samples often do. Prefer zero row sampling for code generation. If examples are necessary, expose a view that removes or masks secrets and direct identifiers, then grant SELECT on that view alone. LIMIT 10 does not make a sensitive query safe; it only makes the leak smaller.

Search path deserves the same treatment. Set it to the approved schema plus pg_catalog, qualify generated table names, and never rely on whichever object PostgreSQL resolves first. An attacker or careless migration can create a same named object in a writable schema. Qualified names such as app.orders remove that ambiguity.

The runtime role should match actual user actions

Discovery and runtime are different jobs. The runtime application may need to insert an order, update a draft, or call a carefully designed function, but that does not justify broad write access across the discovered schema. Build a permission matrix from user actions, then translate each action into the smallest PostgreSQL grant.

For example, an invoice viewer may need SELECT on app.invoices and app.invoice_lines, while a note feature needs SELECT and INSERT on app.invoice_notes. It probably does not need DELETE on invoices, access to password reset records, or schema creation. Grant sequence usage only when an insert actually relies on that sequence. PostgreSQL treats sequences as separate objects, which surprises generators that test with an owner account.

Views and functions can narrow the surface further. A view can expose approved columns while hiding internal fields. A SECURITY DEFINER function can perform one controlled operation that ordinary grants cannot express, but it needs a fixed search_path, strict input checks, and an owner that has no unnecessary powers. Treat such a function as privileged code, not as a shortcut around the permission model.

Row level security adds a data boundary within a shared table. It does not replace table grants. PostgreSQL first checks whether the role can perform the operation, then applies row security policies when enabled and applicable. Test with the exact runtime role because table owners and roles with BYPASSRLS can escape policies. A test run under the migration owner proves almost nothing about what an end user can see.

Keep secrets out of prompts, generated source, browser bundles, build logs, and screenshots. Put the runtime credential in the hosting environment's secret store and inject it only into the server process. Mobile and browser applications cannot keep a PostgreSQL password secret, so they should call a server API rather than connect directly. Rotate discovery, runtime, and migration credentials independently; a leak in one lane should not open the other two.

Migration power belongs to a separate approval path

An app builder may propose migrations, but it should not execute them with its discovery or runtime session. Give migration work a separate role, or let an established deployment system assume that role for one approved job. Keep its credential unavailable during ordinary chat and preview sessions.

Approval must cover exact SQL, the target database identity, the schema fingerprint used to prepare it, and the expected lock or rewrite behavior. Approving a natural language sentence such as "add customer status" leaves too much room. The executable change might add a nullable text column, rebuild a large table, invent an enum, or update every existing row. Those are different operations with different failure modes.

I use a compact migration packet:

1. The reason for the change and the application version that requires it.
2. Exact forward SQL and, where honest, exact reversal SQL.
3. Objects, privileges, and rows the commands can affect.
4. Preflight queries, expected results, and a fresh schema fingerprint.
5. Lock timeout, statement timeout, backup or snapshot reference, and release owner.

A reversal script is not always a rollback. Dropping a newly added column can reverse the catalog change, but it also destroys data written after release. PostgreSQL's transactional DDL helps with many catalog operations, yet a transaction cannot restore external side effects or data that a later command deleted. Label destructive reversals plainly instead of treating DOWN as a magic word.

Set lock_timeout so a migration fails rather than waiting behind a busy transaction while blocking new work. Set statement_timeout according to the reviewed operation. Run preflight queries again inside the change window. If table size, conflicting objects, null counts, or schema fingerprint differ from the approved assumptions, abort. The agent should return a mismatch report, not improvise a new migration against production.

Never auto approve a migration because generated tests passed. Tests usually run on a small, clean schema and miss lock queues, old nulls, unusual constraints, extensions, and application versions still serving traffic. Approval is the point where a person reconciles generated intent with the live system.

Connection pooling changes the safety calculation

A pool reuses database sessions, so session state can outlive the request that created it. If one request runs SET search_path, changes a role, creates a temporary object, or disables a timeout, the next borrower may inherit the result. The application must either avoid mutable session state or reset it reliably when a connection returns to the pool.

Transaction pooling makes the boundary stricter. A client may receive a different server session after each transaction, which breaks assumptions about session prepared statements, temporary tables, advisory locks, and session level settings. Builders often generate code that works through a direct connection and fails behind a pool because they never model this difference. Decide whether the pool uses session or transaction mode, then include that mode in generation and tests.

Budget connections before deployment. Start with the database's allowed connections, reserve capacity for administration, migrations, monitoring, and other services, then divide the remainder across application instances. If ten instances each open twenty connections, PostgreSQL sees two hundred potential sessions even when traffic is quiet. A conservative small pool with a queue is usually safer than multiplying connections until the database refuses them.

Use server side timeouts as backstops: statement_timeout limits long statements, lock_timeout limits waits for locks, and idle_in_transaction_session_timeout removes sessions that hold a transaction open while doing nothing. Set values for each role rather than trusting every generated client to remember them. Verify them with SHOW under the actual role and through the actual pool.

Health checks should be cheap. SELECT 1 confirms a round trip, but it does not confirm the application can reach an approved table or that its search path is correct. A readiness check can query a tiny stable view with the runtime role. Keep migrations out of application startup; simultaneous instances racing to alter the schema create exactly the coupling this design is meant to remove.

Invented columns should fail before a query runs

LLMs invent plausible identifiers. If a prompt discusses a customer's display name, generated code may reach for customers.display_name even when the database stores given_name and family_name. The database will reject that query, which is better than silently reading the wrong field, but a production error is still a poor schema validation strategy.

Generate a typed schema artifact from the approved catalog snapshot and make it the only source for query construction. A table or column absent from that artifact should cause a generation error. Do not let the model repair the error by adding a migration unless the task explicitly enters the migration lane. A missing identifier can mean stale discovery, a spelling mistake, the wrong environment, or a genuine product requirement. Each calls for a different response.

Static checks should parse SQL and resolve every relation and column against the snapshot. Then prepare statements against a disposable database or a transaction that cannot write. PostgreSQL's parser catches unknown columns, ambiguous references, operator type errors, and many bad casts without requiring successful business data. Run integration tests with the runtime role so permissions and row policies participate.

The failure report needs enough detail for a person to decide. Include the SQL location, unresolved identifier, nearby valid identifiers, snapshot fingerprint, and target database identity. Suggestions are useful, but automatic fuzzy replacement is dangerous. Changing billing_address_id to shipping_address_id because the names look close can produce valid SQL with false business meaning.

For dynamic filters and sorts, map public API names to a closed set of qualified SQL expressions. Never paste a model supplied identifier into SQL, even through a value parameter. Parameters protect values, not table or column names. If users can choose a sort field, translate created to a known expression such as app.orders.created_at; reject every unknown token.

Schema drift should stop a release, not trigger creative reconciliation. Regenerate the snapshot, show the diff, and repeat tests. That delay can feel fussy, but it is cheaper than deploying code whose understanding of the database exists only in a conversation transcript.

Destructive SQL needs a deny policy and evidence

A builder should classify SQL before anyone can execute it. Block DROP, TRUNCATE, broad DELETE or UPDATE without a reviewed predicate, ownership changes, privilege escalation, extension changes, and commands aimed outside approved schemas. Treat ALTER TABLE as review required, not automatically safe. A column type change or new nonnull constraint can scan or rewrite data and hold consequential locks.

Text matching alone is weak because SQL has comments, quoted identifiers, functions, and many ways to express side effects. Parse statements with a PostgreSQL aware parser, inspect their syntax trees, and also rely on the database role to deny forbidden actions. The classifier improves review; privileges enforce the boundary. Neither should carry the whole burden.

Use a staging database restored from a recent, properly protected snapshot when a migration depends on real table shapes or data distributions. Apply the exact migration packet there, capture duration and lock observations, run application tests with runtime credentials, then discard the environment. Do not quietly edit SQL between staging and production. Any edit creates a new artifact that needs a new fingerprint and approval.

Logs should connect a proposal to an execution without recording secrets or sensitive rows. Record who approved the immutable migration artifact, its digest, target identity, start and finish status, and PostgreSQL error details. Preserve the generated diff and preflight results. An agent chat is useful context, but it is not an audit record because users can branch, retry, and paraphrase instructions.

Snapshots and rollback controls reduce recovery time, but they do not make destructive SQL acceptable. A snapshot may restore an entire database to an earlier point when the actual need is one dropped column, and restoration can discard legitimate writes made after the snapshot. Test recovery separately and document who can invoke it.

When I use Koder.ai for an app that touches an established database, I keep the work in planning mode until I have reviewed the exported source and the proposed database boundary; snapshots and rollback are recovery controls, not permission to skip that review. The same rule applies to any builder: product convenience must sit behind database enforcement.

Schema changes must tolerate mixed application versions

A migration is safe only when both the old application and the new application can run during the release window. Production rarely flips from one version to another in a single instant. Requests may reach old instances while new instances start, queued jobs may carry older payloads, and a rollback may put yesterday's code back against today's schema. An app builder that validates only the final code against the final schema misses this overlap.

Favor additive changes first. Add a nullable column, add a new table, or add an index without removing the old path. Deploy code that can read both representations and writes the new representation where appropriate. Backfill existing rows with a separately reviewed job, watch errors and lag, then make the new field authoritative. Remove the old column or constraint in a later release after evidence shows no running code uses it.

This sequence takes longer than generating one ALTER TABLE statement, but it isolates failures. If the new code misbehaves before removal, the old path still exists. If a backfill falls behind, it can pause without holding the application release hostage. If deployment rolls back, the old application still recognizes the database. The extra release is cheaper than discovering during rollback that the previous binary queries a column the migration already dropped.

Renames need special care because PostgreSQL changes the name immediately. A generator may propose renaming customer_ref to customer_id because the new name reads better. Old instances will fail as soon as the migration commits. Add customer_id, keep both fields synchronized in application code or a narrowly reviewed trigger, move readers, and remove customer_ref only after old writers disappear. The temporary duplication is visible debt with a removal condition; an immediate rename is invisible release coupling.

Defaults and nonnull constraints can also hide work. Before approving SET NOT NULL, count existing nulls and prove that every active writer supplies a value. For large or busy tables, review how the PostgreSQL version validates the constraint and what locks it takes. A builder should report those preconditions rather than infer them from a schema that contains no representative traffic.

Data backfills should not ride inside an unbounded schema transaction. Update rows in measured batches through an approved worker, record progress with a stable cursor, and make retries idempotent. A retry is idempotent when applying it twice produces the intended state, not merely when PostgreSQL accepts the second query. For derived values, record the derivation version if later code might calculate them differently.

The release packet should name four compatibility points:

1. The oldest application version allowed to run before the migration.
2. The schema state accepted by both old and new versions.
3. The signal that permits the destructive cleanup release.
4. The recovery route if new code rolls back after data has changed.

Generated queries should avoid SELECT * across these transitions. Adding a column can change scan cost, result decoding, positional mapping, and data exposure even though old SQL still parses. List qualified columns explicitly and generate decoders from the same schema snapshot. This also makes a source review reveal exactly which data crosses the database boundary.

Prepared migration tools often track an applied version in a table, but a version number alone does not prove compatibility. Record the digest of the exact SQL artifact, because two files with the same friendly name can contain different commands. The executor should refuse an already recorded version with a different digest. It should also refuse a later migration when a required predecessor is missing.

Do not let every application instance run migrations at startup. Even when a migration tool uses an advisory lock, startup now depends on a privileged credential and on schema work finishing before health checks expire. Put migration execution in one release job, wait for its recorded result, and start runtime instances with an identity that cannot alter the schema. If the release system cannot separate those phases, fix the release system before granting the application owner powers.

Test this timeline, not just the destination: old code on old schema, old code on expanded schema, new code on expanded schema, and rolled back code after new writes. Cleanup gets its own test later. This matrix catches the changes that are syntactically valid yet operationally impossible to reverse.

Prove the boundary with negative tests

A safety design is incomplete until the forbidden actions fail under test. Connect as discovery and attempt an insert, a table creation, and a SET TRANSACTION READ WRITE. Connect as runtime and attempt access to an ungranted table, a cross tenant read covered by row security, and a schema change. The expected result is a PostgreSQL permission error, not a promise in an agent log.

Run positive tests too. Discovery must still read every allowed catalog entry. Runtime must perform each approved user action through the pool. Migration execution must work only through the approval path. A boundary that blocks the product's normal work will invite someone to replace it with an owner credential during an incident.

Keep a small access contract beside the application source. It should name the database, allowed schemas, discovery scope, runtime operations, pool mode, timeout policy, migration approvers, schema fingerprint method, and prohibited statements. Compare actual grants with that contract in continuous checks. PostgreSQL grant drift is configuration drift, even when nobody changed application code.

Recheck after role changes, new tables, restored databases, pool upgrades, and hosting changes. Default privileges matter for future objects: granting SELECT ON ALL TABLES covers current tables, not tables created later. Decide whether new objects should be invisible until reviewed or included through narrowly configured default privileges. I prefer invisible by default because an explicit grant forces the new table into the access conversation.

Include revocation in the test plan. Disable the discovery credential and confirm that runtime traffic continues; disable runtime and confirm that migration tooling does not silently substitute its stronger identity. Then rotate each secret while connections are active and observe whether the pool retires old sessions within the intended window. A password change does not terminate sessions that already authenticated, so rotation procedures need an explicit pool recycle or PostgreSQL session termination policy.

Review failure messages for accidental disclosure during these tests. PostgreSQL errors can include relation names, fragments of SQL, constraint names, and supplied values. Send detailed errors to restricted server logs, return a stable public error to clients, and never feed an entire production error stream back into an agent conversation. The builder needs the statement location and sanitized database response to repair code; it does not need customer values.

One final test catches a surprising number of unsafe integrations: remove the migration credential entirely and run the application test suite. If normal startup, health checks, previews, or request handling fail, schema ownership has leaked into the runtime path. Fix that coupling before connecting the builder to production. An AI app builder can work with a database it does not own, but PostgreSQL must be able to say no when the generated code forgets the arrangement.