tests/app-onboarding-v3-postgres.test.ts
Rama main · réplica de lectura del repositorio original.
import { randomUUID } from 'node:crypto'
import { Pool } from 'pg'
import { afterAll, describe, expect, it, vi } from 'vitest'
import { persistAppOnboarding } from '../supabase/functions/_backend/public/app/put.ts'
import { lockAppOnboardingForWrite } from '../supabase/functions/_backend/utils/appOnboardingWriteLock.ts'
import { getDrizzleClient } from '../supabase/functions/_backend/utils/pg.ts'
import { executeSQL, getPostgresClient, POSTGRES_URL } from './test-utils.ts'
afterAll(async () => (await getPostgresClient()).end())
describe('v3 checklist SQL security and bounded lookups', () => {
it('commits caller-RLS settings and progress together, rolling back both if the progress merge fails', async () => {
const client = await (await getPostgresClient()).connect()
const orgId = randomUUID()
const appId = `com.onboarding.atomic.${randomUUID()}`
let restoreQuery: (() => void) | undefined
try {
const userId = (await client.query('SELECT id FROM public.users WHERE email = \'test@capgo.app\'')).rows[0].id
await client.query('INSERT INTO public.orgs(id, created_by, name, management_email) VALUES ($1, $2, $3, $4)', [orgId, userId, 'Onboarding atomic test', 'test@capgo.app'])
await client.query(`INSERT INTO public.org_users(org_id, user_id, rbac_role_name, is_invite) VALUES ($1, $2, public.rbac_role_org_super_admin(), false) ON CONFLICT DO NOTHING`, [orgId, userId])
await client.query(`INSERT INTO public.role_bindings(principal_type, principal_id, role_id, scope_type, org_id, granted_by, reason, is_direct) SELECT public.rbac_principal_user(), $1::uuid, r.id, public.rbac_scope_org(), $2::uuid, $1::uuid, 'Onboarding atomic test', true FROM public.roles r WHERE r.name = public.rbac_role_org_super_admin() AND r.scope_type = public.rbac_scope_org() ON CONFLICT DO NOTHING`, [userId, orgId])
await client.query('INSERT INTO public.apps(app_id, owner_org, name, icon_url, need_onboarding) VALUES ($1, $2, \'Before\', \'\', false)', [appId, orgId])
const before = (await client.query('SELECT name, onboarding FROM public.apps WHERE app_id = $1', [appId])).rows[0]
const originalRole = (await client.query('SELECT current_user AS role')).rows[0].role
const auth = { authType: 'jwt', userId, claims: { sub: userId, role: 'authenticated' } }
const context = { get: (key: string) => key === 'auth' ? auth : undefined, env: {} } as any
const apikey = { user_id: userId, key: null } as any
const patch = { steps: { add_code: { status: 'done' as const } } }
const originalQuery = client.query.bind(client)
let injected = false
const querySpy = vi.spyOn(client, 'query').mockImplementation(((...args: any[]) => {
const queryText = typeof args[0] === 'string' ? args[0] : args[0]?.text
if (queryText?.includes('merge_app_onboarding_setup')) {
injected = true
return Promise.reject(new Error('Injected progress merge failure'))
}
return (originalQuery as any)(...args)
}) as any)
restoreQuery = () => querySpy.mockRestore()
await expect(persistAppOnboarding(context, appId, patch, apikey, client, false, { name: 'After' })).rejects.toMatchObject({ cause: { message: 'Injected progress merge failure' } })
expect(injected).toBe(true)
restoreQuery()
expect((await client.query('SELECT name, onboarding FROM public.apps WHERE app_id = $1', [appId])).rows[0]).toEqual(before)
const result = await persistAppOnboarding(context, appId, patch, apikey, client, false, { name: 'After' })
expect(result?.app.name).toBe('After')
// New apps use the OTA v4 checklist, which nests steps under steps.ota.
expect((result?.app.onboarding as any).setup.steps.ota.add_code.status).toBe('done')
const committed = (await client.query('SELECT name, onboarding FROM public.apps WHERE app_id = $1', [appId])).rows[0]
expect(committed.name).toBe('After')
expect(committed.onboarding.setup.steps.ota.add_code.status).toBe('done')
expect((await client.query('SELECT current_user AS role')).rows[0].role).toBe(originalRole)
}
finally {
restoreQuery?.()
await client.query('ROLLBACK')
await client.query('DELETE FROM public.apps WHERE app_id = $1', [appId])
await client.query('DELETE FROM public.orgs WHERE id = $1', [orgId])
client.release()
}
})
it('serializes onboarding writes with RBAC revocation and reads permission after the revocation commits', async () => {
// The shared test pool deliberately has max=1; this concurrency check needs
// two independent connections without changing other tests' pool behavior.
const pool = new Pool({ connectionString: POSTGRES_URL, max: 2 })
const revoker = await pool.connect()
const writer = await pool.connect()
const orgId = randomUUID()
const appId = `com.onboarding.lock.${randomUUID()}`
let pendingWrite: Promise<boolean> | undefined
try {
// Reuse only read-only seed identities; every modified resource belongs
// to this test's new organization.
const ownerId = (await revoker.query('SELECT id FROM public.users WHERE email = \'test@capgo.app\'')).rows[0].id
const userId = (await revoker.query('SELECT id FROM public.users WHERE email = \'admin@capgo.app\'')).rows[0].id
await revoker.query('INSERT INTO public.orgs(id, created_by, name, management_email) VALUES ($1, $2, $3, $4)', [orgId, ownerId, 'Onboarding lock test', 'test@capgo.app'])
// Keep a separate active owner so revoking the second member respects the
// database's last-super-admin guard instead of bypassing it.
for (const memberId of [ownerId, userId]) {
await revoker.query(`INSERT INTO public.org_users(org_id, user_id, rbac_role_name, is_invite) VALUES ($1, $2, public.rbac_role_org_super_admin(), false) ON CONFLICT DO NOTHING`, [orgId, memberId])
await revoker.query(`INSERT INTO public.role_bindings(principal_type, principal_id, role_id, scope_type, org_id, granted_by, reason, is_direct) SELECT public.rbac_principal_user(), $1::uuid, r.id, public.rbac_scope_org(), $2::uuid, $3::uuid, 'Onboarding lock test', true FROM public.roles r WHERE r.name = public.rbac_role_org_super_admin() AND r.scope_type = public.rbac_scope_org() ON CONFLICT DO NOTHING`, [memberId, orgId, ownerId])
}
await revoker.query('INSERT INTO public.apps(app_id, owner_org, name, icon_url) VALUES ($1, $2, \'Onboarding lock test\', \'\')', [appId, orgId])
const permissionQuery = 'SELECT public.rbac_check_permission_direct(\'org.create_app\', $1::uuid, $2::uuid, NULL::text, NULL::bigint, NULL::text) AS allowed'
expect((await revoker.query(permissionQuery, [userId, orgId])).rows[0].allowed).toBe(true)
await revoker.query('BEGIN')
await revoker.query('SELECT public.lock_rbac_orgs($1::uuid)', [orgId])
await revoker.query('DELETE FROM public.role_bindings WHERE org_id = $1 AND principal_type = public.rbac_principal_user() AND principal_id = $2', [orgId, userId])
await writer.query('BEGIN')
const pid = (await writer.query('SELECT pg_catalog.pg_backend_pid() AS pid')).rows[0].pid
pendingWrite = (async () => {
expect(await lockAppOnboardingForWrite(getDrizzleClient(writer, { logger: false }), appId)).toMatchObject({ owner_org: orgId })
return (await writer.query(permissionQuery, [userId, orgId])).rows[0].allowed as boolean
})()
await expect.poll(async () => (await revoker.query('SELECT wait_event FROM pg_catalog.pg_stat_activity WHERE pid = $1', [pid])).rows[0]?.wait_event).toBe('advisory')
await revoker.query('COMMIT')
expect(await pendingWrite).toBe(false)
}
finally {
await revoker.query('ROLLBACK')
// Release a blocked writer before cleaning up, including failed assertions.
await pendingWrite?.catch(() => undefined)
await writer.query('ROLLBACK')
await revoker.query('DELETE FROM public.apps WHERE app_id = $1', [appId])
await revoker.query('DELETE FROM public.orgs WHERE id = $1', [orgId])
writer.release()
revoker.release()
await pool.end()
}
})
it('assigns direct authenticated inserts using the real caller and freezes the version on updates', async () => {
const client = await (await getPostgresClient()).connect()
const userId = randomUUID()
const orgId = randomUUID()
const appId = `com.onboarding.direct.${randomUUID()}`
try {
await client.query('BEGIN')
await client.query('INSERT INTO auth.users(id, email) VALUES ($1, $2)', [userId, `v3-direct-${userId}@example.com`])
await client.query(`INSERT INTO public.users(id, email, onboarding) VALUES ($1, $2, $3::jsonb)`, [userId, `v3-direct-${userId}@example.com`, JSON.stringify({ intent: 'builder', abtests: { ota_todo_list_v3: { branch: 'B' } } })])
await client.query('INSERT INTO public.orgs(id, created_by, name, management_email, onboarding) VALUES ($1, $2, $3, $4, $5::jsonb)', [orgId, userId, 'Direct v3 test', `v3-direct-${userId}@example.com`, JSON.stringify({ intent: 'ota' })])
await client.query(`INSERT INTO public.role_bindings(principal_type, principal_id, role_id, scope_type, org_id, granted_by, reason, is_direct) SELECT public.rbac_principal_user(), $1::uuid, r.id, public.rbac_scope_org(), $2::uuid, $1::uuid, 'Direct v3 test', true FROM public.roles r WHERE r.name = public.rbac_role_org_super_admin() AND r.scope_type = public.rbac_scope_org() ON CONFLICT DO NOTHING`, [userId, orgId])
await client.query(`SELECT pg_catalog.set_config('request.jwt.claim.sub', $1, true), pg_catalog.set_config('request.jwt.claims', $2, true)`, [userId, JSON.stringify({ sub: userId, role: 'authenticated' })])
await client.query('SET LOCAL ROLE authenticated')
await client.query(`INSERT INTO public.apps(app_id, owner_org, name, icon_url, onboarding) VALUES ($1, $2, 'Direct v3 test', '', $3::jsonb)`, [appId, orgId, JSON.stringify({ created_by_user_id: randomUUID(), setup: { todo_list_version: 1 } })])
const inserted = await client.query('SELECT onboarding FROM public.apps WHERE app_id = $1', [appId])
expect(inserted.rows[0].onboarding).toMatchObject({ created_by_user_id: userId, setup: { todo_list_version: 4, ota_todo_list_version: '1', paths: ['ota'], steps: { ota: { add_channel: { status: 'pending' } } } } })
const updated = await client.query(`UPDATE public.apps SET onboarding = '{"setup":{"todo_list_version":1}}'::jsonb WHERE app_id = $1 RETURNING onboarding`, [appId])
expect(updated.rows[0].onboarding.setup.todo_list_version).toBe(4)
}
finally {
await client.query('ROLLBACK')
client.release()
}
})
it.concurrent('keeps the merger and assignment trigger internal with empty search paths', async () => {
const rows = await executeSQL(`
SELECT p.proname, p.prosecdef, p.proconfig,
pg_catalog.pg_get_userbyid(p.proowner) AS owner,
pg_catalog.has_function_privilege('anon', p.oid, 'EXECUTE') AS anon,
pg_catalog.has_function_privilege('authenticated', p.oid, 'EXECUTE') AS authenticated,
pg_catalog.has_function_privilege('service_role', p.oid, 'EXECUTE') AS service_role
FROM pg_catalog.pg_proc AS p
JOIN pg_catalog.pg_namespace AS n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
AND p.proname IN ('merge_app_onboarding_setup', 'assign_app_onboarding_todo_list_version')
`)
expect(rows).toHaveLength(2)
for (const row of rows) {
expect(row).toMatchObject({ owner: 'postgres', anon: false, authenticated: false, service_role: true })
expect(row.proconfig).toContain('search_path=""')
}
expect(rows.find(row => row.proname === 'assign_app_onboarding_todo_list_version').prosecdef).toBe(true)
})
it.concurrent('uses a creator/version partial index for the CLI/MCP login lookup', async () => {
const index = await executeSQL(`SELECT indexdef FROM pg_catalog.pg_indexes WHERE schemaname = 'public' AND indexname = 'idx_apps_onboarding_login_creator'`)
expect(index).toHaveLength(1)
expect(index[0].indexdef).toContain('created_by_user_id')
expect(index[0].indexdef).toContain('\'3\'::text')
expect(index[0].indexdef).toContain('\'4\'::text')
})
})