[MDEV-24223] setting up encryption Created: 2020-11-16  Updated: 2022-05-10  Resolved: 2022-05-10

Status: Closed
Project: MariaDB Server
Component/s: Encryption
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Michael Necklas Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

trying to find the path for ssl when its statically linked vs. dynamically linked.

the have_ssl variable is disabled.

I need the variable settings for the .my.cnf file...

tls_version=TLSv1.1,TLSv1.2,TLSv1.3
#ssl_cert = /etc/my.cnf.d/certificates/server-cert.pem
#ssl_key = /etc/my.cnf.d/certificates/server-key.pem
#ssl_ca = /etc/my.cnf.d/certificates/ca.pem



 Comments   
Comment by Daniel Black [ 2020-11-16 ]

Sorry to come back with more questions than asked, I just don't know what you are asking.

Are you talking about compiling the server? Or the connector library? Or using the server?

What path are you talking about? The ssl certs (that you've made absolute)? The ssl libraries, include headers.

Are you using .my.cnf as a server config or client config?

What MariaDB major version? What OS/distro version?

Comment by Michael Necklas [ 2020-11-17 ]

i have an existing MariaDB testing environment with a master + read only slave setup. 10.4.12. I'm trying to setup encryption for 1 account.

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tls_version';
--------------------------------------+

Variable_name Value

--------------------------------------+

tls_version TLSv1.1,TLSv1.2,TLSv1.3

--------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%SSL%';
----------------------------------+

Variable_name Value

----------------------------------+

have_openssl NO
have_ssl DISABLED
ssl_ca  
ssl_capath  
ssl_cert  
ssl_cipher  
ssl_crl  
ssl_crlpath  
ssl_key  
version_ssl_library WolfSSL 4.3.0

----------------------------------+
10 rows in set (0.001 sec)

so i'm trying to enable encryption and yes I'm using .my.cnf

unlike mysql the .pem files don't exist under the data directory.
for mysql i add these records to the .my.cnf and bounce the instance.

[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
tls_version=TLSv1.2

unlike mysql for MariaDB i have to show the absolute path for the 3 pem files.

i ran the ldd and no pem files. i checked under the product directory for the 10.4.12 release and no pem files

$ ldd /opt/app/mysql/product/mariadb-10.4.12/bin/mysqld
linux-vdso.so.1 => (0x00007fff361d4000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f85e30d8000)
librt.so.1 => /lib64/librt.so.1 (0x00007f85e2ecf000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f85e2cce000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f85e2a97000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f85e2892000)
libstdc+.so.6 => /usr/lib64/libstdc+.so.6 (0x00007f85e258c000)
libm.so.6 => /lib64/libm.so.6 (0x00007f85e2308000)
libc.so.6 => /lib64/libc.so.6 (0x00007f85e1f73000)
/lib64/ld-linux-x86-64.so.2 (0x00005611849d5000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007f85e1d70000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f85e1b5a000)

so how do i get their paths to updated the .my.cnf ?

Comment by Michael Necklas [ 2020-11-18 ]

https://www.cyberciti.biz/faq/how-to-setup-mariadb-ssl-and-secure-connections-from-clients/

I used the above document and ran the opensssl commands and created the .pem files (i skipped step 1+2+5)
since MariaDB was already installed and running and no client on the databases server.

SSL is still disabled.

i used the openssl commands but wolfssl shows on the variables

see my attached documentation

Comment by Michael Necklas [ 2020-11-18 ]

Forcing connections to the database server for a specific account = external_user to be encrypted!

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tls_version';
--------------------------------------+

Variable_name Value

--------------------------------------+

tls_version TLSv1.1,TLSv1.2,TLSv1.3

--------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%SSL%';
----------------------------------+

Variable_name Value

----------------------------------+

have_openssl NO
have_ssl DISABLED
ssl_ca  
ssl_capath  
ssl_cert  
ssl_cipher  
ssl_crl  
ssl_crlpath  
ssl_key  
version_ssl_library WolfSSL 4.3.0

----------------------------------+
10 rows in set (0.001 sec)

mysql@zlt11307 IDF REPLICATION /opt/app/mysql/data
$ ll data/*.pem
ls: cannot access data/*.pem: No such file or directory
mysql@zlt11307 IDF REPLICATION /opt/app/mysql/data

ca-key.pem CA private key
ca.pem Self-signed CA certificate
client-cert.pem Client certificate
client-key.pem Client private key
private_key.pem Private member of private/public key pair
public_key.pem Public member of private/public key pair
server-cert.pem Server certificate
server-key.pem Server private key

$ ldd /opt/app/mysql/product/mariadb-10.4.12/bin/mysqld
linux-vdso.so.1 => (0x00007fff361d4000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f85e30d8000)
librt.so.1 => /lib64/librt.so.1 (0x00007f85e2ecf000)
libaio.so.1 => /lib64/libaio.so.1 (0x00007f85e2cce000)
libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f85e2a97000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f85e2892000)
libstdc+.so.6 => /usr/lib64/libstdc+.so.6 (0x00007f85e258c000)
libm.so.6 => /lib64/libm.so.6 (0x00007f85e2308000)
libc.so.6 => /lib64/libc.so.6 (0x00007f85e1f73000)
/lib64/ld-linux-x86-64.so.2 (0x00005611849d5000)
libfreebl3.so => /lib64/libfreebl3.so (0x00007f85e1d70000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f85e1b5a000)

cd /opt/app/mysql/data

$ openssl genrsa 4096 > ca-key.pem

$ openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca-cert.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) [XX]:US
State or Province Name (full name) []:Connecticut
Locality Name (eg, city) [Default City]:Guilford
Organization Name (eg, company) [Default Company Ltd]:ATT
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:zlt11307
Email Address []:mike.necklas@att.com

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

Generating a 4096 bit 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) [XX]:US
State or Province Name (full name) []:Connecticut
Locality Name (eg, city) [Default City]:Guilford
Organization Name (eg, company) [Default Company Ltd]:ATT
Organizational Unit Name (eg, section) []:IT
Common Name (eg, your name or your server's hostname) []:zlt11307
Email Address []:mike.necklas@att.com

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

$ openssl rsa -in server-key.pem -out server-key.pem

writing RSA key

$ 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=US/ST=Connecticut/L=Guilford/O=ATT/OU=IT/CN=zlt11307/emailAddress=mike.necklas@att.com
Getting CA Private Key

$ ll *.pem
rw-rr- 1 mysql mysql 2110 Nov 18 08:36 ca-cert.pem
rw-rr- 1 mysql mysql 3247 Nov 18 08:31 ca-key.pem
rw-rr- 1 mysql mysql 1980 Nov 18 10:04 server-cert.pem
rw-rr- 1 mysql mysql 3243 Nov 18 10:01 server-key.pem
rw-rr- 1 mysql mysql 1801 Nov 18 09:02 server-req.pem

$ chmod 600 *key.pem

$ ll *.pem
rw------ 1 mysql mysql 2110 Nov 18 08:36 ca-cert.pem
rw------ 1 mysql mysql 3247 Nov 18 08:31 ca-key.pem
rw------ 1 mysql mysql 1980 Nov 18 10:04 server-cert.pem
rw------ 1 mysql mysql 3243 Nov 18 10:01 server-key.pem
rw------ 1 mysql mysql 1801 Nov 18 09:02 server-req.pem

To enable the server for encrypted connections, start it with these lines in the my.cnf file:

[mysqld]
ssl_ca=/opt/app/mysql/data/ca.pem
ssl_cert=/opt/app/mysql/data/server-cert.pem
ssl_key=/opt/app/mysql/data/server-key.pem
tls_version=TLSv1.2

$ openssl verify -CAfile ca-cert.pem server-cert.pem
server-cert.pem: C = US, ST = Connecticut, L = Guilford, O = ATT, OU = IT, CN = zlt11307, emailAddress = mike.necklas@att.com
error 18 at 0 depth lookup:self signed certificate
OK

bounce it then...

mysql --execute="SET GLOBAL innodb_fast_shutdown=0";/opt/app/platform/init.d/mysql.server stop
/opt/app/platform/init.d/mysql.server start

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'tls_version';
----------------------+

Variable_name Value

----------------------+

tls_version TLSv1.2

----------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%SSL%';
--------------------------------------------------------+

Variable_name Value

--------------------------------------------------------+

have_openssl NO
have_ssl DISABLED
ssl_ca /opt/app/mysql/data/ca.pem
ssl_capath  
ssl_cert /opt/app/mysql/data/server-cert.pem
ssl_cipher  
ssl_crl  
ssl_crlpath  
ssl_key /opt/app/mysql/data/server-key.pem
version_ssl_library WolfSSL 4.3.0

--------------------------------------------------------+
10 rows in set (0.001 sec)

instace came online with this error...

2020-11-18 11:11:27 0 [Warning] Failed to setup SSL
2020-11-18 11:11:27 0 [Warning] SSL error: SSL_CTX_set_default_verify_paths failed

Comment by Michael Necklas [ 2020-11-19 ]

i had a "_" instead of a "-" in the .my.cnf for one of the pem files. so i now have encryption enabled on the MariaDB database server...

MariaDB [(none)]> SHOW VARIABLES LIKE '%SSL%';
--------------------------------------------------------+

Variable_name Value

--------------------------------------------------------+

have_openssl NO
have_ssl YES
ssl_ca /opt/app/mysql/data/ca-cert.pem
ssl_capath  
ssl_cert /opt/app/mysql/data/server-cert.pem
ssl_cipher  
ssl_crl  
ssl_crlpath  
ssl_key /opt/app/mysql/data/server-key.pem
version_ssl_library WolfSSL 4.3.0

--------------------------------------------------------+
10 rows in set (0.001 sec)

$ ll *.pem
rw------ 1 mysql mysql 2122 Nov 19 07:54 ca-cert.pem
rw------ 1 mysql mysql 3243 Nov 19 07:51 ca-key.pem
rw------ 1 mysql mysql 1996 Nov 19 08:04 server-cert.pem
rw------ 1 mysql mysql 3243 Nov 19 08:00 server-key.pem
rw------ 1 mysql mysql 1809 Nov 19 08:00 server-req.pem

this database server does not have the mysql client software installed.

So now i have an account coming from a remote mysql server that has to encrypt their transactions.
if this was a mysql database server i would have them copy these files from the database server to their remote server to use when running their encrypted transaction...

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

So what do they need to do for this MariaDB server connection?

Comment by Michael Necklas [ 2020-11-20 ]

i figured it out. this can be closed.

Generated at Thu Feb 08 09:28:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.