Hardening a complex application is a challenge, more so for applications that include multiple layers with different authentication schemes. One common question is “how to integrate Cloud SQL for PostgreSQL or MySQL within your authentication flow?”
Cloud SQL has always supported password-based authentication. There are, however, many questions that come with this approach:
Where should you store the password?
How do you manage different passwords for different environments?
Who audits password complexity?
Ideally, it would be preferable to not have to worry about passwords at all. Using username and password authentication also breaks the identity chain. Whoever knows the password can impersonate a database role, effectively making it impossible to ascribe actions on an audit file to a specific person (or service account). Moreover, disabling an account requires finding out all the associated database logins and disabling them as well. But how can you be sure no one else shares the same login?
It’s clear that this approach does not scale well. As just one example, managing multiple database instances with multiple applications can quickly become a daunting task. To solve these challenges, Cloud SQL for PostgreSQL and MySQL users can use Cloud SQL Identity and Access Management (IAM)-mapped logins with Cloud SQL Proxy with Automatic Authentication.
Cloud SQL IAM-mapped logins
Cloud SQL’s IAM Database Authentication feature allows mapping preexisting Cloud IAM principals (users or service accounts) to database native roles. This means you can ask the Google Cloud Platform to create logins that match the email address of the IAM principal.
GCP will also handle the password for you (including storage and rotation). But how can you use it?
If your account has valid IAM credentials (cloudsql.instances.login), Google Cloud will give you the token that you can use to authenticate. Basically, Google Cloud will provide you with the Cloud SQL password, you can then use the password to connect directly to Cloud SQL for PostgreSQL and MySQL.
While you can do that yourself (via manual IAM database authentication), it would be best to have it handled automatically — such as when issuing gcloud sql generate-login-token. Google Cloud provides connectors for many languages that automate this task. (For an example of this, you can see the Golang driver for PostgreSQL in action here.) With these connectors, authenticating to Cloud SQL for PostgreSQL and MySQL can be secure and convenient.
Unfortunately, we don’t always have the luxury of changing the application code to make use of the new drivers. In that scenario you can use a Google Cloud-provided proxy, called Cloud SQL Auth proxy. This proxy allows your application to make use of the new Automatic IAM Database Authentication without any change to your codebase.
Cloud SQL Auth proxy
The Cloud SQL Auth proxy has the Automatic IAM Database authentication feature. It allows applications oblivious to Cloud SQL IAM principals to authenticate as a IAM principal
For example, if the Cloud SQL auth proxy runs in the context of a service account — maybe because it had inherited it from the Compute Engine it runs on — every connection that connects to the proxy will be able to authenticate as that service account.
The following image shows how your application, instead of connecting to Cloud SQL directly, can connect to the Cloud SQL Auth Proxy process running in the same Compute Engine instance. The Proxy will in turn handle authentication and connection to the Cloud SQL Instance via a secure TLS connection.
It’s important to configure the Cloud SQL Auth Proxy to only accept localhost connections – either with TCP or Linux domain sockets. The application only has to specify the service account name without any password (in case of service accounts, this is the service account’s email without the .gserviceaccount.com domain suffix). The password will be added transparently by the Cloud SQL Auth proxy.
Cloud SQL Auth Proxy injection via sidecar
Cloud SQL Auth Proxy can also be used to secure GKE connections. It can be added as a sidecar container, achieving the same result as above. In this case we rely on Kubernetes network isolation to ensure only authorized connections will reach the Cloud SQL Auth Proxy.
As an example, you can refer to the following YAML Kubernetes template file that contains:
The configuration to use the GKE service account using workload identity.
An empty example deployment specification for the application connecting to Cloud SQL for PostgreSQL.
The deployment specification for the execution of the Cloud SQL proxy as a sidecar container.
The application has to be configured to access the PostgreSQL database using a localhost connection. The security is guaranteed by the fact that the application and the sidecar container share the same network. The username of the connection should be the service account email address without the .gserviceaccount.com domain suffix.
You can access the detailed documentation for Cloud SQL for PostgreSQL and MySQL and an example of Cloud SQL Proxy run as a sidecar container here.
Automatic auditing for Cloud SQL for PostgreSQL
Cloud SQL for PostgreSQL and MySQL can be configured to audit both data plane and control plane access. This can be done by enabling the relevant functionalities, as outlined here for PostgreSQL and here for MySQL.
Explore the audit trails
One very important aspect of Google Cloud’s auditing is that everything is collected centrally. The same goes for Cloud SQL for PostgreSQL audit trails, with no need for customers to manage a complex, potentially error-prone audit pipeline. This is vital for security tasks, especially for tasks that are subject to regulations.The data can be explored with Google Cloud Log Explorer or, for people who prefer SQL, we can harness the new Log Analytics engine. Log Analytics can make it easier to extract valuable information from your audit trails.
In the following example below query extracts user, database, and statement issued to configured Cloud SQL for PostgreSQL instances in the last hour:
That’s not all. Since the same query can be run by BigQuery, it’s possible to connect Looker Studio or even Looker to BigQuery and create beautiful dashboards. By leveraging Looker, it’s also possible to create automated alerts whenever specific events occur, such as an unusual access during the weekend or an abnormal number of operations in a short period of time by a specific user.
The following image depicts a demo dashboard built with Looker. Notice how dropdown filters can make exploring the data easy even for people not versed in SQL.
Conclusion
With the recent feature of Integrated IAM authentication, customers can leverage end-to-end authentication for their applications and make use of the best-in-class auditing capabilities of Google Cloud. Google Cloud offers tools you need to start using the IAM authentication right away, even if you don’t have access to the source code of your application.
As usual, Google Cloud and its partners can help you with the implementation; please reach out to us.
Cloud BlogRead More
