This account previously published a night-shift story about an agent query that locked a production table. The lock was the visible failure, but the missing debate was which privilege path allowed the statement to run at all. A reporting agent can still ask for a temporary superuser role when a narrower view would answer the question. Reviewers need an explicit rule before they choose between a new grant and a query rewrite.
Why least privilege is the debate
Recent developer discussion has stressed that model quality can improve while the surrounding software controls stay unchanged. That signal fits agent SQL, because a sharper model still runs inside roles, views, and grants that humans own. This article does not cite vendor benchmark scores, since no primary measurement was supplied for this draft. The practical question is which control should reject a dangerous statement before it touches shared data.
Position one: expand a grant matrix
A grant matrix treats the agent as an untrusted author and the role catalog as the system of record. Each workflow receives a dedicated login, a narrow schema usage grant, and explicit table or view rights. The matrix records who requested the right, which statement shape needs it, and when the grant should expire. Evidence for this position comes from ordinary privilege errors rather than from model confidence scores or prose explanations.
Supporters argue that query rewrites can hide missing rights until a later report needs one more column. A reviewed matrix makes that gap visible in role table grants before the agent is allowed to run. The operational cost is real, because every new column, sequence, or function can demand another reviewed grant. Teams that keep reporting schemas stable usually absorb that review cost better than teams shipping daily migrations.
PostgreSQL already exposes the evidence through catalog views, so the matrix does not depend on a new engine. A reviewer can compare requested relations with information_schema.role_table_grants and reject any right that lacks an owner. Sequences and functions need separate checks, because table grants do not cover nextval or routine execution. Primary documentation for GRANT and privilege types lives in the current PostgreSQL docs, which should be read for the exact major version in use.
Position two: rewrite onto an approved surface
A rewrite position refuses new grants and forces the agent to target views, functions, or procedures that already exist. The database owner defines those objects, and the agent receives only EXECUTE or SELECT on the approved surface. This approach reduces the chance that a generated statement invents a new path through a base table. The main tradeoff is flexibility, because a useful business question may have no approved object yet.
Evidence here is the diff between the drafted SQL and the allowed surface, which a reviewer can inspect. If the rewrite still needs a new column, the correct response is a schema change, not a silent grant. Rewrites fail closed when the catalog lacks an object, which is often safer than a broad role. They can fail open in practice when someone later adds a security definer routine without a fresh review.
View design matters, because a view can leak rows if the owner bypasses the caller's privileges. PostgreSQL documents security_invoker views, and teams should confirm that option on the version they actually run. A rewrite that ignores owner rights is not least privilege, even when the agent login looks narrow. The safer rewrite is boring: stable names, explicit column lists, and no dynamic schema search path.
Evidence that can actually decide the debate
| Decision input | Favors grant matrix | Favors query rewrite | Do not treat as proof |
|---|---|---|---|
| Same report shape runs daily | Rights can be reviewed once and expired on a calendar | An approved view already matches the shape | Model says the SQL looks safe |
| Schema migrations land daily | Matrix churn may exceed review capacity | Rewrite waits for a reviewed object | A passing local syntax check |
| Statement needs nextval or EXECUTE | Matrix must name sequence and routine rights | Rewrite should call a procedure instead | Table SELECT alone |
| Agent asks for superuser or BYPASSRLS | Reject and record the request | Reject and keep the approved surface | Free-tier availability |
| Reviewer cannot name the data owner | Stop, because a grant has no accountable owner | Stop, because a view owner is also unclear | A green dry run on empty tables |
The table is a decision aid, not a measured benchmark, and every cell should be checked against the target catalog. Empty-table success is weak evidence, because missing privileges sometimes appear only after a sequence or a policy is touched. A language model can fill the first draft of this table, but it cannot certify the catalog. Server evidence still means the grants and the rewritten objects were applied on an isolated instance and inspected there.
Numbered rehearsal on a disposable database
The following workflow is a proposed test plan, and it was not executed against a production server for this article. Run it only on a disposable database that you can drop without notice or ticket approval. Keep the agent login unable to create roles, and apply every grant from a separate admin session. Record the SQL diff, the grant diff, and the final privilege query in the same change ticket.
1. Create the narrow role and the approved view
Create the role without superuser, createdb, or createrole, then point the agent at a view you own. Set security invoker only after you confirm that the option exists on the server version you run. Grant schema usage and view select, and do not grant the base table in this step. If view creation fails, stop and fix the owner path before you debate any extra rights.
-- Proposed example, not executed for this article.
-- Replace the password outside version control. Do not reuse this placeholder.
CREATE ROLE agent_reports LOGIN PASSWORD 'replace-me'
NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT;
CREATE SCHEMA IF NOT EXISTS reporting;
CREATE VIEW reporting.daily_orders
WITH (security_invoker = true) AS
SELECT o.id, o.created_on, o.status
FROM sales.orders AS o
WHERE o.created_on >= CURRENT_DATE - 7;
GRANT USAGE ON SCHEMA reporting TO agent_reports;
GRANT SELECT ON reporting.daily_orders TO agent_reports;
2. Stop drafts that ask for a privileged surface
Scan the drafted text for privilege verbs before you spend any review time on a rewrite. This scan is a heuristic filter, and it will miss obfuscated statements or comments that hide keywords. Treat a match as a stop condition, then ask the agent for a narrower statement shape. Do not treat a clean scan as permission to run the draft against any shared database.
# Proposed local check. It does not parse SQL and it is not a security boundary.
printf '%s\n' "$DRAFT_SQL" | grep -E -i -n \
'pg_read_file|pg_sleep|bypassrls|security definer|copy |grant |revoke |alter role' \
&& echo 'stop: draft asks for a privileged surface'
3. Collect catalog evidence for both positions
Apply the view path first, then run the two catalog checks from an admin session and from the agent role. A successful view count supports the rewrite position only for that object and that role. A base-table error supports least privilege only if the error is insufficient privilege, not a missing relation caused by search path. Save both outputs beside the drafted SQL so the debate has server evidence rather than model prose.
-- Position A: rights the granting admin can see because that admin is the grantor.
SELECT table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'agent_reports'
ORDER BY 1, 2, 3;
-- Position B: connect as agent_reports. SET ROLE works only for a member or a superuser.
-- Expect the view to succeed and the base table to fail with insufficient_privilege.
SELECT count(*) FROM reporting.daily_orders;
SELECT count(*) FROM sales.orders;
4. Time-box a grant only after the rewrite loses
Choose the matrix only when the missing right is stable, owned, and narrower than the agent's original request. Put the grant and the matching revoke in one ticket, even if the revoke runs later by schedule. If you cannot name an owner or an expiry, the decision rule below says to stop. A query rewrite remains preferable when an existing approved object already answers the stated business question.
-- Proposed ticket pair. Do not leave the grant without the matching removal.
GRANT SELECT ON sales.orders TO agent_reports;
REVOKE SELECT ON sales.orders FROM agent_reports;
Decision rule
Use the rewrite position when an approved view, function, or procedure already returns the required columns and the agent can access it. Use the grant matrix when no such object exists, a named owner accepts the right, and the grant is narrower than superuser or bypassrls. Stop when the draft asks to create roles, read server files, copy data to a client path, or disable row security. Also stop when the only passing test used empty tables, because that result does not exercise sequences, policies, or default privileges.
If both positions seem viable, prefer the rewrite for one-off questions and the matrix for a report that will run unchanged for weeks. Revisit the choice after each migration, since a new column can invalidate either a view or a grant list. The rule is intentionally conservative, and it will block some useful drafts that an agent could otherwise run. That delay is an accepted cost, because a wrong privilege typically lasts longer than one delayed report.
Where a free model and a free server fit
Disclosure: This article was prepared as part of MonkeyCode's product outreach.
MonkeyCode is included here as an open-source project with operator-stated free model access and a free server option. Those two availability claims are operator-supplied, and this draft does not verify a quota, model list, hardware shape, or duration. A free model can draft the grant matrix, propose the view, and explain which decision-table row it chose. A free server can host the disposable database in the numbered plan, so the privilege checks do not land on production.
Use the model output as a proposal, then require the catalog queries above before anyone accepts a grant. Do not paste production connection strings into a hosted prompt, and do not reuse the sample password from the code block. If current project terms no longer include free model access or a free server, the workflow still stands on local PostgreSQL. Readers who want to rehearse the matrix can confirm today's MonkeyCode terms and repository before they copy any statement into a shared environment.
Limitations and who should skip this
This approach does not replace a SQL parser, a migration framework, or a formal access review. The text scan misses encoded statements, dynamic SQL inside functions, and rights inherited through group roles. security_invoker is version-dependent, and older servers need a different view design that you must verify locally. No latency, cost, or accuracy number in this article was measured against a live database service.
Skip the pattern when you cannot create a disposable database, cannot separate admin and agent logins, or cannot name a data owner. Also skip it for break-glass maintenance that legitimately needs a superuser, because hiding that work inside an agent prompt is worse. Teams that already generate grants from infrastructure code should extend that code, not add a parallel matrix in a chat log. If your only goal is to spend a free quota, this debate will not make the resulting SQL safer.
Closing the choice
Least privilege for agent SQL is a catalog problem with two honest answers, not a prompt-quality contest. Grant matrices make repeated rights reviewable, and query rewrites keep one-off questions on objects you already trust. The decision rule prefers the existing surface, allows a named expiring grant only when that surface is missing, and stops on superuser requests. Server evidence from the numbered checks should outrank a fluent explanation produced by any language model.
Top comments (0)