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

The team behind Reperline, a construction scheduling and budgeting system, built an MCP-connected Claude assistant whose database access is constrained by Postgres rather than application code: the MCP server role can only SELECT under row-level security and INSERT into a single pending_changes table, with no UPDATE or DELETE grants, and changes are applied only by a human approver through a SECURITY INVOKER function. The team also documented a migration bug in which revoking EXECUTE on sensitive functions from the MCP role did nothing because Postgres grants EXECUTE to PUBLIC by default, fixed by revoking from PUBLIC and granting to the app role, and recommended has_function_privilege checks in CI.

We build Reperline https://reperline.com/?utm source=devto , 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. 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' ; 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. 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; 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; 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. 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' 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. 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 ; 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". 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. 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.