/**
* 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 };
}