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:
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:[email protected](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:
Or just visit this link in a browser:
Login is required:
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:
Set up groups and users:
Create two Organisational Units: groups and users.
After these two OUs are created, the directory structure will look like this:
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.
Choose Posix Group for the group=developers.
Choose Generic: User Account for the user=vthompson.
Choose the right password type for the use. In testing, clear is fine.
Finish the rest of the fields:
Completed entries:
Add the memberUid attribute to the developers group:
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:
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 https://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.
No comments