[MariaDB] 외부접속시 TLS/SSL 활성화 (feat. OpenSSL)

재직하고 있는 회사에서 한 서버에 api 서버와 db 서버가 같이 있어 보안에 별로 신경을 안 썼다.

하지만 회사가 조금 커지고 서버도 더 사주셔서 웹서버와 db 서버가 실질적으로 분리되었고,

보안에 조금씩 신경 쓰게 되었다.

첫 번째로 설정한 것이 TLS/SSL 통신이다.

 

테스트 환경

ubuntu 18.04

MariaDB 10.5.13

TLS 설정이 필요한 이유

요약 : 기본값으로 암호화 통신을 안 해서 트래픽 탈취 등의 보안 문제가 발생할 수 있다.

 

기본적으로 MariaDB는 암호화하지 않고 서버와 클라이언트 간에 데이터를 전송합니다. 이는 서버와 클라이언트가 동일한 호스트에서 실행되거나 다른 수단을 통해 보안이 보장되는 네트워크에서 실행될 때 일반적으로 허용됩니다. 그러나 서버와 클라이언트가 별도의 네트워크에 있거나 고위험 네트워크에 있는 경우 암호화가 없으면 악의적인 행위자가 트래픽이 둘 사이의 네트워크를 통해 전송될 때 잠재적으로 도청할 수 있으므로 보안 문제가 발생합니다.

securing-connections-for-client-and-server/

Securing Connections for Client and Server

By default, MariaDB transmits data between the server and clients without encrypting it. This is generally acceptable when the server and client run on the same host or in networks where security is guaranteed through other means. However, in cases where the server and client exist on separate networks or they are in a high-risk network, the lack of encryption does introduce security concerns as a malicious actor could potentially eavesdrop on the traffic as it is sent over the network between them.

https://susoterran.github.io/mysql/mysql_ssl/

[MySQL/MariaDB] MySQL/MariaDB SSL 적용하기

 

db 상태 확인

버전 확인

select version();

설정값 확인

show variables like '%ssl%';

have_ssl이 disabled 인 것을 확인할 수 있다.  (비활성화)

https://mariadb.com/kb/en/ssltls-system-variables/

SSL/TLS System Variables

인증서 생성

인증서는 서버든 클라이언트 어느 쪽에서든 만들어서 사용 및 설정할 수 있다.

pen 키 상세 설명

▶︎ 인증서와 키가 생성되는 케이스

- OpenSSL옵션을 사용하여 MySQL을 컴파일 했을 경우 MySQL서버를 시작할 때 자동으로 생성이 됩니다.

- mysql_ssl_rsa_setup이란 프로그램을 수동으로 실행하여 생성할 수 있습니다.

- RPM으로 설치 후 data디렉토리 초기화시 자동으로 mysql_ssl_rsa_setup을 호출하여 생성합니다.

 

▶︎ SSL, RSA키 파일 생성

- 자동 키 파일 생성.

MySQL 환경 옵션중에  auto_generate_certs와 sha256_password_auto_generate_rsa_keys 라는 환경 옵션에 의해서 SSL, RSA키 파일이 서버 시작시 생성됩니다. 이 옵션들은 기본으로 사용으로 활성화 됩니다.

서버가 시작되면 auto_generate_certs환경변수에 의해 서버와 클라이언트에서 사용되는 SSL 인증서 키 파일들이 데이터 디렉토리에 자동으로 생성됩니다. 만약 서버를 시작할때 --ssl 옵션을 NO로 하셨다면 서버쪽의 SSL 키 파일들은  데이터 디렉토리에 생성되지 않습니다. 자동으로 생성되는 파일들은 다음과 같은 것들이 있습니다.

ca.pem               Self-signed CA certificate
ca-key.pem           CA private key
server-cert.pem      Server certificate
server-key.pem       Server private key
client-cert.pem      Client certificate
client-key.pem       Client private key

 

sha256_password_auto_generate_rsa_keys 파라미터가 사용으로 되어 있으면 다음과 같은 파일이 생성됩니다.

private_key.pem      Private member of private/public key pair
public_key.pem       Public member of private/public key pair

 

- 인증서의 내용 확인방법

shell > openssl x509 -text -in ca.pem
shell > openssl x509 -text -in server-cert.pem
shell > openssl x509 -text -in client-cert.pem

각 항목마다 -----BEGIN CERTIFICATE-----, -----END CERTIFICATE----- 가 나오고 많은 양의 내용물이 출력됩니다.

 

▶︎ 인증서 시작 및 만료 기간 확인

다음과 같은 명령어로 확인합니다.

mysql> SHOW STATUS LIKE 'Ssl_server_not%';
+-----------------------+--------------------------+
| Variable_name         | Value                    |
+-----------------------+--------------------------+
| Ssl_server_not_after  | Nov 22 22:38:01 2029 GMT |
| Ssl_server_not_before | Nov 25 22:38:01 2019 GMT |
+-----------------------+--------------------------+
2 rows in set (0.01 sec)

 

 

(서버) OpenSSL을 이용한 사설 인증서 생성

https://susoterran.github.io/mysql/mysql_privatecertificate/

[MySQL/MariaDB] MySQL/MariaDB에서 사용할 사설 SSL 인증서 생성하기

인증서를 관리용, 서버용, 클라이언트용 총 3가지를 만든다.

 

1. private key 생성

cd /etc/mysql
mkdir certs

cd certs

openssl genrsa 2048 > ca-key.pem

오류 1 -bash: ca-key.pem: Permission denied

sudo openssl genrsa 2048 > ca-key.pem을 해도 똑같다.

 

이건 권한이 없는 건데 제일 쉬운 방법은 그 폴더 또는 파일에  "관리자"(su) 권한을 가지는 것이다.

제일 쉬운 방법은 sudo su 로 아예 admin 계정으로 진행하거나

그 폴더 또는 파일의 권한을 가져도 된다.

근데 su 권한을 가질 수 없다면

그냥 개인 공간에 만들어도 된다.

하지만 해킹에 조심해야한다고 한다.

sudo 권한 및 개인 저장 공간

https://mta.openssl.org/pipermail/openssl-users/2015-April/001081.html

[openssl-users] Permission denied while creating the key

권한 변경

https://worthpreading.tistory.com/m/89

[Homebrew] Permission denied 해결법

 

 

2.  x509 인증서 생성

자세한 옵션 설명

https://www.openssl.org/docs/man1.1.1/man1/openssl-req.html

/docs/man1.1.1/man1/openssl-req.html

★주의★

아래 admin server client 인증서를 만들 경우 "Common Name"을 다 다르게 적어주어야 한다.

그러지 않은 경우 에러가 남.

 

2.1. admin 인증서 

# sudo를 붙이지 않은 경우 Permission Denied 가 뜰 수 있다.
 openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem

 

위 명령어를 실행하면 ca-key.pem과 ca-cert.pem 파일이 생성됩니다.

ca-key.pem은 개인 키, ca-cert.pem은 공개 인증서입니다.

# sudo를 붙이지 않은 경우 Permission Denied 가 뜰 수 있다.
sudo openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.pem
Can't load /home/test/.rnd into RNG
140562180940224:error:2406F079:random number generator:RAND_load_file:Cannot open file:../crypto/rand/randfile.c:88:Filename=/home/test/.rnd
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:
Organizational Unit Name (eg, section) []:SW team
Common Name (e.g. server FQDN or YOUR name) []:MariaDB Admin
Email Address []:

 

상세설명

• openssl
   OpenSSL을 실행하기 위한 명령입니다.
• req
   생성을위한 OpenSSL 유틸리티입니다. CSR.
• -new rsa:2048
   OpenSSL에 새로운 2048 비트 RSA 개인 키를 생성하도록 지시합니다. 4096 비트 키를 선호하는 경우, 이 숫자를 다음과 같이 변경할 수 있습니다. 
  4096
• -x509
  인증서 요청 대신 자체 서명된 인증서를 출력합니다.
• -days 365000
  -x509 옵션을 사용할 때 인증서를 인증할 일 수를 지정합니다. 그렇지 않으면 무시됩니다. n은 양의 정수여야 합니다. 기본값은 30일입니다.
• -key ca-key.pem
  개인 키를 읽을 파일을 지정합니다.
• -out ca-cert.pem
  이것은 새로 생성된 개인 키를 쓸 파일 이름을 제공합니다. 이 옵션을 지정하지 않으면 구성 파일에 있는 파일 이름이 사용됩니다.

 

오류 2 Can't open ca-cert.pem for writing, Permission denied

sudo를 안 붙였을 경우 

# su 권한이 없는 경우 이런 에러가 나올 수 있다.
Can't load /home/test/.rnd into RNG
140627432583616:error:2406F079:random number generator:RAND_load_file:Cannot open file:../crypto/rand/randfile.c:88:Filename=/home/test/.rnd
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:GwangJu
Locality Name (eg, city) []:
Organization Name (eg, company) [Internet Widgits Pty Ltd]:test
Organizational Unit Name (eg, section) []:
Common Name (e.g. server FQDN or YOUR name) []:MariaDB Admin
Email Address []:
Can't open ca-cert.pem for writing, Permission denied
140627432583616:error:0200100D:system library:fopen:Permission denied:../crypto/bio/bss_file.c:72:fopen('ca-cert.pem','w')
140627432583616:error:2006D002:BIO routines:BIO_new_file:system lib:../crypto/bio/bss_file.c:81:

 

2.2. server 인증서

sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

sudo openssl rsa -in server-key.pem -out server-key.pem
 
sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pe

sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
[sudo] password for teest:
Ignoring -days; not generating a certificate
Can't load /home/test/.rnd into RNG
140049027703232:error:2406F079:random number generator:RAND_load_file:Cannot open file:../crypto/rand/randfile.c:88:Filename=/home/test
/.rnd
Generating a RSA private key
....................+++++
..........................................................................................................+++++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:
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) []:MairaDB Server
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
test@t:/etc/mysql/certs$ sudo openssl rsa -in server-key.pem -out server-key.pem
writing RSA key
test@t:/etc/mysql/certs$ sudo openssl x509 -req -in server-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=C = KR, ST = Some-State, O = Internet Widgits Pty Ltd, CN = MairaDB Server
Getting CA Private Key

2.3. 클라이언트용 인증서 생성

sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
 
sudo openssl rsa -in client-key.pem -out client-key.pem

sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

sudo openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Ignoring -days; not generating a certificate
Can't load /home/test/.rnd into RNG
140289980740032:error:2406F079:random number generator:RAND_load_file:Cannot open file:../crypto/rand/randfile.c:88:Filename=/home/test/.rnd
Generating a RSA private key
.............................................+++++
.+++++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [AU]:KR
State or Province Name (full name) [Some-State]:
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) []:MairaDB Client
Email Address []:

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

test@t:/etc/mysql/certs$ sudo openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
test@t:/etc/mysql/certs$ sudo openssl x509 -req -in client-req.pem -days 365000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=C = KR, ST = Some-State, O = Internet Widgits Pty Ltd, CN = MairaDB Client
Getting CA Private Key

2.4. 생성된 인증서 검증

ls -al

2.5. 파일 소유 권한 변경

mariadb 서버가 접근 가능하도록 인증서 파일 소유를 mysql:mysql로 변경

# 권한 바꾸는 방법 1
cd /etc/mysql/certs
sudo chown mysql. *

# 권한 바꾸는 방법 2
sudo chown -R mysql:mysql /etc/mysql/certs

 

 

database에 ssl 활성 및 인증서 추가

https://mariadb.com/kb/en/securing-connections-for-client-and-server/

TLS/SSL 통신 지원 활성화

ssl 접속을 지원을 하기 위해선 ssl 접속 활성화를 해주어야 한다.

설정을 한다고 해서 ssl 아닌 접속이 거부되는 건 아니지만

설정을 안 한다면 ssl 접속은 불가능하다.

ssl=on 또는 ssl 추가 및 ssl 인증서 추가

/etc/mysql/mariadb.conf.d/50-server.cnf 파일 수정

(버전 또는 인스톨 방법 등등에 따라 경로는 다를 수 있음)

 

ssl을 검색하여 (편의를 위해) ssl 을 추가하거나 주석되어있는 ssl=on  속성을 활성화해준다.

기본설정값 ( 다 주석되어있음)

 

ssl=on 또는 ssl 추가 및 ssl 인증서 추가

 

재시작

version 마다 다를 수 있음

service mariadb restart

 

설정 확인

show variables like '%ssl%';

have_ssl 이 disabled에서 YES로 바뀐 것을 확인할 수 있다.

그러나 ssl로 접속할 수 있는 준비가 끝났을 뿐 인증서 등은 전혀 사용하지 않는 상태이다.

인증서를 연결해주자.

 

SSL 접속 (Windows & HeidiSQL)

기본정보 입력 : ip, 사용자명, 암호, 포트

SSL 사용 체크

접속 잘 된다.

윈도우는 따로 ssl 인증서를 등록하지 않아도 된다고 한다.

 

인증서가 꼭 필요한 경우 ssl 개인 키, ssl ca인증서, ssl 인증서, ssl 암호 등 위에서 발급받은 인증서들을 클라이언트 쪽에 카피하여  경로를 적어준다. 상세한 내용은 아래 ubuntu 쪽에서 확인 가능하다.

 

SSL 접속 확인

status를 통하여 ssl 사용하고 있는지 확인하거나

ssl 접속 안했을 경우

아래 명령어를 실행하여 Value값이 나온다면 설정이 잘 되고 잘 접속되는 것이다.

SHOW SESSION STATUS LIKE 'Ssl_cipher';

ssl 접속시

ssl 접속 안했을 경우

SSL 접속 (ubuntu)

ssl  인증서 다운로드

위에서 만든 인증서(ca-cert.pem, client-cert.pem, client-key.pem)들을 클라이언트 PC에 다운로드한다.

만드는 사람마다 pem 키의 이름이 다를 수 있는다.

ca-cert.pem, client-cert.pen, client-key.pem

 

접속 (옵션)

기본정보 입력:  ip, 사용자명, 암호, 포트

# mariadb -u[계정명] -p -h[ip or host] -P[port] --ssl-ca=[ca.pem 경로] --ssl-cert=[client-cert.pem 경로] --ssl-key=[client-key.pem 경로]
mariadb -utest -p -h192.168.0.2 -P3306 --ssl=TRUE --ssl-ca=/etc/mysql/ca-cert.pem --ssl-cert=/etc/mysql/client-cert.pem --ssl-key=/etc/mysql/client-key.pem

ssl 인증서 설정

mysql client 설정(버전마다 install 방법에 따라 경로는 다를 수 있다.)에서 이 인증키들을 설정해주면 그냥 접속해도 ssl로 접속된다.

vim /etc/mysql/mariadb.conf.d/50-client.cnf

# 50-client.cnf
#
# This group is read by the client library
# Use it for options that affect all clients, but not the server
#

[client]
# Example of client certificate usage
ssl-cert = /etc/mysql/client-cert.pem # 경로 적어줌
ssl-key  = /etc/mysql/client-key.pem # 경로 적어줌
#
# Allow only TLS encrypted connections
# ssl-verify-server-cert = on

# This group is *never* read by mysql client library, though this
# /etc/mysql/mariadb.cnf.d/client.cnf file is not read by Oracle MySQL
# client anyway.
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

접속 (설정)

# mariadb -u[계정명] -p -h[ip or host] -P[port]
mariadb -utest -p -h192.168.0.2 -P3306

잘된다.

프로젝트 SSL 연결 (Python - SQL Alchemy)

ssl 경로 적어주기 

다양한 요인에 의하여 문법 등이 달라질 수 있다.

connect_args={'ssl':{    "sslca": "/etc/mysql/ca-cert.pem"  }},

인터넷에 찾아보면 아래 문법이 가장 많이 나오는데 나는 되지 않았다.

connect_args={'ssl_context': ssl_context},

예시 전체 문법

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import ssl

ssl_context = ssl.SSLContext()

SQLALCHEY_DATABASE_URL = 'mysql+pymysql://test:test123@127.0.0.1/test_db'

engine = create_engine(
  SQLALCHEY_DATABASE_URL,
  connect_args={'ssl':{
    "sslca": "/etc/mysql/ca-cert.pem"
  }},
  # connect_args={'ssl_context': ssl_context},
  echo=True,
  pool_recycle=900,
  pool_pre_ping=True
)

SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)

def get_db():
  db = SessionLocal()
  try:
    yield db
  finally:
    db.close()

Base = declarative_base()

확인하기

통신을 확인해보면 알 수 있는데 식자는 할 줄 모른다.

 

그래서 쉽게 할 수 있는 그냥 함수 실행을 하였다.

SHOW SESSION STATUS LIKE 'Ssl_cipher'

예시 함수

async def text(db: Session = Depends(get_db)):
  """
  `test`
  """
  ssl = db.execute(text("SHOW SESSION STATUS LIKE 'Ssl_cipher'")).fetchall()
  return ssl

 

SSL 외부 접속 설정 (작성 중)

위에 적었다시피 ssl 옵션을 활성화 한다고 "지원"을 해주는것이지,  ssl 접속이 강제되는것은 아니다.

그리하여 아래에는 ssl 접속을 강제하는 설정을 작성해 본다.

1. 계정

2. 모두

 

특정 계정에 대해 무조건 SSL 접속을 하도록 설정

계정 생성시 "REQUIRE SSL" 옵션을 주면 된다.

-- 권한부여
grant all privileges on *.* to 'test'@'127.0.0.1' identified by 'test1234' REQUIRE SSL;

-- 권한 조회
show grants for test;

옵션을 줄 경우 아래와 같이 에러가 난다.

SSL 접속 안했을 경우 오류

 

 

참고

https://mariadb.com/kb/ko/data-in-transit-encryption

Data-in-Transit Encryption

https://ddart.net/xe/board/12867

DDART - MariaDB 외부접속시 ssl 사용법, 그리고 ssl 로 replication(동기화) 하기

https://myinfrabox.tistory.com/14

[MySQL][Security] SSL을 이용한 접속방법

https://susoterran.github.io/mysql/mysql_ssl

[MySQL/MariaDB] MySQL/MariaDB SSL 적용하기

https://susoterran.github.io/mysql/mysql_privatecertificate/

[MySQL/MariaDB] MySQL/MariaDB에서 사용할 사설 SSL 인증서 생성하기

https://mariadb.com/kb/en/certificate-creation-with-openssl/

 

 

CN (Common Name)

▶︎ 서버 환경 파라미터

--ssl-ca: 인증기관에서 발행해주는 인증키입니다. 보통 인증기관을 CA(Certificate Authority)라고 하는데 이곳에서 발행해준 인증서 파일 이름을 입력합니다. --ssl-capath라는 옵션이 있는데 이것은 인증서파일들(CA certificataion file)이 있는 디렉토리를 지정하는 옵션입니다.

--ssl-cert : 공개키 파일이 있는 위치와 파일명 입니다. 이 인증서는 클라이언트에게 보내지고 CA인증서에 다시 인증을 받게 됩니다.

--ssl-key : 서버 개인키 파일

What is a Common Name (CN)?

Solution

SSL Certificates

• The Common Name (CN), also known as the Fully Qualified Domain Name (FQDN), is the characteristic value within a Distinguished Name (DN). 
  Typically, it is composed of Host Domain Name and looks like, "www.digicert.com" or "digicert.com".
  The Common Name field is often misinterpreted and is filled out incorrectly.
  Do not use your organization's name as your common name.

• Before you can enroll for a certificate, you must generate a Certificate Signing Request (CSR) from your Web server.
  While generating a CSR, you will be required to enter information in the Common Name field.
   
• Certificates are specific to the Common Name that they have been issued to at the Host level.
  The Common Name must be the same as the Web address you access when connecting to a secure site.
  For example, a Server ID for the domain "digicert.com" will receive a warning if accessing a site named "www.digicert.com" or "knowledge.digicert.com" because "www.digicert.com" and "knowledge.digicert.com" are different from the Common Name, "digicert.com".
  In this case, you must create a CSR for the correct Common Name to resolve the problem.

Code Signing

• The Common Name for Code Signing is not related to a Fully Qualified Domain Name (FQDN) and should be identical to the Organization Name. Code Signing ID's are for digitally signing code and do not utilize any specific URL's.

1. CN은 두 가지로 사용된다.

보통 "웹서버"를 위한 SSL 인증서를 발급받는 경우 

Common Name 에는 인증서를 설치할 사이트의 도메인의 이름을 넣어야 한다. (ip, port, http, https 포함불가능)

ex) *.example.com

 

그러나 "DB 접속"을 위한 SSL 인증서는 구분을 위한  "name"을 적는 경우가 많다. (옵션 -x509)

ex) MySQL admin, MySQL server, MySQL user

 

 

2. 인증서 만들때 과정에서 물어보는 Common Name은 고유해야합니다. (같을 경우 검증시 오류남)

왜? Common Name은 고유해야 하는가?
common name을 기준으로 ssl 인증서가 관리되는데 이름이 중복되면 등록이 안된다고 할 수 있다.

 

 

 

https://myinfrabox.tistory.com/14

[MySQL][Security] SSL을 이용한 접속방법

https://knowledge.digicert.com/solution/SO7239.html

What is a Common Name (CN)?

https://support.dnsimple.com/articles/what-is-common-name/

What is the SSL Certificate Common Name? - DNSimple Help