Enhancing Database Security: ShardingSphere-Proxy’s Authentication
ShardingSphere-Proxy is a transparent database proxy that supports interaction with any client using MySQL, PostgreSQL, or openGauss protocols.
Proxy provides user authentication and can adapt to different authentication modes for various database protocols. However, there is a question that has been rarely addressed and has almost no corresponding issue even in the Github community:
“How does ShardingSphere-Proxy authenticate its clients?”
Note: this post only discusses password authentication, and does not include non-password authentication methods such as UDS (Unix Domain Socket).
To begin, let’s take a look at how several common databases authenticate their users.
MySQL 5.7 as an example, you can easily understand the authentication interaction between the client and the server as follows:
The client and the server achieve protocol handshake, including negotiating which authentication protocol to use. The default value is
The server generates 20-byte random data and sends it to the client.
Based on the random data, the client encrypts passwords entered by users, then sends the encrypted information to the server for password verification. 
MySQL Native Authentication Process
Above is a brief description of MySQL Native Authentication. It can help us understand what happens once users enter passwords.
To adapt to different scenarios, MySQL provides multiple authentication protocols in a plugin form .
mysql_native_password: native authentication, used as the default before version 8.0.
caching_sha2_password: SHA-256-based cache authentication, used as the default after version 8.0.
mysql_clear_password: clear text password authentication, suitable for certain scenarios.
MySQL enterprise versions also provide authentication plugins such as:
authentication_windows: Windows service-based authentication.
authentication_ldap_simple: LDAP-based authentication.
Now let’s take a look at the authentication mechanisms of PostgreSQL and openGauss.
PostgreSQL Common authentication methods for PostgreSQL  include:
scram-sha-256: SHA-256 authentication based on SCRAM (Salted Challenge Response Authentication Mechanism).
md5: using MD5 encryption.
password: using clear text passwords.
openGauss Common authentication methods for openGauss  include：
scram-sha-256: SHA-256 authentication based on SCRAM.
md5: using MD5 encryption.
sm3: using SM3 encryption.
As a powerful database proxy, ShardingSphere-Proxy supports multiple database protocols and provides user authentication through its AuthenticationEngine.
The goal of AuthenticationEngine is to achieve protocol handshake and identity authentication.
ShardingSphere-Proxy supports handshake and authentication protocols for MySQL, PostgreSQL and openGauss, and provides multiple authentication algorithms, including:
Please note that in Proxy, the default authentication algorithms for MySQL, PostgreSQL and openGauss are
ShardingSphere-Proxy Authentication Configuration
In version 5.3.2, ShardingSphere added authenticator-related configuration items to allow users to specify authentication algorithms as needed when using Proxy. The format is as follows:
authority: users: - user: # Combination of the authorized host and the username used to log in to the computer node. Format：<username>@<hostname>. When hostname is % an empty string, it indicates no limit to the authorized host. password: # User password. authenticationMethodName: # Optional, used to specify the password authentication method for users. authenticators: # Optional, no configuration is required by default. Proxy is automatically selected based on the front-end protocol type. authenticatorName: type: # Password authentication type. defaultAuthenticator: # It is optional that you can specify an authenticatorName as the default password authentication method. privilege: type: # Type of Authority provider, with a default value of ALL_PERMITTED.
defaultAuthenticator are all optional and only configured when needed.
Proxy also supports user-level authentication configuration, where users can use different authentication algorithms.
Now, let’s take
openGauss as an example to explain how to use the newly-added MD5 authentication algorithm to log in to psql.
Before making specific configurations, we compare the performance of
psql connecting to Proxy.
authority: users: - user: root@% password: root - user: sharding password: sharding props: proxy-frontend-database-protocol-type: openGauss
openGaussasthe front-end protocol.
No specified authentication type. Proxy adopts the default value of
databaseName: sharding_db dataSources: ds_0: url: jdbc:opengauss://127.0.0.1:15432/demo_ds username: username password: password connectionTimeoutMilliseconds: 30000 idleTimeoutMilliseconds: 60000 maxLifetimeMilliseconds: 1800000 maxPoolSize: 10 minPoolSize: 1
Here we use gsql in opengauss:3.1.0 to access ShardingSphere-Proxy.
We see that it’s actually due to the authentication protocol.
The psql client requires md5 protocol authentication by default, but because Proxy requires the scram-sha-256 under the openGuass protocol, the negotiation fails and an exception is thrown.
Now we specify MD5 as the authentication method for sharding users, while retaining the default setting of using
scram-sha-256 to support different users and clients.
authority: users: - user: root@% password: root - user: sharding password: sharding authenticationMethodName: md5 authenticators: md5: type: MD5 scram_sha256: type: SCRAM_SHA256 # SCRAM_SHA256 is the SPI name that provides scram-sha-256 authentication alogorithm defaultAuthenticator: scram_sha256 props: proxy-frontend-database-protocol-type: openGauss
- We specify
openGaussasthe front-end protocol.
- We specify
MD5as the authentication algorithm for sharding users.
- The specified default authentication remains
scram-sha-256, which means root users require
psql login as root user
Psql fails to connect because no
scram-sha-256 authentication mechanism is supported. Then what about the sharding user?
psql login as sharding user
Now we see that
psql has successfully connected to ShardingSphere-Proxy under the openGuass protocol.
ShardingSphere has already implemented a framework in Proxy for different database protocols and authentication algorithms. We have also provided optional authentication algorithms for several database protocols. In the future, we aim to expand our support for authentication algorithms across a wider range of database protocols.
We believe that our community is the key to making ShardingSphere better. We welcome more people to join us and contribute to the development of the project.
In this post, we discussed the configuration of authentication protocols for ShardingSphere-Proxy. For more information on this topic, please refer to the official documentation on our website .
If you have any questions or suggestions about Apache ShardingSphere, please feel free to raise them in the GitHub issue list , or visit our Slack community  for further discussion.
 MySQL Native Authentication
 MySQL Pluggable Authentication
 PostgreSQL Password Authentication
 openGauss Configuration File Reference
 ShardingSphere-Proxy Authentication and Authorization
 Slack Community