PMM2 - Connection check failed

, ,
1

Hello all,

I am getting the following error when I am trying to add mysql service , I am using mariadb 10.5.6.

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE --host=127.0.0.1 --port=3306

Connection check failed: Error 1045: Access denied for user ‘pmm’@‘localhost’ (using password: YES).

pmm-admin status gives me this output:

PMM Server:
URL : https://X.X.X.X:443/
Version: 2.28.0

PMM Client:
Connected : true
Time drift : -60.452µs
Latency : 566.06µs
pmm-admin version: 2.28.0
pmm-agent version: 2.28.0
Agents:
/agent_id/17227ca7-ef32-4c32-ab9c-8bf0b000cc45 vmagent Running
/agent_id/790765b0-a95b-4ecf-954c-a161afe9bba5 node_exporter Running

And also mariadb server is running and ssl is enabled, and user pmm is created with REQUIRE SSL privilege. I am able to login to mariadb with the user pmm by passing the ssl-certs created , but when I am trying to add the service I am facing error.

Thanks
Sanjana

1 Like
2

Make sure you understand the difference between ‘localhost’ and ‘127.0.0.1’ because they are NOT the same thing when it comes to creating users in MySQL. How did you create the ‘pmm’ user?

1 Like
3

I have used this command:

CREATE USER 'pmm'@'localhost' IDENTIFIED BY 'pass' REQUIRE SSL WITH MAX_USER_CONNECTIONS 10;

GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'localhost';
1 Like
4

Adding more informarion:

$ mysql -u pmm -p --ssl
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 93
Server version: 10.5.6-MariaDB-1:10.5.6+maria~focal mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show grants;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for pmm@localhost                                                                                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, PROCESS, BINLOG MONITOR ON *.* TO `pmm`@`localhost` IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXX' REQUIRE SSL WITH MAX_USER_CONNECTIONS 10 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

When I do not pass the --ssl flag,

$mysql -u pmm -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'pmm'@'localhost' (using password: YES)

So the user pmm is working fine when i log in to mariadb with --ssl.
But the issue is when I try to add the mysql service, its not working.

1 Like
5

this is what the debug command gives,

$ sudo pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE --debug 
DEBUG 2022-06-03 06:20:45.66670904Z: POST /local/Status HTTP/1.1
Host: 127.0.0.1:7777
User-Agent: Go-http-client/1.1
Content-Length: 3
Accept: application/json
Content-Type: application/json
Accept-Encoding: gzip

{}

DEBUG 2022-06-03 06:20:45.668959968Z: HTTP/1.1 200 OK
Content-Length: 792
Content-Type: application/json
Date: Fri, 03 Jun 2022 06:20:45 GMT
Grpc-Metadata-Content-Type: application/grpc

{
  "agent_id": "/agent_id/30b8ee49-55b6-4092-bd27-1a7dbcc27dce",
  "runs_on_node_id": "/node_id/411301f1-fc15-4468-a045-de38e6eb7379",
  "server_info": {
    "url": "https://admin:admin@X.X.X.X:443/",
    "insecure_tls": true,
    "connected": true,
    "version": "2.28.0",
    "latency": null,
    "clock_drift": null
  },
  "agents_info": [
    {
      "agent_id": "/agent_id/17227ca7-ef32-4c32-ab9c-8bf0b000cc45",
      "agent_type": "VM_AGENT",
      "status": "RUNNING",
      "listen_port": 42000
    },
    {
      "agent_id": "/agent_id/790765b0-a95b-4ecf-954c-a161afe9bba5",
      "agent_type": "NODE_EXPORTER",
      "status": "RUNNING",
      "listen_port": 42001
    }
  ],
  "config_filepath": "/usr/local/percona/pmm2/config/pmm-agent.yaml",
  "agent_version": "2.28.0"
}
DEBUG 2022-06-03 06:20:45.669262661Z: POST /local/Status HTTP/1.1
Host: 127.0.0.1:7777
User-Agent: Go-http-client/1.1
Content-Length: 3
Accept: application/json
Content-Type: application/json
Accept-Encoding: gzip

{}

DEBUG 2022-06-03 06:20:45.669831109Z: HTTP/1.1 200 OK
Content-Length: 792
Content-Type: application/json
Date: Fri, 03 Jun 2022 06:20:45 GMT
Grpc-Metadata-Content-Type: application/grpc

{
  "agent_id": "/agent_id/30b8ee49-55b6-4092-bd27-1a7dbcc27dce",
  "runs_on_node_id": "/node_id/411301f1-fc15-4468-a045-de38e6eb7379",
  "server_info": {
    "url": "https://admin:admin@X.X.X.X:443/",
    "insecure_tls": true,
    "connected": true,
    "version": "2.28.0",
    "latency": null,
    "clock_drift": null
  },
  "agents_info": [
    {
      "agent_id": "/agent_id/17227ca7-ef32-4c32-ab9c-8bf0b000cc45",
      "agent_type": "VM_AGENT",
      "status": "RUNNING",
      "listen_port": 42000
    },
    {
      "agent_id": "/agent_id/790765b0-a95b-4ecf-954c-a161afe9bba5",
      "agent_type": "NODE_EXPORTER",
      "status": "RUNNING",
      "listen_port": 42001
    }
  ],
  "config_filepath": "/usr/local/percona/pmm2/config/pmm-agent.yaml",
  "agent_version": "2.28.0"
}
DEBUG 2022-06-03 06:20:45.670142639Z: POST /v1/management/MySQL/Add HTTP/1.1
Host: 10.57.58.215:443
User-Agent: Go-http-client/1.1
Content-Length: 325
Accept: application/json
Authorization: Basic YWRtaW46YWRtaW4=
Content-Type: application/json
Accept-Encoding: gzip

{"node_id":"/node_id/411301f1-fc15-4468-a045-de38e6eb7379","service_name":"MYSQL_NODE","address":"127.0.0.1","port":3306,"pmm_agent_id":"/agent_id/30b8ee49-55b6-4092-bd27-1a7dbcc27dce","username":"pmm","password":"pass","qan_mysql_perfschema":true,"max_slowlog_file_size":"0","metrics_mode":"AUTO","disable_collectors":null}

DEBUG 2022-06-03 06:20:45.692229551Z: HTTP/1.1 400 Bad Request
Content-Length: 247
Connection: keep-alive
Content-Type: application/json
Date: Fri, 03 Jun 2022 06:20:45 GMT
Server: nginx

{
  "error": "Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES).",
  "code": 9,
  "message": "Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES)."
}
DEBUG 2022-06-03 06:20:45.692397734Z: Result: <nil>                               
DEBUG 2022-06-03 06:20:45.692444892Z: Error: &my_sql.AddMySQLDefault{_statusCode:400, Payload:(*my_sql.AddMySQLDefaultBody)(0xc000335c00)}
Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES).
1 Like
6

Could you create an additional mysql user ‘pmm’@‘127.0.0.1’ ?

CREATE USER 'pmm'@'127.0.0.1' IDENTIFIED BY 'pass' REQUIRE SSL WITH MAX_USER_CONNECTIONS 10;
GRANT SELECT, PROCESS, REPLICATION CLIENT, RELOAD ON *.* TO 'pmm'@'127.0.0.1';
1 Like
7

@adivinho

After creating the user with host 127.0.0.1,

+-------------+----------------+-------------------------------------------+
| User        | Host           | Password                                  |
+-------------+----------------+-------------------------------------------+
| mariadb.sys | localhost      |                                           |
| root        | localhost      | invalid                                   |
| mysql       | localhost      | invalid                                   |
|             | localhost      |                                           |
|             | stg-sanjana102 |                                           |
| pmm         | 127.0.0.1      | *196BDEDE2AE4F84CA44C47D54D78478C7E2BD7B7 |
+-------------+----------------+-------------------------------------------+

and when i try to add mysql service,

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE --host=127.0.0.1 --port=3306
Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES).
1 Like
8

When I don’t create user with REQUIRE SSL and try to add mysql service it is successful, so it seems like something to do with the user I have created with REQUIRE SSL.

1 Like
9

If you create the user using @localhost, then tell PMM to use the socket connection, you can remove the SSL as you can’t do SSL over socket since it is a direct connection to MySQL. That will solve your issue and be secure.

1 Like
10

@matthewb, I am afraid,I could not comprehend what you are saying, if you don’t mind can you please explain a little more about this? It’ll be a great help!
About the socket connection do you mean to say I need to use this flag? but as per the document this is given only for slow query log but I am using perfschema.

--socket=/var/run/mysqld/mysqld.sock

And about removing SSL, do you mean to say I should be removing REQUIRE SSL when creating the pmm user ?

I actually want to authenticate the mysql user to use the certs I have created , else that user shouldn’t be given the permission to login to mysql, hence why I have created the user ‘pmm’ to REQUIRE SSL.

Thank you

1 Like
11

I have tried the following commands, still the same issue,

pmm-admin add mysql --query-source=perfschema --username=pmm --password=pass --service-name=MYSQL_NODE --socket=/var/run/mysqld/mysqld.sock
Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES).

pmm-admin add mysql --query-source=perfschema --server-insecure-tls --username=pmm --password=pass --service-name=MYSQL_NODE --socket=/var/run/mysqld/mysqld.sock
Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES).

Seems like a similar issue, but it did’nt help me solve my error.

1 Like
12

REQUIRE SSL does not authenticate using SSL certificates. This flag only requires that an SSL connection be present. Authentication is still based on username/password.

MySQL 101:

  • CREATE USER bob@localhost ← Can only connect to MySQL using socket
  • CREATE USER bob@127.0.0.1 ← Can only connect to MySQL using tcp/ip (-h)

Connections over socket do not use SSL because it is a direct connection to MySQL. Connections over tcp/ip can be intercepted and thus should use SSL for encryption.

Therefor, if you create your PMM user using @localhost, then they can only connect to MySQL via socket and thus don’t need the REQUIRE SSL.

but as per the document this is given only for slow query log but I am using perfschema.

It does not matter. You can access MySQL perfschema using socket or tcp connections (MySQL 101). You should prefer slowlog as you get more information on query stats using the slow log.

1 Like
13

@matthewb okay, will try that and update.

1 Like
14

the issue is resolved now , I have used this command:

pmm-admin add mysql --username=pmm --password=pass --tls --tls-skip-verify --service-name=MYSQL_NODE --host=127.0.0.1 --port=3306

MySQL Service added.
Service ID  : /service_id/1dfba831-2a78-4cbd-88e4-2b03ea0ddabf
Service name: MYSQL_NODE

Table statistics collection enabled (the limit is 1000, the actual table count is 194).

But I have a confusion,
when I use --tls ( use tls to connect to database) it gives me this error:

pmm-admin add mysql --username=pmm --password=pass --tls --service-name=MYSQL_NODE --host=127.0.0.1 --port=3306 
Connection check failed: x509: cannot validate certificate for 127.0.0.1 because it doesn't contain any IP SANs.

and when i use --tls-skip-verify (skip tls cert validation) I am getting this error,

pmm-admin add mysql --username=pmm --password=pass --tls-skip-verify --service-name=MYSQL_NODE --host=127.0.0.1 --port=3306 
Connection check failed: Error 1045: Access denied for user 'pmm'@'localhost' (using password: YES).

Why is this happening? How is --tls --tls-skip-verify working when its used together ?

1 Like
15

MySQL uses a self-signed CA which is considered insecure in the SSL/TLS world. --tls-skip-verify says “I don’t care if this is a self-signed CA”

1 Like
16

so in general its ignoring the self-signed certs and adding MySQL instance to pmm-server , which i guess is insecure, so if i should have a secure connection , then I would be needed to generate my own certs right? so if i do that then I will be able to pass the certs using these flags and connect securely ?

--tls-ca=TLS-CA            Path to certificate authority certificate file
--tls-cert=TLS-CERT     Path to client certificate file
--tls-key=TLS-KEY        Path to client key file

So before I create CA certs on pmm-client for adding mysql instance , is it necessary to create my own certs and mount them onto /srv/ngnix inside my docker ( as my pmm-server is running on a docker) ?

1 Like
17

If you generate your own certs, those are no different from the ones mysql generates. They will both be self-signed. It is absolutely secure; the encryption standards are still there. With self-signed, the CA isn’t some billion dollar company like Verisign. That’s the only difference. If you trust the self-signed, then you are fine.

Yes, if you want to use your own certs you will have to install them into the pmm docker as well. Take a look at this blog post: Renewing the (Default) PMM Certificate

1 Like
18

@matthewb , yes I read the blog and understood it, actually I spent a lot of time understanding about how self-signed ssl cert works, and because I am using the self-signed certs provided by pmm, when I try to access the webpage it shows a message insecure and i need to type ‘thisisunsafe’ to bypass that, and from my research I found out that the browser doesn’t trust the self-signed certs to be secure.
Screenshot 2022-06-04 at 7.40.35 PM

Now, even if i create my own certs in pmm-docker and mount on /srv/nginx for pmm-server that will still be self-signed and hence again the browser will show me ‘not secure’ , right?
and the certificates won’t be considered valid or ‘secure’ until an external CA signs it.

This is my understanding, if I am wrong please correct me.
and one more small confusion, when I add mysql service to the pmm-server , --tls flag is basically used to establish a tls connection to the database and --tls-skip-verify is basically used to ignore the certs which are present, so how will this connection be secured, if I am skipping verifying the certs.

And I am so sorry to bother you a lot, but I am actually very confused.
thanks

1 Like
19

One more thing , this command can only be used when I have a valid cert signed by a valid CA i.e, if I purchased the certificate from a certificate authority (CA) right?

pmm-admin config --server 192.168.100.1 --server-ssl

If i don’t purchase a valid certificate, even If i create my own cert by generating my own CA cert, will it still be considered as self-signed? and hence I should be using this command to enable ssl when connecting PMM Client to PMM server.

$ pmm-admin config --server 192.168.100.1 --server-insecure-ssl

thanks again

1 Like
20

That is correct. You can make your browser trust it by simply importing your CA.

Correct

Not quite. The skip-verify simply says “allow certificates from unknown CA”. In this case, the CA is self-signed and so the OS databases for SSL certs don’t know about it. Again you could import the self-siged CA into your OS’s SSL database and it will then trust it.

No. A valid CA is one trusted by your OS, nothing more. Commercial CA providers are simply trusted by most OS by default. I beg you, do not spend money on a CA from a commercial provider. You can simply trust your own CA, or use CA from LetsEncrypt (Free SSL trusted by all browsers/OS).

1 Like