[MXS-2482] Can't connect to information_schema database Created: 2019-05-13  Updated: 2019-05-28  Resolved: 2019-05-28

Status: Closed
Project: MariaDB MaxScale
Component/s: N/A
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Nicolas Bigler Assignee: Todd Stoffel (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

I'm currently struggling with maxscale when trying to connect to the information_schema database.

I'm able to connect to this special database when connecting to the database server directly:

mysql -h <my_db_server> information_schema
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 101189
 
Server version: 10.2.23-MariaDB-1:10.2.23+maria~bionic-log mariadb.org
 
binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input
 
statement.
 
 
 
MariaDB [information_schema]>

However I can't connect to the database when going through maxscale:

mysql -h 127.0.0.1 information_schema
 
ERROR 1045 (28000): Access denied for user '<my_user>'@'127.0.0.1'
 
(using password: YES) to database 'information_schema'

What does work is to connect to the database first (via maxscale)
without specifying a database and then change to the information_schema
database:

mysql -h 127.0.0.1
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 25321
 
Server version: 10.2.23-MariaDB-1:10.2.23+maria~bionic-log mariadb.org
 
binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input
 
statement.
 
 
 
MariaDB [(none)]> use information_schema
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Database changed

I don't quite understand why it doesn't work directly.
I have a PHP application (magento2) that reads stuff from this database.

The following PHP script shows the same issue:

<?php
 
# Fill our vars and run on cli
 
# $ php -f db-connect-test.php
 
$dbname = 'information_schema';
 
$dbuser = '<my_db_user>';
 
$dbpass = '<my_super_secret_pass';
 
$dbhost = '127.0.0.1';
 
 
 
# pdo_testdb_connect.php - function for connecting to the "test" database
 
 
 
$dbh = new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser,
 
$dbpass);
 
 
 
echo $dbh->exec ("SHOW TABLES FROM $dbname");
 
 
 
?>
 
$ php db-connect-test.php
 
PHP Fatal error:  Uncaught PDOException: SQLSTATE[HY000] [1045] Access
 
denied for user '<my_db_user>'@'127.0.0.1' (using password: YES) to
 
database 'information_schema' in /tmp/db-connect-test.php:12
 
Stack trace:
 
#0 /tmp/db-connect-test.php(12): PDO->__construct('mysql:host=127....',
 
'<my_db_user>', '<my_super_secret_pass>...')
 
#1 {main}
 
  thrown in /tmp/db-connect-test.php on line 12

The maxscale log file (with log_info=1) shows the following:

2019-05-13 11:02:09   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('debian-sys-maint', 'localhost', NULL, 1, '<redacted_pw>')
 
2019-05-13 11:02:09   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('maxscale', '%', NULL, 0, '<redacted_pw>')
 
2019-05-13 11:02:09   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('monitoring', 'localhost', NULL, 0, '<redacted')
 
2019-05-13 11:02:09   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('<my_db_user>', '%', '<my_db_user>', 0, '<redacted_pw>')
 
2019-05-13 11:02:09   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('maxscale', '%', 'mysql', 0, '<redacted_pw>')
 
2019-05-13 11:02:09   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('<my_db_user>', '%', NULL, 0, '<redacted_pw>')
 
2019-05-13 11:02:09   warning: [MySQLAuth] MasterOnly-Service: login attempt for user '<my_db_user>'@[127.0.0.1]:55592, authentication failed. User not found.

There is nothing regarding failed authentication in the logs on the db server itself.

My assumption: The `information_schema` database is a special case in mysql/mariadb where the some GRANTS (eg. SELECT, CONNECT) are implicitly granted to every user and are not listed when using `SHOW GRANTS`

Maxscale first receives a list of GRANTS for the user and determines that the user does not have access to the `information_schema` database and therefore rejects the connection, even though the user has access.

 Comments   
Comment by Todd Stoffel (Inactive) [ 2019-05-14 ]

This looks like a permissions problem. Can you please show the grants of your maxscale user?

SHOW GRANTS FOR '<maxscale_user>'@'<maxscale_host>';

As well as the grants for your mysql user?

SHOW GRANTS FOR '<mysql_user>'@'<maxscale_host>';

It is important to note that both users must have your MaxScale address listed as the host.

Typical permissions should look like this:

Maxscale User:

GRANT RELOAD, SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscale'@'<maxscale_address>' IDENTIFIED BY PASSWORD '*72BCD431560AE71506457E0B36B734EE0E64CA43'
 
GRANT SELECT ON `mysql`.`db` TO 'maxscale'@'<maxscale_address>';
 
GRANT SELECT ON `mysql`.`user` TO 'maxscale'@'<maxscale_address>';
 
GRANT SELECT ON `mysql`.`tables_priv` TO 'maxscale'@'<maxscale_address>';
 
GRANT SELECT ON `mysql`.`roles_mapping` TO 'maxscale'@'<maxscale_address>';

MariaDB/MySQL User:

GRANT SELECT ON *.* TO 'mysql_user'@'%' IDENTIFIED BY PASSWORD '*72BCD431560AE71506457E0B36B734EE0E64CA43';

Also, where is MaxScale installed? The warning about the loopback IP in your ticket is unusual.

From your description, it appears you have MaxScale, MariaDB and Magento all running on the same machine. If that is the case, you'll have two services trying to listen on port 3306 for address 127.0.0.1

Can you please clarify and include your maxscale.cnf in your response?

Comment by Nicolas Bigler [ 2019-05-16 ]

Hi

Here are the grants for the two users:

MariaDB [(none)]> SHOW GRANTS FOR maxscale;
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Grants for maxscale@%                                                                                                                                                     |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| GRANT SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxscale'@'%' IDENTIFIED BY PASSWORD '<pw_redacted>' WITH GRANT OPTION                             |
 
| GRANT SELECT ON `mysql`.`db` TO 'maxscale'@'%' WITH GRANT OPTION                                                                                                          |
 
| GRANT SELECT ON `mysql`.`user` TO 'maxscale'@'%' WITH GRANT OPTION                                                                                                        |
 
| GRANT SELECT ON `mysql`.`tables_priv` TO 'maxscale'@'%' WITH GRANT OPTION                                                                                                 |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
4 rows in set (0.01 sec)
 
MariaDB [(none)]> SHOW GRANTS FOR <mysql_user>;
 
+------------------------------------------------------------------------------------------------------------+
 
| Grants for <mysql_user>@%                                                                                  |
 
+------------------------------------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO '<mysql_user>'@'%' IDENTIFIED BY PASSWORD '<pw_redacted>'                            |
 
| GRANT ALL PRIVILEGES ON `<mysql_user_db>`.* TO '<mysql_user>'@'%'                                          |
 
+------------------------------------------------------------------------------------------------------------+
 
3 rows in set (0.00 sec)

Maxscale is installed on the application server where magento is running and listens on port 3306 on 127.0.0.1
MariaDB is running as a 3-node galera cluster on seperate machines and listens on port 3306 on any interface.

Here is the maxscale.cnf:

cat /etc/maxscale.cnf
 
[maxscale]
 
threads=auto
 
 
 
[ReadWriteSplit Service]
 
localhost_match_wildcard_host=1
 
passwd=<pw_redacted>
 
router=readwritesplit
 
servers=db1,db2,db3
 
type=service
 
user=maxscale
 
 
 
[ReadWriteSplit Listener]
 
address=127.0.0.1
 
port=3307
 
protocol=MySQLClient
 
service=ReadWriteSplit Service
 
type=listener
 
 
 
[MasterOnly Service]
 
localhost_match_wildcard_host=1
 
passwd=<pw_redacted>
 
router_options=master
 
router=readconnroute
 
servers=db1,db2,db3
 
type=service
 
user=maxscale
 
 
 
[MasterOnly Listener]
 
address=127.0.0.1
 
port=3306
 
protocol=MySQLClient
 
service=MasterOnly Service
 
type=listener
 
 
 
[Galera Monitor]
 
module=galeramon
 
passwd=<pw_redacted>
 
servers=db1,db2,db3
 
type=monitor
 
use_priority=true
 
user=maxscale
 
disable_master_failback=false
 
 
 
[CLI]
 
type=service
 
router=cli
 
 
 
[CLI Listener]
 
type=listener
 
service=CLI
 
protocol=maxscaled
 
address=localhost
 
port=6603
 
 
 
[db1]
 
type=server
 
address=<ip_db2>
 
port=3306
 
protocol=MySQLBackend
 
priority=1
 
 
 
[db2]
 
type=server
 
address=<ip_db2>
 
port=3306
 
protocol=MySQLBackend
 
priority=2
 
 
 
[db3]
 
type=server
 
address=<ip_db3>
 
port=3306
 
protocol=MySQLBackend
 
priority=3

Comment by Todd Stoffel (Inactive) [ 2019-05-16 ]

You are using a wildcard for your hostname in your GRANT statements. The wildcard is for normal TCP connections. However loopback (127.0.0.1) and localhost need to be explicitly set.

In your MariaDB database, Add another entry for your user with @'127.0.0.1' and then restart your MaxScale process.

Comment by Nicolas Bigler [ 2019-05-17 ]

I have added the GRANT entry for the localhost address:

MariaDB [(none)]> show grants for '<mysql_user>'@'127.0.0.1';
 
+--------------------------------------------------------------------------------------------------------------------+
 
| Grants for <mysql_user>@127.0.0.1                                                                                  |
 
+--------------------------------------------------------------------------------------------------------------------+
 
| GRANT USAGE ON *.* TO '<mysql_user>'@'127.0.0.1' IDENTIFIED BY PASSWORD '<pw_redacted>'                            |
 
| GRANT ALL PRIVILEGES ON `<mysql_user>`.* TO '<mysql_user>'@'127.0.0.1'                                             |
 
+--------------------------------------------------------------------------------------------------------------------+
 
2 rows in set (0.00 sec)

and I have restarted the maxscale service.
However I still get the same error:

$ mysql information_schema
 
ERROR 1045 (28000): Access denied for user '<mysql_user>'@'127.0.0.1' (using password: YES) to database 'information_schema'

I'm using this .my.cnf for the connection:

[client]
 
user="<mysql_user>"
 
password="<pw_redacted>"
 
host="127.0.0.1"

The log output of maxscale:

2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('debian-sys-maint', 'localhost', NULL, 1, '<pw_redacted>')
 
2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('maxscale', '%', NULL, 0, '<pw_redacted>')
 
2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('<mysql_user>', '%', '<mysql_user>', 0, '<pw_redacted>')
 
2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('<mysql_user>', '127.0.0.1', '<mysql_user>', 0, '<pw_redacted>')
 
2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('maxscale', '%', 'mysql', 0, '<pw_redacted>')
 
2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('<mysql_user>', '%', NULL, 0, '<pw_redacted>')
 
2019-05-17 10:52:53   info   : Added user: INSERT OR REPLACE INTO mysqlauth_users VALUES ('<mysql_user>', '127.0.0.1', NULL, 0, '<pw_redacted>')
 
2019-05-17 10:52:53   warning: [MySQLAuth] MasterOnly-Service: login attempt for user '<mysql_user>'@[127.0.0.1]:51010, authentication failed. User not found.

Comment by Todd Stoffel (Inactive) [ 2019-05-17 ]

I see the issue now, you are trying to SELECT from information_schema even though that user has no SELECT rights for that schema. The USAGE privilege may not be doing what you think it is. Read about the USAGE permission here:

https://mariadb.com/kb/en/library/grant/#the-usage-privilege

Change that GRANT to be:

GRANT SELECT ON *.* TO '<mysql_user>'@'127.0.0.1' IDENTIFIED BY PASSWORD '<pw_redacted>'

Comment by Nicolas Bigler [ 2019-05-28 ]

Thanks for the feedback.
Changing the GRANT solved the problem.

Therefore this issue can be closed.

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