Composes Scribase's own shipped APIs (the `scribase` SDK's imports/migrations/ database/environments resources, plus the GoTrue admin API every self-hosted Supabase migration tool already uses) rather than reinventing a dump format: - supabase: wraps the real POST /v1/imports one-call migration (Postgres, auth.users with hashes, storage). - neon: pg_dump schema+data as plain SQL, submitted through POST .../migrations (environments.connection() is internal-network-only — verified live — so this is the only path that actually reaches a Scribase env from outside); auto-generates owner-based RLS so the real migration lint's BLOCK on RLS-less public tables is satisfied without ever opening a table to every user; branches land in branch_<name> schemas. - clerk: exports users via the real Backend API, creates them via the destination's GoTrue admin API with a one-time password + reset flag (Clerk never exposes a portable hash on any plan). - resend: exports sending domains + broadcasts (as templates) to a portable JSON bundle; honest about Scribase having no write endpoint for either yet. Verified live end-to-end against a real local Supabase-shaped Postgres (Docker, GoTrue-shaped auth.users with a real bcrypt hash) and a real Scribase project (ereboslab/scribase-migrate-verify-internal): schema+RLS applied with 0 lint blocks, data row counts matched via a real HTTP query (2/2 profiles, 3/3 notes), and a real password-grant login returned a real access_token for the migrated user. Full artifacts in test-fixtures/verification-run/. docs/migrate-page.md is a ready-to-paste scribase.com/migrate page.
137 lines
5.6 KiB
JavaScript
137 lines
5.6 KiB
JavaScript
/**
|
|
* 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 (<col> = 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<string, { columns: string[], fks: {column: string, refTable: string, refSchema: string, refColumn: string}[] }> }>}
|
|
*/
|
|
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<string, {columns: string[], fks: {column:string,refSchema:string,refTable:string,refColumn:string}[]}>} 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 };
|
|
}
|