SSL MySQL 8 backend - Do i forgot something within ssl client config?

[germebl]

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

Its definetly the client thats having an issue, cause i tested that user on another (mysql 8) client (instead of pdns mariadb client) and there it works fine. The cert files having the correct user/group/permission.

Do i forgot something?

Best regards

[Habbie]

What version of pdns on what operating system/distro version etc.?

[germebl]

dang it, forgot it:

• 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

//Edit

So actually what i can see - there is no mysql client installed anyway. Just the pdns gmysql backend (and deps like mysql/mariadb-common) as package from official repo - but thats should be enough? It brings the /etc/mysql folder with its configuration files.

Since the ssl.cnf is contained in the conf.d, it is read as a general configuration that affects the use of both the mysql and the mariadb client.

According to the documentation, the default group is client, which should also fit.
https://doc.powerdns.com/authoritative/backends/generic-mysql.html#gmysql-group

[germebl]

It is a error message from the pdns_server (got it from the journalctl)

[Habbie]

Ok, yes, but did pdns get it from ProxySQL or from MariaDB? Presumably those have logs that will tell you.

[germebl]

From the PDNS system i did not get any client logs other than the reported one above. On the ProxySQL systems i did not configured the audit log yet, but as the message includes a ProxySQL error, it seems to comes from there. (ProxySQL Error: Access denied for user 'pdns' (using password: YES). SSL is required)

//Edit
Okay i now have configured the audit log from proxysql and tested a connection. ProxySQL reports:

{
   "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"
}

So the connection comes in without ssl, cause when i try to connect from a working client to the proxysql, ssl is true:

{
   "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"

So i enabled the gmysql-ssl again and tried again, but still ssl is false:

{
   "client_addr":"10.1.201.2:33712",
   "creation_time":"2024-08-26 13:13:03.314",
   "duration":"1.003ms",
   "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":102,
   "time":"2024-08-26 13:13:03.315",
   "timestamp":1724677983315,
   "username":"pdns"
}

[Habbie]

ok, that's a clear confirmation. I don't yet know what goes wrong.

When you connect pdns directly to the database, does it use SSL then?

[germebl]

With gmysql-ssl turned off:

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 (382, 383, 384, 385, 386    -> 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 (382, 383, 384, 385, 386)
    ->     AND s.VARIABLE_NAME = 'Ssl_cipher';
+---------------+------+----------------+------+------------+
| connection_id | user | host           | db   | ssl_status |
+---------------+------+----------------+------+------------+
|           382 | pdns | dns.domain.lan | pdns | No SSL     |
|           383 | pdns | dns.domain.lan | pdns | No SSL     |
|           384 | pdns | dns.domain.lan | pdns | No SSL     |
|           385 | pdns | dns.domain.lan | pdns | No SSL     |
|           386 | pdns | dns.domain.lan | pdns | No SSL     |
+---------------+------+----------------+------+------------+

with gmysql-ssl turned on:

|           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     |

[germebl]

I wanted to find out if there is anything new about this?

[Habbie]

can you try putting your settings in my.cnf, instead of an include file not named my.cnf?

[germebl]

Default:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
socket = /run/mysqld/mysqld.sock

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Changed to:

# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 0. "/etc/mysql/my.cnf" symlinks to this file, reason why all the rest is read.
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# If you are new to MariaDB, check out https://mariadb.com/kb/en/basic-mariadb-articles/

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
# Port or socket location where to connect
# port = 3306
# socket = /run/mysqld/mysqld.sock

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

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

WORKS!

[Habbie]

while this is documented MariaDB behaviour, they are tracking it in a ticket on their end https://jira.mariadb.org/browse/CONC-396