MineGeo/scripts/test-account-controls-sql.mjs

import assert from "node:assert/strict";
import { readFile, readdir } from "node:fs/promises";
import { PGlite } from "../.data/sql-verification/node_modules/@electric-sql/pglite/dist/index.js";

const db = new PGlite();
const id = (n) => `00000000-0000-4000-8000-${String(n).padStart(12,"0")}`;
const [admin, moderator, owner, other, anotherAdmin] = [1,2,3,4,5].map(id);
const hash = "a".repeat(64);
const reason = "Verified account restriction reason.";
const value = async (sql,args=[]) => (await db.query(sql,args)).rows[0]?.value;
const action = (user,op,reviewer=admin) => value("select public.admin_account_action($1,$2,$3,$4) value",[reviewer,user,op,reason]);
const appeal = (user=owner) => value("select public.submit_account_appeal($1,$2) value",[user,"Please independently reconsider my account ban."]);
const review = (a,decision,reviewer=moderator) => db.query("select public.review_account_appeal($1,$2,$3,$4)",[reviewer,a,decision,"Independent review with detailed feedback."]);
const profile = (user=owner) => value("select to_jsonb(p) value from public.profiles p where id=$1",[user]);
try {
  await db.exec(`create role anon; create role authenticated; create role service_role bypassrls; create role supabase_auth_admin bypassrls;
    create schema auth; create table auth.users(id uuid primary key);
    create function auth.uid() returns uuid language sql as $$select null::uuid$$;
    create schema storage; create table storage.buckets(id text primary key,name text,public boolean,file_size_limit bigint,allowed_mime_types text[]); create table storage.objects(id uuid primary key default gen_random_uuid(),bucket_id text,name text); create function storage.foldername(name text) returns text[] language sql as $$ select string_to_array(name, '/') $$;`);
  for(const file of (await readdir("supabase/migrations")).filter(f=>f.endsWith(".sql")).sort())
    await db.exec(await readFile(`supabase/migrations/${file}`,"utf8"));
  await db.exec("grant select,insert,update,delete on all tables in schema public to service_role;");
  for(const user of [admin,moderator,owner,other,anotherAdmin]) await db.query("insert into auth.users values($1)",[user]);
  await db.query("insert into public.profiles(id,role) values($1,'admin'),($2,'moderator'),($3,'contributor'),($4,'contributor'),($5,'admin')",[admin,moderator,owner,other,anotherAdmin]);
  assert.equal((await profile()).account_status,"active");

  // Neither anonymous callers nor logged-in browser roles get server-only powers.
  for(const role of ["anon","authenticated"]) {
    await db.exec(`set role ${role}`);
    if(role==="anon") await assert.rejects(db.query("select * from public.account_appeals"),/permission denied/);
    else assert.equal((await db.query("select * from public.account_appeals")).rows.length,0);
    for(const sql of ["select * from public.account_control_events",
      `select public.admin_account_action('${admin}','${owner}','ban','${reason}')`,
      `select public.submit_account_appeal('${owner}','${reason}')`,
      `select public.review_account_appeal('${admin}','${id(99)}','approve','${reason}')`])
      await assert.rejects(db.query(sql),/permission denied/);
    await db.exec("reset role");
  }
  // Simulate a future browser profile edit policy: protected status fields stay server-only.
  await db.exec("grant select,update on public.profiles to authenticated; create policy fixture_profiles_edit on public.profiles for all to authenticated using(true) with check(true); set role authenticated;");
  await assert.rejects(db.query("update public.profiles set account_status='banned' where id=$1",[owner]),/managed by Admins/);
  await assert.rejects(db.query("update public.profiles set account_status_version=10 where id=$1",[owner]),/managed by Admins/);
  await db.exec("reset role; drop policy fixture_profiles_edit on public.profiles;");
  await db.exec("set role service_role");
  await assert.rejects(action(owner,"ban",moderator),/Active Admin/);
  await assert.rejects(action(owner,"ban",other),/Active Admin/);
  await assert.rejects(action(admin,"ban"),/own account/);
  for(const op of ["ban","deactivate","restore","prepare_delete"]) await assert.rejects(action(anotherAdmin,op),/protected/);
  await assert.rejects(action(id(99),"ban"),/Account not found/);
  await assert.rejects(action(owner,"unknown"),/account action/);
  await assert.rejects(appeal(),/Only banned/);

  // A published map and moderation history survive the Auth user's deletion.
  const map=id(20),revision=id(21),room=id(22),player=id(23),game=id(24);
  await db.query("insert into public.datasets(id,owner_id,name,source,state,bounds) values($1,$2,'Retained map','fabric','published','{}')",[map,owner]);
  await db.query("insert into public.revisions(id,dataset_id,manifest,asset_root) values($1,$2,'{}','fixture')",[revision,map]);
  await db.query("update public.datasets set revision_id=$1 where id=$2",[revision,map]);
  await db.query("insert into public.review_decisions(dataset_id,reviewer_id,decision) values($1,$2,'approve')",[map,owner]);
  await db.query("insert into public.reports(dataset_id,owner_id,reviewer_id,reason) values($1,$2,$2,'History')",[map,owner]);
  await db.query("insert into public.map_appeals(dataset_id,owner_id,reviewer_id,report_cycle,reason) values($1,$2,$2,0,'Historical map appeal')",[map,owner]);
  await db.query("insert into public.contributor_tokens(owner_id,token_hash,label) values($1,'uploadfixture','fixture')",[owner]);
  await db.query("insert into public.pairing_codes(device_hash,user_code,owner_id,expires_at,token_value) values('device','PAIR',$1,clock_timestamp()+interval '1 day','secret')",[owner]);
  const key=await value("select public.developer_key_create($1,'Test',array['maps:read'],$2,'mg_dev_fixture',1) value",[owner,hash]);
  await db.query("insert into public.game_sessions(id,revision_id,owner_id,access_hash,difficulty,difficulty_settings) values($1,$2,$3,'old-solo-secret','easy',public.resolve_difficulty_preset('easy'))",[game,revision,owner]);
  await db.query("insert into public.multiplayer_rooms(id,revision_id,host_player_id,difficulty,player_limit,difficulty_settings) values($1,$2,$3,'easy',4,public.resolve_difficulty_preset('easy'))",[room,revision,player]);
  await db.query("insert into public.multiplayer_players(id,room_id,access_hash,display_name,account_id) values($1,$2,'dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd','Fixture',$3)",[player,room,owner]);
  assert.deepEqual(await action(owner,"ban"),{id:owner,status:"banned",statusVersion:1});
  assert.equal((await profile()).account_reason,reason);
  assert.equal(await value("select revoked value from public.contributor_tokens where owner_id=$1",[owner]),true);
  assert.equal(await value("select count(*)::int value from public.pairing_codes where owner_id=$1",[owner]),0);
  assert.ok(await value("select revoked_at value from public.developer_api_keys where id=$1",[key.id]));
  assert.deepEqual(await value("select public.developer_key_consume($1) value",[hash]),{ok:false,error:"invalid_key"});
  await assert.rejects(value("select public.developer_key_create($1,'Denied',array['maps:read'],$2,'mg_dev_fixture2',1) value",[owner,"b".repeat(64)]),/Account required/);
  assert.notEqual(await value("select access_hash value from public.game_sessions where id=$1",[game]),"old-solo-secret");
  assert.equal(await value("select status value from public.multiplayer_rooms where id=$1",[room]),"closed");
  const departed=await value("select to_jsonb(p) value from public.multiplayer_players p where id=$1",[player]);
  assert.equal(departed.has_left,true); assert.notEqual(departed.access_hash,"dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd");
  await assert.rejects(action(owner,"ban"),/already has/);

  const first=await appeal();
  await assert.rejects(appeal(),/already been submitted/);
  await assert.rejects(review(first,"approve",other),/Active Moderator/);
  await review(first,"reject");
  assert.equal((await profile()).account_status,"banned");
  await assert.rejects(appeal(),/already been submitted/);
  await assert.rejects(review(first,"approve"),/no longer awaiting/);
  await action(owner,"restore");
  assert.equal((await profile()).account_status,"active");
  assert.equal((await profile()).account_reason,null);
  assert.deepEqual(await value("select public.developer_key_consume($1) value",[hash]),{ok:false,error:"invalid_key"},"Restore never resurrects revoked credentials");
  await action(owner,"deactivate");
  await assert.rejects(appeal(),/Only banned/);
  await action(owner,"ban");
  const stale=await appeal();
  await action(owner,"deactivate");
  assert.equal(await value("select state value from public.account_appeals where id=$1",[stale]),"rejected");
  await action(owner,"ban");
  await assert.rejects(review(stale,"approve"),/no longer awaiting/);
  const current=await appeal();
  // A banned moderator cannot review; fresh DB status wins over previous role.
  await action(moderator,"deactivate");
  await assert.rejects(review(current,"approve"),/Active Moderator/);
  await action(moderator,"restore");
  await review(current,"approve");
  assert.equal((await profile()).account_status,"active");
  assert.equal(await value("select state value from public.account_appeals where id=$1",[current]),"approved");

  // Defense in depth still checks current owner status if a credential was not revoked.
  await db.query("update public.profiles set account_status='banned' where id=$1",[other]);
  await db.query("insert into public.developer_api_keys(owner_id,name,prefix,token_hash,scopes,expires_at) values($1,'Old','mg_dev_old',$2,array['maps:read'],clock_timestamp()+interval '1 day')",[other,"c".repeat(64)]);
  assert.deepEqual(await value("select public.developer_key_consume($1) value",["c".repeat(64)]),{ok:false,error:"invalid_key"});
  await db.query("update public.profiles set account_status='active' where id=$1",[other]);
  await value("select public.configure_prometheus($1,$2) value",[admin,"e".repeat(64)]);
  await db.query("update public.profiles set account_status='deactivated' where id=$1",[admin]);
  await assert.rejects(action(other,"ban"),/Active Admin/);
  await assert.rejects(value("select public.prometheus_status($1) value",[admin]),/Admin permission/);
  assert.equal(await value("select public.read_prometheus_metrics($1) value",["e".repeat(64)]),null);
  await db.query("update public.profiles set account_status='active' where id=$1",[admin]);

  const pending=await action(owner,"prepare_delete");
  assert.equal(pending.status,"deleting");
  assert.deepEqual(await action(owner,"prepare_delete"),pending,"Failed Auth deletion can safely be retried");
  await assert.rejects(appeal(),/Only banned/);
  await db.exec("reset role");
  await db.query("delete from auth.users where id=$1",[owner]);
  assert.equal(await profile(),undefined);
  assert.equal(await value("select owner_id value from public.datasets where id=$1",[map]),null);
  assert.equal(await value("select state value from public.datasets where id=$1",[map]),"published");
  assert.equal(await value("select reviewer_id value from public.review_decisions where dataset_id=$1",[map]),null);
  const retained=await value("select to_jsonb(r) value from public.reports r where dataset_id=$1",[map]);
  assert.equal(retained.owner_id,null); assert.equal(retained.reviewer_id,null);
  const mapAppeal=await value("select to_jsonb(a) value from public.map_appeals a where dataset_id=$1",[map]);
  assert.equal(mapAppeal.owner_id,null); assert.equal(mapAppeal.reviewer_id,null);
  assert.equal(await value("select owner_id value from public.game_sessions where id=$1",[game]),null);
  assert.equal(await value("select account_id value from public.multiplayer_players where id=$1",[player]),null);
  assert.equal(await value("select count(*)::int value from public.account_appeals where owner_id=$1",[owner]),0);
  assert.equal(await value("select count(*)::int value from public.developer_api_keys where owner_id=$1",[owner]),0);
  assert.equal(await value("select count(*)::int value from public.contributor_tokens where owner_id=$1",[owner]),0);
  assert.ok(await value("select count(*)::int value from public.account_control_events where owner_id is null")>0);
  // Supabase Auth uses its own internal role. FK cleanup must not be blocked by
  // browser-only field protection when deleting a former decision reviewer.
  await db.exec("grant usage on schema auth to supabase_auth_admin; grant select,delete on auth.users to supabase_auth_admin; set role supabase_auth_admin;");
  await db.query("delete from auth.users where id=$1",[admin]);
  await db.exec("reset role");
  assert.equal((await profile(moderator)).account_changed_by,null);
  console.log("Account controls SQL checks passed: grants, protected fields, fresh role checks, protected Admins, credential/session invalidation, one appeal per ban, stale appeal rejection, Moderator review, deletion retry, retained maps/history and cascading private credentials.");
} finally { await db.close(); }