ProxySQL 쿼리 필터링 실습하기

ProxySQL을 맛보기로나마 다뤄보면서 느낀 것은, 쿼리 필터링 기능 하나로 안 되는 게 없다는 것이다.

특정 쿼리 실행을 막는 것부터 시작해서 반대로 특정 쿼리만 실행할 수 있게 한다든지, 원하지 않는 테이블의 데이터는 가려서 출력되도록 만든다든지, 계정별로, DB 서버 별로 규칙을 커스텀할 수 있다든지...

물론 이 프로젝트를 진행하면서 내가 다뤄본 건 새발의 피 수준일 것이고, 프로젝트가 개발 쪽이 아닌 보안 기능 구현이었기 때문에 제대로 활용한 것은 아닐테지만, 그래도 직접 솔루션을 써보면서 겪은 경험들이 조금이나마 도움이 되지 않을까 싶어 쿼리 필터링 실습 부분도 같이 써 본다.

 

 

TLS/SSL 적용하기

먼저 프록시 서버에서 /etc/ 경로의 proxysql.cnf 파일을 편집기로 연 뒤, 'mysql_variables' 구역 안에 아래와 같이 명령어를 추가한다.

(프록시 서버에 기본으로 내장된 CA 및 개인키를 활용할 예정)

have_ssl=true
ssl_ca=”/var/lib/proxysql/proxysql-ca.pem”
ssl_cert=”/var/lib/proxysql/proxysql-cert.pem”
ssl_key=”/var/lib/proxysql/proxysql-key.pem”

 

그런 다음 프록시 서버를 재시작한 뒤, tmp 경로에서 각 DB 계정마다 연결에 쓸 인증서를 만들어준다.

openssl genrsa -out [DB 계정이름].key 2048 && \
openssl req -new -key devel.key -out [DB 계정이름].csr -subj "/CN=[계정이름]" && \
sudo openssl x509 -req -in [DB 계정이름].csr -CA /var/lib/proxysql/proxysql-ca.pem -CAkey /var/lib/proxysql/proxysql-key.pem -CAcreateserial -out [DB 계정이름].crt -days 3650

얘네들이 한 줄이다.

 

이후 테스트할 PC(개발서버 등)에서 /etc/mysql/ssl 경로 안에 디렉터리를 만들어준 뒤, 프록시 서버에서 scp 명령어 등을 이용해 전송한 인증서를 경로 안에 한 묶음으로 넣는다.

(플젝에서는 그냥 한 개발서버 안에 모든 DB 서버의 인증서를 넣어 테스트했기 때문에 한 서버 안에 디렉터리를 여러 개 만들어 모든 DB에 접근할 수 있도록 했지만, 실제로는 이러면 안 될 것이다..)

아래 사진과 같이 디렉터리 안에 넣는다.

 

사실 csr 파일은 필요 없다.

 

각 DB 접속 계정의 SSL 통신을 활성화하기 위해 아래처럼 명령어를 입력한다.

update mysql_users set use_ssl=1 WHERE username IN ('계정이름1', '계정이름2', ...);

 

모든 작업을 할 때마다 아래 명령어로 적용 및 저장을 해 주는 것을 잊지 말자.

load mysql users to runtime;
save mysql users to disk;

 

이제 테스트용 PC에서 프록시SQL에 접속할 때마다 아래와 같이 쳐야 하는데..

mysql -u devel -p -h 192.168.120.10 -P 6033 \
--ssl-ca=/etc/mysql/ssl/ca-cert.pem \
--ssl-cert=/etc/mysql/ssl/devel/devel.crt \
--ssl-key=/etc/mysql/ssl/devel/devel.key

이건 너무 길으므로.. 홈 디렉터리의 .my.cnf 파일을 좀 건드려서 계정 이름만 쳐도 접속할 수 있도록 바꿔줄 예정이다.

 

이렇게 추가했다. 이러면 이제 각각의 DB에 접속할 때 쓰는 계정 이름인 'devel', 'prsq'만 쳐도 곧바로 계정 비밀번호 입력창이 뜨게 되므로 저 긴 명령어를 일일이 치지 않아도 된다.

(대괄호 안에 계정 이름만 치면 인식을 못 한다. 그래서 앞에 client도 같이 쳐 줘야 한다.)

 

이후 키들에 대한 권한 정책을 수정해준다.

sudo chown -R gaebal:gaebal /etc/mysql/ssl/
sudo chmod 755 /etc/mysql/ssl/ 
sudo chmod 755 /etc/mysql/ssl/devel/ 
sudo chmod 755 /etc/mysql/ssl/prsq/

소유자를 해당 서버의 계정으로 바꿔주었으며, 디렉터리 권한을 위와 같이 설정하여 폴더 목록을 보는 것 자체는 가능하게 수정했다.

 

그 다음 각 키들에 대한 권한 정책은 아래와 같이 바꿔준다.

sudo chmod 600 /etc/mysql/ssl/devel/* 
sudo chmod 600 /etc/mysql/ssl/prsq/*
sudo chmod 600 /etc/mysql/ssl/ca-cert.pem

키들의 경우 소유자만 읽고 쓸 수 있게, 나머지 사용자들은 조회도 못 하게 바꿔주는 작업이다.

 

이후 DB에 접속하여 SSL 상태를 확인해보면 아래와 같이 적용이 되어 있는 것을 볼 수 있다.

 

 

실습해 본 쿼리 규칙들 

이번 플젝 시간이 그리 길지 않아서, 많은 것들을 실습해보진 못했지만, 일단 크게 3가지의 규칙을 설정해 보았다.

(비인가 쿼리 막기, 개인정보 마스킹, 특정 IP만 DB에 접근 허용하기 등)

살짝 하드코딩 느낌이 없잖아 있긴 하지만, 일단은 경험을 해 봤다는 것에 의의를 두기로 했다.

 

먼저 미리 쓰고 가자면, 프록시SQL에서 쓰이는 포트 번호는 크게 두 개가 있다.

6032: 프록시SQL의 관리자 계정을 관리하기 위한 포트(공유기 게이트웨이 느낌)

6033: 클라이언트에 접속하기 위해 쓰이는 트래픽 송/수신용 포트

 

쿼리 규칙들을 추가할 땐 6032번 포트를 이용해 관리자 계정에 접속하여 추가할 예정이며, 이후 테스트용 PC에서의 접근 및 테스트는 6033번 포트를 이용해 이루어진다.

(위에서도 쓰였지만, 모든 규칙을 추가한 뒤엔 항상 load mysql query rules to runtime; 및 save mysql query rules to disk;를 치는 것을 잊지 말자.)

 

1. where 조건 없는 delete문, drop문, truncate문 등

(1) where 조건이 붙지 않은 delete문 차단하기

INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, error_msg, apply) 
VALUES (100, 1, '[db 계정 이름]', '(?i)^DELETE\s+FROM\s+[^\s]+$', '[경고 메시지 자유롭게]', 1);

위와 같이 쓰면 delete문으로 시작하는((?i): 대소문자 구분 무시) 쿼리문 가운데 테이블 이름 뒤에 아무 것도 오지 않는, 그러니까 where절이 쓰이지 않는 delete문을 잡아낸다는 뜻이다.

이후 해당 서버에서 쿼리를 입력해보면, 차단이 되는 것을 볼 수 있다.

 

(2) drop문 차단하기

기본 구조는 delete문과 비슷하다.

INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, error_msg, apply) 
VALUES (102, 1, '[DB 계정 이름]', '(?i)^DROP', '[경고 메시지 자유롭게', 1);

 

(3) truncate문 차단하기

drop 부분만 바꿔주면 된다.

INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, error_msg, apply) 
VALUES (104, 1, '[DB 계정 이름]', '(?i)^TRUNCATE', '[경고 메시지 자유롭게]', 1);

 

그 밖에 union select문((?i)^union\s+SELECT)이나 or 1=1.. 같은 SQL Injection문에 대한 차단 규칙들도 모두 적용시킬 수 있다. 물론 인젝션 구문 같은 건 앱 단계에서 막는 게 정석이고, 요즘 웬만해선 다 막혀있긴 하지만, 다층 방어 느낌으로 추가해둬서 나쁠 것은 없다고 생각한다.

 

2. 개인정보 마스킹

간단하게나마 테이블 안의 특정 정보를 비공개 처리하여 출력하는 마스킹 테스트를 해 보았다.

DB에서 직접 테이블을 들어간 뒤 내용을 조회해보면, 아래와 같이 닉네임이나 비밀번호 등이 모두 적나라하게 노출된다.

나중에 유지보수 등을 위해 개발 서버 등에서 DB에 접속할 때, 불필요한 정보 공개를 막기 위한 용도라는 느낌으로 특정 정보를 마스킹 해보면 어떨까 하는 느낌으로 아래와 같이 명령어를 작성해 봤다.

 

INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, replace_pattern, destination_hostgroup, apply, comment) 
VALUES (110, 1, 'devel', '(?i)^SELECT\s+\*\s+FROM\s+users', 'SELECT id, username, "****" AS password, mc_nickname, created_at FROM users', 2, 1, '[코멘트 자유롭게]');

비밀번호(password) 부분을 강제로 "****"로 바꿔 출력되도록 규칙문을 추가해 주었다.

실전에서는 이름의 경우 가운데 글자만, 주민번호는 8자리부터 그 뒤로만 마스킹을 하는 등으로 더 세세하게 마스킹을 하는 규칙 같은 게 정해져 있다고 한다. 물론 쿼리 규칙을 이용해 그런 것도 가능할 것이다.

 

이후 테스트용 개발pc에서 프록시 통해 해당 db 접속 및 테이블 내용을 확인하면..

잘 작동한다.

 

3. IP 바탕 화이트리스트로 내부 조회 및 수정 제한하기

각각의 DB 계정을 통해 접속하는 것은 모두 인가된 특정 서버에서만 접속할 수 있도록 하고, 그 밖의 IP에서 접속할 때 DB의 조회 및 수정 자체를 불가능하게 만들기 위한 쿼리 규칙이다.

 

먼저 혼동을 막기 위해 각 DB 계정이 어느 DB와 연결되어 있는지를 한 번 확인해 준다.

(prsq는 30.11 DB(계정DB), devel은 30.10 DB(웹DB)에 연결되어 있음)

우리가 진행한 플젝에선 2개의 DB가 있었고, 각각 접속할 수 있는 DB 계정이 나뉘어져 있다. 두 DB 모두 하나의 개발PC에서만 접속할 수 있도록 규칙을 생성할 예정이며, 그 밖의 PC에서 접속하면 조회/수정을 할 수 없도록 짜려고 한다.

 

아래와 같이 각 계정마다 조회 및 수정을 허용할 IP 규칙을 만든다.

INSERT INTO mysql_query_rules (rule_id, active, username, client_addr, match_pattern, destination_hostgroup, apply, comment) 
VALUES (200, 1, '[DB 계정 이름]', '[허용할 IP]', '.*', 2, 1, '[코멘트 자유롭게]');

(여기서 apply 값을 1로 지정해 줘야 규칙을 그냥 지나치지 않는다.)

 

이번엔 아래와 같이 그 밖의 모든 IP에서의 내부 조회 및 수정을 틀어막을 쿼리 규칙을 만든다.

INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, error_msg, apply) 
VALUES (298, 1, '[DB 계정 이름]', '.*', '[경고 메시지 자유롭게]', 1);

(주의할 점은, 화이트리스트의 규칙 번호가 차단 규칙 번호보다 낮아야 먼저 허용된 IP인지를 검사한 뒤 나머지를 거를 수 있다. 그 이유는 마지막에 설명)

 

이후 테스트용 비인가 서버를 하나 만든 뒤, 그 곳에서 DB 접속을 시도해 보면..

미리 생성한 규칙에 따라 조회 자체가 안 되는 것을 볼 수 있다. 물론 위에서 차단 규칙의 매치 패턴을 '.*'로 설정했으므로 조회, 수정은 물론 그냥 그 어떤 글자를 넣어도 저렇게 뜰 것이다.

 

4. 접속 및 쿼리 입력 로그 남기기

먼저 아래 명령어를 프록시SQL의 관리자 계정(6032)에서 추가해 커스텀 로깅 규칙을 활성화한다.

update mysql_query_rules SET log = 1;

 

그 뒤 일반적인 모든 쿼리 출력에 대한 로그도 남기기 위해 아래와 같이 명령어를 입력한다.

INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, log, apply, comment)
VALUES (999, 1, '.', '.*', 1, 0, 'Log All Queries');

(규칙 번호를 가장 낮게 설정해 주기)

위처럼 규칙을 지정하면 select는 use든 show든 모든 쿼리 입력에 대한 로그가 남게 된다.

 

다음 작성글에서 쓸 내용이지만, 위 로그들을 이제 스플렁크에 연동시켜 스플렁크에서 작업할 수 있도록 할 예정인데, ProxySQL의 로그 파일 형식은 바이너리이다보니, cat 명령어로 불러오면 글자가 모두 깨져서 나온다.

이를 해결하기 위해 아래와 같은 명령어를 입력해 파일 형식을 JSON으로 바꿔준다.

SET mysql-eventslog_format = 2;

 

로그를 기록할 파일 이름도 본인이 알아볼 수 있게끔 바꿔주자

SET mysql-eventslog_filename = '로그_파일_이름_대충_아무렇게나.log';

 

이제 로그가 저장되는 /var/lib/proxysql/ 경로에 들어가보면, 위처럼 이름을 지정한 로그 파일이 생기며, 테스트용 서버나 ProxySQL에서 쿼리를 날렸을 때 로그가 쌓이는 걸 볼 수 있다. (tail 명령어로 실시간으로 볼 수도 있고)

이런 식으로

플젝에서는 상관 없겠지만, 실전에선 아마 실시간으로 들어오는 로그의 양이 어마어마할텐데, 파일의 최대 용량 크기를 지정하여 유연하게 관리할 수도 있을 것 같다.

SET mysql-eventslog_filesize = 104857600;(100MB)

이런 식으로.

 

p.s.

아까 위에서 허용 IP 규칙을 차단 IP 규칙보다 낮은 규칙 번호로 지정하라고 했는데, 그 이유는 ProxySQL의 경우 방화벽 규칙과 비슷하게, 위의 규칙부터 먼저 순서대로 읽어 내려가는데 그 순서가 낮은 번호 => 높은 번호 순이기 때문이다. 차단 IP 규칙을 선순위로 지정해버리면 당연히 어떤 IP에서 접근하든 일단 틀어막아 버리기 때문에, 먼저 통과시킬 IP를 윗단계에서 지정해 준 뒤 나머지 IP들을 막아야 제대로 된 화이트리스트를 운영할 수 있을 것이다.

p.s.(2)

apply 값이 0이면 해당 규칙 매칭을 체크한 뒤 아래 규칙으로 넘어가는 것이고, 1이면 해당 규칙과 매칭되었을 때 더 이상의 규칙 검사를 멈추고 그 규칙에서 지정한 행동을 수행하도록 만드는 것이다.