DEV Community

Clément Creuzenet
Clément Creuzenet

Posted on Fully Autonomous

The Supabase view Claude created that anyone could read

Claude Code created a view for me through an MCP SQL tool: a clean create view public.my_view as ..., no security_invoker.

A check found the problem the next day. The view was readable with the anon key, the one that ships in the frontend.

Why it happens

The tables had row level security. The view didn't care. In Postgres, a view runs with the rights of the user who created it, not the user who queries it. The creator was postgres, which bypasses RLS. And Supabase has long granted select on new objects in public to anon and authenticated by default. Its changelog announces a change for new tables from October 30, so check your project rather than trust either rule.

So a view in public without security_invoker is a hole straight through your policies. Claude knows this if you ask. It doesn't think about it while it's focused on making the dashboard work.

The fix is one option:

create view public.my_view
with (security_invoker = true) as
select ...;
Enter fullscreen mode Exit fullscreen mode

The same trap exists with security definer functions in public (callable by anon unless you revoke execute), and with create table without enable row level security.

Catch it before it runs

If Claude talks to your database through an MCP tool, a PreToolUse hook sees every SQL statement before it's sent. A minimal version for the view case:

#!/usr/bin/env python3
import json, re, sys

event = json.load(sys.stdin)
sql = json.dumps(event.get("tool_input") or {})
for stmt in re.findall(r"create\s+(?:or\s+replace\s+)?view\s+[^;]+", sql, re.I):
    if re.search(r"\bpublic\.", stmt, re.I) and "security_invoker" not in stmt.lower():
        print("This view in public runs with its creator's rights and anon can read it. "
              "Add with (security_invoker = true).", file=sys.stderr)
        sys.exit(2)
Enter fullscreen mode Exit fullscreen mode

Register it with "matcher": "mcp__.*" so it runs on MCP tool calls. Claude gets the reason and rewrites the statement with security_invoker.

What I use now

The snippet above closes the common case. A fuller version, which also catches views with no schema, quoted names and materialized views, is free in seatbelt-lite.

Top comments (3)

Collapse
 
vladtkdev profile image
Vlad Tkachenko •

Good catch. The security_invoker default surprises everyone exactly once.

One thing to expect when you turn it on: the view then checks the caller's privileges on the base tables as well, not only their policies. So a view that worked can start answering permission denied for table for anon, which is the same grant question as your 30 October note. It is a per view property rather than an inherited one, too, so a view carrying the flag that selects from one without it still reads the inner tables as that inner view's owner.

Does the hook see migrations as well, or only SQL that goes through the MCP tool? supabase db push is the route I would worry about.

Collapse
 
patchandship profile image
PatchAndShip •

nice writeup. the hook is a good idea. for views claude already made before you had it, security advisor flags them (the "security definer view" lint), and on postgres 15+ alter view ... set (security_invoker = true) fixes each one in place without recreating it.

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