Native LDAP Support for MariaDB Xpand


Xpand provides native authentication and authorization support through the Lightweight Directory Access Protocol (LDAP).

After configuration, each Xpand node in the cluster is able to directly process login requests for LDAP users by making connections with one or multiple LDAP servers using the ldap:// or ldaps:// protocols.

LDAP users are defined on the LDAP server, and not also in the Xpand database.

This provides a convenient option for system administrators to define a centralized access control of users and group permissions, without having to duplicate the setup for each distinct Xpand cluster or any other service that can use LDAP.


  • MariaDB Xpand 23.09

LDAP Login Process

When an LDAP user establishes an SQL connection with any Xpand node in the cluster, that node will defer to the LDAP server to authenticate them.

It is not necessary nor recommended to first create corresponding user accounts in the Xpand database. The definition of LDAP user accounts should only exist in the LDAP server directory. If a user account definition that matches the connecting user already exists in Xpand, that local Xpand user account will be used instead of contacting the LDAP server.

As per the configuration of the Xpand database via global variables, the connecting client's username and origin are automatically translated into a corresponding LDAP user distinguished name (DN), which Xpand will use to authenticate that user with the LDAP server. Xpand will proxy the user's password sent via the SQL client to the LDAP server during the authentication process.

Once Xpand completes authenticating an LDAP user, automatic authorization is applied as appropriate to the user's SQL session according to the current SQL global variable configuration. To achieve this, Xpand first collects the corresponding LDAP group DNs from the LDAP server for the target user.

Once Xpand has collected the LDAP user's list of LDAP group DNs, it will transform them into corresponding Xpand roles and grant those roles to the user's SQL session. These Xpand roles do need to be manually created on Xpand as a one-time setup. If there is no corresponding role in Xpand, then the corresponding LDAP group DN has no influence on the user's permission in Xpand.

To recap, the automatic LDAP login process for Xpand operates in 3 main stages:

  1. Transform the connecting Xpand client's username/hostname into an LDAP user DN, and authenticate using the derived LDAP user DN with the LDAP server.

  2. Query the LDAP server for a list of LDAP group DNs for that user.

  3. Transform the user's list of LDAP group DNs into regular Xpand roles, and grant them on the user's SQL session.

Requirements for Using LDAP with Xpand

LDAP Server

To use LDAP with Xpand, you must first have at least one LDAP server setup somewhere in your network. Ideally the LDAP server will be colocated with the nodes in your Xpand cluster to achieve the lowest latency possible. You can configure Xpand to round-robin its LDAP queries across multiple LDAP servers, so long as each LDAP server presents the same directory structure.

Simple Password Authentication

Xpand currently supports simple password authentication for authenticating a user with LDAP.

For security, Xpand requires that LDAP user passwords sent from an SQL client must be sent over an encrypted SSL (TLS) connection. To further ensure that LDAP user passwords and other LDAP directory data is protected on the network between Xpand and the LDAP server, the use of ldaps:// is highly recommended.

Only use the potentially unencrypted ldap:// protocol if you have taken the appropriate precautions to isolate the network between the Xpand nodes and the LDAP servers.


If you wish to use ldaps:// to connect to an LDAP server that has either a self-signed certificate or privately-signed certificate, then you must also provide each Xpand node with a copy of the certificate authority's certificate that signed the LDAP server's certificate.

This step is not necessary for ldaps:// connections to LDAP servers with certificates signed by the commonly-used certificate authorities already trusted by most systems.

Handling Anonymous Queries to the LDAP Server

If your LDAP server is configured to disallow anonymous queries, you will need to prepare an LDAP user DN that Xpand can use to run LDAP queries. Xpand will never attempt to modify the LDAP directory, so this query user can be limited to read-only access to the parts of the LDAP directory that will be queried by Xpand.

LDAP Directory Structure

One final requirement relates to the LDAP directory structure itself. Xpand provides a concise, yet expressive, collection of SQL global variables for configuring how Xpand connects to and queries the LDAP server.

There are a number of ways you can arrange user and group relationships on an LDAP server.

One common approach is to add key attribute values to the LDAP user entry itself, one value per LDAP group DN. Another common approach is to add key attribute values to the LDAP group entry, one value per LDAP user DN. Other, more novel directory layouts are possible.

The main requirement is that, to provide authorization for any given LDAP user DN, you must be able to compose a single RFC 4516-formatted LDAP query URL that can retrieve a list of LDAP group DNs for the target LDAP user DN.

For more information about configuring the group query string, please see ldap_group_query system variable.

LDAP User Handling

LDAP users are handled differently from standard users on Xpand. One key difference is that there is no persistent record of a particular LDAP user in Xpand's catalog.

There is no need to preemptively create a corresponding user with CREATE USER for every LDAP user you'd like to use with Xpand. In fact, manually creating users on Xpand for each LDAP user would be counter to the main purpose of using LDAP in the first place, which is to instead define the user and group relationships in one centralized place: the LDAP server.

This means that some SQL statements that refer to users previously defined in the database are not applicable to LDAP users (since they are not explicitly defined in Xpand).


Xpand has guardrails in place to prevent statements incompatible with LDAP users. Some of these include:

  • Privileges and roles cannot be explicitly granted to an LDAP user.

    • All permissions granted to an LDAP user on Xpand are instead derived as Xpand roles from the LDAP global variable configuration. These roles are granted implicitly to the LDAP user's session at time of login. To change an LDAP user's granted permissions, either change the permissions granted to a particular role, or change the LDAP configuration so that a user is granted different roles.

    • The GRANT statement can implicitly create users. Since LDAP users do not technically exist in Xpand's catalog, take care to avoid accidentally creating new Xpand users with the same name as the LDAP user when running statements such as GRANT

  • SET DEFAULT ROLE cannot be used for an LDAP user.

    • LDAP users on Xpand must either explicitly enable the roles they want to act as with SET ROLE, or the global variable activate_all_roles_on_login can be enabled to implicitly activate all roles at time of login.

  • LDAP users cannot be specified as the DEFINER for CREATE TRIGGER, CREATE PROCEDURE, or CREATE FUNCTION

    • Instead, a native Xpand user or role should be used as the DEFINER

  • SET PASSWORD for an LDAP user is not allowed.

    • LDAP user passwords are to instead be managed on the LDAP server.

  • Permission changes to roles are reflected immediately in any current LDAP user sessions on Xpand.

Changes to Roles with LDAP

Changes to roles applied to current LDAP user sessions by commands such as GRANT and REVOKE are reflected immediately upon those sessions. The same immediate changes are reflected when nested roles are used.

Roles are applied to LDAP user sessions at login time only. If a group membership change occurs on the LDAP server, any potential role changes on Xpand would only happen when new LDAP user logins occur. Current LDAP user sessions would have to close their Xpand connection and form a new one to see any potential changes.

Dropping a role with DROP will immediately remove the role from any current LDAP sessions on Xpand. Recreating the same role will not add the role back to those same LDAP sessions. The user will have to create a new Xpand connection to have the recently recreated role applied to their session again.

Mitigate the Impact of an LDAP Server Outage

When configuring SQL replication, use a local Xpand user (non-LDAP) so that an LDAP server outage does not affect Xpand's ability to replicate SQL to a secondary cluster.

When configuring the MaxScale query user, similarly use a local Xpand user (non-LDAP) so that an LDAP server outage does not affect MaxScale's ability to monitor or provide load-balancing for Xpand.

SQL Connection Pooling

If you're interested in scraping off every extra millisecond of latency from your application, pooling your SQL connections is often generally good advice. With LDAP in the picture, the latency between the Xpand nodes and the LDAP server during initial SQL connection establishment can become a factor worth considering. Connection pooling could help reduce any apparent latency when Xpand is waiting for the LDAP server to respond, as well as potentially reducing the load on your LDAP server.


This section provides a brief walkthrough for configuring Xpand for LDAP authentication and authorization.

LDAP Directory Structure

For this example, we will assume a simple LDAP directory structure as shown here:

# directory root
dn: dc=example,dc=com
objectClass: dcObject
objectClass: organization
o: Example Organization
dc: example

# users
dn: ou=users,dc=example,dc=com
objectClass: organizationalUnit
ou: users

dn: uid=alice,ou=users,dc=example,dc=com
objectClass: person
objectClass: uidObject
uid: alice
cn: Alice Person
sn: Person
userPassword: secret

dn: uid=bob,ou=users,dc=example,dc=com
objectClass: person
objectClass: uidObject
uid: bob
cn: Bob Person
sn: Person
userPassword: secret

# groups
dn: ou=groups,dc=example,dc=com
objectClass: organizationalUnit
ou: groups

dn: cn=admins,ou=groups,dc=example,dc=com
objectClass: groupOfUniqueNames
cn: admins
uniqueMember: uid=alice,ou=users,dc=example,dc=com

dn: cn=workers,ou=groups,dc=example,dc=com
objectClass: groupOfUniqueNames
cn: workers
uniqueMember: uid=alice,ou=users,dc=example,dc=com
uniqueMember: uid=bob,ou=users,dc=example,dc=com

This LDAP directory structure defines 2 users, alice and bob, and two groups, admins and workers. Both alice and bob are members of the workers group. Only alice is a member of the admins group.

Each LDAP entry has an unambiguous distinguished name (DN) to identify its unique position in the LDAP directory tree. For example, the DN of the LDAP entry for alice is uid=alice,ou=users,dc=example,dc=com, child of the Organizational Unit (OU) entry ou=users,dc=example,dc=com, which represents the root of a subtree in our LDAP directory tree that contains our 2 users.

Similarly, the DN of ou=groups,dc=example,dc=com represents the root of a subtree in our LDAP directory that contains our groups addressable by DNs cn=admins,ou=groups,dc=example,dc=com and cn=workers,ou=groups,dc=example,dc=com.

This particular LDAP directory structure represents group membership by listing the group members by their full DN as attribute values of the LDAP group entry itself. This structure will inform the type of LDAP query that we'll need to create to retrieve a user's group membership from LDAP.

Configure Xpand

Now that we have a simple LDAP server, let's move on to what it looks like to configure Xpand.

The remaining steps will outline a basic Xpand configuration assuming that the above LDAP directory structure is available on an LDAP server reachable from the Xpand nodes via on the default ldaps:// port.

  1. First, some prerequisite setup is necessary to enable SSL (TLS) for SQL client connections to Xpand. This is required to ensure secure transmission of LDAP user passwords from the SQL client to Xpand.

  2. Configure Xpand to connect to the LDAP server. By using ldaps:// here, we ensure that the connection between the Xpand nodes and the LDAP server is also encrypted:

    sql> SET GLOBAL ldap_servers = 'ldaps://';
  3. Optionally configure Xpand if the LDAP server's TLS certificate is self-signed or privately-signed:

    • Copy to each Xpand node the certificate authority certificate file that signed the LDAP server's certificate and key:

      shell> scp ldap-cacert.pem root@hostname:/data/clustrix
    • On each Xpand node, ensure proper ownership of the certificate:

      shell> sudo chown xpand /data/clustrix/ldap-cacert.pem
    • Configure the path to the certificate file:

      sql> SET GLOBAL ldap_tls_cacert = '/data/clustrix/ldap-cacert.pem';
  4. Optionally configure a query user if your LDAP server disallows anonymous queries. Note that you may need to first create a special query user on your LDAP server with the appropriate permissions. The below assumes a query user with LDAP user DN of uid=readonly,ou=users,dc=example,dc=com and password of secret:

    sql> SET GLOBAL ldap_query_user = 'uid=readonly,ou=users,dc=example,dc=com';
    sql> SET GLOBAL ldap_query_password = 'secret';

    Again, this step is not necessary if your LDAP server allows anonymous queries.

  5. Configure Xpand to transform a connecting SQL client's username@hostname into an LDAP user DN. Our LDAP directory structure is simple, so we will use simple regex substitution:

    sql> SET GLOBAL ldap_user_lookup = substitution;
    sql> SET GLOBAL ldap_user_lookup_substitution = '/^(.+)@.+$/uid=\\1,ou=users,dc=example,dc=com/';

    This will transform a connecting SQL client of into the LDAP user DN of uid=alice,ou=users,dc=example,dc=com

  6. Configure the LDAP query for deriving the LDAP group DNs for the connecting client:

    sql> SET GLOBAL ldap_group_query = 'ou=groups,dc=example,dc=com?dn?one?(&(objectClass=groupOfUniqueNames)(uniqueMember={USERDN}))';

    The {USERDN} token in our query template will be replaced with the LDAP user DN derived from the previous step, uid=alice,ou=users,dc=example,dc=com, yielding the final query ou=groups,dc=example,dc=com?dn?one?(&(objectClass=groupOfUniqueNames)(uniqueMember=uid=alice,ou=users,dc=example,dc=com)).

    When executed on the LDAP server, this query will return the DNs of the two groups alice is a member of: cn=admins,ou=groups,dc=example,dc=com and cn=workers,ou=groups,dc=example,dc=com

  7. Configure the final transformation to turn each LDAP group DN into an Xpand role:

    sql> SET GLOBAL ldap_group_to_xpand_role = '/^cn=(.+),ou=groups,dc=example,dc=com$/\\1/';

    This will transform each matching LDAP group DN into an Xpand role as defined by the regex. cn=admins,ou=groups,dc=example,dc=com becomes admins and cn=workers,ou=groups,dc=example,dc=com becomes workers

  8. Add the corresponding roles and desired permissions to Xpand:

    sql> CREATE ROLE admins;
    sql> CREATE ROLE workers;
    sql> GRANT ALL PRIVILEGES ON *.* TO admins;
    sql> GRANT SELECT ON *.* TO workers;
  9. And finally, flip the switch to enable LDAP authentication and authorization!

    sql> SET GLOBAL ldap_enabled = true;

    At this point, our two LDAP users can authenticate through Xpand, while gaining the appropriate authorization as roles granted to their session. An LDAP user can activate any of their own roles in the typical way with SET ROLE

    However, we can turn on one more global variable so that this happens implicitly at login:

    sql> SET GLOBAL activate_all_roles_on_login = true;

Connect to Xpand as an LDAP User

With this configuration in place, connecting to Xpand as an LDAP user would look like this:

shell> mariadb --ssl -A -D test -h -u alice -p'secret' --prompt 'sql-alice> '

To check the roles assigned to the user alice:

| admins, workers |

Let's create a table t1, insert some values into it, and log out of the session:

sql-alice> CREATE TABLE t1 (a INT);
sql-alice> INSERT INTO t1 VALUES (1), (2), (3);
sql-alice> quit

Now let's login as user bob:

shell> mariadb --ssl -A -D test -h -u bob -p'secret' --prompt 'sql-bob> '

To check the roles assigned to the user bob:

| workers        |

To select values from the t1 table:

sql-bob> SELECT * FROM t1 ORDER BY 1;
| a    |
|    1 |
|    2 |
|    3 |

To log out of the session:

sql-bob> quit

LDAP Global Variables

System Variable

Data Type

Default Value





Determines whether to automatically activate roles on login.




Cache the results returned by LDAP search operations.




Duration that entries in the LDAP query cache are valid for, in seconds.




Maximum size of the LDAP query cache, in bytes.




Enable authentication using LDAP.



"" (an empty string)

Template string that specifies an RFC 4516-formatted LDAP query URL for obtaining the LDAP groups a user belongs to. {CLIENTUSER} is replaced with the connecting client's username, {CLIENTHOST} is replaced with the connecting client's hostname, and {USERDN} is replaced with the LDAP user DN.



"" (an empty string)

Regex for transforming an LDAP group DN into an Xpand role.




The seconds value used to time out network connections to the LDAP server. 0 specifies an infinite timeout.



"" (an empty string)

The password used with ldap_query_user when binding to an LDAP server to perform search operations. If this or ldap_query_user is empty, search operations are performed anonymously.



"" (an empty string)

DN of the LDAP user to bind as when performing search operations on an LDAP server. If this or ldap_query_password is empty, search operations are performed anonymously.



"" (an empty string)

Comma-separated list of LDAP servers, each of the form <protocol>://<hostname>[:<port>]. If multiple servers are specified, each node will round-robin its LDAP queries among the list.



"" (an empty string)

By default, Xpand will use the system defaults in order to determine CA trust when establishing TLS connections with an LDAP server. If set, this should specify a path to a file containing the trusted CA certificates. This can be useful if your LDAP server's TLS certificate is self-signed. This option is commonly referred to as TLS_CACERT in other LDAP client documentation.




Strategy for deriving the LDAP user DN. Choices: substitution, query



"" (an empty string)

Regex for transforming a username of the form username@hostname into an RFC 4516-formatted LDAP query URL that obtains an LDAP user DN. Used when ldap_user_lookup = query



"" (an empty string)

Regex for transforming a username of the form username@hostname into an LDAP user DN. Used when ldap_user_lookup = substitution