Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
None
-
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.