Sample Header Ad - 728x90

maxscale - getting access denied when specifying DB name, but successful when I omit it

0 votes
1 answer
57 views
We are implementing MaxScale as a DB proxy between our app and the DB hosted in AWS Aurora MySQL. I've configured MaxScale and verified the servers can all connect, and when I connect from the app server to the proxy endpoint via cli, everything works:
mysql -h proxy.end.point -u admin -p
This works just fine as expected, and opens up a connection. I can call use db_name; and change databases no problem, e.g:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 76
Server version: 8.0.32 Source 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.

MySQL [(none)]> use db_name;
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
MySQL [db_name]>
However, what's super bizarre is when I specify the db_name as a cli argument, I get a permission error:
mysql -h proxy.end.point -u admin -p db_name

ERROR 1044 (42000): Access denied for user 'admin'@'ip.of.data.base' to database 'db_name'
I'm not sure if this is an issue with the mysql admin user, or if it's some nuance with MaxScale, but was curious if anyone has any insights. For reference, I'm using a maxscale user as the proxy user and it has the following permissions:
GRANT SHOW DATABASES ON *.* TO maxscale@%
GRANT SELECT ON mysql.* TO maxscale@%
GRANT SELECT ON mysql.columns_priv TO maxscale@%
GRANT SELECT ON mysql.db TO maxscale@%
GRANT SELECT ON mysql.procs_priv TO maxscale@%
GRANT SELECT ON mysql.proxies_priv TO maxscale@%
GRANT SELECT ON mysql.tables_priv TO maxscale@%
GRANT SELECT ON mysql.user TO maxscale@%
Here is the contents of my /etc/maxscale.cnf file:
[maxscale]
threads=auto
debug=enable-statement-logging

[db_writer]
type=server
address=rds-writer-endpoint.rds.amazonaws.com
port=3306

[db_reader]
type=server
address=rds-reader-endpoint.rds.amazonaws.com
port=3306

[Read-Write-Service]
type=service
router=readwritesplit
servers=db_writer,db_reader
user=maxscale
password=maxscale_password

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=3306
I have installed maxscale version 24.02.4. Any help is appreciated. Thanks!
Asked by Brian Phelan (11 rep)
Jan 16, 2025, 03:16 PM
Last activity: Jan 17, 2025, 05:31 PM