Blame for tests/native_api/test_auth_schema.mjs

// 在本地嵌入Postgres中验证真实SQL权限；不连接外部项目或创建实际用户。
// MESSAGE_PGLITE_MODULE可指向工作区外临时安装的@electric-sql/pglite，避免新增生产依赖。
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 {
  // 仅模拟Supabase系统表的必要结构，应用的表、函数、RLS与grant来自实际迁移。
  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()
}