MySQL Bastion: Streamlined DB Access With ProxySQL, Vault and AD
What is a Bastion?
In our efforts to constantly improve the stability and security of our platform we have moved towards using bastions for restricted, logged, secure, 2FA access to resources like EC2 instances and MySQL DBs. Bastions are centralized access points to those resources, and help us be more secure by enabling standard access controls for all resources through bastions.
Initially we had a SSH authentication workflow for developers using SSH pub-keys and roles. With the impending release of GDPR on May 25th, we looked to revamp our access process to be more secure, in particular making sure developers only have access to the resources they need for the duration they need, and making sure Production/Customer data doesn’t leave the Production environment. Part of this process was replacing direct access to servers with access via a SSH Bastion that provides a locked-down, centralized SSH access point.
This broke some of our DB access patterns, one of which where our Developers would use Sequel Pro combined with an SSH tunnel (other use cases include reporting, migrations, and maintenance). The Developer workflow became unsupported with the introduction of the SSH Bastion, as we turned off TCP forwarding (which meant no SSH tunnel). We looked to find a solution that would meet our current needs, and our updated security and compliance needs.
Enter: MySQL Bastion
Our Developers use Slack as a communication platform, and so we leveraged it for our access request workflow. We created a Slack slash command backed by AWS Lambda called /request_mysql_access. This calls a RESTful API, which does all the necessary AD checks and creates a JIRA ticket. The JIRA ticket is used to review the access request.
The API call from the slash command needs 2 lambdas because i) the Slack slash command has a timeout of 3 seconds which we kept going over, and ii) the API is in our internal VPC which Slack can’t talk to. The first lambda queues the request to an SNS queue, which triggers the second lambda that does the work.
We use Vault to securely generate temporary credentials and write those credentials to a MySQL database. We use AD groups to map access to access policies in Vault.
We use Terraform to configure Vault. To make sure new Databases are setup for access via the Bastion we have a script that detects new DBs that are created in our infrastructure, and auto-generates the TF file to roll out the Bastion compatible changes.
The MySQL Bastion runs ProxySQL which is used to centralize all access to MySQL databases. It provides access verification, and query logging for auditing purposes. Developers would connect directly to ProxySQL, and will forward a restricted set SQL statements to the target DB.
We use a daemon to manage the temporary access, which makes sure the generated temporary MySQL user credentials from the MySQL Bastion are created (and cleaned up) on the target DBs.
Command Line Interface
We have a cli tool called hs-proxysql-client which simply prompts the user to login to Vault with his AD credentials and retrieve the generated temporary credentials by executing a vault read command. Once the temporary credentials are returned, the Developer can use them to login to the MySQL Bastion, and ProxySQL will transparently provide a native interface to the DB for tools like Sequel Pro.
Logging and metrics
We log all queries for auditing purposes, and since SQL queries may contain PII, we restrict access to the logs to the DevOps and Security teams. We send summary logs to SumoLogic for metrics and monitoring purposes.
End user experience
Our goal, to ensure adoption, was to be secure and create great experience for our Developers. As a Hootsuite developer, you can gain access to a DB in 3 steps:
- Request access to production databases by typing /request_mysql_access on Slack.
- Have the JIRA ticket approved by #pod-help, part of the DevOps team.
- Once approved, the access-request is processed by Jenkins, then run $ hs-proxysql-client to get the temporary credentials that can be used to access the database directly.
- We have different types of MySQL databases, MySQL databases that are running on EC2 instances, RDS MySQL instances and RDS Aurora clusters. These differences add complexity to automate certain tasks such as adding sync users used by the Credentials Sync Script to create temporary users to the remote DBs.
- MySQL databases running on EC2 instances only support 16 characters for the user column in the mysql.user table, which means we need to use the mysql-legacy-database-plugin vault plugin.
- The query logs generated by ProxySQL are not human-readable, and the sample app provided to decode the logs is written in C++ (not part of our development stack), and isn’t a friendly example.
Thank you for reading. Make sure to check out more Hootsuite blogs if you are interested!
About the author
Ivan Hoo is a Software Systems major at SFU. Co-op Software Developer at Hootsuite’s Production Operations and Delivery team for 8 months. Video game addict, technology enthusiast and music lover. Contact him on LinkedIn.