SSL MySQL 8 backend - "SSL is required"

[germebl]

Discussed in https://github.com/PowerDNS/pdns/discussions/14566

^{Originally posted by **germebl** August 19, 2024} Hey everyone, actually trying to move the Database from standalone MariaDB Server to our PXC cluster. The cluster requires SSL connection so we need to place a client certificate and configuration. As i checked the default pdns group is "client" - so i created `/etc/mysql/conf.d/ssl.cnf` and filled with: ``` [client] ssl-ca=/etc/mysql/ca.pem ssl-cert=/etc/mysql/client-cert.pem ssl-key=/etc/mysql/client-key.pem ssl-verify-server-cert = on ``` But still getting error, that SSL is required: ``` Aug 19 13:10:22 dns-a pdns_server[44559]: PDNSException while filling the zone cache: Unable to launch gmysql connection: Unable to connect to database: ERROR 1045 (28000): ProxySQL Error: Access denied for user 'pdns' (using password: YES). SSL is required ```

Specs:

• Debian 12
• PowerDNS Authoritative Server 4.9.1

general conf:

# Modus
primary=yes
secondary=yes

# API
api=yes
api-key=hidden

# Webserver
webserver=true

# SOA
default-soa-content=dns.hidden.lan hostmaster.hidden.tld 0 10800 3600 604800 3600

backend conf:

launch=gmysql
gmysql-host=ip.of.proxysql.server
gmysql-port=6033
gmysql-dbname=pdns
gmysql-user=pdns
gmysql-password=hidden
# since we are actually lower than version 5.x
gmysql-ssl=yes

proxysql reports, that the connection comes in without ssl flag:

{
   "client_addr":"10.1.201.2:49348",
   "creation_time":"2024-08-26 13:10:21.938",
   "duration":"2.735ms",
   "event":"MySQL_Client_Close",
   "extra_info":"MySQL_Thread.cpp:4200:process_all_sessions()",
   "proxy_addr":"0.0.0.0:6033",
   "schemaname":"",
   "ssl":false,
   "thread_id":3,
   "time":"2024-08-26 13:10:21.941",
   "timestamp":1724677821941,
   "username":"pdns"
}

Other clients working fine with ssl to proxysql:

{
   "client_addr":"10.1.201.3:38828",
   "creation_time":"2024-08-26 13:06:09.326",
   "duration":"24189.256ms",
   "event":"MySQL_Client_Close",
   "extra_info":"MySQL_Thread.cpp:4200:process_all_sessions()",
   "proxy_addr":"0.0.0.0:6033",
   "schemaname":"information_schema",
   "ssl":true,
   "thread_id":1,
   "time":"2024-08-26 13:06:33.515",
   "timestamp":1724677593515,
   "username":"keycloak"
}

When connecting pdns directly to the database and not to the proxy, same behavior:

mysql> SELECT
    ->     t.PROCESSLIST_ID AS connection_id,
    ->     t.PROCESSLIST_USER AS user,
    ->     t.PROCESSLIST_HOST AS host,
UE != '', 'SSL', 'No SSL') AS ssl_status
FROM
    performance_schema.threads AS t
JOIN
    per    ->     t.PROCESSLIST_DB AS db,
    ->     IF(s.VARIABLE_VALUE != '', 'SSL', 'No SSL') AS ssl_status
HREAD_ID
WHERE
    t.PROCESSLIST_ID IN (396, 397, 398, 399, 400    -> FROM
    ->     performance_schema.threads AS t
)
    AND s.VARIABLE_NAME = 'Ssl    -> JOIN
    ->     performance_schema.status_by_thread AS s
    -> ON
    ->     t.THREAD_ID = s.THREAD_ID
    -> WHERE
    ->     t.PROCESSLIST_ID IN (396, 397, 398, 399, 400)
    ->     AND s.VARIABLE_NAME = 'Ssl_cipher';
+---------------+------+----------------+------+------------+
| connection_id | user | host           | db   | ssl_status |
+---------------+------+----------------+------+------------+
|           396 | pdns            | dns.domain.lan | pdns     | No SSL     |
|           397 | pdns            | dns.domain.lan | pdns     | No SSL     |
|           398 | pdns            | dns.domain.lan | pdns     | No SSL     |
|           399 | pdns            | dns.domain.lan | pdns     | No SSL     |
|           400 | pdns            | dns.domain.lan | pdns     | No SSL     |
+---------------+------+----------------+------+------------+

As there has been no feedback on the discussion for almost 3 weeks, I am now opening it as an issue.

[germebl]

I have not been connected to the DNS server for the last few weeks - due to a completely different change (update of the kernel and the associated restart of the server) this error has now occurred at startup-failure of pdns:

gmysql connection: Unable to connect to database: ERROR 2061 (HY000): Couldn't read RSA public key from server

instead of:

ProxySQL Error: Access denied for user 'pdns' (using password: YES). SSL is required

pdns version is still the same.

[omoerbeek]

I'm closing this as it is very confusing and inappropriate to open an issue when it is clearly a support issue related to the use of ProxySQL. Continue in the existing discussion, we want all info in one place and be patient, we have no time obligation to you.

[germebl]

Please read carefully before closing an issue:

proxysql reports, that the connection comes in without ssl flag:

{ "client_addr":"10.1.201.2:49348", "creation_time":"2024-08-26 13:10:21.938", "duration":"2.735ms", "event":"MySQL_Client_Close", "extra_info":"MySQL_Thread.cpp:4200:process_all_sessions()", "proxy_addr":"0.0.0.0:6033", "schemaname":"", "ssl":false, "thread_id":3, "time":"2024-08-26 13:10:21.941", "timestamp":1724677821941, "username":"pdns" } Other clients working fine with ssl to proxysql:

{ "client_addr":"10.1.201.3:38828", "creation_time":"2024-08-26 13:06:09.326", "duration":"24189.256ms", "event":"MySQL_Client_Close", "extra_info":"MySQL_Thread.cpp:4200:process_all_sessions()", "proxy_addr":"0.0.0.0:6033", "schemaname":"information_schema", "ssl":true, "thread_id":1, "time":"2024-08-26 13:06:33.515", "timestamp":1724677593515, "username":"keycloak" }

and from maintainer @Habbie :

ok, that's a clear confirmation.
Source

Only the error message changed, proxysql still gets connection tries from pdns without ssl:

{"client_addr":"10.1.201.2:53350","creation_time":"2024-09-11 09:56:18.224","duration":"1.063ms","event":"MySQL_Client_Close","extra_info":"MySQL_Thread.cpp:4200:process_all_sessions()","proxy_addr":"0.0.0.0:6033","schemaname":"","ssl":false,"thread_id":5139,"time":"2024-09-11 09:56:18.225","timestamp":1726048578225,"username":"pdns"}

Further when trying to connect directly to the database server and not to proxysql, the connection likewise does not come in with ssl.

[omoerbeek]

OK, maybe I was wrong and there is an issue, but still, it is confusing to have the diagnosis and test cases spread over multiple issues/discussions.

[germebl]

Discussions are for exploring and diagnosing potential issues. Once it's confirmed that there's an actual bug, it belongs in an issue for proper tracking and resolution. That's exactly why I created a new issue and referenced the discussion where the problem was identified.

We need to keep things organized—discussions for investigation, issues for active problems.

spread over multiple issues/discussions

there was one discussion where the problem was identified and now one issue where it's being reported. So nothing is spread across multiple discussions or issues.

[omoerbeek]

It has not been ruled out that there's a configuration issue in your side.

[germebl]

The problem has existed for about 4 weeks now. In 4 weeks, the configuration has never been criticized in the discussion and a maintainer has confirmed that it is clear that this is an issue. Indirectly, this ruled out a configuration error.

Do we want to continue the discussion now or do we want to reopen the issue and declare it correctly so that the problem can finally be solved?

[rgacogne]

May I suggest dialing your tone down quite a bit? Being rude to the exact people that might be able to help you fix the issue is not going to make things move faster, quite the opposite.

[germebl]

Let me clarify something: I'm not the one who didn't read the issue properly or kept debating whether it's actually an issue, despite it being confirmed by a maintainer. I've simply followed the process by opening an issue after the problem was identified, and I've kept things clear and organized.

I'm not the one crossing the line in terms of tone here. But no worries—if the issue stays closed, we'll just switch to another software, and the same issue will likely surface again when someone else encounters it.

[rgacogne]

I'm not the one crossing the line in terms of tone here.

I'm afraid I disagree.

But no worries—if the issue stays closed, we'll just switch to another software, and the same issue will likely surface again when someone else encounters it.

Sure, I hope you find a software that is more to your liking. Best of luck.

[germebl]

It is not the software that is the problem here, but rather developers who feel attacked because I created an issue from a discussion using a function provided by GitHub in order to clearly distinguish between a discussion in which we first determine whether it is simply a configuration error and an issue that is a confirmed error. At no point was my tone intended to attack anyone or anything like that. At no point did I make rude statements.

Of course opinions differ and if you see my error message or the way I explain why I did what as rude, then I'd rather part ways with the software and go back to what has been industry standard for decades.

as it is very confusing and inappropriate to open an issue when it is clearly a support issue related to the use of ProxySQL

If I have already done the troubleshooting with one of you, I find it mean when the whole thing is simply dismissed with such a statement.

Continue in the existing discussion, we want all info in one place

GitHub offers a function to create an issue from a discussion. Surely not without reason? And I can't find any PDNS guidelines that say that you also treat discussions as issues. According to my research, you have not always done this in the past. So what was so bad that I took a discussion in which I was unsure at the time of asking whether it was perhaps a configuration error and decided that it was an issue and then created a correctly confirmed (by you) issue from it?

https://github.com/PowerDNS/pdns/issues/14659#issuecomment-2343181182

I just asked you to check the facts again before closing the issue and quoted you the most important findings.

It has not been ruled out that there's a configuration issue in your side.

Correct, but it was confirmed indirectly, as an error was confirmed and my configuration was not criticized. Is there a configuration error? There is not that much configuration for SSL that can go wrong.

https://github.com/PowerDNS/pdns/issues/14659#issuecomment-2343248367

Again, I just wanted to make it clear that at any point I wanted nothing more than to report a confirmed suspicion that I was previously unsure about as a confirmed issue after @Habbie confirmed that it was probably an issue - even if he wasn't yet sure where it came from.

Thats all. Over and out.

[Habbie]

despite it being confirmed by a maintainer

nope

for reference, the problem was resolved by fixing the configuration, see https://github.com/PowerDNS/pdns/discussions/14566#discussioncomment-10614325