DEV Community

Cover image for Our AI assistant can write to exactly one table. Postgres enforces it, not our code
Viacheslav Bobok
Viacheslav Bobok

Posted on Edited on AI-assisted

Our AI assistant can write to exactly one table. Postgres enforces it, not our code

We build Reperline, a small system that tracks construction schedules, budgets and contracts. It also connects to Claude through MCP (Model Context Protocol), so a project manager can ask "which milestones are late?" or upload an invoice and have Claude prepare the entry.

That second part is where it gets uncomfortable. Budget data is money. We did not want "the AI only proposes changes" to be a promise made by application code that one refactor could break. We wanted the database to make it impossible.

This post is how we set that up in Postgres, and the one default that quietly undid part of it.

Two database roles, two very different grants

The web app and the MCP server connect as different Postgres roles. Neither has BYPASSRLS.

  • app_rw is the web app. It reads and writes, filtered by row level security.
  • app_mcp is the MCP server. It can SELECT (RLS still applies) and it can INSERT into one table: pending_changes.
grant insert on pending_changes to app_mcp;

create policy pending_changes_mcp_ins on pending_changes for insert to app_mcp
   with check (org_id = current_org_id() and status = 'pending' and source = 'claude');
Enter fullscreen mode Exit fullscreen mode

There is no UPDATE or DELETE grant for app_mcp anywhere. So even if a tool handler had a bug, or a prompt talked the model into trying, the worst it can do is file a row that says "pending, from Claude" in its own organisation. A person approves or rejects it in an inbox. The function that applies it, apply_pending_change, is executable by app_rw only and is SECURITY INVOKER on purpose: it runs with the approver's own row level security, so a person can only approve a change they would be allowed to make themselves.

The default that undid it: EXECUTE is granted to PUBLIC

Some writes in our system go through functions, for example applying an imported schedule. In an early migration we wrote, roughly:

revoke execute on function apply_schedule_import(uuid) from app_mcp;
Enter fullscreen mode Exit fullscreen mode

It did nothing. In Postgres a new function is executable by PUBLIC by default, and every role is a member of PUBLIC. Revoking from app_mcp removes a grant that app_mcp never had on its own; the PUBLIC grant still lets it in.

The fix was the next migration:

revoke all on function schedule_import_diff(uuid), apply_schedule_import(uuid) from public;
grant execute on function schedule_import_diff(uuid), apply_schedule_import(uuid) to app_rw;
Enter fullscreen mode Exit fullscreen mode

Since then, sensitive functions are revoked from public and granted to the one role that needs them. If you rely on roles to limit an AI agent (or anything else), check functions as well as tables. has_function_privilege('app_mcp', 'apply_schedule_import(uuid)', 'execute') is a quick test to put in CI.

Permissions as data, checked once per statement

Our roles are configurable per organisation, so policies cannot list role names. A permission is an (area, read or write) pair, and policies call has_perm(area, mode), a SECURITY DEFINER function.

One small detail mattered: we wrap it in a scalar subquery.

using ((select has_perm('budget', 'r')))
Enter fullscreen mode Exit fullscreen mode

Written bare, the planner may call the function for every row. As (select ...) it does not depend on the row, so Postgres evaluates it once per statement as an init plan.

RESTRICTIVE policies for rules that must always hold

Postgres combines ordinary (permissive) policies with OR. That is right for "owner or finance may read", and wrong for rules that must apply on top of everything, like "only the projects you were given" or "a trial that ended is read only".

Those are RESTRICTIVE policies, which are combined with AND. We generate them per table:

create policy budget_lines_proj on budget_lines as restrictive
 for all to app_rw, app_mcp using (<project is visible>) with check (<project is visible>);

create policy budget_lines_w_ins on budget_lines as restrictive
 for insert to app_rw, app_mcp with check (org_writable(current_org_id()));
Enter fullscreen mode Exit fullscreen mode

A new permissive policy added later cannot widen access past them, which is exactly what you want from a rule like "read only after the trial".

A test that finds new tables by itself

The weak spot of RLS is the table someone adds next month and forgets to protect. Our isolation test does not keep a list. It reads the catalogue for every table and view with an org_id column, creates two organisations, and checks, as both app_rw and app_mcp, that a user of A cannot read, update, delete or insert anything of B. A new table is covered the moment it exists, and fails the test run if its policies are missing.

What the database cannot do

Grants and policies decide who can change what. They cannot decide whether Claude's proposal is right. An invoice read wrongly is still a wrong proposal; it just cannot reach the books without a person looking at it. The inbox is where the real control happens, and the database only guarantees that nobody can skip it.

If you are connecting an LLM to data that matters, our one piece of advice: give it its own database role, list exactly what that role may write, and test it the way an attacker would, including functions.

Top comments (22)

Collapse
 
chao_yan_0eca708448360655 profile image
Henry Yan •

The part I'll keep is moving the write limit into the role and the WITH CHECK, so "the AI only proposes" is not a promise sitting in application code that a later refactor can break.

The comments already cover the PUBLIC execute default, current_org_id(), and SECURITY DEFINER search_path, so I won't rehash those. One detail I'd still underline is the insert policy: status = 'pending' and source = 'claude' means app_mcp cannot file an already-approved row. That is the inbox guarantee, and it belongs in the database, not in the tool handler.

I'd apply the same split one layer up. If apply_pending_change or apply_schedule_import is not even a tool the MCP server exposes to the model, a confused call never reaches Postgres. The role is the last refuse; the tool allow-list is the first. The inbox remains the real control for whether the proposal is right, as you said — the database only makes sure nobody can skip it.

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Thanks, that’s the split we ended up with, and it’s actually three layers:

Tool list: the MCP server exposes read tools and four tools that file proposals. There is no apply, import or raw SQL tool, so the model has nothing to call.
EXECUTE: apply_pending_change and apply_schedule_import are granted to the web app’s role only. A test calls apply_pending_change as the MCP role and expects permission denied.
Grants: both are SECURITY INVOKER, so even a call that got through would run with the MCP role’s own rights, and that role can’t write the tables they touch.

Agreed on the inbox: the database only guarantees nobody skips it, and a person decides whether the proposal is right.

Collapse
 
chao_yan_0eca708448360655 profile image
Henry Yan •

The three-layer split is the part I would keep, especially SECURITY INVOKER as the backstop: even a call that slipped past the tool list still cannot write those tables.

The test that calls apply_pending_change as the MCP role and expects permission denied is the one I would not skip, because a later server version can change the tool list. The inbox still only guarantees that a proposal showed up; a person still has to decide whether it is right.

Thread Thread
 
appwebseo profile image
Viacheslav Bobok •

Agreed on both. That test talks to the database role, not to the server, so a future server version that grows an apply tool still hits “permission denied” in CI. Since the first round of comments there is a second net under it: a new function is now executable by nobody until it is granted, and a test walks the catalogue and fails if the MCP role can reach any SECURITY DEFINER function outside a short allow-list.

Thread Thread
 
chao_yan_0eca708448360655 profile image
Henry Yan •

The catalogue walk is the part I would keep next to the apply_pending_change denial.

A new SECURITY DEFINER function that is executable by nobody until it is granted is the right default, but the default only holds if something fails when the MCP role can still reach a function that is not on the allow-list. The walk of the catalogue is that check: it talks to the database role, not to the server, so a function added later cannot slip through just because the tool list never mentioned it.

Collapse
 
hitensethiya profile image
Hiten Sethiya •

the line about the db not being able to tell if a proposal is actually right is the part most people skip. we keep the same split at searchspot, the ai can line up a hotel or flight but nothing paid happens until the traveler confirms it. who ends up reviewing the pending budget rows on your side, the PM or whoever asked claude

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Good question. It follows the area, not who asked. A budget row can only be approved by someone who can both decide in the inbox and write the budget: by default the owner or finance. A site manager can open the inbox, but approving a budget row fails, because the approval runs with the approver’s own rights.

The proposal records who asked Claude, and the decision records who approved it. We don’t block approving your own proposal today: at a small developer, the finance person often asks Claude to read an invoice and then approves the row. Same as your traveler, really. What matters is that a person looks at it before it becomes a number.

Do you ever require a second person at SearchSpot, say above some amount?

Collapse
 
hitensethiya profile image
Hiten Sethiya •

not a second person, no. the hard line is that nothing paid goes through unless the traveler confirms it themselves, and the browser assistant stops before payment. same idea as yours, a person looks before it becomes money. do you think you'll add a threshold for budget rows later or is the who asked / who approved trail enough for small teams

Thread Thread
 
appwebseo profile image
Viacheslav Bobok •

Same line then: the person who will be held to it looks before it counts.

No threshold today. For budget rows the split is by role rather than by amount: a site manager can file one from Telegram, but only owner or finance (by default) can approve it. The gap is the one you’d expect: when finance asks Claude for a row, finance can approve it too, and what’s left is the trail of who asked, from where, who approved and when.

For small teams I think that’s mostly enough, partly because nothing in Reperline moves money. A budget row changes the plan; the payment still goes through the bank with whatever sign-off the company already has. If we add a threshold, it would be a four eyes rule rather than a block: above an amount the org sets, the person who asked can’t approve their own row. Cheap to build, but I’d rather wait until a team asks for it than guess the number for them.

Thread Thread
 
hitensethiya profile image
Hiten Sethiya •

a four eyes rule above an amount feels right, and waiting for a team to ask for the number is fair. asked and approved by the same person is the case id surface first even without blocking it, so finance can filter those later

Collapse
 
mike-johnstone profile image
Mike Johnstone •

A proposal can go stale in the inbox too. If someone changes a milestone before approval, the reviewer may be looking at a diff against an old value. I'd store the base row version with the proposal and check it atomically when applying, so a stale proposal needs a fresh review. Does apply_pending_change already cover that?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

It did not, and it matters more here than usual because a milestone proposal carries an absolute value. If Claude proposed 55% to 65% and a site manager then entered 70%, approving would write 65%: progress lowered with no note. The inbox showed the live value next to the proposal and the controller got a flag afterwards, but nothing stopped the approve.

Done the way you describe, going out with the next deploy. A BEFORE INSERT trigger stamps the base into the proposal: the milestone’s progress and the number of entries in its journal. It overwrites whatever the client sent, so Claude cannot supply its own base. apply_pending_change locks the milestone row, compares, and refuses the approval if anything moved. Reject still works, so a stale proposal can be thrown away but not approved, and the inbox says so.

The count is there because progress alone misses 70 back to 55 back to 70. One side effect I like: approving one of two pending proposals for the same milestone makes the other one stale. Budget lines and transactions already re-check at apply (unique code, duplicate reference), so only milestones needed this. Thanks, this was a real gap.

Collapse
 
mike-johnstone profile image
Mike Johnstone •

The journal count is a nice touch for a value that changes and then returns to its starting point. Keeping Reject available makes sense too. Thanks for coming back with the implementation details.

Collapse
 
ascendra_ventures_92c21f2 profile image
Ascendra Ventures •

Moving "the AI only proposes" out of application code and into grants is what makes this worth reading, and the PUBLIC EXECUTE default is a great catch because it fails silently: the revoke runs, nothing errors, and the hole stays open. X1Y1's note that the IN SCHEMA form of ALTER DEFAULT PRIVILEGES cannot take away what the global default grants is the same trap one level up.

We reached the same conclusion while building memory for our own coding agents at Ascendra Ventures: anything that has to hold should be enforced by something the model cannot talk to. The agent we can reach from a phone is read-only and has no internet tools, so a prompt injection has no way out, and the knowledge store keeps references to secrets, never the secrets themselves, because folder boundaries on one machine do not protect anything. The model sits in the middle and plain code guards both edges.

Mike's point about stale proposals looks like the next thing to close. Do you plan to store the base row version with each pending change and reject it on apply if the row has moved since?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Thanks. “Enforced by something the model cannot talk to” is a good way to put it, and the stale proposal was exactly that kind of gap: the reviewer was the guard, but the number on the reviewer’s screen could have moved under them. Yes, that is how it works now: a trigger stamps the base when the proposal is filed (anything the client sends is overwritten) and the approval is refused once the milestone has moved. Details in the reply to Mike.

Collapse
 
x1y1co profile image
X1Y1 •

The PUBLIC default can be closed once instead of per function: ALTER DEFAULT PRIVILEGES FOR ROLE migrator REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;, run for whichever role owns your migrations. One catch is that it has to be the global form. The Postgres docs say a per-schema IN SCHEMA rule can't revoke what the global default grants, so the IN SCHEMA version looks right and does nothing, much like your first revoke. We read a similar story in the AWS API MCP server's changelog this week: in 1.3.47 its denylist was silently skipped for the whole process if a startup index failed to load. Its own README already says IAM is the primary control, which is the same call you made by putting the rule in Postgres instead of the server.

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Thanks, the global form is the cleaner fix, and the IN SCHEMA trap is real: the docs say a per-schema rule only adds to the global defaults and can’t take anything away. We’re adding it for the role that owns our migrations, and keeping the explicit revokes on sensitive functions as a second layer.

Collapse
 
techdevfun profile image
TECHDEV •

The PUBLIC EXECUTE default is the part of this post I'd steal first, and it points at a gap in the test design itself. Your isolation test enumerates every table with an org_id column, but the hole you actually found was a function default, not a table default. The same catalog-driven trick should cover functions: enumerate every function in the schema and assert none sensitive is executable by PUBLIC or app_mcp. The principle that makes the table test good - enumerate the catalog instead of keeping a list - is exactly what would have caught the next default-grant surprise in the same place you found this one.

The other thing the whole stack rests on is current_org_id(). The RLS policies, the with check on pending_changes, the restrictive policies, all of them take that function's answer as given. If it's bound once at connect time from the authenticated session, the chain is solid: the model can talk the MCP server into anything it likes and the database still only ever sees its own org. If it's set per request from something the MCP server parsed, the binding is only as strong as the MCP server's discipline, and a prompt that gets the model to switch org context is the same shape of failure as the PUBLIC default, a quiet default undoing the enforcement one layer down. Which is it in your setup?

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Fair hit. Our function grants are checked against a hand-written list, the exact weak spot you describe. We’re switching to a catalog walk: every function in the schema, with an allow-list for what PUBLIC and the MCP role may execute.

On current_org_id(): it’s set per transaction by the MCP server, from its own config locally or from a verified OAuth token on the remote endpoint. No tool accepts an org or raw SQL from the model, so a prompt can’t switch it. The honest limit is that Postgres doesn’t verify that setting, so the binding is as strong as the MCP server process, not stronger.

Collapse
 
kolmopdf profile image
KolmoPDF •

For the SECURITY DEFINER permission helper, pin a safe search_path inside the function and schema-qualify objects it calls. Otherwise an attacker who can create objects in a searched schema may be able to shadow a referenced object. Also test with the actual MCP login role, including inherited memberships: a direct GRANT audit alone can miss privileges inherited through another role.

Collapse
 
appwebseo profile image
Viacheslav Bobok •

Every SECURITY DEFINER function here pins search_path = public, but your comment made me check the gap: without pg_temp listed last, the session’s temp schema is searched first for table names, and PUBLIC can create temp tables by default. We’re moving to public, pg_temp. And you’re right about the login role: our tests checked the group role, not the role the MCP server logs in as. Fixing both.

Some comments may only be visible to logged-in visitors. Sign in to view all comments.