Use ProxySQL as a Database Auditor for secure access to MySQL

Rajat Venkatesh — 1/3/2020 — 2 Min Read

AWS and GCP best practices suggest running databases in a VPC and subnet. Applications run in a separate subnet, exposing them 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 offers centralized access management for MySQL databases.

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, it is an extra layer of defense 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 workflows. So this article will set up ProxySQL with default authentication and audit logging.

Architecture Diagram

A Kubernetes cluster running on AWS EKS runs 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 instructions, check out this blog post.

Setup a ProxySQL cluster on AWS EKS

dblogs has a helm chart to set up 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. You can transport these logs to any log analysis platform. Shown below is an example log:

{"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 the documentation.

Google Cloud SQL

Google Cloud allows the use of the same architecture. It has basic instructions to connect a GKE cluster to cloud SQL. Detailed instructions are at Cloud SQL docs.

Conclusion

This article uses ProxySQL to set up a reverse proxy to MySQL databases. Similarly, PgPool works as a reverse proxy for PostgreSQL. Also, check out these similar projects: Tokern DbAudit Database Auditor setup at HootSuite Bastion Proxy is another project on Github that uses ProxySQL If you want to try out a database auditor built on ProxySQL or Tokern DbAudit, get in touch with us via our Slack Community.

Similar Posts