DBMS 및 SQL 활용 #2 트랜젝션 격리수준, pgaudit, AI 시스템 운영 #

#2025-08-27

1. 트랜젝션 격리수준 #

트랜젝션

데이터베이스에서 하나의 작업 단위.
여러 개의 쿼리나 연산이 묶여 하나로 실행되는데 그 결과는 전부 성공하거나 아니면 전부 실패해서 원래 상태로 되돌아가야 한다.
- 그렇지 않으면 데이터가 꼬인다.

문제는?

여러 사람이 동시에 같은 데이터베이스를 건드린다.
그래서 데이터가 뒤섞이지 않도록 격리 수준이라는 규칙을 둬야한다.

데이터가 뒤섞인다?

은행 계좌에서 A 트랜잭션이 “잔액 100만 원에서 10만 원 빼기” 작업을 하고 있고 동시에 B 트랜잭션이 “잔액 100만 원에서 20만 원 빼기” 작업을 한다고 하면
- 각각 따로 실행하면 당연히 최종 잔액은 70만 원이 되어야 한다.
- 그런데 둘이 겹쳐서 실행되면 이런 일이 생길 수 있다.
  1. A가 잔액을 읽음 → 100만 원
  2. B도 잔액을 읽음 → 100만 원
  3. A는 100만 원에서 10만 원 빼서 90만 원을 저장
  4. B는 자기도 100만 원이라고 알고 있으니까 20만 원 빼서 80만 원을 저장
  5. 결과적으로 최종 잔액은 80만 원이 됨 근데 사실 두 번 다 반영되려면 70만 원이 되는 게 맞음.
결론
- 뒤섞인다 = 여러 트랜잭션이 동시에 실행되면서 서로의 중간 작업 결과가 충돌하거나 덮어씌워져서 최종 데이터가 잘못된 상태로 기록된다.

#

격리 수준(Isolation Levels)

“내 작업이 다른 사람 작업과 얼마나 떨어져 있나”를 정하는 규칙.
격리 수준이 낮으면 동시에 빨리 처리할 수 있지만 데이터가 꼬일 위험이 크고 격리 수준이 높으면 꼬임은 막을 수 있지만 속도가 느려진다.

Read Uncommitted

다른 사람이 아직 확정하지 않은 값도 읽을 수 있음
작업의 거리가 가까워서 발생할수있는 문제: A가 계좌 잔액을 100만 원에서 50만 원으로 바꾸려다가 아직 완료하지 않은 순간에 B가 그 값을 읽어버리면 B는 50만 원이라는 잘못된 값을 보고 계산을 시작할 수 있음(Dirty Read)

Read Committed

확정된 데이터만 읽을 수 있음
같은 데이터를 두번 조회했을때 값이 다를 수 있음. A가 잔액을 조회했을 때는 100만 원이었는데 그 사이 B가 그 값을 200만 원으로 바꾸고 확정해버리면 A가 다시 같은 잔액을 조회했을 때 값이 달라져 있다(Non-Repeatable Read)

의문점

‘같은 데이터를 두번 조회했을때 값이 다를 수 있음’이 왜 문제가 되는가? (당연한거 아닌가 변화가 확정된건데)
- 트랜잭션이라는 단위가 가져야 하는 “일관성 보장”이 깨짐.
  - 트랜잭션은 하나의 논리적 작업 단위인데 즉 그 안에서 여러 SQL 문이 실행될 때 그 문들은 같은 시점의 데이터 상태를 공유한다는 가정이 필요하다.
  - 예를 들어 트랜잭션 T1이 “잔액을 읽어서 100만 원 이상이면 10% 이자를 주는 UPDATE” 작업을 할때 T1이 먼저 SELECT 잔액을 해서 100만 원이라고 확인 -> 그 사이에 트랜잭션 T2가 잔액을 200만 원으로 바꾸고 커밋 -> 이제 T1이 다시 SELECT 잔액을 해서 계산하려 하면 200만 원이 보임 -> 같은 트랜잭션 안에서 읽은 값이 불일치하므로 T1의 로직은 잘못된 가정 위에서 실행될 수 있다.
이런 Non-Repeatable Read는 격리 수준을 더 올리면 막을 수 있다.

#

Repeatable Read

같은 데이터를 여러 번 읽어도 값이 변하지 않는다 즉 내가 한 번 확인한 계좌의 값은 트랜잭션이 끝날 때까지 변하지 않는다.
“고객 수가 몇 명인지” 같은 조건을 걸고 데이터를 읽는 트랜젝션을 수행할때 그 사이에 다른 사람이 새로운 고객을 추가할 경우, 나는 같은 조건으로 다시 조회했을 때 처음보다 고객 수가 늘어난 것을 보게 된다 예를 들어 처음엔 고객이 10명이었는데 다시 보니 11명으로 바뀌어 있다. 이미 본 고객들의 정보는 그대로지만, 집합 자체가 달라진다(Phantom Read).

의문점2

‘이미 본 고객들의 정보는 그대로지만, 집합 자체가 달라진다’가 왜 문제가 되는가? (트랜젝션 자체는 잘돌아갔어도 트랜젝션의 근본적인 목적인 ‘고객 전체 데이터에 대한 결과 내기’가 안돼서 문제인지?)

답2

트랜잭션의 목적(예: 고객 전체 데이터를 기준으로 무언가 계산하거나 판단하는 것)이 제대로 달성되지 못한게 문제다.
트랜잭션의 목적
- 단순히 SQL을 순서대로 실행하는 것이 아니라 “논리적으로 일관된 하나의 시점(state)을 기준으로 작업을 수행한다”는 걸 보장해서 전체 집합에 대한 일관된 결과를 내는 것이 목적.
예를 들어 트랜잭션의 목적이 “현재 전체 고객 수를 기준으로 통계를 계산하는 것”일때
- 내트랜잭션을 시작해서 SELECT * FROM customers WHERE condition… 으로 전체 집합을 조회했을 때는 10명이었고 -> 같은 트랜잭션 안에서 이 10명에 대해 뭔가 합계·평균·비율 등을 계산하는데 -> 그 사이에 다른 트랜잭션이 조건에 해당하는 새로운 고객을 INSERT하고 COMMIT해버리면 -> 내가 같은 조건으로 다시 SELECT 하면 이제는 11명이 나와서 -> 내 트랜잭션 안의 앞부분과 뒷부분이 “서로 다른 현실”을 보게 됨
“고객 전체를 대상으로 한 통계”라는 내 작업의 논리적 일관성을 깨뜨린다.
요약
- 트랜잭션의 목적이 단순히 한 행을 읽거나 수정하는 게 아니라, “조건에 맞는 전체 집합을 기준으로 어떤 결과를 계산하거나 보장하는 것”이라면
- 격리 수준이 낮으면 트랜잭션 안에서 집합 자체가 변해서 논리적으로 앞뒤가 안 맞는 결과를 낼 수 있고,
- 그렇기 때문에 SQL 표준은 이런 현상을 “문제”라고 규정하고, 격리 수준을 통해 제어할 수 있도록 만든 것입니다.

의문점3

Repeatable Read랑 Unrepeatable Read 차이?

답3

Non-Repeatable Read (문제 현상)
- 트랜잭션 안에서 동일한 조건으로 같은 “특정 행”을 두 번 읽었는데 값이 달라진 경우
- 고객 ID=5번을 첫 번째 조회에서는 나이=30살로 읽었는데 다른 트랜잭션이 그 고객의 나이를 40살로 바꾸면 내가 다시 ID=5번을 읽으면 40살로 보인다.
  - 같은 행의 값이 바뀌어 반복 불가능한 읽기가 되었다.
Repeatable Read (격리 수준)
- Non-Repeatable Read라는 현상을 막기위한 ‘이미 읽은 행의 값은 트랜잭션 종료까지 고정’이라는 방식.
- 고객 ID=5번을 첫 번째 조회에서는 나이=30살로 읽었는데 다른 트랜잭션이 그 고객의 나이를 40살로 바꾸고 커밋하더라도 내가 같은 트랜잭션 안에서 다시 ID=5번을 조회했을 때 여전히 30살로 보인다.
- 이미 읽은 행의 값은 트랜잭션이 끝날 때까지 변하지 않는다.
Phantom Read (문제 현상)
- 트랜잭션 안에서 동일한 조건으로 “같은 집합"을 두 번 읽었을 때 새로운 행이 끼어들어 결과 집합이 달라지는 경우(기존 행의 값은 변하지 않음)
- 나이 ≥ 30살 조건으로 고객 집합을 조회했을 때 10명이었다. 다른 트랜잭션이 나이=35살인 고객(ID=11번)을 새로 INSERT하고 커밋하면 내가 같은 조건으로 다시 조회했을 때 11명으로 보인다.
- 기존에 읽은 행들의 값은 그대로지만 집합에 새로운 행이 끼어들어 결과 건수가 달라졌다.

의문점4

‘집합에 새로운 행이 끼어들어 결과 건수가 달라짐’이 왜 문제가 되는가? (고객이 추가된건데 당연한 결과 아닌가? 트랜젝션도 문제없는데)

답4

트랜잭션이 한 덩어리의 논리적 작업으로서 동일한 기준(같은 시점·같은 집합) 위에서 결론을 내야 하는 경우는 집합 일관성이 요구되는데 그게 깨져서.
집합 일관성이 요구되는 경우?
- case1: “나이 ≥ 30 고객이 10명 이상이면 VIP 프로모션 집행”이라는 로직에서
  1. T1이 처음 조회해 10명을 확인해 프로모션을 집행하기로 결정
  2. 그 사이 T2가 1명 INSERT
  3. T1이 다시 확인하니 11명
  4. 정책 근거의 일관성이 깨짐. 로그엔 “10명이라 집행”이라 찍혔는데, 검증 단계에선 “11명 기준으로 집행됐어야 한다”가 되어 회계/감사·추적 시 앞뒤가 맞지 않게 된다프로모션 집행한다고했는데 예산/재고 산정이 “10명분”으로 계산된 뒤 “11명”으로 검증되면 과소/과다 집행 이슈 발생.
- case2: “10명 이하일 때만 집행” 로직에서
  1. 첫 조회 10명 -> 집행(YES)
  2. 그 사이 1명 INSERT로 11명 -> 동일 트랜잭션에서 재조회 시 미집행 -> ‘집행여부’ 결론 뒤집힘
- case3: “10명 이하일 때만 집행” 로직에서
  1. 첫 조회 10명 기준으로 10장 발급
  2. 재조회 11명 -> 미발급 1명 발생해서 무결성/공정성 깨짐
결론
- 집행 여부가 같아도 근거가 변해 논리적 일관성·정합성이 깨지거나, 결론 자체가 뒤집힘 또는 현시점에 적절하지않은 결론이 도출되어서 트랜젝션 성공 여부와 관련없이 트랜젝션 수행 목적이 제대로 이행되지않는게 문제다.

~~의문점4는 다시보니 의문점2랑 똑같은 질문…~~

#

Seriesable

모든 트랜잭션이 순차적으로 실행된 것과 같은 결과를 보장
동일한 시점의 데이터를 기준으로 처리하므로 Dirty Read, Non-Repeatable Read, Phantom Read 모두 발생하지 않는다 예를 들어 “나이 ≥ 30 고객이 몇 명인지”를 조회했을 때 처음 10명이었다면, 트랜잭션이 끝날 때까지는 다른 트랜잭션이 고객을 추가하더라도 여전히 10명으로 보이며, 새로운 행이 끼어드는 일이 없다.

의문점5

Repeatable Read도 트랜잭션이 끝날 때까지 동일한 값이 보장된다고 했는데 Serializable이랑 다른점?

답5

Repeatable Read
- 보장하는 것: 이미 읽은 행(row)의 값은 트랜잭션 종료까지 변하지 않는다.
- 보장하지 않는 것: 아직 읽지 않은 “범위(gap)”에 새로운 행이 삽입되는 것은 막지 않는다.
- WHERE age >= 30 같은 조건 조회 시, 이미 읽은 고객들의 나이는 그대로지만, 그 조건에 맞는 새로운 고객이 추가되어 “집합”이 달라질 수 있다(Phantom Read)
Serializable
- 보장하는 것: 트랜잭션 전체가 직렬(순차) 실행된 것과 동일한 결과 즉 단순히 이미 읽은 행만 고정하는 게 아니라, 조건/범위 전체를 잠가서 새로운 행이 끼어드는 것까지 차단함.
- WHERE age >= 30 조건으로 처음 10명이었다면, 내 트랜잭션이 끝날 때까지는 집합이 변하지 않는다. 다른 트랜잭션이 INSERT를 시도하면 내 트랜잭션이 끝날 때까지 대기하거나 충돌로 막힌다.

의문점5 결론

집합이 바뀌는건 트랜젝션 수행에 영향을 안준다 « 가 전제되는듯.
트랜젝션 수행에는 영향이 없고 트랜잭션의 논리적 목표(집합 단위의 일관된 판단/계산)에 문제가 생긴다.
Serializable은 그것마저 차단한다.

#

격리수준-비유없는 정의

동시에 실행되는 여러 트랜잭션 간의 상호작용을 얼마나 차단할지를 정의하는 규칙.
격리 수준이 낮으면 동시성은 높지만 데이터 일관성이 약해지고 격리 수준이 높으면 데이터 일관성은 강해지지만 동시성이 떨어진다.

#

2. pgaudit #

필요성

데이터베이스를 운영할 때 단순히 쿼리가 잘 돌아가는지만 보는 게 아니라, 누가 언제 어떤 SQL을 실행했는지 기록으로 남겨야 함.
보안 규정이나 법적 규제에서는 “권한 변경이 있었는가, 데이터가 언제 어떻게 수정되었는가, 누가 조회했는가” 같은 사항을 추적할 수 있어야 하고 내부 직원이 부적절하게 데이터를 열람하거나 외부 공격자가 침입했을 때를 대비해 이러한 흔적을 감시할 수 있는 장치가 필요하다

설치

#1 Homebrew PostgreSQL 17 PATH 추가

# ~/.zshrc에 추가
export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"

# 설정 반영
$ source ~/.zshrc

# 버전 확인
$ which psql
$ psql --version

/opt/homebrew/opt/postgresql@17/bin/psql
psql (PostgreSQL) 17.6 (Homebrew)

#2 슈퍼유저 postgres role 생성 후 postgres로 접속

#bash

$ psql -U yshmbid -d postgres

#psql

CREATE ROLE postgres WITH LOGIN SUPERUSER CREATEDB CREATEROLE PASSWORD 'postgres';

#bash

$ psql -U postgres -d postgres

#3 pgaudit 라이브러리 로드 설정

#bash

# /opt/homebrew/var/postgresql@17/postgresql.conf에 추가
shared_preload_libraries = 'pgaudit'

# PostgreSQL 재시작
$ brew services restart postgresql@17

# postgres로 접속
$ psql -U postgres -d postgres

#sql

// pgaudit 확장 설치 실행
CREATE EXTENSION pgaudit;

#4 주요 설정값 세팅

#bash로 하기

# /opt/homebrew/var/postgresql@17/postgresql.conf에 추가
pgaudit.log = 'read, write, ddl, role'
pgaudit.log_catalog = off
pgaudit.role = 'postgres'

#sql로 하기

ALTER SYSTEM SET pgaudit.log = 'read, write, ddl, role';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.role = 'postgres';
SELECT pg_reload_conf();

#5 테스트1

SHOW pgaudit.log;
SHOW pgaudit.log_catalog;
SHOW pgaudit.role;

------------------------
 read, write, ddl, role
(1개 행)

 pgaudit.log_catalog 
---------------------
 off
(1개 행)

 pgaudit.role 
--------------
 postgres
(1개 행)

#6 테스트2 - DDL/DML 실행후 로그 확인

CREATE TABLE temp_test(id INT);
INSERT INTO temp_test VALUES (1);
SELECT * FROM temp_test;
GRANT SELECT ON temp_test TO postgres;

ERROR:  relation "temp_test" already exists
INSERT 0 1
 id 
----
  1
  1
(2개 행)

GRANT

CREATE TABLE temp_test(id INT);
- 이미 같은 이름의 테이블이 있어서 relation “temp_test” already exists 에러 발생 (정상 동작)
INSERT INTO temp_test VALUES (1);
- 두 번 실행됨
- 그래서 id 값이 1인 레코드가 두 개 들어감
GRANT SELECT ON temp_test TO postgres;
- 권한 부여 정상 완료

# 로그가 파일로 저장되도록 /opt/homebrew/var/postgresql@17/postgresql.conf에 추가
logging_collector = on
log_directory = 'log'
log_filename = 'postgres.log'
log_statement = 'all'

# PostgreSQL 재시작
$ brew services restart postgresql@17

다시 쿼리 생성해야된대서 다시하기

CREATE TABLE temp_test(id INT);
INSERT INTO temp_test VALUES (1);
SELECT * FROM temp_test;
GRANT SELECT ON temp_test TO postgres;

FATAL:  terminating connection due to unexpected postmaster exit
서버가 갑자기 연결을 닫았음.
	이런 처리는 클라이언트의 요구를 처리하는 동안이나
	처리하기 전에 서버가 갑자기 종료되었음을 의미함.
서버로부터 연결이 끊어졌습니다. 다시 연결을 시도합니다: 성공.
INSERT 0 1
 id 
----
  1
  1
  1
(3개 행)

GRANT

FATAL: terminating connection due to unexpected postmaster exit
- PostgreSQL 서버가 잠깐 죽었다가(FATAL) 자동으로 재기동
INSERT INTO temp_test VALUES (1);
- 세 번 실행됨
- 그래서 id 값이 1인 레코드가 3개 들어감
GRANT SELECT ON temp_test TO postgres;
- 권한 부여 정상 완료

# 로그에서 확인
$ tail -f /opt/homebrew/var/postgresql@17/log/postgres.log

           (SELECT sum(tup_fetched) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 5)) AS "Fetched",
           (SELECT sum(tup_returned) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 5)) AS "Returned"
        ) t
        UNION ALL
        SELECT 'bio_stats' AS chart_name, pg_catalog.row_to_json(t) AS chart_data
        FROM (SELECT
           (SELECT sum(blks_read) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 5)) AS "Reads",
           (SELECT sum(blks_hit) FROM pg_catalog.pg_stat_database WHERE datname = (SELECT datname FROM pg_catalog.pg_database WHERE oid = 5)) AS "Hits"

자꾸 pgAdmin 자체가 실행한 모니터링 쿼리만 뜨는데 … 머지 ㅠㅠ

#

3. AI 시스템 운영 #

AI 파이프라인

데이터를 수집하고 정제 -> 벡터화·임베딩을 거쳐 데이터베이스에 저장 -> 그 후 학습과 추론 과정을 통해 모델을 활용 -> 서비스나 API로 결과를 노출

특성?

각 단계는 담당자와 보안 위험이 다르다.
- 수집 단계에서는 민감한 원본 데이터가 노출될 수 있고, 정제 단계에서는 변조가 일어날 수 있다. 임베딩 단계에서는 모델 노출이 위험 요소가 되고, DB 저장은 권한 누수가 문제가 된다. 학습·추론 단계는 반복 호출과 탈취가 이슈이고, 서비스/API 단계에서는 불필요한 노출을 막아야 한다.
- 이에따라 ETL 담당자, 데이터 엔지니어, ML 엔지니어, DBA, 서비스 관리자, API 사용자처럼 책임 담당자가 나뉜다.

권한 분리

분리 방식?
- 수집을 맡은 data_ingestor는 INSERT나 TRUNCATE 권한만, 정제를 맡은 data_cleaner는 SELECT와 UPDATE 권한만, 모델을 다루는 ml_engineer는 SELECT와 실행 권한만 가진다. API 사용자(api_user)는 결과 조회만 허용되고, 최종적으로 admin만 모든 권한과 보안 정책 관리 권한을 갖는다.
PostgreSQL에서 구현
- 벡터 저장 테이블을 만들고 각 역할에 필요한 권한만 부여.
- data_ingestor는 INSERT, SELECT, ml_engineer는 SELECT, UPDATE, api_user는 SELECT만 허용하는 식.

데이터 보호 전략

민감한 필드는 뷰(View)로 가공해 노출을 제한
행 단위 보안(Row-Level Security)을 적용해 “자신이 생성한 데이터만 볼 수 있다” 같은 조건 생성
접근 기록은 pgaudit 같은 로깅 확장이나 API Gateway 로그를 통해 남기고 API 키 인증을 통해 모델 접근 제한

API 접근 통제

FastAPI나 Flask에서 사용자 인증 토큰(OAuth, JWT)을 활용해 접근을 검증
추론 요청 시에는 사용자 IP와 쿼리 내용을 저장해 추적 가능성을 확보
OpenAI나 BERT 같은 대형 모델을 활용할 경우 응답 길이 제한, 시간 제한, 비속어 필터링
벡터 검색 결과는 SCORE 기준으로 중요도 있는 일부만 노출되도록 제어해 불필요한 데이터 유출 통제
GraphRAG 같은 방식은 노드·엣지 단위로 권한을 세분화해 특정 사용자에게 필요한 정보만 노출