MySQL Bugs: #38247: Server does not resolve connecting ip's

Bug #38247	Server does not resolve connecting ip's
Submitted:	20 Jul 2008 15:16	Modified:	7 Mar 2010 18:22
Reporter:	Paul Freeman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	6.0.5-alpha,6.0-bzr,cluster-7.0.5,,5.4	OS:	Linux (Gentoo 2.6.24)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	dns, privilege, resolve. security

Description:
Server does not resolve the ip's of incoming connections, (worked in 6.0.4-alpha and earlier) ip is not resolved to hostname resulting in connection being dropped where privilege table entries use fqdn hosts or wildcard hosts. 

the ip used in testing has an fqdn with correct reverse and forward dns entries, (also /etc/hosts has been tried)

skip-name-resolve is not enabled although the server would appear to be behaving as if this option were enabled!

a wireshark tap also shows no dns queries attempted by the mysql server during connection/authentication, nameservice caching was also disabled to test this

How to repeat:
setup 6.0.5-alpha with privileges based on the resolved hostname of the connecting ip address, to find that it is not resolved and the connection is rejected.

Suggested fix:
there seems to have been a number of changes dns/hostname handling functions in 6.0.5, possibly the problem lies in this area!?

please ignore title typo s/reolve/resolve

Thank you for the report.

Please provide output of query `select user, host from mysql.user`, name of host you are trying to connect from, command line you are trying to connect with (like `mysql -uroot -h127.0.0.1 -p`)

output from connecting client on host a6k-lan.trl.noc4.net. (192.168.0.8)
mysql -u testuser --password=testpass -h 192.168.0.9 -P 3307
ERROR 1045 (28000): Access denied for user 'testuser'@'192.168.0.8' (using password: YES)

dig output, on server host:

dig 8.0.168.192.in-addr.arpa ptr
;; QUESTION SECTION:
;8.0.168.192.in-addr.arpa.	IN	PTR
;; ANSWER SECTION:
8.0.168.192.in-addr.arpa. 30	IN	PTR	a6k-lan.trl.noc4.net.

dig a6k-lan.trl.noc4.net a
;; QUESTION SECTION:
;a6k-lan.trl.noc4.net.		IN	A
;; ANSWER SECTION:
a6k-lan.trl.noc4.net.	1227	IN	A	192.168.0.8

mysql.user entries:

+---------------+------------------------+
| user          | host                   |
+---------------+------------------------+
| cache         | %                      | 
| testuser      | %.noc4.net             | 
+---------------+------------------------+

setting testuser's host to 192.168.0.8 seems to be all that works currently.

Thank you for the feedback.

Verified as described.

$uname -n
shella.mysql.com

$mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.7-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> grant all on *.* to 'bug38247'@'%.mysql.com';
Query OK, 0 rows affected (0.02 sec)

mysql> grant all on *.* to 'bug38247_1'@'shella.mysql.com';
Query OK, 0 rows affected (0.00 sec)

mysql> \q
Bye

$mysql -h shella -P33160 -ubug38247
ERROR 1130 (HY000): Host '::ffff:10.100.1.73' is not allowed to connect to this MySQL server

$mysql -h shella -P33160 -ubug38247_1
ERROR 1130 (HY000): Host '::ffff:10.100.1.73' is not allowed to connect to this MySQL server

With version 5.1 and 6.0.3 all work fine

mysql> show grants for 'huggla'@'83.226.130.167';
+--------------------------------------------------------+
| Grants for huggla@83.226.130.167                       |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO 'huggla'@'83.226.130.167'        | 
| GRANT SELECT ON `test2`.* TO 'huggla'@'83.226.130.167' | 
+--------------------------------------------------------+
2 rows in set (0.01 sec)

=> Connection fails for machine on IP 83.226.130.167 to connect as 'huggla'.

mysql> grant select on test2.* to 'huggla'@'::ffff:83.226.130.167';
Query OK, 0 rows affected (0.00 sec)

=> Connection succeeds.

Seems server is per default translating all IPv4 addresses to compressed IPv6 representations.

The privilege system in itself is ignorant of IPv6, and IP numbers are compared to each other, not as numbers but as strings (example: acl_update_db).

A quick-hackish solution is to investigate all IPs to be stored in the user- and host tables and make sure the leading ::ffff: prefix is stripped.

In addition, if the reverse lookup fails, a buffer of address-objects are doubled free which leads to a crash.

If the authentication protocol was a plugin and authentication didn't care for hosts then a dual stack approach would make sense. The real patch for this is to revert the IPv6 stack patch and introduce a new socket listener so that the server has a separate interface for IPv6 and a legacy IPv4 interface.

Marc pointed out that creating a new service just for IPv6 is way overkill, and the application is suppose to be independent of the network layer anyway. Hence the dual-stack approach taken is the right way to choose according to him. An improved patch would: 1. address the broken support for the host name cache; we fix this by projecting all IPv4 addresses to IPv6 addresses. 2. Fix acl_get_* functions to handle IPv6 address lookups; WL4526 created for this.

Also, a new bug report will be created to address the crash bugs when reverse host name lookup fail in ip_to_hostname.

After applying the patch to my installation, hostnames were still not being resolved.

I believe that the call to getnameinfo is the culprit, specifying NI_NUMERICHOST returns the IP address rather than the hostname.

  gxi_error= getnameinfo((struct sockaddr *)in, addrLen,
                         hostname_buff, NI_MAXHOST,
                         NULL, 0, NI_NUMERICHOST);

I believe the call should be:

  gxi_error= getnameinfo((struct sockaddr *)in, addrLen,
                         hostname_buff, NI_MAXHOST,
                         NULL, 0, 0);

Merged into mysql-next.

See Bug #46336 for documentation.

Set status to Path Pending, waiting for push to 5.4-main.

Ignore previous comment (I'll have to write a separate changelog entry for each bug in this group).

Documented bugfix in the NDB-7.0.7 changelog as follows:

        An IPv6-enabled MySQL server did not resolve the IP addresses of
        incoming connections correctly, with the result that a
        connection that attempted to match any privilege table entries
        using fully-qualified domain names for hostnames or hostnames
        using wildcards were dropped.

Left status as Patch Pending, waiting for additional merges.

Pushed into 5.6.0-beta (revid:alik@sun.com-20091202160026-699xnqx8c6a6t2lk) (version source revid:alik@sun.com-20091125105323-j82ts9ns936i45vv) (merge vers: 5.6.0-beta) (pib:13)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091202161413-u81nw1foglcu7nao) (version source revid:alik@sun.com-20091125105553-vvedtv1bpmugb1nj) (merge vers: 6.0.14-alpha) (pib:13)

Noted in 5.6.0, 6.0.14 changelogs.

Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:wlad@sol-20091204200504-9d3d3b6ad75ijkqo) (merge vers: 5.6.0-beta) (pib:16)

Moved 5.6.0 changelog entry to 5.5.3.