# Beginner question about user creation **URL:** https://forums.percona.com/t/beginner-question-about-user-creation/32105 **Category:** ProxySQL **Created:** [August 7, 2024, 9:36am UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105 "2024-08-07T09:36:35Z") **Posts on this page:** 18 **Page:** 1
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 7, 2024, 9:36am UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/1 "2024-08-07T09:36:35Z")
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: > **[Users configuration - ProxySQL](https://proxysql.com/documentation/users-configuration/)** > > Guide to configuring users in ProxySQL, including creating users, setting default hostgroups, limiting connections, enabling transaction persistence, and saving configurations. > **[Password management - ProxySQL](https://proxysql.com/documentation/password-management/)** > > Learn about ProxySQL password management for securing database connections and managing user credentials efficiently. **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: > **[MySQL Variables - ProxySQL](https://proxysql.com/documentation/global-variables/mysql-variables/#mysql-default_authentication_plugin)** > > Comprehensive documentation on ProxySQL global MySQL variables for efficient database configuration and management. > This option **doesn’t** disable any of the [authentication methods](https://proxysql.com/documentation/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](https://proxysql.com/documentation/global-variables/mysql-variables/#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](https://dev.mysql.com/doc/dev/mysql-server/latest/page_caching_sha2_authentication_exchanges.html), 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: > **[Percona XtraDB Cluster - Encrypt PXC traffic](https://docs.percona.com/percona-xtradb-cluster/8.0/encrypt-traffic.html#encrypt-client-server-communication)** > > There are two kinds of traffic in Percona XtraDB Cluster: > 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: > **[Percona XtraDB Cluster - Encrypt PXC traffic](https://docs.percona.com/percona-xtradb-cluster/8.0/encrypt-traffic.html#__tabbed_1_3)** > > There are two kinds of traffic in Percona XtraDB Cluster: 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: > **[SSL Support - ProxySQL](https://proxysql.com/documentation/ssl-support/)** > > Learn how to configure SSL for ProxySQL backends and frontends to ensure secure connections. Detailed instructions and examples for SSL setup and certificate renewal included. So i think this is still missing to be able to work correct… I will give it a try…will take some time… ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 7, 2024, 2:31pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/2 "2024-08-07T14:31:04Z")
> [@Zody](#): > > Do i also need to deploy the same client cert and key to all nodes Yes. All nodes should have the same CA certs. > [@Zody](#): > > Not every application supports ssl mysql with cert 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. ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 7, 2024, 2:47pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/3 "2024-08-07T14:47:36Z")
> [@matthewb](#): > > Yes. All nodes should have the same CA certs. 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? > **[Percona XtraDB Cluster - Encrypt PXC traffic](https://docs.percona.com/percona-xtradb-cluster/8.0/encrypt-traffic.html#__tabbed_1_3)** > > There are two kinds of traffic in Percona XtraDB Cluster: ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 7, 2024, 3:03pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/4 "2024-08-07T15:03:47Z")
> [@Zody](#): > > Every client needs the same client certificate and key? 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. ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 7, 2024, 3:07pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/5 "2024-08-07T15:07:43Z")
Okay, so > [@matthewb](#): > > You can create multiple client certs that will all validate against the single server cert 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: > [@matthewb](#): > > There should be a way to override 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. ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 7, 2024, 3:12pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/6 "2024-08-07T15:12:16Z")
> [@Zody](#): > > ERROR 2026 (HY000): TLS/SSL error: self-signed certificate in certificate chain Please provide the full command which generates this error. ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 7, 2024, 3:18pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/7 "2024-08-07T15:18:44Z")
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: ```auto [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. > **[Percona XtraDB Cluster - Encrypt PXC traffic](https://docs.percona.com/percona-xtradb-cluster/8.0/encrypt-traffic.html#__tabbed_1_3)** > > There are two kinds of traffic in Percona XtraDB Cluster: ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 7, 2024, 4:51pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/8 "2024-08-07T16:51:03Z")
Can you connect directly to mariadb using SSL certs? If so, this appears to be a ProxySQL-SSL issue. ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 7, 2024, 4:51pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/9 "2024-08-07T16:51:37Z")
Wait… Why are you using mariadb client if you are connecting to Percona XtraDB Cluster? You should be using the mysql client/libraries. ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 7, 2024, 8:07pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/10 "2024-08-07T20:07:11Z")
`openssl verify -CAfile /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem /var/lib/mysql/client-cert.pem` cluster node 1: ```auto /var/lib/mysql/server-cert.pem: OK /var/lib/mysql/client-cert.pem: OK ``` cluster node 2: ```auto /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: ```auto /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: ```auto 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! 😃 - but frontend or backend or both? ^^… client → proxysql = frontend proxysql → cluster-nodes = backend ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 7, 2024, 9:27pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/11 "2024-08-07T21:27:37Z")
i test another mysql client tomorrow to be sure, its just proxysql. But for now… 😴 🛌 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. ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 8, 2024, 8:04am UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/12 "2024-08-08T08:04:59Z")
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: - 🙂 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) - 😐 no SSL works for frontend (will check) - 😔 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? - 😐 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 - 😔 frontend → backend has issues (will check) I will work now on: > **[SSL Support - ProxySQL](https://proxysql.com/documentation/ssl-support/)** > > Learn how to configure SSL for ProxySQL backends and frontends to ensure secure connections. Detailed instructions and examples for SSL setup and certificate renewal included. ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 8, 2024, 1:48pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/13 "2024-08-08T13:48:36Z")
> [@Zody](#): > > SSL didnt work for frontend (will do proxysql ssl config) You must use ProxySQL’s ssl certificates. /var/lib/proxysql Or install yours in their place. Filenames must match. > [@Zody](#): > > They need to be the client certs created on the cluster node, doesnt it? I believe that is correct. > [@Zody](#): > > Doesnt it need the same server certs as the nodes to be compatible to the client certs Yes ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 8, 2024, 2:03pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/14 "2024-08-08T14:03:43Z")
Okay, so yeah - then the issue still persists. Because it was much information until now, here is a clear setup description: - all debian 12 - 1 proxysql node - 3 pxc 8 nodes as cluster - no firewall restrictions - pxc setup as here: [Install Percona XtraDB Cluster on Debian or Ubuntu - Percona XtraDB Cluster](https://docs.percona.com/percona-xtradb-cluster/8.0/apt.html#get-expert-help) - pxc write-set repl.: [Configure nodes for write-set replication - Percona XtraDB Cluster](https://docs.percona.com/percona-xtradb-cluster/8.0/configure-nodes.html#template-of-the-configuration-file) - bootstrap: [Bootstrap the first node - Percona XtraDB Cluster](https://docs.percona.com/percona-xtradb-cluster/8.0/bootstrap.html#bootstrap) - added nodes: [Add nodes to cluster - Percona XtraDB Cluster](https://docs.percona.com/percona-xtradb-cluster/8.0/add-node.html) - verified repl.: [Verify replication - Percona XtraDB Cluster](https://docs.percona.com/percona-xtradb-cluster/8.0/verify-replication.html) - each node got its client certs from the ca.pem of node 1, including the node 1 itself (created fresh one) [Encrypt PXC traffic - Percona XtraDB Cluster](https://docs.percona.com/percona-xtradb-cluster/8.0/encrypt-traffic.html#encrypt-traffic), including conf for sst and client-server communication using this - proxysql install here: [Download and Install ProxySQL - ProxySQL](https://proxysql.com/documentation/installing-proxysql/) - added the 3 nodes and configured monitoring / created MySQL replication hostgroups but actually dont using read-only [How to configure ProxySQL for the first time - ProxySQL](https://proxysql.com/documentation/ProxySQL-Configuration/) - created user as destribed here: [Beginner question about user creation](https://forums.percona.com/t/beginner-question-about-user-creation/32105) - have set global mysql-have\_ssl=1 - have set users use\_ssl=1 - have set all nodes use\_ssl=1 - created client-cert/key.pem with ca.pem from pxc node 1 and moved it to proxysql /etc/mysql/client-cert/key.pem - created server-cert/key.pem with ca.pem from pxc node 1 and moved it to proxysql /var/lib/proxysql/proxysql-cert/key/ - copied pxc node 1 ca.pem to proxysql /var/lib/proxysql/proxysql-ca.pem - copied pxc node 1 ca.pem to proxysql /etc/mysql/ca.pem - set mysql-ssl\_p2s ca,cert,key to /etc/mysql/ca.pem / /etc/mysql/client-cert/key.pem - created another client certificate from pxc node 1 ca.pem and used it on a deb12 testserver with mysql 8 client in my.cnf in [client] section like a pxc node has it for [server] and [client] Thats should be all…I hope i dont forgot something but my own docs says thats it. ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 8, 2024, 2:45pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/15 "2024-08-08T14:45:05Z")
Got one thing done… ```auto +-------------+--------+---------+-------------------+---------+----------+ | username | active | use_ssl | default_hostgroup | backend | frontend | +-------------+--------+---------+-------------------+---------+----------+ | exampleuser | 1 | 1 | 1 | 1 | 1 | +-------------+--------+---------+-------------------+---------+----------+ ``` ```auto 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: ```auto 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 … > [@matthewb](#): > > > [@Zody](#): > > > > Doesnt it need the same server certs as the nodes to be compatible to the client certs > > Yes 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](https://proxysql.com/documentation/ssl-support/) 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 > **[SSL Encryption at ProxySQL Part 1 - ProxySQL](https://proxysql.com/blog/ssl-at-proxysql-part1/)** > > Learn how to enable SSL encryption for MySQL connections through ProxySQL, ensuring secure data in transit. This guide covers backend SSL setup and configuration. 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. ```auto 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 ``` ```auto | 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: ```auto LOAD MYSQL VARIABLES TO RUNTIME; SAVE MYSQL VARIABLES TO RUNTIME; ``` ```auto 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 ``` ```auto 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: ```auto 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: ```auto 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) ``` ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 9, 2024, 10:04am UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/16 "2024-08-09T10:04:03Z")
Very interesting: ```auto 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: > **[SSL Support - ProxySQL](https://proxysql.com/documentation/ssl-support/)** > > Learn how to configure SSL for ProxySQL backends and frontends to ensure secure connections. Detailed instructions and examples for SSL setup and certificate renewal included. > 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, … ---
### Author: ![Zody](https://avatars.discourse-cdn.com/v4/letter/z/13edae/32.png) [@Zody](https://forums.percona.com/u/Zody) #### Post date: [August 12, 2024, 9:48am UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/17 "2024-08-12T09:48:36Z")
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` ---
### Author: ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb) #### Post date: [August 12, 2024, 12:01pm UTC](https://forums.percona.com/t/beginner-question-about-user-creation/32105/18 "2024-08-12T12:01:05Z")
Awesome! Thanks for sharing this to the community!