Spaces:

Esketch
/

CarboAny

Sleeping

App Files Files Community

CarboAny / database /tests /ledger_integrity_test.sql

Esketch

Claude Fable 5

deploy: Campaign Control Plane + Security Hardening Release (Orphan Clean Build v3)

5c98911 4 months ago

Raw History Blame Contribute Delete

2.97 kB

/**
 * DB-INT-01: 비가역 원장 트리거 및 멱등성 제약 검증 스크립트
 *
 * 이 스크립트는 PostgreSQL 환경에서 실행되어 settlement_ledger 테이블의
 * 비가역성(Immutability)과 멱등성(Idempotency)이 물리적으로 보장되는지 확인합니다.
 *
 * 안전장치:
 * - 기본적으로 실행을 거부합니다. 같은 psql 세션에서
 *   SET app.carboany_test_mode = 'on';
 *   을 먼저 지정한 로컬/테스트 DB에서만 실행하십시오.
 * - 모든 변경은 마지막에 ROLLBACK 됩니다.
 */

BEGIN;

DO $$
BEGIN
    IF current_setting('app.carboany_test_mode', true) IS DISTINCT FROM 'on' THEN
        RAISE EXCEPTION
            'Refusing to run ledger integrity test without SET app.carboany_test_mode = on';
    END IF;
END $$;

-- 1. 테스트용 데이터 삽입
INSERT INTO public.settlement_ledger (
    transaction_id, campaign_id, sender_id, sender_name, receiver_id, receiver_name,
    category, asset, amount, idempotency_key, previous_hash, entry_hash, description
) VALUES (
    'test_txn_001', NULL, 'sponsor-A', 'Sponsor A', 'citizen-001', 'John Doe',
    'POINT_REWARD', 'POINT', 1000.0, 'test_idemp_key_001', NULL, 'test_hash_001', 'Test Entry'
);

-- 2. UPDATE 시도 (에러 발생 기대)
DO $$
BEGIN
    BEGIN
        UPDATE public.settlement_ledger SET amount = 2000.0 WHERE idempotency_key = 'test_idemp_key_001';
        RAISE EXCEPTION 'Update should have been blocked by trigger';
    EXCEPTION WHEN raise_exception THEN
        IF SQLERRM NOT LIKE 'settlement_ledger is append-only:%' THEN
            RAISE;
        END IF;
        RAISE NOTICE 'SUCCESS: Update blocked as expected: %', SQLERRM;
    END;
END $$;

-- 3. DELETE 시도 (에러 발생 기대)
DO $$
BEGIN
    BEGIN
        DELETE FROM public.settlement_ledger WHERE idempotency_key = 'test_idemp_key_001';
        RAISE EXCEPTION 'Delete should have been blocked by trigger';
    EXCEPTION WHEN raise_exception THEN
        IF SQLERRM NOT LIKE 'settlement_ledger is append-only:%' THEN
            RAISE;
        END IF;
        RAISE NOTICE 'SUCCESS: Delete blocked as expected: %', SQLERRM;
    END;
END $$;

-- 4. 동일 Idempotency Key 재삽입 시도 (Unique Violation 기대)
DO $$
BEGIN
    BEGIN
        INSERT INTO public.settlement_ledger (
            transaction_id, campaign_id, sender_id, sender_name, receiver_id, receiver_name,
            category, asset, amount, idempotency_key, previous_hash, entry_hash, description
        ) VALUES (
            'test_txn_002', NULL, 'sponsor-A', 'Sponsor A', 'citizen-001', 'John Doe',
            'POINT_REWARD', 'POINT', 1000.0, 'test_idemp_key_001', 'test_hash_001', 'test_hash_002', 'Duplicate Entry'
        );
        RAISE EXCEPTION 'Duplicate insert should have been blocked by unique constraint';
    EXCEPTION WHEN unique_violation THEN
        RAISE NOTICE 'SUCCESS: Duplicate insert blocked by unique constraint';
    END;
END $$;

ROLLBACK;

	/**
	* DB-INT-01: 비가역 원장 트리거 및 멱등성 제약 검증 스크립트
	*
	* 이 스크립트는 PostgreSQL 환경에서 실행되어 settlement_ledger 테이블의
	* 비가역성(Immutability)과 멱등성(Idempotency)이 물리적으로 보장되는지 확인합니다.
	*
	* 안전장치:
	* - 기본적으로 실행을 거부합니다. 같은 psql 세션에서
	* SET app.carboany_test_mode = 'on';
	* 을 먼저 지정한 로컬/테스트 DB에서만 실행하십시오.
	* - 모든 변경은 마지막에 ROLLBACK 됩니다.
	*/

	BEGIN;

	DO $$
	BEGIN
	IF current_setting('app.carboany_test_mode', true) IS DISTINCT FROM 'on' THEN
	RAISE EXCEPTION
	'Refusing to run ledger integrity test without SET app.carboany_test_mode = on';
	END IF;
	END $$;

	-- 1. 테스트용 데이터 삽입
	INSERT INTO public.settlement_ledger (
	transaction_id, campaign_id, sender_id, sender_name, receiver_id, receiver_name,
	category, asset, amount, idempotency_key, previous_hash, entry_hash, description
	) VALUES (
	'test_txn_001', NULL, 'sponsor-A', 'Sponsor A', 'citizen-001', 'John Doe',
	'POINT_REWARD', 'POINT', 1000.0, 'test_idemp_key_001', NULL, 'test_hash_001', 'Test Entry'
	);

	-- 2. UPDATE 시도 (에러 발생 기대)
	DO $$
	BEGIN
	BEGIN
	UPDATE public.settlement_ledger SET amount = 2000.0 WHERE idempotency_key = 'test_idemp_key_001';
	RAISE EXCEPTION 'Update should have been blocked by trigger';
	EXCEPTION WHEN raise_exception THEN
	IF SQLERRM NOT LIKE 'settlement_ledger is append-only:%' THEN
	RAISE;
	END IF;
	RAISE NOTICE 'SUCCESS: Update blocked as expected: %', SQLERRM;
	END;
	END $$;

	-- 3. DELETE 시도 (에러 발생 기대)
	DO $$
	BEGIN
	BEGIN
	DELETE FROM public.settlement_ledger WHERE idempotency_key = 'test_idemp_key_001';
	RAISE EXCEPTION 'Delete should have been blocked by trigger';
	EXCEPTION WHEN raise_exception THEN
	IF SQLERRM NOT LIKE 'settlement_ledger is append-only:%' THEN
	RAISE;
	END IF;
	RAISE NOTICE 'SUCCESS: Delete blocked as expected: %', SQLERRM;
	END;
	END $$;

	-- 4. 동일 Idempotency Key 재삽입 시도 (Unique Violation 기대)
	DO $$
	BEGIN
	BEGIN
	INSERT INTO public.settlement_ledger (
	transaction_id, campaign_id, sender_id, sender_name, receiver_id, receiver_name,
	category, asset, amount, idempotency_key, previous_hash, entry_hash, description
	) VALUES (
	'test_txn_002', NULL, 'sponsor-A', 'Sponsor A', 'citizen-001', 'John Doe',
	'POINT_REWARD', 'POINT', 1000.0, 'test_idemp_key_001', 'test_hash_001', 'test_hash_002', 'Duplicate Entry'
	);
	RAISE EXCEPTION 'Duplicate insert should have been blocked by unique constraint';
	EXCEPTION WHEN unique_violation THEN
	RAISE NOTICE 'SUCCESS: Duplicate insert blocked by unique constraint';
	END;
	END $$;

	ROLLBACK;