Connection Management #

Login Authentication #

Proxy has a strict login authentication mechanism that only allows connections to be established by authenticated users.

Concept #

To ensure the security of user data and distributed configuration information, DBPlusEngine-Proxy is equipped with user authentication functionality that cannot be disabled. Any unauthenticated client connections will be rejected. Presently, DBPlusEngine-Proxy supports several user authentication protocols, including:

• MySQL client: mysql_native_password, mysql_clear_password
• PostgreSQL client: MD5, password
• openGauss client: scram-sha-256

Additionally, to facilitate unified identity management for enterprise users, DBPlusEngine supports LDAP (Lightweight Directory Access Protocol) authentication, which is compatible with the MySQL and PostgreSQL clients.

- !AUTHORITY
  users:
    - user: root@%
      password: root
      authenticationMethodName: md5
    - user: sharding
      password: sharding
  authenticators:
    md5:
      type: MD5
    scram_sha256:
      type: SCRAM_SHA256
  defaultAuthenticator: scram_sha256

Features #

In fact, users who are using DBPlusEngine-Proxy need not be concerned with selecting the appropriate protocol. The negotiation process for the protocol is automatically handled by DBPlusEngine and the client.

• For MySQL clients, the default protocol used is mysql_native_password. Only when a user needs to perform LDAP authentication will DBPlusEngine request the client to switch to the mysql_clear_password protocol for communication.
• For PostgreSQL clients, the default protocol used is MD5. Only when a user needs to perform LDAP authentication will DBPlusEngine require the client to switch to the password protocol for communication.

Password Authentication #

By default, the Proxy uses password authentication, requiring users to provide the correct username and password for login.

Notably, as Proxy supports multiple database protocols (such as MySQL and PostgreSQL), it can automatically adapt the password communication protocol when users utilize different database clients. This ensures a consistent security experience for users in complex scenarios.

Host Restrictions #

Administrators can restrict login host addresses for Proxy users to improve security. For example:

- !AUTHORITY
    users:
      - user: root@127.0.0.1
        password: root

The above configuration specifies that the root user can only access the Proxy from the 127.0.0.1 address. Even if the password is correct, access from other addresses will be denied.

LDAP Authentication #

To facilitate unified authentication management for enterprise users, Proxy also provides LDAP (Lightweight Directory Access Protocol) authentication, which is now compatible with MySQL and PostgreSQL clients. Moreover, the Proxy offers flexible LDAP access options for users, such as:

1. Default LDAP configuration: The Proxy can be configured to use LDAP by default, enabling all users to be authenticated through LDAP.
2. Authentication attribute configuration: Users can specify whether to use password or LDAP authentication by configuring the “auth” attribute, allowing different users to utilize different methods.
3. LDAP authenticator configuration: Different users can use different LDAP authenticators, enabling connection with different LDAP services.
4. DN template specification: Support for specifying DN templates for users to meet the demands of complex scenarios.
5. LDAPS protocol support: The Proxy also supports the LDAPS protocol, providing further security enhancements.

Connection Configuration #

SphereEx-DBPlusEngine supports connection through MySQL or PostgreSQL. Users can connect through standard databases, and can use multiple methods such as JDBC and ODBC for connection.

Proxy + LDAP & LDAPS Application Case #

Background #

SphereEx-DBPlusEngine now supports LDAP login authentication. The following application case illustrates the process of utilizing LDAP login authentication.

In this example, Wireshark is used to capture packets to vividly display the contrast between the LDAP and LDAPS protocols.

It’s worth noting that LDAPS is an SSL/TLS-based communication method for LDAP.

Basic Environment #

• config-sharding-databases.yaml

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
    minPoolSize: 1
  ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 10
    minPoolSize: 1

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..1}.t_order
      keyGenerateStrategy:
        column: order_id
        keyGeneratorName: snowflake
  defaultDatabaseStrategy:
    standard:
      shardingColumn: user_id
      shardingAlgorithmName: database_inline
  defaultTableStrategy:
    none:
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
  keyGenerators:
    snowflake:
      type: SNOWFLAKE

LDAP Server Configuration #

Uses ApacheDS™ Docker image: tremolosecurity/apacheds.

a. Pull the image

docker pull tremolosecurity/apacheds:latest

b. Generate SSL certificates

Documentation page: https://directory.apache.org/apacheds/basic-ug/3.3-enabling-ssl.html

Use keytool to generate certificates according to the page.

Here, we take the Common name as localhost to generate two files:

.
├── localhost.ks
└── localhost.cer

Because the container used has special requirements for file names, rename localhost.ks to apacheds.jks here.

.
├── apacheds.jks
└── localhost.cer

Note:

The certificate files can be saved on any path, such as:

• /Users/${yourname}/apacheds/apacheds.jks
• /Users/${yourname}/apacheds/localhost.cer

c. Start the container

docker run --detach --rm --name apacheds \
  -p 10389:10389 \
  -p 10636:10636 \
  -v /Users/${yourname}/apacheds:/etc/apacheds \
  -e APACHEDS_ROOT_PASSWORD=secret \
  -e APACHEDS_TLS_KS_PWD=secret \
  tremolosecurity/apacheds:latest

Note:

• The container maps out two ports, with 10389 used for non-encrypted LDAP connections and 10636 used for encrypted LDAPS connections.
• ApacheDS service includes a default user which uid = admin and ou = system, and its password is specified as secret through the APACHEDS_ROOT_PASSWORD parameter.

After starting the container, check the logs to see if everything is in order:

docker logs -f apacheds

d. ldapsearch test

The ldapsearch command can be used to easily access the LDAP service and verify if it is working properly:

docker exec -it apacheds ldapsearch -x -H ldap://localhost:10389 -b ou=system -D "uid=admin,ou=system" -w secret

With this, the LDAP server configuration is complete.

Importing the JDK Certificate #

As the LDAP server uses a self-signed certificate, it needs to be imported into the JRE’s keystore on the client side before accessing it. Note that the import process requires the key for this certificate: “secret”.

keytool -import -alias localhost -keystore $JAVA_HOME/jre/lib/security/cacerts -file /Users/${yourname}/apacheds/localhost.cer

Proxy-LDAP Test #

a. server.yaml

authority:
 users:
   - user: root@%
   - user: admin
   - user: sharding
 authenticators:
   auth_ldap:
     type: LDAP
     props:
       ldap_server_url: ldap://localhost:10389
       ldap_dn_template: uid={0},ou=system
 defaultAuthenticator: auth_ldap

b. Start the Proxy.

c. Start Wireshark, begin capturing packets on port 10389.

d. MySQL client login test.

Note to specify the –enable-cleartext-plugin parameter.

# Since there is only one admin user in the LDAP server, we use admin to log in here.
# If we attempt to use other users, the login will be failed.
mysql -h 127.0.0.1 -P 3307 -A -u admin -p --enable-cleartext-plugin

After entering the password “secret”, the login is successful:

e. View the captured packets

From the captured TCP packets, we can easily find a message containing the user DN and password:

f. Summary

From the server.yaml configuration, it can be seen that using LDAP for login authentication is not complicated if there is an existing LDAP server. Only a simple configuration is required.

On the other hand, because the LDAP protocol is unencrypted, there is a risk of password leakage when using LDAP authentication over public networks.

Proxy-LDAPS Test #

a. server.yaml

The difference from LDAP is only the URL of the LDAP server is changed.

authority:
  users:
    - user: root@%
    - user: admin
    - user: sharding
  authenticators:
    auth_ldap:
      type: LDAP
      props:
        ldap_server_url: ldaps://localhost:10636
        ldap_dn_template: uid={0},ou=system
  defaultAuthenticator: auth_ldap

b. Start the proxy.

c. Start Wireshark and begin capturing packets on port 10636.

d. MySQL client login test.

Note to specify the –enable-cleartext-plugin parameter.

# Since there is only one admin user in the LDAP server, we use admin to log in here.
# If we attempt to use other users, the login will be failed.
mysql -h 127.0.0.1 -P 3307 -A -u admin -p --enable-cleartext-plugin

After entering the password “secret”, the login is successful:

e. View the captured packets

From the packets, it can be seen that TLS communication has been established between the Proxy and the LDAP server, and communication content cannot be captured:

f. Summary

With SSL/TLS encryption, user login information can be effectively protected. SphereEx-DBPlusEngine has excellent support for LDAPS, and switching from LDAP to LDAPS only requires importing certificates and replacing the URL.

Other questions #

• Is SSL connection supported?

  This can be divided into two cases: one is the connection between SphereEx-DBPlusEngine and the underlying database, and the other is the connection between the front-end and SphereEx-DBPlusEngine. For the former, it mainly depends on the JDBC driver built into SphereEx-DBPlusEngine, and SSL connection is supported for MySQL and PostgreSQL. However, for the latter, SSL connection is not currently supported by SphereEx-DBPlusEngine.

• Is connection pooling configuration supported?

  SphereEx-DBPlusEngine supports mainstream database connection pools, such as DBCP, C3P0, BoneCP, HikariCP, etc. When configuring the connection pool, we need to focus on the upper limit of the pool to meet the requirements of possible high-concurrency scenarios.

• Is load balancing configuration supported?

  SphereEx-DBPlusEngine provides load balancing for the read capability of the underlying database, as described in readwrite-splitting . For the connection between the front-end and SphereEx-DBPlusEngine, theoretically, any Layer 4 load balancing can be used as the load balancing for SphereEx-DBPlusEngine.

• Is VIP access supported by SphereEx-DBPlusEngine?

  SphereEx-DBPlusEngine itself does not provide VIP capabilities, but this can be achieved through third-party services. Treat SphereEx-DBPlusEngine as a standard service and bind it to a VIP to improve overall availability.