In the world of DevOps, you must maintain an environment where your fellow developers can take full advantage of the technologies available, and those technologies must be used in a way that does not create security gaps.
In this post, we will discuss how to safely and efficiently grant database access to users who need it using ProxySQL, a Google Cloud Platform (GCP) tool.
Data management is complicated
Cloud database management can be a headache for our programming friends. This is because having our database within a VPC that is only accessible from microservices with sufficient credentials is the de facto alternative to keep our valuable data safe. This implies that, if in non-production environments, developers need to perform SQL operations within the database, they will face the problem of not having direct access from their local environments.
To deal with this problem, three possibilities arise before us, each with a different level of security and complexity.
-
- Access by developers to one of the microservices and perform command line operations: this access can be granted by Kubernetes control tools such as Rancher, or from GCP itself. With this option we maintain security and apply the rule of least possible privilege. Unfortunately, performing database management from these microservices becomes too manual a task, especially when it comes to data.
- Create a microservice that runs a simple phpMyAdmin server with the necessary credentials to access the database: here we can also have control over the access credentials using an Identity Aware Proxy and guarantee a much friendlier environment than using MySQL in the Command line. One disadvantage of this method is that there are many more database management tools available and we are forcing all the management to be carried out from the same entity. This solution is close, but we can do better.
- Use ProxySQL: the proposal is quite simple a priori, ProxySQL will be used as a kind of tunnel between the developer and the database. It is enough to have the database with the public IP address option provided by GCP for CloudSQL and perform the local installation of ProxySQL depending on the operating system in question. If you want to know a little more about this process you can see it here.
With all these steps ready, it is enough to execute the following set of commands which creates the connection with the database through ProxySQL proprietary encryption and, in our local port-to-port, we will already have access to the database from which we can pull the thread to do SQL operations.
First of all, we need the instance ID, which we can get from the GCP CLI or from the console:
gcloud sql instances describe <SQL instance name>
Then we use this command to create the connection. Remember that you must have sufficient permissions to access. This is done by enabling the Cloud SQL Client role for the IAM user that we are using.
./cloud_sql_proxy -instances=<instance Id>=tcp:0.0.0.0:<port to expose>
Just like that, you can get hold of your favorite database management software on that port.
The need for greater security
One compliance and security hurdle is that, although that public IP in CloudSQL is restricted, in the end, it is an IP that we have to manage and take care of. In many scenarios, and depending on the internal rules of the company, this is not allowed. That’s a big hurdle for our current setup since ProxySQL needs it to work.
Fortunately, there is a configuration in Kubernetes that we can enable so that our deployment works without much problem, albeit making the deployment a little more complicated.
The savior in this case is our friend PortForward, which allows you to create a port mapping between a remote Kubernetes cluster and the computer of a subject with sufficient access credentials.
In this case, we can create a pod in our deployment, which already has access to the same private VPC within GCP. There are ready-made ProxySQL images provided and maintained by Google (https://github.com/GoogleCloudPlatform/cloudsql-proxy), which, with a few configuration parameters, is ready to operate as a microservice. As additional data, ProxySQL can also serve as access for the rest of the microservices in the cluster. Once this is done, we simply enable the PortForward option and run this command to access our Port Mapping.
Here the point is that, for our architecture to work, developers must be granted access to the Kubernetes cluster. If we do it this way, we will not be respecting the principle of Less Privileges, since we will be giving access to all the underpinnings that support the applications running. The developer should not have access to Ingress or cache microservices.
The solution again is relatively simple: we use a different namespace for the microservice that contains the ProxySQL. That is, we grant permissions to developers ONLY to said namespace through a service account, a role created in IAM and a role binding.
In conclusion…
We cannot stress enough the importance of securing data and meeting security requirements. Of course, to have the luxury of not having a private IP, the deployment can be a bit complicated, adding more hoops between the local environment and the database, in addition to the configuration parameters.
But we have to say that this is worth it, because it only has to be done once and we will have everything ready and insured.
The tools that exist are many and the interactions make the possibilities infinite, so we can find other solutions that are more suitable for other cases.
Whether you need a security audit, a custom security solution, or help to install turnkey services, our team can help. Send us a message (info@makingscience.com) or click the button below to get in touch.
Cookie configuration