MariaDB not perfroming DNS lookup

Question

First the setup. I did a fresh & clean install of MariaDB on my Debian server. Next I configurred MariaDB to use my server's IPv6 address. This is working because I can use tcpdump to monitor the traffic between my desktop and server on port 3306. I then created a database and a remote user access to said database with the commands:

CREATE DATABASE db_name;
GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'workstation.local-network' to  IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

Tried connecting from the workstation and got the message:

ERROR 2002 (HY000): Received error packet before completion of TLS handshake. The authenticity of the following error cannot be verified: 1130 - Host '2a0e:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:be38' is not allowed to connect to this MariaDB server

This is when I check the network traffic was happening between workstation and server (spoiler: is was). I also checked that my DDNS setwork was working:

$ host workstation
workstation.local-network has address 192.168.1.188
workstation.local-network has IPv6 address 2a0e:xxxx:xxxx:xxxx::3e
workstation.local-network has IPv6 address 2a0e:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:be38
$ host 2a0e:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:be38
host 2a0e:xxxx:xxxx:xxxx:xxxx:xxxx:xxxx:be38
8.3.e.b...e.0.a.2.ip6.arpa domain name pointer workstation.local-network.

So I went back into the database and configure a wildcard user:

GRANT ALL PRIVILEGES ON db_name.* TO 'user_name'@'%' to  IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

This was allowed to connect.

In writing this post I found the following just above where I configure the IPV6 binding:

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
# skip-name-resolve

So I changed it to:

skip-name-resolve         = false

And restarted MariaDB and checked:

$ mariadb -se "SHOW VARIABLES" | grep -e skip_name
skip_name_resolve       OFF

Removed the wildcard user and back I was at not being able to connect.

Is there any way I can turn off skip-name-resolve? I don't really want to allow global IP address just incase some cracker breaks through my firewall.

Note: To those that would suggest using the IP address in the user configure I'm using "prefer temporary addresses" in my NetworkManager so I get a new temporary IPv6 address about every 24 hours.

Answer 1

skip-name-resolve is off by default. So technically turing it off is setting it to true. However your configuration requires name resolution.

As noted on GRANT Manual Page, FLUSH PRIVILEGES is not needed.

As was discovered in MDEV-35663, FLUSH PRIVILEGES was causing issues making it possible to get the ER_HOST_NOT_PRIVILEGED error, which is the same one that was in the question. I don't think this is the case as it was a race condition causes this.

In resolving these there is performance_schema.host_cache that shows the state of the host cache within MariaDB after enabling performance-schema=1. Look at the COUNT_HOST_ACL_ERRORS column.

If still no resolution, create a bug report.

nit:"to IDENTIFIED" isn't the right syntax "to" isn't there