Use ProxySQL as a Database Auditor for secure access to MySQL

Rajat Venkatesh — 01/03/20202 Min Read — In MySQL, ProxySQL, Database Audit, Data Security

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 Database Auditor that provides centralized access management for MySQL databases.

Checkout Tokern DbAudit to monitor production usage by devops and support teams

What is a Database Auditor ?

A Database Auditor 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 as a Database Auditor. 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.

Requirements of a MySQL Database Auditor

A MySQL Database Auditor 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.

Architecture Diagram

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.

Setup a ProxySQL cluster on AWS EKS

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.

Google Cloud SQL

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.

Conclusion

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 a database auditor built on ProxySQL or Tokern DbAudit, get in touch using the chat widget.