📢 Webinar Alert! Future-Proofing IT Operations: How Charter Enhanced Reliability with Squadcast. Register Here! 🌟

Configuration, Access, and Connection to GCP CloudSQL for PostgreSQL

Oct 11, 2021
Last Updated:
Oct 11, 2021
Share this post:
Configuration, Access, and Connection to GCP CloudSQL for PostgreSQL

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.

    References

    Squadcast is an incident management tool that’s purpose-built for SRE. Your team can get rid of unwanted alerts, receive relevant notifications, work in collaboration using the virtual incident war rooms, and use automated tools like runbooks to eliminate toil.

    squadcast
    What you should do now
    • Schedule a demo with Squadcast to learn about the platform, answer your questions, and evaluate if Squadcast is the right fit for you.
    • Curious about how Squadcast can assist you in implementing SRE best practices? Discover the platform's capabilities through our Interactive Demo.
    • Enjoyed the article? Explore further insights on the best SRE practices.
    • Schedule a demo with Squadcast to learn about the platform, answer your questions, and evaluate if Squadcast is the right fit for you.
    • Curious about how Squadcast can assist you in implementing SRE best practices? Discover the platform's capabilities through our Interactive Demo.
    • Enjoyed the article? Explore further insights on the best SRE practices.
    • Get a walkthrough of our platform through this Interactive Demo and see how it can solve your specific challenges.
    • See how Charter Leveraged Squadcast to Drive Client Success With Robust Incident Management.
    • Share this blog post with someone you think will find it useful. Share it on Facebook, Twitter, LinkedIn or Reddit
    • Get a walkthrough of our platform through this Interactive Demo and see how it can solve your specific challenges.
    • See how Charter Leveraged Squadcast to Drive Client Success With Robust Incident Management
    • Share this blog post with someone you think will find it useful. Share it on Facebook, Twitter, LinkedIn or Reddit
    • Get a walkthrough of our platform through this Interactive Demo and see how it can solve your specific challenges.
    • See how Charter Leveraged Squadcast to Drive Client Success With Robust Incident Management
    • Share this blog post with someone you think will find it useful. Share it on Facebook, Twitter, LinkedIn or Reddit
    What you should do now?
    Here are 3 ways you can continue your journey to learn more about Unified Incident Management
    Discover the platform's capabilities through our Interactive Demo.
    See how Charter Leveraged Squadcast to Drive Client Success With Robust Incident Management.
    Share the article
    Share this blog post on Facebook, Twitter, Reddit or LinkedIn.
    We’ll show you how Squadcast works and help you figure out if Squadcast is the right fit for you.
    Experience the benefits of Squadcast's Incident Management and On-Call solutions firsthand.
    Compare our plans and find the perfect fit for your business.
    See Redis' Journey to Efficient Incident Management through alert noise reduction With Squadcast.
    Discover the platform's capabilities through our Interactive Demo.
    We’ll show you how Squadcast works and help you figure out if Squadcast is the right fit for you.
    Experience the benefits of Squadcast's Incident Management and On-Call solutions firsthand.
    Compare Squadcast & PagerDuty / Opsgenie
    Compare and see if Squadcast is the right fit for your needs.
    Compare our plans and find the perfect fit for your business.
    Learn how Scoro created a solid foundation for better on-call practices with Squadcast.
    Discover the platform's capabilities through our Interactive Demo.
    We’ll show you how Squadcast works and help you figure out if Squadcast is the right fit for you.
    Experience the benefits of Squadcast's Incident Management and On-Call solutions firsthand.
    We’ll show you how Squadcast works and help you figure out if Squadcast is the right fit for you.
    Learn how Scoro created a solid foundation for better on-call practices with Squadcast.
    We’ll show you how Squadcast works and help you figure out if Squadcast is the right fit for you.
    Discover the platform's capabilities through our Interactive Demo.
    Enjoyed the article? Explore further insights on the best SRE practices.
    We’ll show you how Squadcast works and help you figure out if Squadcast is the right fit for you.
    Experience the benefits of Squadcast's Incident Management and On-Call solutions firsthand.
    Enjoyed the article? Explore further insights on the best SRE practices.
    Written By:
    October 11, 2021
    October 11, 2021
    Share this post:
    Subscribe to our LinkedIn Newsletter to receive more educational content
    Subscribe now

    Subscribe to our latest updates

    Enter your Email Id
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.
    FAQ
    More from
    Uzziah Eyee
    Protecting internal services with Cloudflare Access
    Protecting internal services with Cloudflare Access
    September 29, 2021
    Learn how organizations are using Squadcast
    to maintain and improve upon their Reliability metrics
    Learn how organizations are using Squadcast to maintain and improve upon their Reliability metrics
    mapgears
    "Mapgears simplified their complex On-call Alerting process with Squadcast.
    Squadcast has helped us aggregate alerts coming in from hundreds...
    bibam
    "Bibam found their best PagerDuty alternative in Squadcast.
    By moving to Squadcast from Pagerduty, we have seen a serious reduction in alert fatigue, allowing us to focus...
    tanner
    "Squadcast helped Tanner gain system insights and boost team productivity.
    Squadcast has integrated seamlessly into our DevOps and on-call team's workflows. Thanks to their reliability...
    Alexandre Lessard
    System Analyst
    Martin do Santos
    Platform and Architecture Tech Lead
    Sandro Franchi
    CTO
    Squadcast is a leader in Incident Management on G2 Squadcast is a leader in Mid-Market IT Service Management (ITSM) Tools on G2 Squadcast is a leader in Americas IT Alerting on G2 Best IT Management Products 2022 Squadcast is a leader in Europe IT Alerting on G2 Squadcast is a leader in Mid-Market Asia Pacific Incident Management on G2 Users love Squadcast on G2
    Squadcast awarded as "Best Software" in the IT Management category by G2 🎉 Read full report here.
    What our
    customers
    have to say
    mapgears
    "Mapgears simplified their complex On-call Alerting process with Squadcast.
    Squadcast has helped us aggregate alerts coming in from hundreds of services into one single platform. We no longer have hundreds of...
    Alexandre Lessard
    System Analyst
    bibam
    "Bibam found their best PagerDuty alternative in Squadcast.
    By moving to Squadcast from Pagerduty, we have seen a serious reduction in alert fatigue, allowing us to focus...
    Martin do Santos
    Platform and Architecture Tech Lead
    tanner
    "Squadcast helped Tanner gain system insights and boost team productivity.
    Squadcast has integrated seamlessly into our DevOps and on-call team's workflows. Thanks to their reliability metrics we have...
    Sandro Franchi
    CTO
    Revamp your Incident Response.
    Peak Reliability
    Easier, Faster, More Automated with SRE.
    Incident Response Mobility
    Manage incidents on the go with Squadcast mobile app for Android and iOS devices
    google playapple store
    Squadcast is a leader in Incident Management on G2 Squadcast is a leader in Mid-Market IT Service Management (ITSM) Tools on G2 Squadcast is a leader in Americas IT Alerting on G2 Best IT Management Products 2024 Squadcast is a leader in Europe IT Alerting on G2 Squadcast is a leader in Enterprise Incident Management on G2 Users love Squadcast on G2
    Squadcast is a leader in Incident Management on G2 Squadcast is a leader in Mid-Market IT Service Management (ITSM) Tools on G2 Squadcast is a leader in Americas IT Alerting on G2
    Best IT Management Products 2024 Squadcast is a leader in Europe IT Alerting on G2 Squadcast is a leader in Enterprise Incident Management on G2
    Users love Squadcast on G2
    Copyright © Squadcast Inc. 2017-2024