[#MXS-605] MaxScale not authenticating database users

[MXS-605] MaxScale not authenticating database users Created: 2016-03-07 Updated: 2016-04-15 Resolved: 2016-04-15
Status:	Closed
Project:	MariaDB MaxScale
Component/s:	Core
Affects Version/s:	1.3.0
Fix Version/s:	1.4.2

Type:

Bug

Priority:

Major

Reporter:

Rafael

Assignee:

Timofey Turenko

Resolution:

Cannot Reproduce

Votes:

Labels:

None

Environment:

Ubuntu 14.04 LTS MairaDB 10.0.24

Description

Unable to authenticate users other than pma (phpmyadmin), root and MaxScale service account.

For example, I have a test user (wordpress) that should be able to connect to database wordpress. Here is the debug log after a failed attempt:

2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] epoll_wait found 1 fds
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] event 1 dcb 0x2d66920 role DCB_ROLE_SERVICE_LISTENER
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] Accept in fd 11
2016-03-07 01:21:32 debug : 140318328051584 [dcb_write] Wrote 86 Bytes to dcb 0x2e0e3b0 in state DCB_STATE_ALLOC fd 13
2016-03-07 01:21:32 debug : 140318328051584 [poll_add_dcb] Added dcb 0x2e0e3b0 in state DCB_STATE_POLLING to poll set.
2016-03-07 01:21:32 debug : 140318328051584 [gw_MySQLAccept] Added dcb 0x2e0e3b0 for fd 13 to epoll set.
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] epoll_wait found 1 fds
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] event 4 dcb 0x2e0e3b0 role DCB_ROLE_REQUEST_HANDLER
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] epoll_wait found 1 fds
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] event 5 dcb 0x2e0e3b0 role DCB_ROLE_REQUEST_HANDLER
2016-03-07 01:21:32 debug : 140318328051584 [poll_waitevents] Read in dcb 0x2e0e3b0 fd 13
2016-03-07 01:21:32 debug : 140318328051584 [dcb_read] Read 99 bytes from dcb 0x2e0e3b0 in state DCB_STATE_POLLING fd 13.
2016-03-07 01:21:32 debug : Receiving connection from 'wordpress' to database 'wordpress'.
2016-03-07 01:21:32 debug : 140318328051584 [MySQL Client Auth], checking user [wordpress@127.0.0.1] db: wordpress
2016-03-07 01:21:32 debug : Dbusers : Loading data from backend database with Master role [192.168.1.220:3306] for service [Splitter Service]
2016-03-07 01:21:32 debug : Splitter Service: Adding database information_schema to the resouce hash.
2016-03-07 01:21:32 debug : Splitter Service: Adding database linuxmint_blog to the resouce hash.
2016-03-07 01:21:32 debug : Splitter Service: Adding database linuxmint_phpbb to the resouce hash.
2016-03-07 01:21:32 debug : Splitter Service: Adding database mysql to the resouce hash.
2016-03-07 01:21:32 debug : Splitter Service: Adding database performance_schema to the resouce hash.
2016-03-07 01:21:32 debug : Splitter Service: Adding database phpmyadmin to the resouce hash.
2016-03-07 01:21:32 debug : Splitter Service: Adding database wordpress to the resouce hash.
2016-03-07 01:21:32 debug : Loaded 7 MySQL Database Names for service [Splitter Service]
2016-03-07 01:21:32 error : Failed to obtain address for host ::1, Address family for hostname not supported
2016-03-07 01:21:32 warning: Failed to add user root@::1 for service [Splitter Service]. This user will be unavailable via MaxScale.
2016-03-07 01:21:32 warning: Duplicate MySQL user found for service [Splitter Service]: root@127.0.0.1 for database: (null)
2016-03-07 01:21:32 debug : 140318328051584 [replace_mysql_users] users' tables not switched, checksum is the same
2016-03-07 01:21:32 debug : 140318328051584 [MySQL Client Auth], checking user [wordpress@127.0.0.1] db: wordpress
2016-03-07 01:21:32 notice : Splitter Service: login attempt for user 'wordpress', authentication failed.
2016-03-07 01:21:32 debug : 140318328051584 [dcb_write] Wrote 105 Bytes to dcb 0x2e0e3b0 in state DCB_STATE_POLLING fd 13
2016-03-07 01:21:32 debug : 140318328051584 [gw_read_client_event] after gw_mysql_do_authentication, fd 13, state = MYSQL_AUTH_FAILED.
2016-03-07 01:21:32 debug : 140318328051584 [dcb_process_zombies] Remove dcb 0x2e0e3b0 fd 13 in state DCB_STATE_POLLING from the list of zombies.
2016-03-07 01:21:32 debug : 140318328051584 [dcb_maybe_add_persistent] Not adding DCB 0x2e0e3b0 to persistent pool, user , max for pool 0, error handle called false, hung flag false, server status 0, pool count -1.
2016-03-07 01:21:32 debug : 140318328051584 [gw_client_close]
2016-03-07 01:21:32 debug : 140318328051584 [dcb_process_zombies] Remove dcb 0x2e0e3b0 fd 13 in state DCB_STATE_NOPOLLING from the list of zombies.
2016-03-07 01:21:32 debug : 140318328051584 [dcb_process_victim_queue] Closed socket -1 on dcb 0x2e0e3b0.
2016-03-07 01:21:40 debug : 140317851350784 [dcb_hangup_foreach]
2016-03-07 01:21:40 debug : 140317851350784 [dcb_hangup_foreach]
2016-03-07 01:21:40 debug : 140317851350784 [dcb_hangup_foreach]
2016-03-07 01:21:40 debug : 140317851350784 [dcb_hangup_foreach]

Looking at the backend server, grants for wordpress user

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Grants for wordpress@%

GRANT USAGE ON . TO 'wordpress'@'%' IDENTIFIED BY PASSWORD '*C260A4F79FA905AF65142FFE0B9A14FE0E1519CC'

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `wordpress`.* TO 'wordpress'@'%'

Grants for wordpress@127.0.0.1

GRANT USAGE ON . TO 'wordpress'@'127.0.0.1' IDENTIFIED BY PASSWORD '*C260A4F79FA905AF65142FFE0B9A14FE0E1519CC'

Comments

Comment by markus makela [ 2016-03-07 ]

What are the grants for the MaxScale service user? I noted that the wordpress user only has grants to the wordpress database so granting the permissions to that might have an effect. The authentication should still work even if MaxScale doesn't have read access to the mysql.db table.

Excerpt from the KB:
Additionally, GRANT SELECT on the mysql.db table and SHOW DATABASES privileges are required in order to load databases name and grants suitable for database name authorization.

MariaDB [(none)]> GRANT SELECT ON mysql.db TO 'maxscale'@'maxscalehost';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscalehost';

Query OK, 0 rows affected (0.00 sec)

Please also enable the authentication warnings with log_auth_warnings=true and provide the error logs that will be generated this option enabled.

For more details about these parameters and MaxScale's configuration in general, please refer to the MaxScale configuration guide on KB: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-configuration-usage-scenarios/

Comment by Rafael [ 2016-03-08 ]

MaxScale service user has GRANT full access on all databases on the server:

here are the debug logs:

2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] epoll_wait found 1 fds
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] event 1 dcb 0x39aa920 role DCB_ROLE_SERVICE_LISTENER
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] Accept in fd 11
2016-03-07 20:17:24 debug : 140360515295104 [dcb_write] Wrote 86 Bytes to dcb 0x3a533a0 in state DCB_STATE_ALLOC fd 13
2016-03-07 20:17:24 debug : 140360515295104 [poll_add_dcb] Added dcb 0x3a533a0 in state DCB_STATE_POLLING to poll set.
2016-03-07 20:17:24 debug : 140360515295104 [gw_MySQLAccept] Added dcb 0x3a533a0 for fd 13 to epoll set.
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] epoll_wait found 1 fds
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] event 4 dcb 0x3a533a0 role DCB_ROLE_REQUEST_HANDLER
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] epoll_wait found 1 fds
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] event 5 dcb 0x3a533a0 role DCB_ROLE_REQUEST_HANDLER
2016-03-07 20:17:24 debug : 140360515295104 [poll_waitevents] Read in dcb 0x3a533a0 fd 13
2016-03-07 20:17:24 debug : 140360515295104 [dcb_read] Read 99 bytes from dcb 0x3a533a0 in state DCB_STATE_POLLING fd 13.
2016-03-07 20:17:24 debug : Receiving connection from 'wordpress' to database 'wordpress'.
2016-03-07 20:17:24 debug : 140360515295104 [MySQL Client Auth], checking user [wordpress@127.0.0.1] db: wordpress
2016-03-07 20:17:24 debug : Dbusers : Loading data from backend database with Master role [192.168.1.220:3306] for service [Splitter Service]
2016-03-07 20:17:24 debug : Splitter Service: Adding database information_schema to the resouce hash.
2016-03-07 20:17:24 debug : Splitter Service: Adding database linuxmint_blog to the resouce hash.
2016-03-07 20:17:24 debug : Splitter Service: Adding database linuxmint_phpbb to the resouce hash.
2016-03-07 20:17:24 debug : Splitter Service: Adding database mysql to the resouce hash.
2016-03-07 20:17:24 debug : Splitter Service: Adding database performance_schema to the resouce hash.
2016-03-07 20:17:24 debug : Splitter Service: Adding database phpmyadmin to the resouce hash.
2016-03-07 20:17:24 debug : Splitter Service: Adding database wordpress to the resouce hash.
2016-03-07 20:17:24 debug : Loaded 7 MySQL Database Names for service [Splitter Service]
2016-03-07 20:17:24 error : Failed to obtain address for host ::1, Address family for hostname not supported
2016-03-07 20:17:24 warning: Failed to add user root@::1 for service [Splitter Service]. This user will be unavailable via MaxScale.
2016-03-07 20:17:24 warning: Duplicate MySQL user found for service [Splitter Service]: root@127.0.0.1 for database: (null)
2016-03-07 20:17:24 debug : 140360515295104 [replace_mysql_users] users' tables not switched, checksum is the same
2016-03-07 20:17:24 debug : 140360515295104 [MySQL Client Auth], checking user [wordpress@127.0.0.1] db: wordpress
2016-03-07 20:17:24 notice : Splitter Service: login attempt for user 'wordpress', authentication failed.
2016-03-07 20:17:24 debug : 140360515295104 [dcb_write] Wrote 105 Bytes to dcb 0x3a533a0 in state DCB_STATE_POLLING fd 13
2016-03-07 20:17:24 debug : 140360515295104 [gw_read_client_event] after gw_mysql_do_authentication, fd 13, state = MYSQL_AUTH_FAILED.
2016-03-07 20:17:24 debug : 140360515295104 [dcb_process_zombies] Remove dcb 0x3a533a0 fd 13 in state DCB_STATE_POLLING from the list of zombies.
2016-03-07 20:17:24 debug : 140360515295104 [dcb_maybe_add_persistent] Not adding DCB 0x3a533a0 to persistent pool, user , max for pool 0, error handle called false, hung flag false, server status 0, pool count -1.
2016-03-07 20:17:24 debug : 140360515295104 [gw_client_close]
2016-03-07 20:17:24 debug : 140360515295104 [dcb_process_zombies] Remove dcb 0x3a533a0 fd 13 in state DCB_STATE_NOPOLLING from the list of zombies.
2016-03-07 20:17:24 debug : 140360515295104 [dcb_process_victim_queue] Closed socket -1 on dcb 0x3a533a0.

Comment by markus makela [ 2016-03-18 ]

Could you execute the following query on the backend database:

ELECT

    user.user AS user,

    user.host AS host,

    user.password AS password,

    concat(user.user,user.host,user.password,user.Select_priv,IFNULL(db,'')) AS userdata,

    user.Select_priv AS anydb,

    db.db AS db

    FROM mysql.user LEFT JOIN mysql.db

    ON user.user=db.user AND user.host=db.host

    WHERE user.user IS NOT NULL;

This is the query that MaxScale executes when retrieving database users. Check if the wordpress user is in that list.

Comment by markus makela [ 2016-03-21 ]

Please test this with the 1.4.0 version of MaxScale.

Comment by markus makela [ 2016-04-15 ]

ralphy I'm closing this until further progress is made. If possible, test this with 1.4.1 and reopen it if it still happens.

Generated at Thu Feb 08 04:00:34 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MXS-605] MaxScale not authenticating database users Created: 2016-03-07 Updated: 2016-04-15 Resolved: 2016-04-15