0

By default mysql server creates these 6 certificates in /var/mysql/lib:

-rw------- 1 mysql mysql 1676 Aug  3 00:16 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Aug  3 00:16 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Aug  3 00:16 client-cert.pem
-rw------- 1 mysql mysql 1676 Aug  3 00:16 client-key.pem
-rw------- 1 mysql mysql 1680 Aug  3 00:16 private_key.pem
-rw-r--r-- 1 mysql mysql  452 Aug  3 00:16 public_key.pem
-rw-r--r-- 1 root  root  1172 Aug  6 12:20 server-cert.pem
-rw------- 1 mysql mysql 1676 Aug  3 00:16 server-key.pem

As mentioned in https://dev.mysql.com/doc/mysql-security-excerpt/5.7/en/creating-ssl-rsa-files-using-mysql.html , public_key.pem and private_key.pem are used in:

These key-pair files enable secure password exchange using RSA over unencrypted connections for accounts authenticated by the sha256_password plugin;

If I understand correctly, the other 6 certificates are used for mutual tls verification.

I tested it with DataGrip and there are 3 options: Require, Verifa CA and Full Verification. (commandline interface e.g. mysqlsh has corresponding options)

  • Require has nothing to with certificates, it starts a tls encryption with server as ordinary https does.
  • Verify CA verifies the ca.pem, which you get from the server /var/lib/ca.pem.
  • Full Verification is the client verifying not only the ca.pem, but also server-cert.pem and server-key.pem.
    ( you need to re-generate a server-cert.pem for this work. Because the auto-generated server-cert.pem 's Subject: field doesn't match the server ip/domain)

enter image description here

None of the options reuire specifying client cert and key. So what's the usage of proving client-key.pem and client-cert.pem in the client? I don't find an option like "verify the client" in mysql server. So it's merely one way verification and client certs are completely useless? I don't get it.

1 Answer 1

2

You must have missed this in the documentation: when you create a user, you can specify various TLS authentication options. For example,

CREATE USER 'rick'@'%' REQUIRE SUBJECT 'CN=Rick' AND ISSUER '...'
1
  • Yes, the linked documentaion explains how client certs work. I tried CREATE USER 'rick'@'%' IDENTIFIED BY 'password' require X509; in the X509 section and had some experiments with my client. It matches my expectation. Thanks!
    – Rick
    Commented Aug 7, 2022 at 16:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.