May 29, 2018

Simplify User Account Management with MariaDB MaxScale 2.2 and MariaDB Server 10.3

Configuring database user accounts for MariaDB MaxScale and a backend cluster has typically required a duplicate effort. This is because an account entry must exist for both the real client host and the MaxScale host. MaxScale authenticates incoming users against the user entry with the real client host. When MaxScale creates the routing session, it uses the client’s username and password scramble to authenticate the client to the backend. The backend sees the connection coming from the machine running MaxScale. Unless the host name uses wildcards (low security), entries for both hosts are required. If user accounts are often modified, this duplication may get cumbersome and lead to errors.

MariaDB Server 10.3 adds support for the proxy protocol which allows a connection to self-designate its host. The protocol states that when a connection has been established, the client should first (before responding to the MySQL handshake) send a proxy protocol header. This header contains the hostname that the server should imagine the connection originating from instead of using the real hostname. For security reasons, proxy headers are only allowed from addresses listed in the server variable “proxy_protocol_networks”. The feature thus allows select ip addresses to act as proxies without having actual user accounts on the database backend. As an example, the header “PROXY TCP4 192.168.0.1 192.168.0.2 56324 443” instructs the server to authenticate the client as if the client was connecting from 192.168.0.1.

This feature can be used to simplify user account management when using MaxScale 2.2 and MariaDB Server 10.3. To enable the feature in MaxScale, add the line “proxy_protocol=on” to a server definition in your MaxScale configuration file (typically this should be added to all server sections).

An example of a MaxScale server definition:

[MyServer1]
type=server
address=123.456.789.0
port=3306
protocol=MariaDBBackend
proxy_protocol=yes

When MaxScale attempts to create a client session on the server, MaxScale first sends a proxy header with the original hostname of the client. If the MaxScale IP is found within the “proxy_protocol_networks” of the server, the header is read and the connection authenticated using the real client address. For the server setting, see server documentation for more information.

Assuming MaxScale IP is “111.222.333.4”, add the following to the [mysqld]-section of the server configuration:

proxy_protocol_networks = 111.222.333.4

With these settings, an incoming client “normal_user” does not need to have an entry for host “111.222.333.4” in the mysql.user-table. Only an entry for the real client host is required.

Get MariaDB MaxScale 2.2 and MariaDB Server 10.3 as part of MariaDB TX 3.0, available for download now.