| |
| |
| import assert from 'node:assert/strict' |
| import { readFile } from 'node:fs/promises' |
|
|
| const { PGlite } = await import(process.env.MESSAGE_PGLITE_MODULE || '@electric-sql/pglite') |
| const db = new PGlite() |
| const admin = '11111111-1111-4111-8111-111111111111' |
| const outsider = '22222222-2222-4222-8222-222222222222' |
| const adminSession = '33333333-3333-4333-8333-333333333333' |
| const outsiderSession = '44444444-4444-4444-8444-444444444444' |
| const keyId = '55555555-5555-4555-8555-555555555555' |
| const keyHash = 'a'.repeat(64) |
| let passed = 0 |
|
|
| async function check(name, fn) { |
| await fn() |
| passed += 1 |
| console.log(`通过：${name}`) |
| } |
| async function scalar(sql, params = []) { |
| const result = await db.query(sql, params) |
| return Object.values(result.rows[0])[0] |
| } |
| async function login(user = admin, session = adminSession, method = 'password') { |
| await db.exec('reset role; set role authenticated') |
| await db.query('select set_config($1,$2,false)', ['request.jwt.claims', JSON.stringify({ |
| sub: user, session_id: session, role: 'authenticated', is_anonymous: false, amr: [{ method }], |
| })]) |
| } |
| async function anonymous() { |
| await db.exec('reset role; set role anon') |
| await db.query('select set_config($1,$2,false)', ['request.jwt.claims', '{}']) |
| } |
| async function denied(sql, params = []) { |
| await assert.rejects(() => db.query(sql, params), error => error.code === '42501') |
| } |
|
|
| try { |
| |
| await db.exec(` |
| create role anon; create role authenticated; create role service_role; |
| create schema auth; |
| create table auth.users(id uuid primary key, is_anonymous boolean default false, banned_until timestamptz); |
| create table auth.sessions(id uuid primary key, user_id uuid, not_after timestamptz, created_at timestamptz default now()); |
| create function auth.jwt() returns jsonb language sql stable as |
| 'select coalesce(nullif(current_setting(''request.jwt.claims'',true),''''),''{}'')::jsonb'; |
| create function auth.uid() returns uuid language sql stable as 'select (auth.jwt()->>''sub'')::uuid'; |
| `) |
| await db.exec(await readFile(new URL('../../supabase/migrations/20261001030957_message_auth_keys.sql', import.meta.url), 'utf8')) |
| await db.query('insert into auth.users(id) values ($1),($2)', [admin, outsider]) |
| await db.query('insert into auth.sessions(id,user_id) values ($1,$2),($3,$4)', [adminSession, admin, outsiderSession, outsider]) |
| await db.query('insert into public.message_admins(user_id) values ($1)', [admin]) |
|
|
| await check('所有Message表启用RLS', async () => { |
| const rows = (await db.query("select relrowsecurity from pg_class where relname in ('message_admins','message_api_keys','message_auth_audit')")).rows |
| assert.equal(rows.length, 3) |
| assert.ok(rows.every(row => row.relrowsecurity)) |
| }) |
| await check('public RPC无SECURITY DEFINER', async () => { |
| assert.equal(await scalar("select count(*)::integer from pg_proc p join pg_namespace n on p.pronamespace=n.oid where n.nspname='public' and p.proname like 'message_%' and p.prosecdef"), 0) |
| }) |
| await anonymous() |
| await check('匿名不能读取哈希或授予管理员', async () => { |
| await denied('select key_hash from public.message_api_keys') |
| await denied('insert into public.message_admins(user_id) values ($1)', [outsider]) |
| await denied('select public.message_current_admin()') |
| }) |
| await check('匿名校验错误哈希只返回null', async () => { |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', 'invalid']), null) |
| }) |
| await login(outsider, outsiderSession) |
| await check('普通注册用户无管理员身份与管理能力', async () => { |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| await denied('select public.message_list_api_keys($1)', ['alpha']) |
| await denied('insert into public.message_admins(user_id) values ($1)', [outsider]) |
| }) |
| await login(admin, outsiderSession) |
| await check('其他用户的session不能绑定管理员', async () => { |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| }) |
| await login() |
| await check('显式管理员与有效session通过身份检查', async () => { |
| assert.equal(await scalar('select public.message_current_admin()'), true) |
| assert.equal(await scalar('select public.message_recovery_admin()'), false) |
| }) |
| await check('管理员也不能直接读取key_hash或修改membership', async () => { |
| await denied('select key_hash from public.message_api_keys') |
| await denied('delete from public.message_admins') |
| }) |
| await check('密钥创建输出不含哈希且有效期受限', async () => { |
| const create = 'select public.message_create_api_key($1,$2,$3,$4,$5,$6)' |
| const params = [keyId, 'alpha', 'fixture', 'msg_key.55555555', keyHash, 30] |
| const created = await scalar(create, params) |
| assert.equal(created.id, keyId) |
| assert.equal(created.expires_at - created.created_at, 30 * 86400) |
| assert.equal(Object.hasOwn(created, 'key_hash'), false) |
| assert.equal(Object.hasOwn(created, 'key'), false) |
| for (const days of [0, 366, null]) { |
| await assert.rejects(() => scalar(create, [...params.slice(0, 5), days]), error => error.code === '22023') |
| } |
| }) |
| await check('列表只有当前profile且不泄露哈希', async () => { |
| const rows = await scalar('select public.message_list_api_keys($1)', ['alpha']) |
| assert.equal(rows.length, 1) |
| assert.equal(Object.hasOwn(rows[0], 'key_hash'), false) |
| assert.deepEqual(await scalar('select public.message_list_api_keys($1)', ['beta']), []) |
| }) |
| await anonymous() |
| await check('匿名密钥校验只能取得绑定id/profile', async () => { |
| assert.deepEqual(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', keyHash]), { id: keyId, profile_id: 'alpha' }) |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['beta', keyHash]), null) |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', 'b'.repeat(64)]), null) |
| await denied('select public.message_auth_audit()') |
| }) |
| await login(admin, adminSession, 'recovery') |
| await check('恢复会话可核对身份但无任何管理RPC权限', async () => { |
| assert.equal(await scalar('select public.message_recovery_admin()'), true) |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| await denied('select public.message_list_api_keys($1)', ['alpha']) |
| await denied('select public.message_revoke_api_key($1,$2)', ['alpha', keyId]) |
| await denied('select public.message_auth_audit()') |
| await denied('select public.message_record_auth_event($1)', ['logout']) |
| }) |
| await login() |
| await check('封禁管理员不能通过RPC或客户端key认证', async () => { |
| await db.exec('reset role') |
| await db.query("update auth.users set banned_until=now()+interval '1 day' where id=$1", [admin]) |
| await login() |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', keyHash]), null) |
| await db.exec('reset role') |
| await db.query('update auth.users set banned_until=null where id=$1', [admin]) |
| await login() |
| }) |
| await check('管理员绝对会话12小时、恢复会话10分钟上限', async () => { |
| await db.exec('reset role') |
| await db.query("update auth.sessions set created_at=now()-interval '13 hours' where id=$1", [adminSession]) |
| await login() |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| await db.exec('reset role') |
| await db.query("update auth.sessions set created_at=now()-interval '11 minutes' where id=$1", [adminSession]) |
| await login(admin, adminSession, 'recovery') |
| assert.equal(await scalar('select public.message_recovery_admin()'), false) |
| await login() |
| assert.equal(await scalar('select public.message_current_admin()'), true) |
| await db.exec('reset role') |
| await db.query('update auth.sessions set created_at=now() where id=$1', [adminSession]) |
| await login() |
| }) |
| await check('撤销不会跨profile且立刻阻止key验证', async () => { |
| assert.equal(await scalar('select public.message_revoke_api_key($1,$2)', ['beta', keyId]), false) |
| assert.equal(await scalar('select public.message_revoke_api_key($1,$2)', ['alpha', keyId]), true) |
| assert.equal(await scalar('select public.message_revoke_api_key($1,$2)', ['alpha', keyId]), false) |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', keyHash]), null) |
| }) |
| await check('审计仅有固定事件且重复撤销不重复写入', async () => { |
| await scalar('select public.message_record_auth_event($1)', ['login.succeeded']) |
| await scalar('select public.message_record_auth_event($1)', ['logout']) |
| const rows = await scalar('select public.message_auth_audit()') |
| assert.equal(rows.length, 4) |
| assert.deepEqual(rows.map(row => row.event).sort(), ['key.created', 'key.revoked', 'login.succeeded', 'logout']) |
| assert.ok(rows.every(row => !Object.hasOwn(row, 'key_hash') && !Object.hasOwn(row, 'key'))) |
| await assert.rejects(() => scalar('select public.message_record_auth_event($1)', ['untrusted-secret']), error => error.code === '22023') |
| }) |
| await db.exec('reset role') |
| await db.query("update public.message_api_keys set revoked_at=null,created_at=now()-interval '2 days',expires_at=now()-interval '1 day' where id=$1", [keyId]) |
| await anonymous() |
| await check('过期密钥拒绝认证', async () => { |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', keyHash]), null) |
| }) |
| await db.exec('reset role') |
| await db.query("update public.message_api_keys set expires_at=now()+interval '1 day' where id=$1", [keyId]) |
| await db.query('delete from public.message_admins where user_id=$1', [admin]) |
| await anonymous() |
| await check('取消管理员membership立即禁用其客户端keys', async () => { |
| assert.equal(await scalar('select public.message_validate_api_key($1,$2)', ['alpha', keyHash]), null) |
| }) |
| await db.exec('reset role') |
| await db.query('insert into public.message_admins(user_id) values($1)', [admin]) |
| await db.query("update auth.sessions set not_after=now()-interval '1 minute' where id=$1", [adminSession]) |
| await login() |
| await check('达到session截止时间拒绝管理', async () => { |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| await denied('select public.message_list_api_keys($1)', ['alpha']) |
| }) |
| await db.exec('reset role') |
| await db.query('delete from auth.sessions where id=$1', [adminSession]) |
| await login() |
| await check('已退出的session即使旧JWT尚未到期也拒绝管理', async () => { |
| assert.equal(await scalar('select public.message_current_admin()'), false) |
| await denied('select public.message_list_api_keys($1)', ['alpha']) |
| }) |
| console.log(`全部${passed}项Postgres安全验证通过`) |
| } finally { |
| await db.close() |
| } |
|
|
