ProxySQL Bastion for secure access to MySQL

AWS and GCP best practices suggest running databases in a VPC and subnet. Applications run in a separate subnet and are exposed to the outside network through load balancers. There are many options for security monitoring of load balancers.

However there are no industry standard best practices to provide secure database access to operation and customer teams. This article describes a MySQL Bastion that provides centralized access management for MySQL databases.

A bastion host is typically the only host in a secure network exposed to outside connections. A reverse proxy is one of the most common choices to run on a bastion host. A reverse proxy forwards requests to the database and returns results to the requesting application. Among other advantages a reverse proxy is an extra layer of defence against security attacks. We will use ProxySQL as a reverse proxy to MySQL databases on AWS RDS, AWS Aurora and Google Cloud SQL.

A MySQL Bastion should provide the following features :

• Authentication workflow
• Authorization workflow
• Audit logs

Companies have custom authentication and authorization policies as well as work flows. So this article will setup ProxySQL with default authentication and audit logging.

A Kubernetes cluster running on AWS EKS run in its own VPC. Similarly an RDS instance is also running in its own VPC. The two can be connected using VPC peering and a Kubernetes service. For detailed instruction, check out this blog post.

dblogs has a helm chart to setup a ProxySQL cluster. Setup a cluster with the following command:

helm install -n <name> mysqllogs/

An admin can connect with the following command:

# Connect as proxysql admin 
kubectl exec -it $POD_NAME -c proxysql -- mysql -h 127.0.0.1 -u {{ .Values.proxysql.config.adminUser }} \ -p {{ .Values.proxysql.config.adminPassword}} -P {{ .Values.proxysql.config.adminPort }}

Connect as a user:

# Connect as a user. 
mysql --default-auth=mysql_native_password -h $NODE_PORT -P $NODE_IP -h <user> -p<password>

All user activity on the database is logged and available in container logs of the ProxySQL cluster in JSON format. The logs can be transported to any log analysis platform. An example logs is shown below:

{"thread_id":12,"username":"wordpress","schemaname":"information_schema","start_time":1562299355646801,"end_time":1562299355647212,"query_digest":7064796034145638170,"query":"SELECT DATABASE()","server":"mysql:3306","client":"10.1.1.1:43558","et":0,"hid":10} {"thread_id":12,"username":"wordpress","schemaname":"wordpress","start_time":1562299355649264,"end_time":1562299355650034,"query_digest":145028081500634608,"query":"show databases","server":"mysql:3306","client":"10.1.1.1:43558","et":0,"hid":10} {"thread_id":12,"username":"wordpress","schemaname":"wordpress","start_time":1562299355650353,"end_time":1562299355650673,"query_digest":-7398540144894491391,"query":"show tables","server":"mysql:3306","client":"10.1.1.1:43558","et":0,"hid":10} {"thread_id":12,"username":"wordpress","schemaname":"wordpress","start_time":1562299358632176,"end_time":1562299358632902,"query_digest":-8523428617193305551,"query":"select i from a","server":"mysql:3306","client":"10.1.1.1:43558","et":0,"hid":10}

For detailed instructions on setting up ProxySQL, refer to documentation.

The same architecture can be used on Google Cloud. Google cloud has simpler instructions to connect a GKE cluster to cloud SQL. For detailed instructions read Cloud SQL docs.

This article uses ProxySQL to setup a reverse proxy to MySQL databases. Similarly, PgPool can be used as a reverse proxy for PostgreSQL. Also checkout these similar projects:

If you want to try out bastion built on ProxySQL or Tokern Bastion, get in touch using the chat widget.

Originally published at https://tokern.io.