Beginner question about user creation

Polyglot Projects ProxySQL
1

Hey everyone,

actually just started to discover percona xtradb with proxysql as lb and pmm. The cluster itself is basically running and now i’m looking how to create a mysql user with privileges for a db and a caching_sha2 password.

So what do i done?

  • create database
    • create database exampledb;
  • created the user on the cluster:
    • CREATE USER 'exampleuser'@'%' IDENTIFIED WITH 'caching_sha2_password' BY 'Ofoonaishoo4raezi6ainah9xi9raedieW6AiMoochooW2Chie';
  • grant permissions
    • GRANT ALL PRIVILEGES ON exampledb.* TO 'exampleuser'@'%';
  • flush privs
    • FLUSH PRIVILEGES;
  • created user on proxysql
    • INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('exampleuser', '', 1);
  • Got the hex of the password from the cluster
    • SELECT HEX(authentication_string) FROM mysql.user WHERE user = 'exampleuser';
  • updated the proxysql users password with the HEX of cluster user:
    • UPDATE mysql_users SET password=UNHEX('24412430303524434B59784977416E56565F35645C155A6B68090157314C2E61487976566673476B416F533368456A317646476F4562686B4B316B6134444430576B7A4C2F39') WHERE username='exampleuser';
  • apply and save the configuration:
    • LOAD MYSQL USERS TO RUNTIME; SAVE MYSQL USERS TO DISK;

When now trying to connect from a client to the user with:
mysql -u exampleuser -p -h pmysql.domain.tld -P6033

I get the following access denied:
ProxySQL Error: Access denied for user 'exampleuser'@'10.1.99.2' (using password: YES)

I used the following documentations for that:

Do i misunderstood something?

//Edit

So the string within mysql_users/password is the same as within the mysql.users/authentication_string.

I’m able to login with exampleuser from and to the cluster nodes, but not with exampleuser from a client to proxysql:6033 to one of the nodes.

//Edit2

As of here:

This option doesn’t disable any of the authentication methods supported by ProxySQL, just allows to optimize the client-server communication during handshake by making the default authentication announced by the server, match the authentication the client is expected to request. Thus avoiding unnecessary AuthSwitchRequests.

This variable overrides mysql-have_ssl, enabling SSL for fronted connections when set to caching_sha2_password. This is because caching_sha2_password requires a safe channel for performing full authentication, due to clear text password sharing.

So as since i’m new to caching_sha2_authentication and just discovered by this, ssl certificate is in need to be able to get a working authentication.

I just thought to change the default to prevent these AuthSwitchRequests and get a cleared error message which i got:
ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain

Seems like it does not like the self signed certificate. In the past days i bootstrapped the cluster again - does it generates a new ssl certificate then which i manually need to roll out to all nodes again?

As of here:

The auto-generated files are suitable for automatic SSL configuration, but you should use the same key and certificate files on all nodes.

For SST i already deployed the server-key/cert to all nodes. Do i also need to deploy the same client cert and key to all nodes or do they need their own client cert/key?

//Edit3
Last question: Is it still more applicable to use native_authentication_method when dealing with different type of applications? Not every application supports ssl mysql with cert ^^

//Edit 4
So…The cluster nodes got their server and client ssl-cert/key/ca configuration and a restart. Then i created a client certificate from one of the nodes with these commands:

Then i copied the client-cert, client-key and ca.pem to the clients /etc/mysql folder and configured the my.cnf with:
[client]
ssl-ca=/etc/mysql/ca.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem

When now trying to connect to the proxysql i still get:
ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain

So after research i found:

So i think this is still missing to be able to work correct… I will give it a try…will take some time…

2

Yes. All nodes should have the same CA certs.

Every application that links against libmysqlclient (which is 99% of all major coding languages) does support SSL because this is core/baked-in to the client library.

Seems like it does not like the self signed certificate.

There should be a way to override this. self-signed certs are extremely common, even in massive Enterprise environments.

3

So just to be sure:
Every client needs the same client certificate and key? So the 3 nodes need the same as also clients who like to access the named user above and do not create a own client certificate for each client with the same ca.pem and ca-key.pem as mentioned here?

4

Not technically, no. The way SSL works is that you have a Certificate Authority (CA) which is what you trust. From that you create server certs signed by that CA. The server loads these on startup. Then you create client certs, also signed by the CA. The server can verify that client cert X matches “the chain”. You can create multiple client certs that will all validate against the single server cert, or you can create one client cert and copy that to every client app.

5

Okay, so

thats what i have done. Created a client cert for my client which i provided the cert within its my.cnf in the client section including the ca.pem to get it validated for connection.

Do i need the ssl configuration for backends and this is mostly the issue why i still cant connect or in which way i need to dig deeper?

Because the error:
ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain

is still present and i actually cant find that fast this:

//Edit
As for my understanding i’m providing the ca.pem to the clients so they can verify the ca the client cert is coming from. So the self-signed shouldnt be a problem here.

6

Please provide the full command which generates this error.

7

from a deb12 mariadb client:
mariadb -u exampleuser -p -h pmysql.domain.lan -P6033

while pmysql.domain.lan is the proxysql server. clients mariadb my.cnf includes:

[client]
ssl-ca = /etc/mysql/ca.pem
ssl-cert = /etc/mysql/client-cert.pem
ssl-key  = /etc/mysql/client-key.pem

where ca.pem comes from one of the nodes and client-cert and client-key are generated with this with the same ca.pem/ca-key.pem as the nodes client uses.

8

Can you connect directly to mariadb using SSL certs? If so, this appears to be a ProxySQL-SSL issue.

9

Wait… Why are you using mariadb client if you are connecting to Percona XtraDB Cluster? You should be using the mysql client/libraries.

10

openssl verify -CAfile /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/client-cert.pem

cluster node 1:

/var/lib/mysql/server-cert.pem: OK
/var/lib/mysql/client-cert.pem: OK

cluster node 2:

/var/lib/mysql/server-cert.pem: OK
CN = MySQL_Server_8.0.36-28_Auto_Generated_Client_Certificate
error 7 at 0 depth lookup: certificate signature failure
error /var/lib/mysql/client-cert.pem: verification failed
803B7A63497F0000:error:0200008A:rsa routines:RSA_padding_check_PKCS1_type_1:invalid padding:../crypto/rsa/rsa_pk1.c:75:
803B7A63497F0000:error:02000072:rsa routines:rsa_ossl_public_decrypt:padding check failed:../crypto/rsa/rsa_ossl.c:598:
803B7A63497F0000:error:1C880004:Provider routines:rsa_verify:RSA lib:../providers/implementations/signature/rsa_sig.c:774:
803B7A63497F0000:error:06880006:asn1 encoding routines:ASN1_item_verify_ctx:EVP lib:../crypto/asn1/a_verify.c:217:

cluster node 3:

/var/lib/mysql/server-cert.pem: OK
CN = MySQL_Server_8.0.36-28_Auto_Generated_Client_Certificate
error 7 at 0 depth lookup: certificate signature failure
error /var/lib/mysql/client-cert.pem: verification failed
808BE3D7777F0000:error:0200008A:rsa routines:RSA_padding_check_PKCS1_type_1:invalid padding:../crypto/rsa/rsa_pk1.c:75:
808BE3D7777F0000:error:02000072:rsa routines:rsa_ossl_public_decrypt:padding check failed:../crypto/rsa/rsa_ossl.c:598:
808BE3D7777F0000:error:1C880004:Provider routines:rsa_verify:RSA lib:../providers/implementations/signature/rsa_sig.c:774:
808BE3D7777F0000:error:06880006:asn1 encoding routines:ASN1_item_verify_ctx:EVP lib:../crypto/asn1/a_verify.c:217:

While setting up the cluster i transfered the server certificates from cluster node 1 to 2 and 3 with:

rsync -avz /var/lib/mysql/server-cert.pem root@pmysql-2.domain.lan:/var/lib/mysql/server-cert.pem
rsync -avz /var/lib/mysql/server-key.pem root@pmysql-2.domain.lan:/var/lib/mysql/server-key.pem
rsync -avz /var/lib/mysql/ca.pem root@pmysql-2.domain.lan:/var/lib/mysql/ca.pem

rsync -avz /var/lib/mysql/server-cert.pem root@pmysql-3.domain.lan:/var/lib/mysql/server-cert.pem
rsync -avz /var/lib/mysql/server-key.pem root@pmysql-3.domain.lan:/var/lib/mysql/server-key.pem
rsync -avz /var/lib/mysql/ca.pem root@pmysql-3.domain.lan:/var/lib/mysql/ca.pem

The client certificates, which seem to fail, where the auto generated ones from the installation itself. Its getting late… I just remembered that it is probably still the generated client-certs of node 2 and 3 from the generated ca.pem, which must be the one from the primary node everywhere. So I’ll generate new client certificates for both of them tomorrow. I just created new client certs for the client i want to connect from from the ca.pem - but not for node2 and node3. Now it’s time to call it a day.

//Edit

Its never to late ^^ … I created the new client certs/keys on node2 and node3 and the verify says now, that everything is okay. So all 3 nodes have now same ca,server-key,server-cert and each its own client-cert&client-key from the named ca. And all of them got a restart (no bootstrap, just restarted one after the other).

When trying to connect from cluster node to proxysql with mysql client:
mysql -u exampleuser -p -h pmysql.domain.lan -P6033
ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed

When trying to connect from cluster node to another cluster node with mysql client:
mysql -u exampleuser -p -h pmysql-3.domain.lan -P3306

works

When trying to connect from node to itself with mysql client:
mysql -u exampleuser -p -h localhost -P3306

works

when trying from mariadb client to proxysql:
mariadb -u exampleuser -p -h pmysql.domain.lan -P6033
ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain

when trying from mariadb client to cluster node 1:
mariadb -u exampleuser -p -h pmysql-1.domain.lan -P3306
ERROR 2026 (HY000): TLS/SSL error: Validation of SSL server certificate failed

trying from mariadb client to mariadb server cant get reproduced actually cause the server isnt setup with ssl yet (mariadb testserver).

So i really think this now proxysql ssl and mariadb client is bad! :smiley: - but frontend or backend or both? ^^…
client → proxysql = frontend
proxysql → cluster-nodes = backend

11

i test another mysql client tomorrow to be sure, its just proxysql. But for now… :sleeping: :sleeping_bed: Goodnight!

P.S.
I like documentations…if u are having an idea whats missing now, i would like if you know a link with some information about it .- otherwise i will check myself when i’m back.

12

So…i got a MySQL 8 Client setup and i deployed ca.pem,client-cert.pem,client-key.pem and added them to the client section in my.cnf.

With configured main.cnf connecting to the proxysql i get the error:
ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed

When i use a unconfigured mysql 8 client connecting to the proxysql (no edited client section in my.cnf) i can connect without an issue?

But when i’m connected and i’m doing a’n easy show databases; i get:
ERROR 9001 (HY000): Max connect timeout reached while reaching hostgroup 1 after 10000ms

//Edit
So i tested to connect from that test client to node 1 of the cluster, which works with and without SSL.

So the current status is probably:

  • :slightly_smiling_face: SSL/no SSL works for client->backend (will be shut down with fw, need to check how to restrict to just SSL for maintenance connections)
  • :neutral_face: no SSL works for frontend (will check)
  • :pensive: SSL didnt work for frontend (will do proxysql ssl config)
    • have set the global ssl p2s variables for ca,cert,key → They need to be the client certs created on the cluster node, doesnt it?
  • :neutral_face: need to check how to restrict the proxysql for only ssl (will check proxysql docs)
    • about /var/lib/proxysql/proxysql-cert/key/ca → Doesnt it need the same server certs as the nodes to be compatible to the client certs?
    • frontend encryption enforcing in mysql_users.use_ssl ^^ So this is done
  • :pensive: frontend → backend has issues (will check)

I will work now on:

13

You must use ProxySQL’s ssl certificates. /var/lib/proxysql Or install yours in their place. Filenames must match.

I believe that is correct.

Yes

14

Okay, so yeah - then the issue still persists. Because it was much information until now, here is a clear setup description:

Thats should be all…I hope i dont forgot something but my own docs says thats it.

15

Got one thing done…

+-------------+--------+---------+-------------------+---------+----------+
| username    | active | use_ssl | default_hostgroup | backend | frontend |
+-------------+--------+---------+-------------------+---------+----------+
| exampleuser | 1      | 1       | 1                 | 1       | 1        |
+-------------+--------+---------+-------------------+---------+----------+

mysql> select hostgroup_id,hostname,port,status,weight,use_ssl from mysql_servers;
+--------------+------------+------+--------+--------+---------+
| hostgroup_id | hostname   | port | status | weight | use_ssl |
+--------------+------------+------+--------+--------+---------+
| 0            | 10.1.202.2 | 3306 | ONLINE | 1      | 1       |
| 0            | 10.1.202.3 | 3306 | ONLINE | 1      | 1       |
| 0            | 10.1.202.4 | 3306 | ONLINE | 1      | 1       |
+--------------+------------+------+--------+--------+---------+

you got it?^^ … hostgroup… So “no ssl” from client to proxysql works now.

ssl client to proxysql still fails with:
ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed

ssl and “no ssl” works to cluster node directly with that user.

So actually only the proxysql frontend ssl still seems to have a problem, cause for backend ssl testing:

root@test:~# mysql -h pmysql.domain.lan -P6033 -uexampleuser -pOfoonaishoo4raezi6ainah9xi9raedieW6AiMoochooW2Chie -e 'SHOW SESSION STATUS LIKE "Ssl_cipher"'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+

that seem to work…

So its now all about the proxysql’s cert in /var/lib/proxysql …

But thats already the case - got from pxc node 1 → proxysql
/var/lib/mysql/ca.pem → /var/lib/proxysql/proxysql-ca.pem
/var/lib/mysql/server-cert.pem → /var/lib/proxysql/proxysql-cert.pem
/var/lib/mysql/server-key.pem → /var/lib/proxysql/proxysql-key.pem

So it should be fine? Do I have to reload the changed ProxySQL certificates somehow?
As of here SSL Support - ProxySQL i just need to update mysql-have_ssl variable and load to runtime (and disk probably) - which i did.

or does the server certificate in /var/lib/proxysql-cert.pem (and key.pem and ca.pem) need to be a own server certificate and not the same as the nodes, just generated with the same ca.pem?

//Edit

There is the language of client certificates. I have now also tested it. Same error. So both with its self-generated with its own ca.pem and the server & client certificates like the ca.pem from the PXC nodes what is the plan to.

root@pmysql:/var/lib/proxysql# ls -ahl
total 1.4M
drwxr-xr-x  2 proxysql proxysql 4.0K Aug  8 23:43 .
drwxr-xr-x 22 root     root     4.0K Aug  5 11:34 ..
-rw-r--r--  1 root     root     1.1K Aug  8 23:26 ca.pem
-rw-r--r--  1 root     root     1.1K Aug  8 23:26 client-cert.pem
-rw-r--r--  1 root     root     1.7K Aug  8 23:26 client-key.pem
-rw-r-----  1 proxysql proxysql 220K Aug  8 23:32 proxysql.db
-rw-rw----  1 proxysql proxysql  831 Aug  8 23:36 proxysql.log
-rw-r--r--  1 proxysql proxysql    5 Aug  5 11:35 proxysql.pid
-rw-r-----  1 proxysql proxysql 1.1M Aug  8 23:43 proxysql_stats.db

| mysql-have_ssl                                                       | true                                               |
| mysql-ssl_p2s_ca                                                     | /var/lib/proxysql/ca.pem                           |
| mysql-ssl_p2s_capath                                                 |                                                    |
| mysql-ssl_p2s_cert                                                   | /var/lib/proxysql/client-cert.pem                  |
| mysql-ssl_p2s_key                                                    | /var/lib/proxysql/client-key.pem                   |

After setting the variables:

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO RUNTIME;

root@pmysql:~# cat /etc/mysql/conf.d/ssl.cnf
[client]
ssl-ca=/var/lib/proxysql/ca.pem
ssl-cert=/var/lib/proxysql/client-cert.pem
ssl-key=/var/lib/proxysql/client-key.pem

root@pmysql:~# mysql -u exampleuser -p -h pmysql.domain.lan -P6033
Enter password:
ERROR 2026 (HY000): SSL connection error: error:0A000086:SSL routines::certificate verify failed

Just added the Cipher from:

mysql -P6033 -u exampleuser -p -h pmysql.domain.lan -e 'SHOW SESSION STATUS LIKE "Ssl_cipher"'

to the global variable “mysql-ssl_p2s_cipher” and LOAD/SAVE the MYSQL VARIABLES but that does not changed anything.

Maybe someone of you can do something with it:

root@pmysql:~# openssl s_client -connect pmysql.domain.lan:6033 -CAfile /var/lib/proxysql/ca.pem -cert /var/lib/proxysql/client-cert.pem -key /var/lib/proxysql/client-key.pem -state -debug

CONNECTED(00000003)
SSL_connect:before SSL initialization
write to 0x5561fd223590 [0x5561fd237a80] (323 bytes => 323 (0x143))
0000 - 16 03 01 01 3e 01 00 01-3a 03 03 cf 31 e4 79 44   ....>...:...1.yD
0010 - 68 d5 8d 37 fc 7a 68 d7-dc e2 d5 8a 83 de a4 63   h..7.zh........c
0020 - 29 1e bd 1c fb 97 c3 1e-90 7f 23 20 1d de 25 d3   ).........# ..%.
0030 - be 81 7f c6 79 4d dc ec-c8 08 e1 d7 02 0e 6a 14   ....yM........j.
0040 - 3e d9 1a 0c c8 88 b0 74-35 4e 30 cf 00 3e 13 02   >......t5N0..>..
0050 - 13 03 13 01 c0 2c c0 30-00 9f cc a9 cc a8 cc aa   .....,.0........
0060 - c0 2b c0 2f 00 9e c0 24-c0 28 00 6b c0 23 c0 27   .+./...$.(.k.#.'
0070 - 00 67 c0 0a c0 14 00 39-c0 09 c0 13 00 33 00 9d   .g.....9.....3..
0080 - 00 9c 00 3d 00 3c 00 35-00 2f 00 ff 01 00 00 b3   ...=.<.5./......
0090 - 00 00 00 16 00 14 00 00-11 70 6d 79 73 71 6c 2e   .........pmysql.
00a0 - 68 65 79 64 61 79 2e 6c-61 6e 00 0b 00 04 03 00   domain.lan......
00b0 - 01 02 00 0a 00 16 00 14-00 1d 00 17 00 1e 00 19   ................
00c0 - 00 18 01 00 01 01 01 02-01 03 01 04 00 23 00 00   .............#..
00d0 - 00 16 00 00 00 17 00 00-00 0d 00 2a 00 28 04 03   ...........*.(..
00e0 - 05 03 06 03 08 07 08 08-08 09 08 0a 08 0b 08 04   ................
00f0 - 08 05 08 06 04 01 05 01-06 01 03 03 03 01 03 02   ................
0100 - 04 02 05 02 06 02 00 2b-00 09 08 03 04 03 03 03   .......+........
0110 - 02 03 01 00 2d 00 02 01-01 00 33 00 26 00 24 00   ....-.....3.&.$.
0120 - 1d 00 20 47 68 2b 1e 4d-94 5a 90 71 e6 fd 72 e7   .. Gh+.M.Z.q..r.
0130 - 5a a7 e9 aa cf ff 7f 1c-06 49 35 cb 2d 06 89 ff   Z........I5.-...
0140 - 04 d3 4c                                          ..L
SSL_connect:SSLv3/TLS write client hello
read from 0x5561fd223590 [0x5561fd22e853] (5 bytes => 5 (0x5))
0000 - 4a 00 00 00 0a                                    J....
SSL_connect:error in error
800B93811E7F0000:error:0A00010B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:354:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 5 bytes and written 323 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---
read from 0x5561fd223590 [0x5561fd1d5080] (8192 bytes => 73 (0x49))
0000 - 35 2e 35 2e 33 30 00 89-00 00 00 7b 16 29 44 06   5.5.30.....{.)D.
0010 - 19 57 08 00 2f fa 21 02-00 8f 81 15 00 00 00 00   .W../.!.........
0020 - 00 00 00 00 00 00 73 44-03 11 31 06 1f 76 2a 11   ......sD..1..v*.
0030 - 28 04 00 63 61 63 68 69-6e 67 5f 73 68 61 32 5f   (..caching_sha2_
0040 - 70 61 73 73 77 6f 72 64-00                        password.
read from 0x5561fd223590 [0x5561fd1d5080] (8192 bytes => 0)
16

Very interesting:

root@pmysql:~# mysql -u exampleuser -p -h pmysql.domain.lan -P6033 --ssl-mode=REQUIRED --ssl-ca=/var/lib/proxysql/ca.pem --ssl-cert=/var/lib/proxysql/client-cert.pem --ssl-key=/var/lib/proxysql/client-key.pem
WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY.

In that moment when i added --ssl-mode=REQUIRED, it started to work.

But I would like the certificates to be checked. They should fit.

An openssl verify of the proxysql /var/lib/proxysql/client-cert.pem with the ca.pem that every PXC node and also the ProxySQL has, confirms that the client-cert is valid.

…

So this says:

  1. Make sure SSL is enabled for client connections, if not, set mysql-have_ssl=true and then issue command: LOAD MYSQL VARIABLES TO RUNTIME.
  2. Replace the previously mentioned certificates files, with the new certificates files:
  • ${DATADIR_PATH}/proxysql-ca.pem
  • ${DATADIR_PATH}/proxysql-cert.pem
  • ${DATADIR_PATH}/proxysql-key.pem
  1. Issue the new command: PROXYSQL RELOAD TLS.
  • So i created a new server certificate with the ca.pem from the pxc nodes and tested it: dont work
  • So i got the server certificate of pxc node 1 and its ca.pem to the proxysql, tested it: dont work
  • So i deleted them, created new with above command and tested it: dont work

dont understand it…All systems using the same ca.pem … all pxc nodes the same server cert from this, each its own client-cert from it, …

17

Solved. The issue for frontend was, that the service was started as init file, where the rlimit is to low, so its cant run the reload of certficiates. I killed the process, started with systemd and it worked fine. (systemctl enable --now proxysql)

To be clear was the issue was:

  • above was the frontend ssl certs which need to be the same server cert as the one from the pxc nodes in /var/lib/proxysql/proxysql-ca.pem/-cert.pem/-key.pem which need to get reloaded by the reload command proxysql reload tls
18

Awesome! Thanks for sharing this to the community!