들어가며
파이썬에서 MySQL 을 사용할 일이 있었다면 한번쯤 들어봤을 만한 PyMySQL 패키지입니다. DictCursor 등 아주 쉽고 사용자 친화적인 기능을 제공해줍니다. 이 글은 PyMySQL 패키지를 사용함에 있어 평문통신이 영 마음에 걸리는 분들을 위해 암호화 통신을 하는 법에 대해 다룹니다. 잘못된 내용이 있다면 알려주세요.
PyMySQL 의 기본 설정
PyMySQL 을 이용, 별도의 설정 없이 접속한다면, 기본적으로 암호화 통신을 하지 않습니다. DB 암호라던가 쿼리내용, 민감함 데이터가 패킷 스니핑에 그대로 노출되겠죠. 실제로 SELECT VERSION()
쿼리가 평문으로 노출되는 걸 확인할 수 있습니다. (비밀번호는 salt 값과 해쉬화되서 전송되는지 평문이 노출되진 않았지만, 아이디는 평문으로 보이는것을 확인했습니다.)
암호화 통신의 예
예제로 사용한 MySQL 서버는 MySQL Docker 의 latest 태그버전을 별도의 설정없이 설치한 DB입니다. WorkBench1 에선 기본 설정으로 가능한 경우 SSL 암호화를 지원하는데요, WorkBench 에서 별도의 세팅 없이 기본 값만으로도 SSL 통신이 잘 이뤄지는걸 보니 서버설정에는 문제가 없는것 같습니다.
암호화 통신 방법
그렇다면 파이썬 스크립트에서도 암호화 통신을 하기 위해 어떻게 코드를 짜야 할까요? 방법은 두 가지 입니다. SSL 과 SSH Tunneling 이 그것입니다.
SSL 통신은 웹브라우저에서 많이 볼 수 있는 HTTPS 통신과 같습니다. 좀더 깊숙히 들어간다면 TLS, SSL 등 자세한 설명이 필요하지만… 글의 목적과 부합하지 않을뿐더러 제가 잘 모르는 분야라 여기선 생략하겠습니다. 중요한것은 상호간 인증서라 불리는 공개키를 통해 암호화 통신을 한다는 것만 생각하시면 되겠습니다.
SSH Tunneling 은 VPN(Virtual Private Network) 과 같다고 생각하면 편합니다. 국내 최고 점유율을 차지한 IpTime 공유기에서도 제공하는 기능이라 이젠 많이 익숙한 용어입니다. 중국에 출장간 사람들이 카카오톡을 쓸 때 VPN을 많이 이용하죠? 카카오톡이 중국지역의 IP를 막아두기 때문인데요. VPN 을 통해 집에 있는 IpTime 공유기에 접속한다면, 중국에 있지만 집에 있는것과 논리적으로 같은 네트워크를 쓸 수 있습니다.
SSH Tunneling 도 이와 같습니다. PuTTy 로 원격 서버에 접속하면 mysql -uroot -p
로 로컬에 있는것 처럼 DB에 접근할 수 있는것이죠. SSL 통신과 차이가 있다면, SSL 통신은 서버측이 제공하는 암호화 방식과 클라이언트측 방식, 암호화 키 등 신경쓸 것이 좀 있지만, SSH 통신은 로컬서버에 접속하는것과 같기에 MySQL 의 암호화설정은 신경쓸게 없다는 것입니다. 하지만 SSH 는 꼭 서비스해야 합니다.
SSL 통신 예제
아시다시피 SSL 통신은 인증서를 기반으로 합니다. 윈도우키+R -> certlm.msc 나 윈도우 검색에서 컴퓨터 인증서 관리를 입력해 확인할 수 있는 신뢰할 수 있는 루트 인증 기관 이 SSL 인프라의 핵심이죠. 여기선 인증서 체인에 관한 자세한 내용은 차치하고 MySQL 서버는 이미 SSL 통신을 제공한단 가정하에 핵심만 짚어봅시다.
PyMySQL 이 SSL 통신을 하기 위해선 MySQL 서버측 인증서를 사전에 공유할 필요가 있습니다. 그리고 그 인증서의 위치는 서버측 mysqld 설정파일에 나와있습니다. 예제 환경에서 MySQL 은 docker container 이고, 설정파일을 마운팅 하지 않았기에 이를 보려면 좀 복잡한 절차를 거쳐야 하지만 차치하고 인증서를 가져왔다 치죠.2
client-cert.pem
과 client-key.pem
파일은 MySQL 컨테이너에서 가져온 파일입니다. 스크립트와 동일 폴더에 넣고 ssl
파라미터를 주면 아래와 같이 SSL 통신이 활성화 됩니다. 키파일에 관한 더 자세한 내용은 여기 를 참고하세요.
SSH Tunneling 예제
아래는 SSH Tunneling 을 이용해 암호화 통신을 구현한 예제입니다. SSHTunnelForwarder
는 sshtunnel
패키지를 설치하면 됩니다. sshtunnel
패키지는 SSH 관련 여러 기능을 제공해주는 paramiko
의 래퍼입니다. 이 코드는 conn.ssl
을 확인해 본다 한들 암호화 되지 않았다고 나올겁니다. SSH Tunneling 방식의 암호화는 MySQL 커넥션을 암호화 하는게 아니라 서버로 가는 ‘구간’을 암호화 하는것이기 때문입니다.
from sshtunnel import SSHTunnelForwarder
# SSH address mapping setup (not actually connects)
tunnel = SSHTunnelForwarder((ssh_host, ssh_port), # SSH hosting server
ssh_username=ssh_user,
ssh_password=ssh_passwd,
remote_bind_address=('127.0.0.1', 3306), # addr which SSH server can access
local_bind_address=('0.0.0.0', 3305)) # mapping addr which python will access
# connect and map remote addr to local addr
tunnel.start()
# connect MySQL like local
conn = pymysql.connect(host=tunnel.local_bind_host,
port=tunnel.local_bind_port,
user=db_user,
passwd=db_pass,
db=scheme)
cur = conn.cursor()
cur.execute('SELECT VERSION()')
print(cur.fetchone())
# close connection
cur.close()
conn.close()
tunnel.end()
여기서 주의할 점이 있습니다. 서버측는 SSH 세션당 약 1MB 정도의 메모리를 할당하기에 tunnel.end()
를 반드시 호출해줘야 합니다. 본의아니게 자기 서버에 SSH Connection Flooding 공격을 할 수 있으니 꼭 챙겨주세요. 24시간 SFTP 세션을 유지하는 스크립트를 작성하는데 이를 빼먹어서 서버를 먹통으로 만들고, 디버깅에 한참이나 소요한 삽질을 하고 나니 ‘닫는것’의 중요성을 깨닫게 되었습니다.
좀더 pythonic 한 방법을 쓸 수도 있습니다. with
키워드를 사용하면 코드도 줄고, 열고 닫는데 드는 노력도 줄이고, 더 명시적인 코드를 작성할 수 있습니다. 아래 코드는 위와 완전히 동일합니다.
with SSHTunnelForwarder((ssh_host, ssh_port),
ssh_username=ssh_user,
ssh_password=ssh_passwd,
remote_bind_address=('127.0.0.1', 3306),
local_bind_address=('0.0.0.0', 3305)) as tunnel:
with pymysql.connect(host=tunnel.local_bind_host,
port=tunnel.local_bind_port,
user=db_user,
passwd=db_pass,
db=scheme) as cur:
cur.execute('SELECT VERSION()')
print(cur.fetchone())
다만 SSH Tunneling 은 SSH서버와 MySQL 컨테이너가 같은 서버에 있을경우 사용할 수 없습니다. 컨테이너에 접근하기 위해선 포트포워딩을 타야하는데 SSH로 접근하면 도커호스트에 직접 붙기 때문이죠. 해결방안을 아시는분은 꼭 제보 부탁드립니다.
마치며
최근 주업무가 개발로 넘어가며 정말 24시간이 모자른 나날을 보내고 있습니다. 전문 개발자가 아니기에 이런 간단한 부분들에서 막히는 제가 좀 한심하기도 하고요… 이번 글도 누군가에겐 도움이 되었으면 좋겠습니다.
- PhpMyAdmin 이 웹 기반 MySQL/MariaDB 관리도구라면 WorkBench 는 MySQL 을 개발한 ORACLE 이 함께 제공하는 DB 관리도구 입니다.(커뮤니티버전 무료) ↩
- 호스트에 바로 설치된 환경이라면 쉽게 설정파일을 갖고올 수 있지만, 도커에 설치된 MySQL 은 설정파일이 컨테이너 내부에 있습니다. 컨테이너를 생성할 때 설정파일이 있는 폴더를 호스트 PC에 노출(마운트) 시킬 수 있지만 그러지 않았죠. 따라서 컨테이너 내부로 쉘로 접근하여 설정파일을 확인해야 하는 상당히 귀찮은 절차를 거쳐야 합니다. MySQL Docker Hub 에서 Using a custom MySQL configuration file 섹션은 컨테이너 생성시 사용자정의 설정파일을 사용하는 법에 대해 다룹니다. ↩
with SSHTunnelForwarder((‘ssh_host, 22),
ssh_username=”jithu”,
ssh_pkey=”/home/ji/.ssh/id_rsa”,
remote_bind_address=(‘db_host_address’, 3306),
# local_bind_address=(‘0.0.0.0′, 3305)
) as tunnel:
with pymysql.connect(host=tunnel.remote_bind_host,
port=tunnel.remote_bind_port,
user=’db_user’,
passwd=’db_pwd’,
db=”) as cur:
cur.execute(‘SELECT VERSION()’)
print(cur.fetchone())
But not working tried with python 3.6 and 2.7.15 ….. cloud you tell me what is wrong here..
I am using ubuntu , and I have a local my sql instance running on 3306 port.
Hello Jithu, thanks for the reply. I can not specify the problem without an error message, but something looks weird in your environment.
I don’t think local MySql instance is a good environment for testing SSH tunneling. Let’s see what code means.
The code above abstracts does these things.
Connect to the remote host using SSH
In this case, you connect
'ssh_host'
as user'jithu'
using key authentication.Bind remote address to local address
This is the tricky part. For better understanding, let’s consider that
# local_bind_address=('0.0.0.0', 3305)
is uncommented.remote_bind_address
is the address whichssh_host
accesses, not your python running PC accesses.local_bind_address
is mapping address which can python running PC can accessremote_bind_address
like a local address.If you don’t define
local_bind_address
, it will be a randomly assigned local bind port.Connect remote host using binded local address
Now you can access to
('db_host_address', 3306)
using('localhost', 3305)
.As you commented
local_bind_address
, you have to usetunnel.local_bind_port
.In the code above, you are trying to connect to remote host which is not accessible. You can only access to remote host using binded address.
I hope this reply helps you.