Skip to content
SlashClone
DiscoverUse in ClaudeSubmit a link
Sign in
Made by@shipcheck

Supabase rls audit

@shipcheck/supabase-rls-audit

Audit row level security in your Supabase migrations before you launch. Finds tables with RLS off, policies that let anyone write, policies that trust user_metadata, views that skip RLS, and risky SECURITY DEFINER functions, then drafts a fix migration for you to review. Reads .sql files only and never touches your database.

SkillDevOpsv1.0.0 (latest)Tested
Use in Claude Save Like Clone Comment
Activity
New: be the first to try it
  1. 1#!/usr/bin/env node
  2. 2// Audit Supabase row-level security from migration files.
  3. 3// Reads every .sql file in a folder (default supabase/migrations) in name order,
  4. 4// replays CREATE TABLE, ALTER TABLE ... ROW LEVEL SECURITY, CREATE/DROP POLICY,
  5. 5// CREATE VIEW and SECURITY DEFINER functions, and reports risky end states.
  6. 6// Node built-ins only. Never connects to a database.
  7. 7//
  8. 8// Usage:
  9. 9// node scripts/rls-audit.mjs [migrations-dir] [--json out/rls-audit.json]
  10. 10// [--md out/rls-audit.md] [--fail-on high|medium|low]
  11. 11
  12. 12import { existsSync, mkdirSync, readFileSync, readdirSync, statSync, writeFileSync } from "node:fs";
  13. 13import { dirname, join } from "node:path";
  14. 14
  15. 15const SEVERITY_ORDER = ["high", "medium", "low", "info"];
  16. 16const SKIP_SCHEMAS = new Set([
  17. 17 "auth",
  18. 18 "storage",
  19. 19 "extensions",
  20. 20 "realtime",
  21. 21 "supabase_functions",
  22. 22 "graphql",
  23. 23 "graphql_public",
  24. 24 "pgsodium",
  25. 25 "vault",
  26. 26 "net",
  27. 27 "cron",
  28. 28]);
  29. 29const CLIENT_ROLES = new Set(["public", "anon", "authenticated"]);
  30. 30
  31. 31function parseArgs(argv) {
  32. 32 const args = { dir: "supabase/migrations", json: null, md: null, failOn: null };
  33. 33 const rest = [];
  34. 34 for (let i = 0; i < argv.length; i++) {
  35. 35 const a = argv[i];
  36. 36 if (a === "--json") args.json = argv[++i];
  37. 37 else if (a === "--md") args.md = argv[++i];
  38. 38 else if (a === "--fail-on") args.failOn = argv[++i];
  39. 39 else if (a === "-h" || a === "--help") {
  40. 40 console.log(
  41. 41 "Usage: node scripts/rls-audit.mjs [migrations-dir] [--json file] [--md file] [--fail-on high|medium|low]",
  42. 42 );
  43. 43 process.exit(0);
  44. 44 } else rest.push(a);
  45. 45 }
  46. 46 if (rest[0]) args.dir = rest[0];
  47. 47 if (args.failOn && !SEVERITY_ORDER.includes(args.failOn)) {
  48. 48 console.error(`--fail-on must be one of high, medium, low (got "${args.failOn}")`);
  49. 49 process.exit(2);
  50. 50 }
  51. 51 return args;
  52. 52}
  53. 53
  54. 54/** Remove comments and split into statements, respecting quotes and $tag$ bodies. */
  55. 55export function splitStatements(sql) {
  56. 56 const out = [];
  57. 57 let cur = "";
  58. 58 let i = 0;
  59. 59 while (i < sql.length) {
  60. 60 const ch = sql[i];
  61. 61 const next = sql[i + 1];
  62. 62 if (ch === "-" && next === "-") {
  63. 63 while (i < sql.length && sql[i] !== "\n") i++;
  64. 64 continue;
  65. 65 }
  66. 66 if (ch === "/" && next === "*") {
  67. 67 const end = sql.indexOf("*/", i + 2);
  68. 68 i = end === -1 ? sql.length : end + 2;
  69. 69 cur += " ";
  70. 70 continue;
  71. 71 }
  72. 72 if (ch === "'") {
  73. 73 let j = i + 1;
  74. 74 while (j < sql.length) {
  75. 75 if (sql[j] === "'" && sql[j + 1] === "'") j += 2;
  76. 76 else if (sql[j] === "'") break;
  77. 77 else j++;
  78. 78 }
  79. 79 cur += sql.slice(i, j + 1);
  80. 80 i = j + 1;
  81. 81 continue;
  82. 82 }
  83. 83 if (ch === "$") {
  84. 84 const m = /^\$[A-Za-z_]*\$/.exec(sql.slice(i));
  85. 85 if (m) {
  86. 86 const tag = m[0];
  87. 87 const end = sql.indexOf(tag, i + tag.length);
  88. 88 const stop = end === -1 ? sql.length : end + tag.length;
  89. 89 cur += sql.slice(i, stop);
  90. 90 i = stop;
  91. 91 continue;
  92. 92 }
  93. 93 }
  94. 94 if (ch === ";") {
  95. 95 if (cur.trim()) out.push(cur.trim());
  96. 96 cur = "";
  97. 97 i++;
  98. 98 continue;
  99. 99 }
  100. 100 cur += ch;
  101. 101 i++;
  102. 102 }
  103. 103 if (cur.trim()) out.push(cur.trim());
  104. 104 return out;
  105. 105}
  106. 106
  107. 107const IDENT = String.raw`(?:"[^"]+"|[A-Za-z_][A-Za-z0-9_$]*)`;
  108. 108const QNAME = String.raw`(${IDENT}(?:\s*\.\s*${IDENT})?)`;
  109. 109
  110. 110function normName(raw) {
  111. 111 const parts = raw.split(".").map((p) => p.trim().replace(/^"|"$/g, ""));
  112. 112 const [schema, name] = parts.length === 2 ? parts : ["public", parts[0]];
  113. 113 return { schema: schema.toLowerCase(), name: name.toLowerCase(), key: `${schema.toLowerCase()}.${name.toLowerCase()}` };
  114. 114}
  115. 115
  116. 116/** Text inside the parenthesis that starts at or after `from`, balanced. */
  117. 117function balanced(text, from) {
  118. 118 const start = text.indexOf("(", from);
  119. 119 if (start === -1) return null;
  120. 120 let depth = 0;
  121. 121 for (let i = start; i < text.length; i++) {
  122. 122 if (text[i] === "(") depth++;
  123. 123 else if (text[i] === ")") {
  124. 124 depth--;
  125. 125 if (depth === 0) return text.slice(start + 1, i).trim();
  126. 126 }
  127. 127 }
  128. 128 return text.slice(start + 1).trim();
  129. 129}
  130. 130
  131. 131function clauseExpr(stmt, keyword) {
  132. 132 const re = new RegExp(String.raw`\b${keyword}\s*\(`, "i");
  133. 133 const m = re.exec(stmt);
  134. 134 return m ? balanced(stmt, m.index) : null;
  135. 135}
  136. 136
  137. 137function simplify(expr) {
  138. 138 if (expr === null) return null;
  139. 139 let e = expr.replace(/\s+/g, " ").trim().toLowerCase();
  140. 140 while (e.startsWith("(") && balanced(e, 0) === e.slice(1, -1).trim()) e = e.slice(1, -1).trim();
  141. 141 return e;
  142. 142}
  143. 143
  144. 144const ALWAYS_TRUE = new Set(["true", "1 = 1", "1=1", "'t'", "true = true"]);
  145. 145const isAlwaysTrue = (e) => e !== null && ALWAYS_TRUE.has(e);
  146. 146const isOnlySignedIn = (e) =>
  147. 147 e !== null &&
  148. 148 /^(?:auth\.role\(\) ?= ?'authenticated'|auth\.uid\(\) is not null|\(select auth\.uid\(\)\) is not null|auth\.jwt\(\) is not null)$/.test(
  149. 149 e,
  150. 150 );
  151. 151const readsUserMetadata = (e) => e !== null && /user_metadata/.test(e);
  152. 152
  153. 153export function audit(files) {
  154. 154 const tables = new Map();
  155. 155 const views = [];
  156. 156 const functions = [];
  157. 157 const policies = new Map(); // key: table|policy
  158. 158
  159. 159 const table = (key, file) => {
  160. 160 if (!tables.has(key)) tables.set(key, { key, rls: false, forced: false, file, rlsFile: null });
  161. 161 return tables.get(key);
  162. 162 };
  163. 163
  164. 164 for (const { name: file, sql } of files) {
  165. 165 for (const stmt of splitStatements(sql)) {
  166. 166 const s = stmt.replace(/\s+/g, " ");
  167. 167 let m;
  168. 168 if ((m = new RegExp(String.raw`^create (?:unlogged )?table (?:if not exists )?${QNAME}`, "i").exec(s))) {
  169. 169 const n = normName(m[1]);
  170. 170 if (!SKIP_SCHEMAS.has(n.schema)) table(n.key, file);
  171. 171 continue;
  172. 172 }
  173. 173 if ((m = new RegExp(String.raw`^drop table (?:if exists )?${QNAME}`, "i").exec(s))) {
  174. 174 const n = normName(m[1]);
  175. 175 tables.delete(n.key);
  176. 176 for (const k of [...policies.keys()]) if (k.startsWith(`${n.key}|`)) policies.delete(k);
  177. 177 continue;
  178. 178 }
  179. 179 if ((m = new RegExp(String.raw`^alter table (?:if exists )?(?:only )?${QNAME} rename to (${IDENT})`, "i").exec(s))) {
  180. 180 const from = normName(m[1]);
  181. 181 const to = normName(`${from.schema}.${m[2]}`);
  182. 182 const t = tables.get(from.key);
  183. 183 if (t) {
  184. 184 tables.delete(from.key);
  185. 185 t.key = to.key;
  186. 186 tables.set(to.key, t);
  187. 187 }
  188. 188 for (const [k, p] of [...policies.entries()]) {
  189. 189 if (k.startsWith(`${from.key}|`)) {
  190. 190 policies.delete(k);
  191. 191 p.table = to.key;
  192. 192 policies.set(`${to.key}|${p.name}`, p);
  193. 193 }
  194. 194 }
  195. 195 continue;
  196. 196 }
  197. 197 if ((m = new RegExp(String.raw`^alter table (?:if exists )?(?:only )?${QNAME} (enable|disable|force|no force) row level security`, "i").exec(s))) {
  198. 198 const n = normName(m[1]);
  199. 199 if (SKIP_SCHEMAS.has(n.schema)) continue;
  200. 200 const t = table(n.key, file);
  201. 201 const action = m[2].toLowerCase();
  202. 202 if (action === "enable") {
  203. 203 t.rls = true;
  204. 204 t.rlsFile = file;
  205. 205 } else if (action === "disable") {
  206. 206 t.rls = false;
  207. 207 t.rlsFile = file;
  208. 208 } else if (action === "force") t.forced = true;
  209. 209 else t.forced = false;
  210. 210 continue;
  211. 211 }
  212. 212 if ((m = new RegExp(String.raw`^create policy (${IDENT}) on ${QNAME}`, "i").exec(s))) {
  213. 213 const pname = m[1].replace(/^"|"$/g, "");
  214. 214 const n = normName(m[2]);
  215. 215 if (SKIP_SCHEMAS.has(n.schema)) continue;
  216. 216 const after = s.slice(m.index + m[0].length);
  217. 217 const cmd = (/\bfor (all|select|insert|update|delete)\b/i.exec(after)?.[1] ?? "all").toLowerCase();
  218. 218 const restrictive = /\bas restrictive\b/i.test(after);
  219. 219 const toMatch = /\bto ((?:[A-Za-z_"][\w"]*\s*,\s*)*[A-Za-z_"][\w"]*)/i.exec(after);
  220. 220 const roles = toMatch
  221. 221 ? toMatch[1].split(",").map((r) => r.trim().replace(/"/g, "").toLowerCase())
  222. 222 : ["public"];
  223. 223 policies.set(`${n.key}|${pname}`, {
  224. 224 table: n.key,
  225. 225 name: pname,
  226. 226 cmd,
  227. 227 roles,
  228. 228 restrictive,
  229. 229 using: simplify(clauseExpr(after, "using")),
  230. 230 check: simplify(clauseExpr(after, "with check")),
  231. 231 file,
  232. 232 });
  233. 233 continue;
  234. 234 }
  235. 235 if ((m = new RegExp(String.raw`^drop policy (?:if exists )?(${IDENT}) on ${QNAME}`, "i").exec(s))) {
  236. 236 const pname = m[1].replace(/^"|"$/g, "");
  237. 237 const n = normName(m[2]);
  238. 238 policies.delete(`${n.key}|${pname}`);
  239. 239 continue;
  240. 240 }
  241. 241 if ((m = new RegExp(String.raw`^create (?:or replace )?(?:materialized )?view (?:if not exists )?${QNAME}`, "i").exec(s))) {
  242. 242 const n = normName(m[1]);
  243. 243 if (SKIP_SCHEMAS.has(n.schema)) continue;
  244. 244 const invoker = /security_invoker\s*=\s*(?:true|on|1)/i.test(s);
  245. 245 const idx = views.findIndex((v) => v.key === n.key);
  246. 246 const v = { key: n.key, invoker, materialized: /materialized view/i.test(s), file };
  247. 247 if (idx === -1) views.push(v);
  248. 248 else views[idx] = v;
  249. 249 continue;
  250. 250 }
  251. 251 if ((m = new RegExp(String.raw`^create (?:or replace )?function ${QNAME}`, "i").exec(s))) {
  252. 252 const n = normName(m[1]);
  253. 253 if (SKIP_SCHEMAS.has(n.schema)) continue;
  254. 254 if (/\bsecurity definer\b/i.test(s)) {
  255. 255 const pinned = /\bset search_path\b/i.test(s);
  256. 256 const idx = functions.findIndex((f) => f.key === n.key);
  257. 257 const f = { key: n.key, pinned, file };
  258. 258 if (idx === -1) functions.push(f);
  259. 259 else functions[idx] = f;
  260. 260 }
  261. 261 continue;
  262. 262 }
  263. 263 }
  264. 264 }
  265. 265
  266. 266 const findings = [];
  267. 267 const add = (severity, rule, target, message, fix, file) =>
  268. 268 findings.push({ severity, rule, target, message, fix, file });
  269. 269
  270. 270 const byTable = new Map();
  271. 271 for (const p of policies.values()) {
  272. 272 if (!byTable.has(p.table)) byTable.set(p.table, []);
  273. 273 byTable.get(p.table).push(p);
  274. 274 }
  275. 275
  276. 276 for (const t of tables.values()) {
  277. 277 const ps = byTable.get(t.key) ?? [];
  278. 278 if (!t.rls) {
  279. 279 add(
  280. 280 "high",
  281. 281 "rls-disabled",
  282. 282 t.key,
  283. 283 "Row level security is off. Anyone with your public anon key can read and change every row through the API.",
  284. 284 `alter table ${t.key} enable row level security;`,
  285. 285 t.rlsFile ?? t.file,
  286. 286 );
  287. 287 } else if (ps.length === 0) {
  288. 288 add(
  289. 289 "info",
  290. 290 "rls-no-policies",
  291. 291 t.key,
  292. 292 "RLS is on with no policies, so only the service role can reach it. Fine for server-only tables; add policies if the app reads it from the browser.",
  293. 293 null,
  294. 294 t.rlsFile ?? t.file,
  295. 295 );
  296. 296 }
  297. 297 }
  298. 298
  299. 299 for (const p of policies.values()) {
  300. 300 const target = `${p.table} / ${p.name}`;
  301. 301 const clientRoles = p.roles.filter((r) => CLIENT_ROLES.has(r));
  302. 302 if (clientRoles.length === 0 || p.restrictive) continue;
  303. 303 const anyone = clientRoles.includes("public") || clientRoles.includes("anon");
  304. 304 const who = anyone ? "anyone, signed in or not," : "any signed-in user";
  305. 305 const writes = p.cmd !== "select";
  306. 306 const openUsing = isAlwaysTrue(p.using);
  307. 307 const openCheck = isAlwaysTrue(p.check);
  308. 308 if (p.cmd === "insert" && openCheck) {
  309. 309 add(
  310. 310 "medium",
  311. 311 "policy-open-insert",
  312. 312 target,
  313. 313 `FOR INSERT with WITH CHECK (true) lets ${who} insert any row, including rows that claim to belong to someone else. Fine for a public form; risky if the table has an owner or status column.`,
  314. 314 "If rows have an owner, use with check ((select auth.uid()) = user_id). Add a rate limit for public forms.",
  315. 315 p.file,
  316. 316 );
  317. 317 } else if (writes && (openUsing || openCheck)) {
  318. 318 add(
  319. 319 "high",
  320. 320 "policy-open-write",
  321. 321 target,
  322. 322 `FOR ${p.cmd.toUpperCase()} with ${openCheck ? "WITH CHECK (true)" : "USING (true)"} lets ${who} ${p.cmd === "all" ? "read and change" : p.cmd} every row.`,
  323. 323 "Scope it to the owner, for example using ((select auth.uid()) = user_id) with check ((select auth.uid()) = user_id).",
  324. 324 p.file,
  325. 325 );
  326. 326 } else if (writes && (isOnlySignedIn(p.using) || isOnlySignedIn(p.check))) {
  327. 327 add(
  328. 328 "high",
  329. 329 "policy-any-user-write",
  330. 330 target,
  331. 331 `FOR ${p.cmd.toUpperCase()} only checks that someone is signed in, so any user can ${p.cmd === "all" ? "read and change" : p.cmd} other users' rows.`,
  332. 332 "Compare the row's owner column with (select auth.uid()) instead.",
  333. 333 p.file,
  334. 334 );
  335. 335 } else if (!writes && openUsing && anyone) {
  336. 336 add(
  337. 337 "medium",
  338. 338 "policy-public-read",
  339. 339 target,
  340. 340 "USING (true) for anon or public: the whole table is readable by anyone on the internet. Fine for truly public data (blog posts, pricing), a leak for anything else.",
  341. 341 "If it is not meant to be public, restrict to authenticated and scope to the owner.",
  342. 342 p.file,
  343. 343 );
  344. 344 } else if (!writes && (openUsing || isOnlySignedIn(p.using))) {
  345. 345 add(
  346. 346 "low",
  347. 347 "policy-all-users-read",
  348. 348 target,
  349. 349 "Every signed-in user can read every row. Check that no row holds another user's private data.",
  350. 350 "Scope to the owner or to a team membership table if rows are private.",
  351. 351 p.file,
  352. 352 );
  353. 353 }
  354. 354 if (readsUserMetadata(p.using) || readsUserMetadata(p.check)) {
  355. 355 add(
  356. 356 "high",
  357. 357 "policy-user-metadata",
  358. 358 target,
  359. 359 "The policy trusts user_metadata, which users can edit themselves. Anyone can grant themselves this access.",
  360. 360 "Use app_metadata (set only by the server) or a roles table instead.",
  361. 361 p.file,
  362. 362 );
  363. 363 }
  364. 364 }
  365. 365
  366. 366 for (const v of views) {
  367. 367 if (v.invoker) continue;
  368. 368 add(
  369. 369 "medium",
  370. 370 v.materialized ? "materialized-view-no-rls" : "view-security-definer",
  371. 371 v.key,
  372. 372 v.materialized
  373. 373 ? "Materialized views ignore RLS. If it is in an exposed schema, anyone with the anon key can read it."
  374. 374 : "This view runs with its creator's rights, so it skips the RLS of the tables it reads.",
  375. 375 v.materialized
  376. 376 ? `revoke select on ${v.key} from anon, authenticated; (or move it to a private schema)`
  377. 377 : `alter view ${v.key} set (security_invoker = true);`,
  378. 378 v.file,
  379. 379 );
  380. 380 }
  381. 381
  382. 382 for (const f of functions) {
  383. 383 add(
  384. 384 f.pinned ? "low" : "medium",
  385. 385 "security-definer-function",
  386. 386 f.key,
  387. 387 f.pinned
  388. 388 ? "SECURITY DEFINER function: it skips RLS. Check it only touches rows the caller may touch."
  389. 389 : "SECURITY DEFINER function without a fixed search_path: it skips RLS and can be hijacked through the search path.",
  390. 390 f.pinned ? null : "Add set search_path = '' and schema-qualify every name in the body.",
  391. 391 f.file,
  392. 392 );
  393. 393 }
  394. 394
  395. 395 findings.sort(
  396. 396 (a, b) => SEVERITY_ORDER.indexOf(a.severity) - SEVERITY_ORDER.indexOf(b.severity) || a.target.localeCompare(b.target),
  397. 397 );
  398. 398 const counts = Object.fromEntries(SEVERITY_ORDER.map((s) => [s, findings.filter((f) => f.severity === s).length]));
  399. 399 const tableList = [...tables.values()]
  400. 400 .map((t) => ({ table: t.key, rls: t.rls, policies: (byTable.get(t.key) ?? []).length }))
  401. 401 .sort((a, b) => a.table.localeCompare(b.table));
  402. 402 return {
  403. 403 summary: {
  404. 404 files: files.length,
  405. 405 tables: tableList.length,
  406. 406 rls_enabled: tableList.filter((t) => t.rls).length,
  407. 407 ...counts,
  408. 408 },
  409. 409 tables: tableList,
  410. 410 findings,
  411. 411 };
  412. 412}
  413. 413
  414. 414function toMarkdown(report, dir) {
  415. 415 const s = report.summary;
  416. 416 const lines = [
  417. 417 `# Supabase RLS audit: ${dir}`,
  418. 418 "",
  419. 419 `${s.files} migration files, ${s.tables} tables, ${s.rls_enabled} with RLS on.`,
  420. 420 `High: ${s.high} | Medium: ${s.medium} | Low: ${s.low} | Info: ${s.info}`,
  421. 421 "",
  422. 422 "## Tables",
  423. 423 "",
  424. 424 "| Table | RLS | Policies |",
  425. 425 "| --- | --- | --- |",
  426. 426 ...report.tables.map((t) => `| ${t.table} | ${t.rls ? "on" : "**OFF**"} | ${t.policies} |`),
  427. 427 "",
  428. 428 ];
  429. 429 for (const sev of SEVERITY_ORDER) {
  430. 430 const list = report.findings.filter((f) => f.severity === sev);
  431. 431 if (list.length === 0) continue;
  432. 432 lines.push(`## ${sev[0].toUpperCase()}${sev.slice(1)}`, "");
  433. 433 for (const f of list) {
  434. 434 lines.push(`- **${f.target}** (${f.rule}, ${f.file}): ${f.message}`);
  435. 435 if (f.fix) lines.push(/^(?:alter|revoke|create|drop) /.test(f.fix) ? ` - Fix: \`${f.fix}\`` : ` - Fix: ${f.fix}`);
  436. 436 }
  437. 437 lines.push("");
  438. 438 }
  439. 439 if (report.findings.length === 0) lines.push("No findings. Nice.", "");
  440. 440 return lines.join("\n");
  441. 441}
  442. 442
  443. 443function write(path, text) {
  444. 444 mkdirSync(dirname(path), { recursive: true });
  445. 445 writeFileSync(path, text);
  446. 446}
  447. 447
  448. 448function main() {
  449. 449 const args = parseArgs(process.argv.slice(2));
  450. 450 if (!existsSync(args.dir) || !statSync(args.dir).isDirectory()) {
  451. 451 console.error(`No migrations folder at ${args.dir}. Pass the folder that holds your .sql migrations, for example supabase/migrations.`);
  452. 452 process.exit(2);
  453. 453 }
  454. 454 const names = readdirSync(args.dir).filter((f) => f.endsWith(".sql")).sort();
  455. 455 if (names.length === 0) {
  456. 456 console.error(`No .sql files in ${args.dir}.`);
  457. 457 process.exit(2);
  458. 458 }
  459. 459 const files = names.map((name) => ({ name, sql: readFileSync(join(args.dir, name), "utf8") }));
  460. 460 const report = audit(files);
  461. 461 const md = toMarkdown(report, args.dir);
  462. 462 if (args.json) write(args.json, JSON.stringify(report, null, 2) + "\n");
  463. 463 if (args.md) write(args.md, md);
  464. 464 if (!args.json && !args.md) console.log(md);
  465. 465 else {
  466. 466 const s = report.summary;
  467. 467 console.log(`${s.tables} tables, ${s.rls_enabled} with RLS. High ${s.high}, medium ${s.medium}, low ${s.low}, info ${s.info}.`);
  468. 468 }
  469. 469 if (args.failOn) {
  470. 470 const limit = SEVERITY_ORDER.indexOf(args.failOn);
  471. 471 if (report.findings.some((f) => SEVERITY_ORDER.indexOf(f.severity) <= limit)) process.exit(1);
  472. 472 }
  473. 473}
  474. 474
  475. 475main();
Sign in to comment on a line