supabase-grants-check

1// Free Supabase grants guard, as a Claude Code mod (Claude Code 2.1.287+). MIT.
2// Refuses SQL that grants anon (or PUBLIC, which every role inherits) more than SELECT: ALL, INSERT, UPDATE, DELETE,
3// TRUNCATE, REFERENCES, TRIGGER or EXECUTE, and role-membership grants (GRANT <role> TO anon/authenticated),
4// in Bash commands, file writes and MCP tool inputs. Comments are removed first. Linear-time: one pass to strip
5// comments, then split into statements and check each one with indexOf/split (no backtracking regex).
6const RESERVED = new Set(['tool', 'tool_use_id', 'consent', 'agentId'])
7const MAX = 1 << 20
8const WRITE_PRIVS = ['all', 'insert', 'update', 'delete', 'truncate', 'references', 'trigger', 'execute', 'create', 'maintain', 'temporary', 'temp']
9// CREATE (e.g. on schema public) is refused for authenticated too; the other privileges only for anon/PUBLIC.
10const CREATE_TARGETS = new Set(['anon', 'authenticated', 'public'])
11const ANON_LIKE = new Set(['anon', 'public'])
12const MEMBER_TARGETS = new Set(['anon', 'authenticated'])
13
14/** Replace block comments and -- line comments with a space. Single pass, O(n). */
15export function stripComments(t) {
16  const parts = []
17  const n = t.length
18  let i = 0, nb = t.indexOf('/*'), nl = t.indexOf('--')
19  while (i < n) {
20    if (nb >= 0 && nb < i) nb = t.indexOf('/*', i) // cached positions only move forward: O(n) overall
21    if (nl >= 0 && nl < i) nl = t.indexOf('--', i)
22    const b = nb < 0 ? n : nb, l = nl < 0 ? n : nl
23    const nx = Math.min(b, l)
24    if (nx >= n) { parts.push(t.slice(i)); break }
25    parts.push(t.slice(i, nx), ' ')
26    if (nx === b) { const e = t.indexOf('*/', nx + 2); i = e < 0 ? n : e + 2 }
27    else { const e = t.indexOf('\n', nx + 2); i = e < 0 ? n : e }
28  }
29  return parts.join('')
30}
31
32const ROLE_OPT_END = new Set(['in', 'role', 'user', 'admin', 'with', 'password', 'login', 'nologin', 'inherit', 'noinherit', 'superuser', 'nosuperuser', 'createdb', 'nocreatedb', 'createrole', 'nocreaterole', 'replication', 'noreplication', 'bypassrls', 'nobypassrls', 'connection', 'valid', 'sysid', 'encrypted', 'unencrypted'])
33
34/** CREATE ROLE/GROUP/USER ... [WITH] ROLE|USER|ADMIN anon, and ALTER GROUP ... ADD USER anon, make anon (or authenticated)
35 * a member of the new role, so it inherits whatever that role is granted later. IN ROLE anon is the other direction (harmless). */
36export function findMembershipStatement(raw) {
37  if (!raw.includes('anon') && !raw.includes('authenticated')) return null
38  if (!raw.includes('create') && !raw.includes('alter')) return null
39  const tok = raw.split(/[\s,"'`\\()]+/).filter(Boolean)
40  for (let i = 0; i + 2 < tok.length; i++) {
41    const verb = tok[i], kind = tok[i + 1]
42    if (verb === 'create' && (kind === 'role' || kind === 'group' || kind === 'user')) {
43      // tok[i+2] is the new role's name; scan its options until the statement ends.
44      let inList = false
45      for (let j = i + 3; j < tok.length; j++) {
46        const t = tok[j]
47        if (t === 'role' || t === 'user' || t === 'admin') { inList = tok[j - 1] !== 'in'; continue }
48        if (ROLE_OPT_END.has(t)) { inList = false; continue }
49        if (inList && MEMBER_TARGETS.has(t)) return tok.slice(i, j + 1).join(' ').slice(0, 200)
50      }
51      return null
52    }
53    if (verb === 'alter' && kind === 'group') {
54      for (let j = i + 3; j + 1 < tok.length; j++)
55        if (tok[j] === 'add' && tok[j + 1] === 'user') {
56          for (let k = j + 2; k < tok.length; k++) if (MEMBER_TARGETS.has(tok[k])) return tok.slice(i, k + 1).join(' ').slice(0, 200)
57          return null
58        }
59      return null
60    }
61  }
62  return null
63}
64
65const STMT_WORDS = ['grant', 'create', 'alter', 'reassign', 'owner']
66const ROLE_ATTRS = new Set(['bypassrls', 'superuser', 'createrole', 'createdb', 'replication', 'login'])
67const OWNER_TARGETS = new Set(['anon', 'authenticated', 'public'])
68
69/** Unicode-escaped identifiers (U&"\0061non") are not decoded: any U&" in a grant, role or owner statement is refused.
70 *  OWNER TO / REASSIGN OWNED ... TO anon/authenticated/public, and ALTER ROLE|USER anon/authenticated with
71 *  BYPASSRLS/SUPERUSER/CREATEROLE/CREATEDB/REPLICATION, are refused. One token pass per statement: O(n). */
72export function findOwnerOrRoleAttr(raw) {
73  const flat = raw.replace(/[\s\\]+/g, '') // backslashes too: psql -c "... U&\"\\0061non\""
74  if (flat.includes('u&"') && STMT_WORDS.some((w) => raw.includes(w))) return raw.trim().replace(/\s+/g, ' ').slice(0, 200)
75  if (!raw.includes('owner') && !raw.includes('owned') && !raw.includes('alter') && !raw.includes('authorization')) return null
76  const tok = raw.split(/[\s,"'`\\()]+/).filter(Boolean)
77  for (let i = 0; i < tok.length; i++) {
78    const t = tok[i]
79    if (t === 'owner' && tok[i + 1] === 'to' && OWNER_TARGETS.has(tok[i + 2])) return tok.slice(Math.max(0, i - 4), i + 3).join(' ')
80    if (t === 'create' && tok[i + 1] === 'schema') {
81      // CREATE SCHEMA [IF NOT EXISTS] [name] AUTHORIZATION role makes role the owner, like ALTER SCHEMA ... OWNER TO.
82      for (let j = i + 2; j + 1 < tok.length; j++) if (tok[j] === 'authorization') return OWNER_TARGETS.has(tok[j + 1]) ? tok.slice(i, j + 2).join(' ') : null
83      return null
84    }
85    if (t === 'create' && tok[i + 1] === 'database') {
86      // CREATE DATABASE d [WITH] OWNER [=] role
87      for (let j = i + 2; j < tok.length; j++) if (tok[j].startsWith('owner=')) return OWNER_TARGETS.has(tok[j].slice(6) || tok[j + 1]) ? tok.slice(i, j + 2).join(' ') : null
88      else if (tok[j] === 'owner') { const k = tok[j + 1] === '=' ? j + 2 : j + 1; return OWNER_TARGETS.has(tok[k]) || OWNER_TARGETS.has((tok[j + 1] ?? '').replace(/^=/, '')) ? tok.slice(i, k + 1).join(' ') : null }
89      return null
90    }
91    if (t === 'reassign' && tok[i + 1] === 'owned') {
92      for (let j = i + 2; j + 1 < tok.length; j++) if (tok[j] === 'to') return OWNER_TARGETS.has(tok[j + 1]) ? tok.slice(i, j + 2).join(' ') : null
93      return null
94    }
95    if (t === 'alter' && (tok[i + 1] === 'role' || tok[i + 1] === 'user') && MEMBER_TARGETS.has(tok[i + 2])) {
96      for (let j = i + 3; j < tok.length; j++) if (ROLE_ATTRS.has(tok[j])) return tok.slice(i, j + 1).join(' ').slice(0, 200)
97      return null
98    }
99  }
100  return null
101}
102
103/** Returns the offending statement (trimmed) or null. */
104export function findAnonWriteGrant(text) {
105  if (typeof text !== 'string' || text.length === 0) return null
106  if (text.length > MAX) text = text.slice(0, MAX)
107  let lower = text.toLowerCase()
108  if (!lower.includes('grant') && !lower.includes('role') && !lower.includes('group') && !lower.includes('user') && !lower.includes('owner') && !lower.includes('owned') && !lower.includes('u&') && !lower.includes('authorization')) return null
109  if (lower.includes('/*') || lower.includes('--')) lower = stripComments(lower)
110  for (const raw of lower.split(';')) {
111    const m = findMembershipStatement(raw) ?? findOwnerOrRoleAttr(raw)
112    if (m) return m
113    if (!raw.includes('grant')) continue
114    if (!raw.includes('anon') && !raw.includes('public') && !raw.includes('authenticated')) continue
115    const norm = raw.replace(/\s+/g, ' ')
116    const g = norm.indexOf('grant ')
117    if (g < 0) continue
118    const stmt = norm.slice(g)
119    const to = stmt.lastIndexOf(' to ')
120    if (to < 0) continue
121    // Quotes, backticks and backslashes are dropped so a shell-quoted statement (psql -c "... to anon") and "anon" both match.
122    const roles = stmt.slice(to + 4).split(/[\s,"'`\\()]+/).filter(Boolean)
123    const on = stmt.indexOf(' on ')
124    if (on < 0 || on > to) {
125      // Role membership: GRANT <role>[, ...] TO anon/authenticated gives them every privilege that role has.
126      if (roles.some((r) => MEMBER_TARGETS.has(r))) return stmt.trim().slice(0, 200)
127      continue
128    }
129    const privs = stmt.slice(6, on).split(/[\s,()]+/).filter(Boolean)
130    if (roles.some((r) => ANON_LIKE.has(r)) && privs.some((p) => WRITE_PRIVS.includes(p))) return stmt.trim().slice(0, 200)
131    if (roles.some((r) => CREATE_TARGETS.has(r)) && privs.includes('create')) return stmt.trim().slice(0, 200)
132  }
133  return null
134}
135
136function textOf(e) {
137  const parts = []
138  for (const k of Object.keys(e ?? {})) {
139    if (RESERVED.has(k)) continue
140    const v = e[k]
141    parts.push(typeof v === 'string' ? v : JSON.stringify(v ?? ''))
142  }
143  return parts.join('\n')
144}
145
146export function decide(e) {
147  const hit = findAnonWriteGrant(textOf(e))
148  if (!hit) return null
149  return 'Blocked: this SQL grants the anon role (directly, through PUBLIC, or through role membership) more than select (' + hit + '). Signed-out visitors rarely need writes, and with RLS off this opens the table to anyone with the anon key. Safe order: enable RLS, add policies, then grant the minimum (usually select for anon, the needed commands for authenticated).'
150}
151
152export function register(on) {
153  on('session.start', async ($, e, next) => {
154    await $.command.register({ name: 'grants-check', description: 'How to run the Supabase grants checker and skill' })
155    return next(e)
156  })
157
158  on('tool.call', async ($, e, next) => {
159    const reason = decide(e)
160    if (reason) return { deny: reason }
161    return next(e)
162  })
163
164  on('command.run', { command: 'grants-check' }, async () => ({
165    text: [
166      'Supabase grants check (free, MIT):',
167      '1. Migrations: ask "check my Supabase grants" to run the supabase-grants-check skill (G1-G8).',
168      "2. Live database: open this plugin's checker/supabase-grants-check.sql, paste it into the Supabase SQL Editor and run it. It only reads catalog views and makes no changes.",
169      'Safe fix order: enable RLS, add policies, then grant the minimum. Never grant all to anon.',
170      'A clean result is not proof the app is secure.',
171    ].join('\n'),
172  }))
173}
174