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
Triage: Triaged: D2 (Serious)

[20 Jul 2008 15:16] Paul Freeman
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!?
[20 Jul 2008 15:18] Paul Freeman
please ignore title typo s/reolve/resolve
[21 Jul 2008 10:58] Sveta Smirnova
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`)
[21 Jul 2008 13:36] Paul Freeman
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.
[21 Jul 2008 20:06] Sveta Smirnova
Thank you for the feedback.

Verified as described.
[21 Jul 2008 20:09] Sveta Smirnova
$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
[19 Aug 2008 13:33] Kristofer Pettersson
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.
[22 Aug 2008 14:11] Kristofer Pettersson
In addition, if the reverse lookup fails, a buffer of address-objects are doubled free which leads to a crash.
[22 Aug 2008 14:20] Kristofer Pettersson
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.
[30 Aug 2008 8:17] Kristofer Pettersson
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.
[12 Dec 2008 20:22] Earl Crowder
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);
[24 Aug 2009 17:58] Alexander Nozdrin
Merged into mysql-next.
[28 Aug 2009 11:49] Jon Stephens
See Bug #46336 for documentation.

Set status to Path Pending, waiting for push to 5.4-main.
[28 Aug 2009 11:56] Jon Stephens
Ignore previous comment (I'll have to write a separate changelog entry for each bug in this group).
[28 Aug 2009 12:05] Jon Stephens
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.
[3 Dec 2009 9:08] Bugs System
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)
[3 Dec 2009 9:10] Bugs System
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)
[3 Dec 2009 20:43] Paul Dubois
Noted in 5.6.0, 6.0.14 changelogs.
[6 Mar 2010 11:03] Bugs System
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)
[7 Mar 2010 18:22] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.