Authenticating Vault Against LDAP for Accessing MySQL Through ProxySQL

Posted in: DevOps, MySQL, Open Source, Technical Track

Earlier this year, I was presented with the challenge of streamlining user access to MySQL, allowing users self-serve access using their LDAP credentials, while logging all access. Of course, various MySQL forks allow for user auditing, but the solution is also needed to eventually support other data storage systems without native user auditing. This gave me the opportunity to do a trial integration of MySQL, Vault, ProxySQL, and LDAP; Vault would be used to dynamically create user accounts, and ProxySQL would be used to limit access and log activity. To evaluate the functionality and configuration of the integration, I used Docker to set up a test environment.

Below I will present the methods used to:

  1. Provision the Docker environment.
  2. Configure OpenLDAP.
  3. Initialize Vault and configure it to support LDAP and MySQL.
  4. Add users to ProxySQL.

 

1. Provision the Docker environment

I typically use docker-compose to spin up test environments, because it makes networking and container configuration easy. All files needed to set up the environment described here can be cloned from https://github.com/pythian/bastion-proxy

From the directory containing the docker-compose-vault.yaml file, start the cluster with the following statement:

docker-compose -f docker-compose-vault.yaml up -d

I use Kitematic for an easy-to-manage list of running containers. From Kitematic, it is easy to jump into any container by clicking the EXEC button:

Kitematic screenshot

Initialization scripts

In the docker-compose yaml file, I have included several initialization helper scripts. If you were provisioning this kind of cluster for production, most of these things would already be set up – for example, replication would already be running and LDAP would be in place already. For this test environment, just run these four quick scripts.

This one sets up replication between the primary and replica containers:

source initiate_replication.sh mysqlprimary mysqlreplica

This script is used to add the required monitor user for proxysql.

source initiate_proxysql.sh

This one installs LDAP utilities on the app container (for various reasons, this was nontrivial to add to the Docker image):

source install_ldap.sh

Finally, this script is used to initialize Vault using three generated keys:

source initiate_vault.sh

Example output:

Sealed: false
Key Shares: 1
Key Threshold: 1
Unseal Progress: 0
Unseal Nonce:

Set up MySQL Authorization

I am ready to add MySQL to Vault. This section is an update to a previous Vault walk-through by Derek Downey on this blog (https://blog.pythian.com/dynamic-mysql-credentials-vault/), using updated syntax and plugins.

vault mount database

Example output:

Successfully mounted 'database' at 'database'!

If you are using MySQL 5.6 or earlier, use the plugin_name=mysql-legacy-database-plugin, because those earlier versions limit the username to 16 characters. More on the error you will otherwise see below. Here, root is the username and password is the password. The hostname is mysqlprimary and the port is the default 3306.

vault write database/config/mysql \
plugin_name=mysql-database-plugin \
connection_url="root:password@tcp(mysqlprimary:3306)/" \
allowed_roles="readonly"

2. Configure OpenLDAP

Configure LDAP Authentication Backend

Next, I will configure LDAP to set up groups and users in openldap/phpldapadmin.

Using Kitematic, I can click directly to the web address hosting the phpldapadmin GUI, using the upward facing arrow icon in the WEB PREVIEW section on the right:

Opening web preview for phpldapadmin

Or just visit this link in a browser:

http://localhost:8080

Login is required:

Login for openldap GUI

The credentials configured in the docker-compose yaml, for the openldap container, are:

user = cn=admin,dc=proxysql,dc=com

password = password

Click login and enter these credentials:

Authenticate to server openldap

Set up groups and users:

Set up groups and users in LDAP

Create two Organisational Units: groups and users.

Create an organisational unit

Create groups organisational unit

Create users organisational unit

After these two OUs are created, the directory structure will look like this:

View of hierarchy in phpldapadmin after OU creation

Then create a child entry under ou=groups called developers, and a child entry under ou=users called vthompson.

Click on ou=groups, and then choose Create a child entry.

Creating a child groups entry

Choose Posix Group for the group=developers.

Create a Posix Group for the developers group

Enter text to add developers group

Choose Generic: User Account for the user=vthompson.

Create a User Account for the user

Add text for the user object in phpldapadmin

Choose the right password type for the use. In testing, clear is fine.

Enter a password for the LDAP user

Finish the rest of the fields:

Finish the user creation in openldap

Completed entries:

Completed hierarchy for LDAP setup

Add the memberUid attribute to the developers group:

Add the memberUID attribute to the developers group

Choose memberUid

Configure LDAP

The IP address of the openldap container needs to be added to several configuration statements below. Here is a way to get the IP address from the local machine running Docker:

docker inspect --format='{{ .NetworkSettings.Networks.adjacents_default.IPAddress }}' openldap

Example output:

172.18.0.5

Test the user created above directly against ldap. The IP address is that found above, 172.18.0.5; the username is vthompson and the password is thompson.

ldapwhoami -vvv -H ldap://172.18.0.5 -D cn=vthompson,ou=users,dc=proxysql,dc=com -x -wthompson

Example output:

dn:cn=vthompson,ou=users,dc=proxysql,dc=com
Result: Success (0)

If you get a failure at this point, one of two things may have happened. You may have forgotten to add the memberUid as a new attribute (see above), or you may have entered the password in phpldapadmin incorrectly. You can test the password using the check password functionality in the GUI:

Verify the LDAP password

The password checker compares the stored password to your entry

Another way to ensure the LDAP configuration is done correctly is to test binddn against it; you should get an output of all groups and users set up in phpldapadmin. Here we are using the admin credentials.

ldapsearch -H ldap://172.18.0.5 -x -D "cn=admin,dc=proxysql,dc=com" -w password -b "dc=proxysql,dc=com"

Example output:

# extended LDIF
#
# LDAPv3
# base <dc=proxysql,dc=com> with scope subtree
# filter: (objectclass=*)
# requesting: ALL
#

# proxysql.com
dn: dc=proxysql,dc=com
objectClass: top
objectClass: dcObject
objectClass: organization
o: proxysql
dc: proxysql

# admin, proxysql.com
dn: cn=admin,dc=proxysql,dc=com
objectClass: simpleSecurityObject
objectClass: organizationalRole
cn: admin
description: LDAP administrator
userPassword:: e1NTSEF9bVI3NUhCRTl4dG1nbK0raHVCc0lGZnpINU0wVlRJK2c=

# groups, proxysql.com
dn: ou=groups,dc=proxysql,dc=com
ou: groups
objectClass: organizationalUnit
objectClass: top

# users, proxysql.com
dn: ou=users,dc=proxysql,dc=com
ou: users
objectClass: organizationalUnit
objectClass: top

# developers, groups, proxysql.com
dn: cn=developers,ou=groups,dc=proxysql,dc=com
cn: developers
gidNumber: 500
objectClass: posixGroup
objectClass: top
memberUid: vthompson

# vthompson, users, proxysql.com
dn: cn=vthompson,ou=users,dc=proxysql,dc=com
givenName: Valerie
sn: Thompson
cn: vthompson
uid: vthompson
userPassword:: dGhvbRBzb24=
uidNumber: 1000
gidNumber: 500
homeDirectory: /home/users/vthompson
loginShell: /bin/sh
objectClass: inetOrgPerson
objectClass: posixAccount
objectClass: top

# search result
search: 2
result: 0 Success

# numResponses: 7
# numEntries: 6

3. Initialize Vault and configure it to support LDAP and MySQL.

Attach LDAP to Vault

Continue to do the next steps from the app container.

First, set up LDAP authentication inside Vault:

vault auth-enable ldap

Example output:

Successfully enabled 'ldap' at 'ldap'!

 

Verify LDAP was set up in Vault:

vault auth -methods

Example output:

Path Type Default TTL Max TTL Replication Behavior Description
ldap/ ldap system system replicated
token/ token system system replicated token based credentials

 

Next, write the LDAP configs to Vault. First, look at the default presets:

vault read auth/ldap/config

Example output:

Key  Value
---  -----
binddn
case_sensitive_names false
certificate
deny_null_bind  true
discoverdn  false
groupattr  cn
groupdn
groupfilter  (|(memberUid={{.Username}})(member={{.UserDN}})(uniqueMember={{.UserDN}}))
insecure_tls  false
starttls  false
tls_max_version  tls12
tls_min_version  tls12
upndomain
url  ldap://127.0.0.1
userattr  cn
userdn

 

Then write the appropriate configs. The groupfilter shown is particular to openldap.

vault write auth/ldap/config \
url="ldap://172.18.0.5" \
binddn="cn=admin,dc=proxysql,dc=com" \
bindpass="password" \
userdn="ou=users,dc=proxysql,dc=com" \
userattr="uid" \
groupdn="ou=groups,dc=proxysql,dc=com" \
groupattr="cn" \
groupfilter="(|(memberUid={{.Username}})(member={{.UserDN}})(uniqueMember={{.UserDN}}))" \
insecure_tls=true

 

Example output:

Success! Data written to: auth/ldap/config

 

Now look at settings again after writing configs:

vault read auth/ldap/config

Example output:

Key  Value
---  -----
binddn  cn=admin,dc=proxysql,dc=com
case_sensitive_names false
certificate
deny_null_bind  true
discoverdn  false
groupattr  cn
groupdn  ou=groups,dc=proxysql,dc=com
groupfilter  (|(memberUid={{.Username}})(member={{.UserDN}})(uniqueMember={{.UserDN}}))
insecure_tls  true
starttls  false
tls_max_version  tls12
tls_min_version  tls12
upndomain
url  ldap://172.18.0.5
userattr  uid
userdn  ou=users,dc=proxysql,dc=com

Now test LDAP authentication against Vault. It is important to do this in a new connection to the app container, because the connection being used to do the above configuration has the root token authenticated.

Until policies are mapped in a later command below, this command will report “* user is not a member of any authorized group.” That is okay for now.

vault auth -method=ldap username=vthompson
[thompson]

Example output:

Error making API request.
URL: PUT http://vault:9200/v1/auth/ldap/login/vthompson
Code: 400. Errors:
* user is not a member of any authorized group

 

Map LDAP Groups to Roles and Policies

Create a mysql readonly role with a default expiration. In this case, MySQL is the database, but a variety of database connections are supported. This section is done back in the root-authenticated app container connection.

vault write database/roles/readonly \
db_name=mysql \
creation_statements="CREATE USER '{{name}}'@'%' IDENTIFIED BY '{{password}}';GRANT SELECT ON *.* TO '{{name}}'@'%';" \
default_ttl="1h" \
max_ttl="24h"

Example output:

Success! Data written to: database/roles/readonly

 

View the vault policies:

vault policies

Example output:

default
root

 

The following hcl configuration file was pre-loaded into the app container in the docker-compose file. Take a look at the contents:

# cat mysqlread.hcl
path "database/creds/readonly" {
policy = "read"
capabilities = ["list", "read"]
}

 

Use this file to create and apply this to the mysql read-only role created above.

vault policy-write mysqlread mysqlread.hcl

Example output:

Policy 'mysqlread' written.

 

And set the mapping:

vault write auth/ldap/groups/developers policies=mysqlread

Example output:

Success! Data written to: auth/ldap/groups/developers

 

Use Token to Connect

Here, the test is as the created user, not root, so go back to the connection to the app container that is not root-authenticated, or open a new connection to the app container.

vault auth -method=ldap username=vthompson
[thompson]

Example output:

Successfully authenticated! You are now logged in.
The token below is already saved in the session. You do not
need to "vault auth" again with the token.
token: e17233fc-a30b-717e-20f5-d5faa293ad61
token_duration: 2764799
token_policies: [default mysqlread]

 

When LDAP is authenticated as above, there will be a token in the session. Use this to get a MySQL credential from the path associated with the read-only policy granted above.

Again, do this as the created user, not root.

vault read database/creds/readonly

Example output:

Key  Value
---  -----
lease_id  database/creds/readonly/e0ece849-d53d-7ab8-8502-ff1533a61b74
lease_duration  12h0m0s
lease_renewable true
password  A1a-7y7zzr463qqzr34u
username  v-ldap-vthom-readonly-ux9rx9z3pr

 

Note: If your setup uses MySQL 5.6 or earlier, the default username generated by Vault will be too long to fit in the data field. For example:

* Error 1470: String 'v-ldap-vthom-readonly-ux9rx9z3pr' is too long for user name (should be no longer than 16)

To fix this, change the plugin used when setting up MySQL authorization above to “mysql-legacy-database-plugin.”

 

Test database access directly from the app container with username/password returned from that output. The name of the container running MySQL is mysqlprimary.

mysql -hmysqlprimary -uv-ldap-vthom-readonly-ux9rx9z3pr -cA1a-7y7zzr463qqzr34u

 

At this point, Vault is serving temporary MySQL user accounts, authenticated by LDAP. I also wanted to test ProxySQL functionality, so I added this temporary MySQL user to ProxySQL. I did this manually for testing, but in any production scenario, this part would be automated with a wrapper script.

 

4. Add users to ProxySQL.

Connect ProxySQL

If ProxySQL were supported as a database connection from Vault, then I would use a direct connection to it. I did some experimentation to force the authorization step to write to ProxySQL; the primary blocker was lack of support for transactions in ProxySQL. I understand there may have been further work on this since my tests.

Here, I will add the user manually to ProxySQL. From the proxysql container, login as admin:

mysql -u admin -padmin -h 127.0.0.1 -P6032

 

Then add the user provided by Vault above. Remember ProxySQL requires writing to runtime and disk:

insert into mysql_users (username,password) values ("v-ldap-vthom-readonly-ux9rx9z3pr","A1a-7y7zzr463qqzr34u");
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

 

To allow tracking user activity through ProxySQL, enable proxysql query logging:

SET mysql-eventslog_filename='queries.log';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

 

The following statements turn on the default query rules:

select * from mysql_query_rules\G
UPDATE mysql_query_rules SET log=1;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

 

Now MySQL can be accessed through ProxySQL:

mysql -hproxysql -u v-ldap-vthom-readonly-ux9rx9z3pr -pA1a-7y7zzr463qqzr34u -P3306

 

Network restrictions could force connections to go through ProxySQL if needed. Filters could also be enabled inside ProxySQL to further limit access to the data.

Do a sample select to populate the log. The default pattern above is defined as match_pattern: ^SELECT, so either run your select in uppercase, or add a different match_pattern.

SELECT * from mysql.user;

 

The logs are stored in the following directory on the proxysql container:

ls -al /var/lib/proxysql/ | grep log

 

The tool to read the logs in plain text is not installed by default. Here is one way to enable it on the proxysql container:

yum install git -y
git clone https://github.com/sysown/proxysql.git
yum groupinstall "Development Tools" -y
cd proxysql/tools
make

 

Finally, use the tool to read the logs:

./eventslog_reader_sample /var/lib/proxysql/queries.log.00000001

 

Example output:

ProxySQL LOG QUERY: thread_id="3" username="v-ldap-vthom-readonly-ux9rx9z3pr" schemaname=information_schema" client="172.18.0.3:40728" HID=1 server="mysqlreplica:3306" starttime="2018-04-27 02:13:33.430834" endtime="2018-04-27 02:13:33.436488" duration=5654us digest="0x3B281446289B0759"
SELECT * from mysql.user

 

Summary

As shown, Vault can be used to authenticate against LDAP and provide (and expire) temporary MySQL credentials. These can additionally be added to ProxySQL to restrict access to the data stored in MySQL via filters and to log activity on the database.

Using Docker, you can set up a test environment easily to test the functionality and configuration settings needed for this type of stack. Once the process and functionality are confirmed, further automation can be used to deploy a similar stack to production.

 

email

Interested in working with Valerie? Schedule a tech call.

About the Author

Cassandra Consultant
With experience as an open-source DBA and developer for software-as-a-service environments, Valerie has expertise in web-scale data storage and data delivery.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *