48 lines
2.5 KiB
SQL
48 lines
2.5 KiB
SQL
-- W3-5 Unity WebGL 업로드 감사 로그. 멱등. db/apply-local-ddl.sh 로 비파괴 적용.
|
|
-- 업로드 성공/거부 추적(zip-slip/zip-bomb/포맷거부 패턴 가시성). games 메타는 games.webgl_path 활용 — 신규 메타테이블 없음.
|
|
|
|
CREATE SEQUENCE IF NOT EXISTS "game_upload_audit_log_id_seq";
|
|
|
|
CREATE TABLE IF NOT EXISTS "game_upload_audit_log" (
|
|
"id" bigint DEFAULT nextval('game_upload_audit_log_id_seq'::regclass) NOT NULL,
|
|
"actor_id" bigint NOT NULL, -- 업로드 수행 사용자 users.id
|
|
"game_uuid" character varying(36), -- 생성/교체된 UUID(거부 시 null 가능)
|
|
"outcome" character varying(20) NOT NULL, -- SUCCESS / REJECTED
|
|
"reject_reason" character varying(40), -- REJECTED 시 사유코드(아래 reason 카탈로그)
|
|
"original_name" character varying(255), -- 업로드 파일 원본명(sanitize 후 저장)
|
|
"upload_bytes" bigint, -- zip 원본 크기
|
|
"entry_count" integer, -- 추출 엔트리 수(성공 시)
|
|
"extracted_bytes" bigint, -- 누적 해제 크기(성공 시)
|
|
"created_at" timestamp with time zone DEFAULT now() NOT NULL,
|
|
PRIMARY KEY ("id")
|
|
);
|
|
ALTER SEQUENCE "game_upload_audit_log_id_seq" OWNED BY "game_upload_audit_log"."id";
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'game_upload_audit_log_actor_id_fkey') THEN
|
|
ALTER TABLE "game_upload_audit_log"
|
|
ADD CONSTRAINT "game_upload_audit_log_actor_id_fkey"
|
|
FOREIGN KEY ("actor_id") REFERENCES "users" ("id");
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
DO $$
|
|
BEGIN
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'game_upload_audit_log_outcome_check') THEN
|
|
ALTER TABLE "game_upload_audit_log"
|
|
ADD CONSTRAINT "game_upload_audit_log_outcome_check"
|
|
CHECK ("outcome" IN ('SUCCESS', 'REJECTED'));
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
CREATE INDEX IF NOT EXISTS "idx_game_upload_audit_actor"
|
|
ON "game_upload_audit_log" ("actor_id", "created_at" DESC);
|
|
CREATE INDEX IF NOT EXISTS "idx_game_upload_audit_outcome"
|
|
ON "game_upload_audit_log" ("outcome", "created_at" DESC);
|
|
|
|
COMMENT ON TABLE "game_upload_audit_log" IS 'WebGL 업로드 감사 로그(성공/거부 추적, W3-5)';
|
|
COMMENT ON COLUMN "game_upload_audit_log"."reject_reason" IS 'REJECTED 사유코드: NOT_ZIP/MAGIC_FAIL/TOO_LARGE/TOO_MANY_ENTRIES/ENTRY_TOO_LARGE/EXTRACTED_TOO_LARGE/ZIP_SLIP/SYMLINK/BAD_ENTRY_NAME/NO_INDEX/INCOMPLETE_BUILD';
|