Authenticating Vault against LDAP for accessing MySQL through ProxySQL
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:
- Provision the Docker environment.
- Configure OpenLDAP.
- Initialize Vault and configure it to support LDAP and MySQL.
- 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](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image8.png?width=583&height=380&name=image8.png)
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](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image7.png?width=694&height=407&name=image7.png)
![Login for openldap GUI](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image14.png?width=322&height=231&name=image14.png)
![Authenticate to server openldap](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image3-3.png?width=442&height=357&name=image3-3.png)
![Set up groups and users in LDAP](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image15.png?width=373&height=348&name=image15.png)
![Create an organisational unit](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image1-3.png?width=273&height=38&name=image1-3.png)
![Create groups organisational unit](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image20.png?width=516&height=297&name=image20.png)
![Create users organisational unit](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image21.png?width=531&height=263&name=image21.png)
![View of hierarchy in phpldapadmin after OU creation](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image4-2.png?width=322&height=362&name=image4-2.png)
![Creating a child groups entry](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image19.png?width=606&height=296&name=image19.png)
![Create a Posix Group for the developers group](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image22.png?width=258&height=35&name=image22.png)
![Enter text to add developers group](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image11.png?width=580&height=458&name=image11.png)
![Create a User Account for the user](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image2-4.png?width=261&height=36&name=image2-4.png)
![Add text for the user object in phpldapadmin](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image12.png?width=639&height=460&name=image12.png)
![Enter a password for the LDAP user](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image13.png?width=670&height=186&name=image13.png)
![Finish the user creation in openldap](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image9.png?width=610&height=346&name=image9.png)
![Completed hierarchy for LDAP setup](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image6-2.png?width=343&height=449&name=image6-2.png)
![Add the memberUID attribute to the developers group](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image10.png?width=597&height=410&name=image10.png)
![Choose memberUid](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image18.png?width=814&height=437&name=image18.png)
![](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image5-3.png?width=596&height=123&name=image5-3.png)
docker inspect --format='' 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](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image16.png?width=615&height=102&name=image16.png)
![The password checker compares the stored password to your entry](https://www.pythian.com/hs-fs/hubfs/Imported_Blog_Media/image17.png?width=486&height=176&name=image17.png)
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 credentialsNext, 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=)(member=)(uniqueMember=)) insecure_tls false starttls false tls_max_version tls12 tls_min_version tls12 upndomain url ldap://127.0.0.1 userattr cn userdnThen 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=)(member=)(uniqueMember=))" \ insecure_tls=trueExample 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=)(member=)(uniqueMember=)) 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=comNow 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 https://vault:9200/v1/auth/ldap/login/vthompson Code: 400. Errors: * user is not a member of any authorized groupMap 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 'Authenticating Vault against LDAP for accessing MySQL through ProxySQL'@'%' IDENTIFIED BY '';GRANT SELECT ON *.* TO 'Authenticating Vault against LDAP for accessing MySQL through ProxySQL'@'%';" \ default_ttl="1h" \ max_ttl="24h"Example output:
Success! Data written to: database/roles/readonly
View the vault policies:
vault policies
Example output:
default rootThe 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-ux9rx9z3prNote: 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:
Share this
You May Also Like
These Related Stories
Docker Orientation
Docker Orientation
Jul 1, 2020
9
min read
Diagnosing Orchestrator Crashes Due to Time Drift Events
Diagnosing Orchestrator Crashes Due to Time Drift Events
Jan 19, 2021
5
min read
Configuring MySQL in a Docker Container
Configuring MySQL in a Docker Container
Nov 6, 2020
3
min read
No Comments Yet
Let us know what you think