2023-07-16 게시 됨2024-03-06 업데이트 됨Programming / Database10분안에 읽기 (약 1431 단어)

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

글 타래:

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

글 진행 순서

클라이언트 인증서 다운로드
DB API: python 에서 SSL 접속
HeidiSQL 에서 SSL 접속
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 을 위해서 아래 변수를 사용할 수 있다고 한다.

1	ssl_cipher: The list of permissible ciphers for connection encryption.

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

1	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을 권한을 추가한다.

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

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

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

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

—

2023-07-16 게시 됨2024-03-06 업데이트 됨Programming / Database13분안에 읽기 (약 1959 단어)

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

MySQL / MaraiDB 서버에 TLS 를 활성화 해서 암호화 통신을 할 수 있다. MySQL (MariaDB도 동일함)에서는 서버-클라이언트 사이에 전송되는 데이터를 TLS (이전 SSL) 프로토콜을 이용하여 암호화하여 DB 정보가 노출되지 않게 방지할 수 있다. SSL을 통해서 Replication Master - Slave 도 가능하다.

글 타래:

글 진행 순서:

사설 CA 인증서 생성
클라이언트 인증서 생성
사설 CA 인증서 설정

시스템 사양

Armbian 23.8.1 Bullseye / mariadb-server-10.5

1. 사설 CA 인증서 생성

openssl을 사용해서 ssl_cert, ssl_key, ssl_ca 에 사용하는 서버용 사설 인증서를 생성한다.

openssl을 이용한 사설 인증서 생성에 대해서는 HTTPS 를 위한 Private SSL 기사도 참조할 수 있다.

이 글에서는 아래 참고1, 참고2 를 적용했다.

인증서는 사설 CA(Certificate Authority)용 인증서, 서버용, 클라이언트용 총 3가지를 만든다.

인증서 저장 위치 생성

1 2	(SERVER) $ sudo mkdir /etc/ssl/mysql (SERVER) $ cd /etc/ssl/mysql

생성한 TLS 인증서는 MySQL/MariaDB 의 my.cnf 설정파일의 변수를 3가지 설정한다.

ssl_cert 시스템 변수: 서버 인증서(X509) 경로 지정
ssl_key 시스템 변수: 서버 개인키 경로 지정
ssl_ca 혹은 ssl_capath 시스템 변수: Self-signed CA certificate CA 키 경로

openssl 사설 CA 인증서 생성

저장 위치 /etc/ssl/mysql 에서 관리용 CA 인증서 생성한다.

먼저 openssl에서 rsa 알고리즘으로 4096 크기 비밀키를 생성한다.

1
2
3

(SERVER) $ sudo openssl genrsa -out ca-key.pem 4096
Generating RSA private key, 4096 bit long modulus (2 primes)
...

비밀키 파일 ca-key.pem 파일을 사용해서 CA인증서 ca.pem 파일로 생성한다. 비밀키로 CA를 위한 CSR(certificate signing request) 과정을 거쳐 ca.pem 을 생성해9서 필요한 인증서 정보를 묻는다.

(SERVER) $ sudo openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca.pem
...
Country Name (2 letter code) [AU]: KR
State or Province Name (full name) [Some-State]:Seoul
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:YOUR company
Organizational Unit Name (eg, section) []: Administration
Common Name (e.g. server FQDN or YOUR name) []::mysql-admin.DOMAIN.name
Email Address []:

현재까지

1
2
3

(SERVER) $ ls -l
-rw------- 1 root root 3243 Jul 16 15:04 ca-key.pem
-rw-r--r-- 1 root root 1972 Jul 16 15:11 ca.pem

서버 인증서 생성

서버용 인증서 생성한다. 단, openssl 명령 과정에서 Common Name 을 3가지 모두 다르게 해야 검증오류를 피할 수 있다.

(SERVER) $ sudo openssl req -newkey rsa:4096 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:Seoul
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:YOUR company
Organizational Unit Name (eg, section) []:Administration
Common Name (e.g. server FQDN or YOUR name) []:server.DOMAIN.name
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
...

현재 디렉토리

(SERVER) $ ls -l
-rw------- 1 root root 3243 Jul 16 15:04 ca-key.pem
-rw-r--r-- 1 root root 1972 Jul 16 15:11 ca.pem
-rw------- 1 root root 3272 Jul 16 15:15 server-key.pem
-rw-r--r-- 1 root root 1704 Jul 16 15:16 server-req.pem

RSA 알고리즘으로

1 2	(SERVER) $ sudo openssl rsa -in server-key.pem -out server-key.pem writing RSA key

CA 비밀키 ca-key.pem 를 사용해 X509 인증서를 사이닝한다.

(SERVER) $ sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = KR, ST = Seoul, O = Thinkbee company, OU = Administration, CN = admin.thinkbee.kr
Getting CA Private Key

1 2	(SERVER) $ sudo openssl verify -CAfile ca.pem server-cert.pem server-cert.pem: OK

현재 디렉토리

(SERVER) $ ls -l
-rw------- 1 root root 3243 Jul 16 15:04 ca-key.pem
-rw-r--r-- 1 root root 1972 Jul 16 15:11 ca.pem
-rw-r--r-- 1 root root 1862 Jul 16 15:20 server-cert.pem
-rw------- 1 root root 3243 Jul 16 15:19 server-key.pem
-rw-r--r-- 1 root root 1704 Jul 16 15:16 server-req.pem

2. 클라이언트 인증서 생성

클라이언트 인증서는 다음과 같은 용도로 사용한다. 단, openssl 명령 과정에서 Common Name 을 3가지 모두 다르게 해야 검증오류를 피할 수 있다.

DB 서버와 별도로 존재하는 웹 서버에서 DB 서버로 SSL 통신을 할 때 웹 서버에 적용
접속하고자 하는 DB 서버와 별도의 리눅스 환경에서 mysql 클라이언트 프로그램으로 DB 서버에 접속할 때 클라이언트에 적용
Replication 에서 Master와 Slave 간의 SSL 통신을 하고자 할 때 Slave 서버에 적용

(SERVER) $ sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:Seoul
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []::mysql-client.thinkbee.kr

...

1 2	(SERVER) $ sudo openssl rsa -in client-key.pem -out client-key.pem writing RSA key

(SERVER) $ sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=C = KR, ST = Seoul, O = Internet Widgits Pty Ltd, CN = client-thinkbee.kr
Getting CA Private Key

1 2	(SERVER) $ sudo openssl verify -CAfile ca.pem client-cert.pem client-cert.pem: OK

현재 디렉토리를 접근 권한을 조정한다.

(SERVER) $ sudo chown mysql.mysql *.*
(SERVER) $ ls -l
-rw-r--r-- 1 mysql mysql 3243  7월 16 15:43 ca-key.pem
-rw-r--r-- 1 mysql mysql 1984  7월 16 15:46 ca.pem
-rw-r--r-- 1 mysql mysql 1497  7월 16 15:51 client-cert.pem
-rw-r--r-- 1 mysql mysql 1679  7월 16 15:51 client-key.pem
-rw-r--r-- 1 mysql mysql  989  7월 16 15:50 client-req.pem
-rw-r--r-- 1 mysql mysql 1838  7월 16 15:48 server-cert.pem
-rw------- 1 mysql mysql 3243  7월 16 15:48 server-key.pem
-rw-r--r-- 1 mysql mysql 1671  7월 16 15:47 server-req.pem

파일 모드를 644 으로 변경한다.

1	(SERVER) $ sudo chmod 644 .

인증서의 내용 확인방법

1
2
3

(SERVER) $ openssl x509 -text -in ca.pem
(SERVER) $ openssl x509 -text -in server-cert.pem
(SERVER) $ openssl x509 -text -in client-cert.pem

3. 사설 CA 인증서 설정

my.cnf 에 TLS 를 위한 구성을 해야 한다. 주로 ssl_cert, ssl_key, ssl_ca 에 대한 인증서 파일을 지정해 준다.

ssl_cert 시스템 변수: X509 인증서 경로 지정
ssl_key 시스템 변수: 서버 개인키 경로 지정
ssl_ca 혹은 ssl_capath 시스템 변수: Certificate Authority (CA) 경로

TLS 활성화

참고3 의 mysql 설정파일 my.cnf 에 따르면 아래 변수가 설정되야 한다.

[mariadb]
...
ssl_cert = /etc/ssl/mysql/server-cert.pem
ssl_key = /etc/ssl/mysql/server-key.pem
ssl_ca = /etc/ssl/mysql/ca.pem

서버를 재시작한다.

TLS 가 활성화 되었는지 환경변수로 확인할 수 있다.

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| have_openssl        | YES                            |
| have_ssl            | YES                            |
| ssl_ca              | /etc/ssl/mysql/ca.pem          |
| ssl_capath          |                                |
| ssl_cert            | /etc/ssl/mysql/server-cert.pem |
| ssl_cipher          |                                |
| ssl_crl             |                                |
| ssl_crlpath         |                                |
| ssl_key             | /etc/ssl/mysql/server-key.pem  |
| version_ssl_library | OpenSSL 1.1.1n  15 Mar 2022    |
+---------------------+--------------------------------+
10 rows in set (0.003 sec)

이렇게 구성한 TLS 인증서는 만료기간이 있다. 아래 같이 만료 기간을 확인 할 수 있다.

>  SHOW STATUS LIKE 'Ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | Nov 16 06:48:41 3022 GMT |
| Ssl_server_not_before | Jul 16 06:48:41 2023 GMT |
+-----------------------+--------------------------+

mysql client TLS 사용 접속

mysql 클라이언트로 TLS 인증서를 사용해서 접속해 보자.

1	(SERVER) $ mysql -u root -p -h localhost --ssl=TRUE --ssl-ca=/etc/ssl/mysql/ca.pem --ssl-cert=/etc/ssl/mysql/client-cert.pem --ssl-key=/etc/ssl/mysql/client-key.pem

접속한 클라인트에서 status 를 살펴보면 TLS 로 접속한 내역을 SSL: Cipher in use is TLS_AES_256_GCM_SHA384 항목으로 확인할 수 있다.

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.11.2-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper

Connection id:          315
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.11.2-MariaDB-1:10.11.2+maria~deb11 mariadb.org binary distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb3
Conn.  characterset:    utf8mb3
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 6 min 54 sec

Threads: 69  Questions: 2386  Slow queries: 0  Opens: 37  Open tables: 30  Queries per second avg: 5.763
--------------