MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)

MariaDB 클라언트-서버 TLS/SSL 클라이언트 사용

글 타래:

1. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1)
2. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(2)
3. MariaDB 클라언트-서버 TLS/SSL 암호화 연결(3)

서버측에서 MariaDB/MySQL 의 TLS를 활성화한 다음 실제 다양한 클라이언트에서 접속을 시도해 보자. 서버에서 생성한 CA 파일 ca.pem, 클라이언트 인증서 client-ssl.pem, 클라이언트 키 client-key.pem 을 다운로드해서 사용한다.

글 진행 순서

1. 클라이언트 인증서 다운로드
2. DB API: python 에서 SSL 접속
3. HeidiSQL 에서 SSL 접속
4. MySQL Workbench 에서 SSL 접속

1. 클라이언트 인증서 다운로드

앞서 MariaDB 클라언트-서버 TLS/SSL 암호화 연결(1) 만든 ca.pem, client-cert.pem, client-key.pem 을 외부접속할 클라이언트 PC로 다운받는다.

> mkdir .ssl/mysql/
> cd .ssl/mysql
# 다운로드
> scp USERID@HOST_IP:/etc/ssl/mysql/ca.pem .
> scp USERID@HOST_IP:/etc/ssl/mysql/client-cert.pem .
> scp USERID@HOST_IP:/etc/ssl/mysql/client-key.pem .

SSH 에 다른 포트 번호를 사용하면 scp -P PORT 로 사용한다.

다운로드한 파일은 3종류로

> ls .ssl/mysql
Mode                 LastWriteTime         Length Name
----                 -------------         ------ ----
-a---        2023-07-16  오후 4:38           1984 ca.pem
-a---        2023-07-16  오후 4:38           1497 client-cert.pem
-a---        2023-07-16  오후 4:38           1679 client-key.pem

서버와 비슷하게 mysql client 설정에 인증키들을 설정해주면 그냥 접속해도 ssl로 접속된다. 아래 참고2 에서 mysql client 의 클라이언트 측 my.cnf/my.ini 파일에 ssl-ca, ssl-cert, ssl-key 를 설정하면 된다.

2. DB API: python 에서 SSL 접속

pymysql, sqlalchemy 등 mysql client API 에 ssl_ca, ssl_key, ssl_cert 인자에 클라이언트용 인증 파일을 연결한다.

pymysql

pymysql.connect 에 ssl_ca, ssl_key, ssl_cert 인자 클라이언트용 인증 파일의 경로가 SSL_CA, SSL_CERT, SSL_KEY 에 있다고 가정한다.

conn = pymysql.connect(host='192.168.0.10', 
                       user=DB_USER, 
                       password=DB_PW,
                       db='bookstore', 
                       ssl_ca=SSL_CA,
                       ssl_key=SSL_KEY,
                       ssl_cert=SSL_CERT,
                       charset='utf8')
 
# Connection 으로부터 Cursor 생성
curs = conn.cursor()
sql = "select * from book"
curs.execute(sql)
rows = curs.fetchall()
print(rows)     # 전체 rows
conn.close()

SQLAlchemy : engine

sqlalchemy의 create_engin에 connect_args 인자에 클라이언트 인증 파일의 경로가 SSL_CA, SSL_CERT, SSL_KEY 에 있다고 가정한다.

ssl_args = {'ssl_ca': SSL_CA,
           'ssl_cert': SSL_CERT,
           'ssl_key': SSL_KEY}
engine = sqla.create_engine(
    f'mysql+pymysql://{DB_USER}:{DB_PW}@192.168.0.24/bookstore',
    connect_args=ssl_args)

query = """SELECT * FROM book;"""
df = pd.read_sql(sqla.text(query), con=engine)

C 라이브러리 사용 SSL 접속

참고2 를 보면 C API를 사용한 `libmariadb.dll`` 라이브러리에서 SSL 사용하는 C++ 콘솔 예제 가 있다.

3. HeidiSQL 에서 SSL 접속

HeidiSQL 에서 SSL 을 이용해 접속할 수 있다. 아래 그림 같이 DB 정보를 입력한 후에 SSL 탭에서 SSL을 체크만 하면 된다.

접속이 되면 프로그램 아래 상태바의 정보를 클릭해 보면 SSL 연결을 확인할 수 있다.

앞서 다운로드한 서버측이 제고한 클라이언트 인증 키를 지정하고 연결하면 아래 같은 cipher mis match 에러가 뜬다.

이것은 아마도 서버측의 openssl.cnf 에 정의되어 있는 CipherString 버전이 달라서 그런것 같다. 여기 Connect error “SEC_E_ALGORITHM_MISMATCH”.. 의 글타래에 설명되어 있다.

• 테스트한 서버는 ** Armbian 23.02.2 Bullseye** 로 CipherString 이 DEFAULT@SECLEVEL=2 로 나온다.

TLS versions

> show global variables like 'tls_version';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| tls_version   | TLSv1.1,TLSv1.2,TLSv1.3 |
+---------------+-------------------------+
1 row in set (0.007 sec)

CipherString 테스트

MySQL 설정파일 참조3에 따르면 server-side encrypted-connection control 을 위해서 아래 변수를 사용할 수 있다고 한다.

ssl_cipher: The list of permissible ciphers for connection encryption.

my.cnf 의 SSL/TLS 영역 다음을 추가해 보자.

ssl_cipher=DEFAULT:@SECLEVEL=1

재시작한후 아래 그림 같이 서버에서 만든 클라이언트 인증서를 사용해보니 잘 된다. root 계정 접속도 잘 된다.

4. MySQL Workbench 에서 SSL 접속

MySQL Workbench 에서도 SSL 연결로 데이터베이스에 접속할 수 있다. DB 접속 계정 정보를 입력한다.

SSL 탭에서 SSL 관련 옵션을 선택하는데 여기서는 if available 로 지정했다. 서버측이 SSL활성화가 되어 있으면 자동으로 SSL 통신을 진행한다.

Test 로 확인해 보면 SSL 접속이 잘 되고 있는 것을 알 수 있다.

5. 사용자 SSL 권한 부여

새 사용자를 생성할 때 아래 같이 접속시 REQUIRE 인자로 SSL 로만 접속하도록 할 수 있다.

사용자의 추가/권한 부여에 대해서 MySQL CLI/Admin: 권한부여 글을 참고.

# SSL/TLS(가장 기본적인 암호화 접속)
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE SSL;

# X509
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE X509;

# CIPHER 'cipher'
mysql> CREATE USER 'admin'@'localhost' IDENTIFIED BY '********' REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

mysql> flush privileges;

기존 사용자는 사용자 권한에서 SSL을 권한을 추가한다.

mysql> GRANT ALL PRIVILEGES ON DB이름.* TO 'USERID'@'%' REQUIRE SSL;
mysql> flush privileges;

주어진 grant에 SSL/X509 등이 주어졌는지 확인한다.

mysql> SHOW GRANTS FOR 'USERID'@'HOST';

내부 네트워크 외부 네트워크로 분리해 사용한다면 외부에서는 무조건 SSL 을 사용하도록 할 수 있을 것 같다.

—

1. 참고1 : OpenSSL을 이용한 사설 인증서 생성
2. 참고2 : MariaDB 외부접속시 ssl 사용법, 그리고 ssl 로 replication(동기화) 하기
3. 참고3 : Configuring MySQL to Use Encrypted Connections