Native LDAP Support for MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Security
Topics on this page:
Overview
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.
Compatibility
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:
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.
Query the LDAP server for a list of LDAP group DNs for that user.
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.
Certificates
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).
Guardrails
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 asGRANT
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 variableactivate_all_roles_on_login
can be enabled to implicitly activate all roles at time of login.
LDAP users cannot be specified as the
DEFINER
forCREATE TRIGGER
,CREATE PROCEDURE
, orCREATE 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.
Example
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 example.com
on the default ldaps://
port.
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.
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://example.com';
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'; sql> ALTER CLUSTER RELOAD LDAP;
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 ofsecret
: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.
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
[email protected]
into the LDAP user DN ofuid=alice,ou=users,dc=example,dc=com
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 queryou=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
andcn=workers,ou=groups,dc=example,dc=com
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
becomesadmins
andcn=workers,ou=groups,dc=example,dc=com
becomesworkers
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;
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 example.com -u alice -p'secret' --prompt 'sql-alice> '
To check the roles assigned to the user alice
:
sql-alice> SELECT CURRENT_ROLE();
+-----------------+
| CURRENT_ROLE() |
+-----------------+
| 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 example.com -u bob -p'secret' --prompt 'sql-bob> '
To check the roles assigned to the user bob
:
sql-bob> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| 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 | Description |
---|---|---|---|
|
| 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. | |
|
| Template string that specifies an RFC 4516-formatted LDAP query URL for obtaining the LDAP groups a user belongs to. | |
|
| Regex for transforming an LDAP group DN into an Xpand role. | |
|
| The seconds value used to time out network connections to the LDAP server. | |
|
| The password used with | |
|
| DN of the LDAP user to bind as when performing search operations on an LDAP server. If this or | |
|
| Comma-separated list of LDAP servers, each of the form | |
|
| 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 | |
|
| Strategy for deriving the LDAP user DN. Choices: | |
|
| Regex for transforming a username of the form | |
|
| Regex for transforming a username of the form |