Native LDAP Support for MariaDB 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.

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 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.

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.

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://example.com';
   

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';
     sql> ALTER CLUSTER RELOAD LDAP;
     

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 alice@someplace.com 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 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