/** * Scribase's migration lint BLOCKs any new `public` table that has neither * RLS enabled nor a policy (verified live against a real environment while * building this tool — see README "Why RLS is generated"). A raw * `pg_dump --schema-only` of a Supabase/Neon app has no RLS at all (Neon * has no RLS concept; many Supabase apps relied on the service key and * never wrote policies), so a one-command migration either stalls on that * lint for every table, or — far worse — some tool "fixes" it by writing a * blanket `using (true)` policy that exposes every row to every signed-in * user. This module infers a safe, specific policy instead: * * 1. A column that is itself a foreign key to `auth.users(id)` -> that row * belongs to that user: `using ( = auth.uid())`. * 2. A column that is a foreign key to another public table which itself * has a case-1 owner column (one hop) -> an `exists` policy through that * table: the common `notes.profile_id -> profiles.id -> auth.users` * shape. * 3. No owner column found by either rule -> RLS enabled, zero grants * (`using (false)`): the service-role key the migration itself used * still reads/writes the table (service role bypasses RLS entirely); * no anon/authenticated client can. Logged clearly so the operator * knows to replace it with a real policy before shipping that table to * an app. * * This is a deliberately conservative heuristic, not a schema-design tool: * it never widens access past what rule 1/2 can prove, and it says exactly * which tables it could not infer for. */ /** * @param {import('pg').Client} pgClient connected to the SOURCE database * @param {string} schema * @returns {Promise<{ tables: Map }>} */ export async function introspectSchema(pgClient, schema = 'public') { const columnsResult = await pgClient.query( `select table_name, column_name from information_schema.columns where table_schema = $1 order by table_name, ordinal_position`, [schema], ); const fkResult = await pgClient.query( `select tc.table_name, kcu.column_name, ccu.table_schema as ref_schema, ccu.table_name as ref_table, ccu.column_name as ref_column from information_schema.table_constraints tc join information_schema.key_column_usage kcu on tc.constraint_name = kcu.constraint_name and tc.table_schema = kcu.table_schema join information_schema.constraint_column_usage ccu on tc.constraint_name = ccu.constraint_name where tc.constraint_type = 'FOREIGN KEY' and tc.table_schema = $1`, [schema], ); const tables = new Map(); for (const row of columnsResult.rows) { const table = tables.get(row.table_name) ?? { columns: [], fks: [] }; table.columns.push(row.column_name); tables.set(row.table_name, table); } for (const row of fkResult.rows) { const table = tables.get(row.table_name) ?? { columns: [], fks: [] }; table.fks.push({ column: row.column_name, refSchema: row.ref_schema, refTable: row.ref_table, refColumn: row.ref_column, }); tables.set(row.table_name, table); } return { tables }; } /** * @param {Map} tables * @returns {{ sql: string, decisions: {table: string, kind: 'direct'|'join'|'locked', detail: string}[] }} */ export function generateRlsStatements(tables) { const decisions = []; const statements = []; const directOwnerColumn = (fks) => fks.find((fk) => fk.refSchema === 'auth' && fk.refTable === 'users' && fk.refColumn === 'id'); for (const [name, table] of tables) { statements.push(`alter table public.${name} enable row level security;`); const direct = directOwnerColumn(table.fks); if (direct) { decisions.push({ table: name, kind: 'direct', detail: `${direct.column} = auth.uid()` }); statements.push( `create policy "${name}_owner" on public.${name} for all using (${direct.column} = auth.uid()) with check (${direct.column} = auth.uid());`, ); continue; } let joined = null; for (const fk of table.fks) { if (fk.refSchema !== 'public') continue; const target = tables.get(fk.refTable); if (!target) continue; const targetOwner = directOwnerColumn(target.fks); if (targetOwner) { joined = { via: fk, ownerColumn: targetOwner.column }; break; } } if (joined) { decisions.push({ table: name, kind: 'join', detail: `${joined.via.column} -> public.${joined.via.refTable}.${joined.via.refColumn} -> ${joined.ownerColumn} = auth.uid()`, }); statements.push( `create policy "${name}_owner_via_${joined.via.refTable}" on public.${name} for all using (\n` + ` exists (select 1 from public.${joined.via.refTable} t where t.${joined.via.refColumn} = public.${name}.${joined.via.column} and t.${joined.ownerColumn} = auth.uid())\n` + `) with check (\n` + ` exists (select 1 from public.${joined.via.refTable} t where t.${joined.via.refColumn} = public.${name}.${joined.via.column} and t.${joined.ownerColumn} = auth.uid())\n` + `);`, ); continue; } decisions.push({ table: name, kind: 'locked', detail: 'no owner column found by rule 1 or 2 — locked to the service role only', }); statements.push( `create policy "${name}_service_role_only" on public.${name} for all using (false) with check (false);`, ); } return { sql: statements.join('\n'), decisions }; }