Relational databases are the preferred choice for persisting data in most non-trivial applications. CloudSQL is Google Cloud Platform’s solution that provides a managed PostgreSQL and MySQL server.
In the past, dedicated DB Admins handled database management; now, the industry expects cloud infrastructure engineers to provision and manage these resources.
But these are complex systems that require a good understanding of several abstraction layers to provision or fix issues effectively. Hence, this article aims to peel back the layers of abstraction and expose the core ideas in the entire infrastructure needed to provision and maintain PostgreSQL as a managed service.
Setting up the environment
Getting a CloudSQL instance up and running is relatively straightforward; an official Terraform module provides various resources and configurations like VM instance, databases, and users.
However, as a managed service, the CloudSQL environment alters the conventional PostgreSQL setup in unique ways, like protecting the VM instance, configuring the underlying PostgreSQL server, and integrating with the PostgreSQL RBAC system. We’ll briefly examine each of these — this might be review material if you’re an experienced PostgreSQL admin.
Configuring PostgreSQL
CloudSQL provides an API to set various configurations for the PostgreSQL server. Then, behind the scenes, the API modifies the traditional server configuration files to enforce these settings.
When a PostgreSQL server starts, it reads its configuration from a conventionally named postgresql.conf file. This file specifies parameters like the SSL keys for secure connections, the memory and CPU allocated to specific sub-processes, and the user authentication methods to support — conventionally declared in a pg_hba.conf file referenced in the main configuration file. There you can specify things like: “use Password Auth for traffic from CIDR 178.0.0.1/24 connecting via TLS to the “testdb” database.
As a managed service, CloudSQL does not let you directly edit these configuration files. Instead, it provides an API to configure these settings. Still, it would be best to understand that this is where you set up authentication methods like passwords, certificates, and *whispers* Google Cloud IAM.
Securing the instance
One way to protect the CloudSQL instance is to create it in a private VPC so that only workloads in the same network can connect to it. However, there are cases where it makes sense to expose the instance to the internet through a public IP, for example, when giving contractors access to data. Let’s look at security in this case.
The first layer of protection comes from setting a list of CIDRs as Authorized Networks. CloudSQL configures the instance’s “iptables” to allow traffic from the provided IPs and drops all other traffic. Note that this firewall protection only applies to traffic from regular clients; traffic from the CloudSQL Auth Proxy client is not checked against the list of authorized networks — more on that later.
Suppose the request makes it past the firewall. Then, the PostgreSQL server process is responsible for enforcing access control using its RBAC system that ensures the client has the necessary permissions to SELECT, INSERT, DROP, or perform any other SQL actions.
Controlling access to database objects
PostgreSQL organizes data in the following hierarchy: a Server contains one or more Databases, which house one or more Schemas, which namespace one or more Tables; which have one or more Rows; which include one or more Columns. Users, however, are global to the server.
So, for a client to view the data stored in a particular table, it must connect using the credentials of a user that we have granted the following privileges: “LOGIN” to the server; “CONNECT” to the database; “USAGE” on the schema containing the table, and “SELECT” on the rows in the table. We can even go a step further and control access to specific columns using Row Security Policies.
Hence, in addition to controlling what IP addresses can access a CloudSQL instance, the PostgreSQL RBAC system gives SREs a great deal of control over which client can access what data. Next, we’ll see how CloudSQL integrates with this system.
How CloudSQL integrates with PostgreSQL RBAC
To make bootstrapping easy, CloudSQL provisions each instance with a default “postgres” user that we can use to log in and create other users. This user, and every subsequent user created through the API, has access to all the object hierarchies mentioned above: it can create databases and CRUD tables from all databases. Of course, you may not want such permissive access for compliance with standards like PCI-DSS.
So, Instead of creating database users with the CloudSQL API, you may prefer to manually create your users using a tool like psql for greater control over what objects they can access.
One important thing to note is that, unlike regular PostgreSQL installations, the default “postgres” user created by CloudSQL does not have the SUPERUSER role. Instead, it, and all subsequent users you create via the API, has the “cloudsqlsuperuser” role, which is less powerful than SUPERUSER — CloudSQL prohibits assigning SUPERUSER for security reasons since it is a managed service.
Dealing with database users is not intuitive and natural for infrastructure engineers who are more acquainted with Cloud IAM users and service accounts — let’s see how to use these instead.
Using IAM entities to access PostgreSQL
You may already be using IAM entities to access VMs, storage buckets, Kubernetes clusters, and other cloud resources, so using them to access PostgreSQL is desirable.
Fortunately, CloudSQL lets us set up a database user linked to an IAM subject. Essentially, you enable the flag “cloudsql.iam-authentication” on the instance and create a database user whose username matches an IAM subject’s email address. Then, CloudSQL will link the database user to the corresponding IAM subject, enabling you to log in using the credentials of that IAM subject.
I assume that CloudSQL maps the IAM subject to a PostgreSQL user by adding entries in the pg_ident.conf file; this is the conventional file in PostgreSQL where you can map usernames from external authentication methods to database users.
IAM integration unlocks benefits like granting access to an entire Gsuite group or easily revoking users’ access when they change teams or leave the company by removing them from the group.
With this solid grasp of PostgreSQL configuration in CloudSQL, we are ready to start using it.
Connecting to PostgreSQL on CloudSQL
We face different challenges depending on where the client resides. For example, a developer connecting from their local machine using psql does not have to worry about re-connections, but a production app running in a Kubernetes pod has to worry about re-connections due to scaling events. So, let’s examine these two connection scenarios.
Connecting from a local development machine
Suppose a developer wants to access data on a staging database from their local machine using psql, the client-server connection is mostly uninterrupted once established, so we are not concerned about re-connections, only ensuring that it is encrypted.
We can use the Certificate Authentication method of PostgreSQL to provision a database user with a certificate keypair, as opposed to username-password; we can do this via the CloudSQL API. psql uses the client cert credentials to establish a secure connection to the instance and authenticate as the DB user.
Presently, you can only provision ten client certificates per CloudSQL instance, limiting you to only ten users with certificate auth. However, we can avoid this limitation by using the following connection approach.
Connecting from a Production cluster with the CloudSQL Auth Proxy
In a non-local environment like a production Kubernetes cluster, we would like to keep application code simple by abstracting the intricacies of DB connections to an external process.
CloudSQL Auth Proxy is an application that abstracts away these concerns. It runs as a sidecar to your app, establishing and maintaining a connection to the CloudSQL server so that your application can connect to a localhost address as if you were running PostgreSQL locally.
Since CloudSQL Auth Proxy must establish a secure connection to the CloudSQL Instance, You must provide it with credentials like a JSON key file of an authorized IAM Service Account (or the access token of an IAM user).
CloudSQL Auth Proxy differs from regular clients in that it does not connect directly to the PostgreSQL server but instead to a counterpart ‘proxy server’ process on the instance on port 3307. So you should ensure that your firewall rules allow egress to the CloudSQL instance IP on port 3307.
Some benefits of using the CloudSQL Auth Proxy are that you don’t need to explicitly allow the client IP in the CloudSQL instance Authorized Network. Also, it automatically establishes a secure TLS connection to the server, so you don’t have to provision a client SSL cert and potentially hit the 10-cert limit.
Conclusion
This article introduced GCP CloudSQL for PostgreSQL and explored various aspects relevant to infrastructure engineers. First, we looked at the internals of PostgreSQL, its authentication and RBAC systems, then tied it with how CloudSQL integrates with all of that. Finally, backed with this knowledge, we explored ways to connect our client applications to CloudSQL and the trade-offs of each method.
Even when provided as a managed service, database systems like PostgreSQL are complex, and the infrastructure behind them can be daunting. Still, hopefully, this article has provided a clearer understanding of what you need to provision and maintain such a service as an infrastructure engineer.
GCP is increasingly being used by teams all over. In this blog, Uzziah explains how CloudSQL is Google Cloud Platform’s solution that provides a managed PostgreSQL and MySQL server.
Table of Contents:
GCP CloudSQL for PostgreSQL — Uzziah Eyee
Relational databases are the preferred choice for persisting data in most non-trivial applications. CloudSQL is Google Cloud Platform’s solution that provides a managed PostgreSQL and MySQL server.
In the past, dedicated DB Admins handled database management; now, the industry expects cloud infrastructure engineers to provision and manage these resources.
But these are complex systems that require a good understanding of several abstraction layers to provision or fix issues effectively. Hence, this article aims to peel back the layers of abstraction and expose the core ideas in the entire infrastructure needed to provision and maintain PostgreSQL as a managed service.
Setting up the environment
Getting a CloudSQL instance up and running is relatively straightforward; an official Terraform module provides various resources and configurations like VM instance, databases, and users.
However, as a managed service, the CloudSQL environment alters the conventional PostgreSQL setup in unique ways, like protecting the VM instance, configuring the underlying PostgreSQL server, and integrating with the PostgreSQL RBAC system. We’ll briefly examine each of these — this might be review material if you’re an experienced PostgreSQL admin.
Configuring PostgreSQL
CloudSQL provides an API to set various configurations for the PostgreSQL server. Then, behind the scenes, the API modifies the traditional server configuration files to enforce these settings.
When a PostgreSQL server starts, it reads its configuration from a conventionally named postgresql.conf file. This file specifies parameters like the SSL keys for secure connections, the memory and CPU allocated to specific sub-processes, and the user authentication methods to support — conventionally declared in a pg_hba.conf file referenced in the main configuration file. There you can specify things like: “use Password Auth for traffic from CIDR 178.0.0.1/24 connecting via TLS to the “testdb” database.
As a managed service, CloudSQL does not let you directly edit these configuration files. Instead, it provides an API to configure these settings. Still, it would be best to understand that this is where you set up authentication methods like passwords, certificates, and *whispers* Google Cloud IAM.
Securing the instance
One way to protect the CloudSQL instance is to create it in a private VPC so that only workloads in the same network can connect to it. However, there are cases where it makes sense to expose the instance to the internet through a public IP, for example, when giving contractors access to data. Let’s look at security in this case.
The first layer of protection comes from setting a list of CIDRs as Authorized Networks. CloudSQL configures the instance’s “iptables” to allow traffic from the provided IPs and drops all other traffic. Note that this firewall protection only applies to traffic from regular clients; traffic from the CloudSQL Auth Proxy client is not checked against the list of authorized networks — more on that later.
Suppose the request makes it past the firewall. Then, the PostgreSQL server process is responsible for enforcing access control using its RBAC system that ensures the client has the necessary permissions to SELECT, INSERT, DROP, or perform any other SQL actions.
Controlling access to database objects
PostgreSQL organizes data in the following hierarchy: a Server contains one or more Databases, which house one or more Schemas, which namespace one or more Tables; which have one or more Rows; which include one or more Columns. Users, however, are global to the server.
So, for a client to view the data stored in a particular table, it must connect using the credentials of a user that we have granted the following privileges: “LOGIN” to the server; “CONNECT” to the database; “USAGE” on the schema containing the table, and “SELECT” on the rows in the table. We can even go a step further and control access to specific columns using Row Security Policies.
Hence, in addition to controlling what IP addresses can access a CloudSQL instance, the PostgreSQL RBAC system gives SREs a great deal of control over which client can access what data. Next, we’ll see how CloudSQL integrates with this system.
How CloudSQL integrates with PostgreSQL RBAC
To make bootstrapping easy, CloudSQL provisions each instance with a default “postgres” user that we can use to log in and create other users. This user, and every subsequent user created through the API, has access to all the object hierarchies mentioned above: it can create databases and CRUD tables from all databases. Of course, you may not want such permissive access for compliance with standards like PCI-DSS.
So, Instead of creating database users with the CloudSQL API, you may prefer to manually create your users using a tool like psql for greater control over what objects they can access.
One important thing to note is that, unlike regular PostgreSQL installations, the default “postgres” user created by CloudSQL does not have the SUPERUSER role. Instead, it, and all subsequent users you create via the API, has the “cloudsqlsuperuser” role, which is less powerful than SUPERUSER — CloudSQL prohibits assigning SUPERUSER for security reasons since it is a managed service.
Dealing with database users is not intuitive and natural for infrastructure engineers who are more acquainted with Cloud IAM users and service accounts — let’s see how to use these instead.
Using IAM entities to access PostgreSQL
You may already be using IAM entities to access VMs, storage buckets, Kubernetes clusters, and other cloud resources, so using them to access PostgreSQL is desirable.
Fortunately, CloudSQL lets us set up a database user linked to an IAM subject. Essentially, you enable the flag “cloudsql.iam-authentication” on the instance and create a database user whose username matches an IAM subject’s email address. Then, CloudSQL will link the database user to the corresponding IAM subject, enabling you to log in using the credentials of that IAM subject.
I assume that CloudSQL maps the IAM subject to a PostgreSQL user by adding entries in the pg_ident.conf file; this is the conventional file in PostgreSQL where you can map usernames from external authentication methods to database users.
IAM integration unlocks benefits like granting access to an entire Gsuite group or easily revoking users’ access when they change teams or leave the company by removing them from the group.
With this solid grasp of PostgreSQL configuration in CloudSQL, we are ready to start using it.
Connecting to PostgreSQL on CloudSQL
We face different challenges depending on where the client resides. For example, a developer connecting from their local machine using psql does not have to worry about re-connections, but a production app running in a Kubernetes pod has to worry about re-connections due to scaling events. So, let’s examine these two connection scenarios.
Connecting from a local development machine
Suppose a developer wants to access data on a staging database from their local machine using psql, the client-server connection is mostly uninterrupted once established, so we are not concerned about re-connections, only ensuring that it is encrypted.
We can use the Certificate Authentication method of PostgreSQL to provision a database user with a certificate keypair, as opposed to username-password; we can do this via the CloudSQL API. psql uses the client cert credentials to establish a secure connection to the instance and authenticate as the DB user.
Presently, you can only provision ten client certificates per CloudSQL instance, limiting you to only ten users with certificate auth. However, we can avoid this limitation by using the following connection approach.
Connecting from a Production cluster with the CloudSQL Auth Proxy
In a non-local environment like a production Kubernetes cluster, we would like to keep application code simple by abstracting the intricacies of DB connections to an external process.
CloudSQL Auth Proxy is an application that abstracts away these concerns. It runs as a sidecar to your app, establishing and maintaining a connection to the CloudSQL server so that your application can connect to a localhost address as if you were running PostgreSQL locally.
Since CloudSQL Auth Proxy must establish a secure connection to the CloudSQL Instance, You must provide it with credentials like a JSON key file of an authorized IAM Service Account (or the access token of an IAM user).
CloudSQL Auth Proxy differs from regular clients in that it does not connect directly to the PostgreSQL server but instead to a counterpart ‘proxy server’ process on the instance on port 3307. So you should ensure that your firewall rules allow egress to the CloudSQL instance IP on port 3307.
Some benefits of using the CloudSQL Auth Proxy are that you don’t need to explicitly allow the client IP in the CloudSQL instance Authorized Network. Also, it automatically establishes a secure TLS connection to the server, so you don’t have to provision a client SSL cert and potentially hit the 10-cert limit.
Conclusion
This article introduced GCP CloudSQL for PostgreSQL and explored various aspects relevant to infrastructure engineers. First, we looked at the internals of PostgreSQL, its authentication and RBAC systems, then tied it with how CloudSQL integrates with all of that. Finally, backed with this knowledge, we explored ways to connect our client applications to CloudSQL and the trade-offs of each method.
Even when provided as a managed service, database systems like PostgreSQL are complex, and the infrastructure behind them can be daunting. Still, hopefully, this article has provided a clearer understanding of what you need to provision and maintain such a service as an infrastructure engineer.
.svg){kind=small}
.svg){kind=small}
.svg){kind=small}
.png)
