접근 감사와 사용자 행동 로그 남기기

네 데이터베이스가 엄청 엄격한 멤버십 규칙이 있는 클럽이라고 생각해봐. 거기엔 자기 사람만 들어올 수 있는데, 우리는 누가 언제 왔고, 얼마나 있었고, 뭘 했는지 다 알고 싶잖아. 바로 이런 걸 위해 PostgreSQL의 감사 기능이 필요한 거야. 이걸로 할 수 있는 건:

1. 사용자 행동 추적하기. 예를 들어, 누가 언제 중요한 쿼리를 실행했는지 알 수 있어.

2. 수상한 활동 찾기. 이건 예를 들면, 접근 권한이 없는 데이터를 읽으려고 하는 누군가일 수도 있어.

3. 법적 요구사항과 표준 준수. 많은 산업(예: 금융, 의료)에서는 사용자 행동을 자세히 감사해야 해.

4. 시스템 내부 파악하기. 로그를 남기면 어떤 쿼리가 자주 실행되는지, 어디가 병목인지, 성능을 어떻게 최적화할 수 있는지 파악할 수 있어.

로그 설정 파라미터

PostgreSQL은 log_statement랑 log_connections 같은 설정 파라미터로 사용자 행동 감사를 할 수 있어. 하나씩 살펴보자.

1. log_statement: 뭘 기록할까?

log_statement 파라미터는 어떤 SQL 쿼리를 로그에 남길지 정해. 이거 잘 쓰면 시스템에서 무슨 일이 일어나는지 쉽게 알 수 있어.

log_statement 값 옵션:

• none — 아무것도 기록 안 함(스릴 넘치는 삶을 원하면 이거 써도 됨).
• ddl — 데이터베이스 구조를 바꾸는 명령만 기록(CREATE TABLE 같은 거).
• mod — 데이터를 바꾸는 명령만 기록(INSERT, UPDATE, DELETE 등).
• all — 전부 다 기록.

설정 예시: 이 파라미터를 바꾸려면 postgresql.conf 파일을 수정해야 해:

# 모든 SQL 쿼리 로그 남기기 설정
log_statement = 'all'

이제 변경사항 저장하고 PostgreSQL 서버를 재시작해:

pg_ctl reload

이제 네 클럽은 음료 주문부터 댄스 동작까지 모든 행동을 다 기록할 거야.

2. log_connections: 누가 클럽에 들어오나?

log_connections 파라미터는 데이터베이스에 새로 접속할 때마다 로그에 기록해. 그리고 log_disconnections라는 관련 파라미터도 있는데, 이건 연결이 끊길 때를 기록해.

설정 예시: 역시 postgresql.conf 파일을 수정해:

# 접속 로그 남기기
log_connections = on

# 연결 종료 로그 남기기
log_disconnections = on

이게 뭐가 좋냐면, 예를 들어 네 매니저가 두 시간 동안 잘못된 비밀번호로 접속 시도한 것도 로그에서 볼 수 있어. 그것도 새벽 3시에 말이지.

로그 분석: 뭘 찾을 수 있을까?

log_statement랑 log_connections 파라미터를 설정하면 PostgreSQL이 로그를 남기기 시작해. log_statement = 'mod'일 때 로그 파일이 어떻게 생겼는지 예시를 볼게:

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

재밌는 포인트:

1. 누가 접속했는지: user=admin database=university — 이건 우리 보스야.
2. 뭐 했는지: Alice라는 이름으로 행을 추가하고 나이도 업데이트했어.
3. 언제 나갔는지: 12:48:45, 2분 30초 활동 후에.

실전 예시: 감사 기능을 실제로 어떻게 쓰나

감사와 로그 남기기가 어디서 유용한지 몇 가지 시나리오를 볼게.

시나리오 1: 데이터 변경 추적

누군가 students 테이블의 데이터를 바꾸고 있다고 의심돼. 이렇게 감사 설정하면 돼:

1. log_statement = 'mod'로 모든 데이터 변경 쿼리를 기록해.
2. 로그를 분석해:

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

이제 누가 언제 어떤 변경을 했는지 다 볼 수 있어.

시나리오 2: 수상한 접속 찾기

로그에 여러 IP에서 자주 접속이 보이면 뭔가 수상한 거야. 분석하려면 이렇게 해:

1. 접속 로그(log_connections)를 확인해.
2. IP별로 필터링해:

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

시나리오 3: 쿼리 성능 분석

서버가 왜 느린지 알고 싶어? 간단하게 log_statement = 'all'을 잠깐(예: 1시간) 켜고, 로그를 모아서 서버가 어디서 시간을 많이 쓰는지 보면 돼.

베스트 프랙티스

모든 걸 다 로그하지 마. log_statement를 DDL이랑 데이터 변경만 기록하도록 설정해.

로그 분석 자동화해. grep, awk 같은 스크립트나 ELK Stack(Elasticsearch, Logstash, Kibana) 같은 툴로 정기적으로 로그를 읽고 분석해.

로그 크기 관리해. PostgreSQL 파라미터나 운영체제 툴(예: Linux의 logrotate)로 로그 순환 설정해.

이제 너도 데이터베이스의 진짜 보안관이 된 기분이지? 로그 남기기랑 감사는 단순히 보안만이 아니라, 네 시스템에서 무슨 일이 일어나는지 더 잘 이해할 수 있는 좋은 방법이야. 실제 프로젝트에서 이 지식이 어떻게 쓰이는지 곧 볼 거야!