xiaoshu-app/scripts/sql/account-security.sql

-- Run after registering the corresponding additive Parse _User fields.
-- Existing accounts deliberately default to no legacy fallback: their password history is unknown.
ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "legacyPasswordMigrationAllowed" boolean DEFAULT false;
ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "credentialRevision" integer DEFAULT 0;
ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "credentialExpectedRevision" integer;
ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "credentialMigrationAttempt" boolean DEFAULT false;
ALTER TABLE "_User" ADD COLUMN IF NOT EXISTS "passwordResetRequired" boolean DEFAULT false;
CREATE OR REPLACE FUNCTION xs_credential_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_OP='INSERT' THEN
  NEW."legacyPasswordMigrationAllowed":=false; NEW."credentialRevision":=0;
  NEW."credentialExpectedRevision":=NULL; NEW."credentialMigrationAttempt":=false;
  RETURN NEW;
 END IF;
 IF NEW."legacyPasswordMigrationAllowed" IS TRUE AND OLD."legacyPasswordMigrationAllowed" IS DISTINCT FROM true AND COALESCE(current_setting('xiaoshu.credential_bootstrap',true),'')<>'on' THEN RAISE EXCEPTION '禁止重新启用旧密码'; END IF;
 IF OLD."passwordResetRequired" IS TRUE AND NEW."passwordResetRequired" IS DISTINCT FROM true AND NEW."_hashed_password" IS NOT DISTINCT FROM OLD."_hashed_password" THEN RAISE EXCEPTION '须通过改密解除临时密码状态'; END IF;
 IF NEW."credentialRevision" IS DISTINCT FROM OLD."credentialRevision" THEN RAISE EXCEPTION '密码版本由系统维护'; END IF;
 IF NEW."_hashed_password" IS DISTINCT FROM OLD."_hashed_password" THEN
  IF NEW."credentialExpectedRevision" IS NOT NULL AND NEW."credentialExpectedRevision"<>COALESCE(OLD."credentialRevision",0) THEN RAISE EXCEPTION '密码已更新，请重新登录后重试'; END IF;
  IF NEW."credentialMigrationAttempt" IS TRUE AND (OLD."legacyPasswordMigrationAllowed" IS DISTINCT FROM true OR OLD."passwordResetRequired" IS TRUE) THEN RAISE EXCEPTION '旧密码迁移已关闭'; END IF;
  NEW."credentialRevision":=COALESCE(OLD."credentialRevision",0)+1;
  NEW."legacyPasswordMigrationAllowed":=false;
  DELETE FROM "_Session" WHERE "user"=OLD."objectId";
 END IF;
 NEW."credentialExpectedRevision":=NULL; NEW."credentialMigrationAttempt":=false;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS xs_credential_guard_trigger ON "_User";
CREATE TRIGGER xs_credential_guard_trigger BEFORE INSERT OR UPDATE ON "_User" FOR EACH ROW EXECUTE FUNCTION xs_credential_guard();
-- Only the server-side importer calls this immediately after creating a random-password account.
CREATE OR REPLACE FUNCTION xs_enable_initial_legacy_login(co text, uid text) RETURNS void LANGUAGE plpgsql AS $$
BEGIN
 PERFORM set_config('xiaoshu.credential_bootstrap','on',true);
 UPDATE "_User" SET "legacyPasswordMigrationAllowed"=true WHERE "objectId"=uid AND company=co AND COALESCE("credentialRevision",0)=0 AND "createdAt">now()-interval '2 minutes' AND NOT COALESCE("passwordResetRequired",false);
 IF NOT FOUND THEN RAISE EXCEPTION '账号不符合首次迁移条件'; END IF;
 PERFORM set_config('xiaoshu.credential_bootstrap','off',true);
END $$;