0

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!

Brian Phelan
  • 11
  • 3

1 Answers1

1

So, after some thorough debugging, it appears the issue was the username.

Whenever I would connect with the username admin I would get permission errors, but I created a new user with identical permissions and was able to successfully connect.

So, some combination of MaxScale and connecting with the username admin was causing the issue.

The fix was to create a user with identical permissions and connect with that.

Brian Phelan
  • 11
  • 3