In this tutorial, we’ll walk you through how to spin up a Google CloudSQL PostgreSQL instance and then connect it to Stitch as a destination.

For more info on Google CloudSQL’s features and limitations, check out the official Google documentation.


Prerequisites


Step 1: Create the CloudSQL instance

  1. In the Google Cloud Platform Console, navigate to the CloudSQL Instances page.
  2. Select the project you created and click Continue.
  3. Click Create Instance.
  4. Click PostgreSQL.
  5. In the Instance ID field, enter an ID for the instance. Note that this ID is permanent and must begin with a letter.
  6. Enter a password for the postgres (master) user.
  7. If you want specific values for other fields, enter them. Otherwise, you can use the defaults. More info on the Instance Settings can be found here in Google’s documentation.
  8. When finished, click Create.

The instance may take a few minutes to finish initializing. After the process completes, click the instance to open it in the CloudSQL Instances page.


Step 2: Create a database in the CloudSQL instance

This step is optional. If you want to use the instance’s default database (postgres), you can skip this step.

  1. In the CloudSQL Instances page, click the Databases tab.
  2. In the Databases tab, click New database.
  3. In the window that displays, enter a name for the database in the Name field. Note that Google clone a template database to create the new database - we’re going to use the template as-is, but you can change the settings afterwards if you like.
  4. Click Create.

Connect to your instance as a user with createdb permissions and run the following command, replacing [stitch_database] with whatever you want the database name to be:

CREATE DATABASE [stitch_database];

Responses are only returned if the command is not successful. If you don’t receive a response, then the command was successful and the database was created.

If you encounter errors - for example, createdb: command not found - then you’ll need to do some troubleshooting before you can move onto the next step. This Postgres article contains troubleshooting steps for some of the most common issues when creating databases.

Stitch doesn’t require any particular configuration for the database nor do you need to create any tables or schemas. Stitch will take care of that for you after the destination setup is complete and you’ve connected integrations.


Step 3: Configure security and access settings

Next, you’ll configure the access settings for the instance. Google access control has two levels: at the instance and at the database.

  • At the instance-level, you’ll whitelist Stitch’s IP addresses. This will allow Stitch to connect to the instance.

  • At the database-level, you’ll create a database user for Stitch. This will allow Stitch to load your data into the database. We’ll cover how to create the user and assign permissions in Step 4.

To whitelist Stitch’s IP addresses:

  1. In the CloudSQL Instances page, locate and click the instance you created in Step 1.
  2. Click Access Control > Authorization.
  3. In the Authorized Networks section, click Add Network.
  4. Enter one of Stitch’s IP addresses in the Network field:

    • 52.23.137.21/32

    • 52.204.223.208/32

    • 52.204.228.32/32

    • 52.204.230.227/32

  5. Click Done.
  6. Repeat steps 3-5 for each of Stitch’s IP addresses.
  7. Click Save to update the instance.

Step 4: Create a PostgreSQL Stitch user

Configuring the second part of the access control settings requires creating a database user for Stitch. This guide will use the psql method to create the user, which requires the use of a SQL client.

  1. If you haven’t already, connect to your CloudSQL PostgreSQL instance using your SQL client.

  2. After connecting, run this command to create the user:

    CREATE USER <stitch_username> WITH PASSWORD '<password>';
  3. Next, you’ll assign the CREATE permissions to the user. For <database_name>, enter the name of the database where all Stitch-replicated data should be loaded.

    Note: This must be a pre-existing database.

    GRANT CREATE ON DATABASE <database_name> TO <stitch_username>
  4. If you restricted access to the system tables, you’ll also need to run the following commands to grant the Stitch user SELECT permissions.

    GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO <stitch_username>
    
    GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO <stitch_username>

In the table below are the database user privileges Stitch requires to connect to and load data into CloudSQL PostgreSQL.

Privilege name Reason for requirement
CREATE ON DATABASE

Required to create the necessary database objects to load and store your data.

CREATE permissions on the database are required to successfully load data. When Stitch loads data, it will run a CREATE SCHEMA IF NOT EXISTS command, which will create a schema if it doesn’t already exist. To run this command, the Stitch user must have the CREATE ON DATABASE permission.

Note: The CREATE ON SCHEMA permission is not a sufficient alternative for CREATE ON DATABASE. As outlined in CloudSQL PostgreSQL’s documentation, this permission only allows a user to create objects within a schema, but not the schema itself.

SELECT ON ALL TABLES IN information_schema

Required to select rows from tables in the information_schema schema. Prior to loading data, Stitch will use the data in this schema to verify the existence and structure of integration schemas and tables.

Note: Stitch will only ever read data from systems tables.

SELECT ON ALL TABLES IN pg_catalog

Required to select rows from tables in the pg_catalog schema. Prior to loading data, Stitch will use the data in this schema to verify the existence and structure of integration schemas and tables.

Note: Stitch will only ever read data from systems tables.


Step 5: Connect Stitch

To complete the setup, you need to enter your PostgreSQL connection details into the Destination Settings page in Stitch.

Step 5.1: Locating the connection details in the Google Console

  1. In the CloudSQL Instances page, locate and click the instance you created in Step 1.
  2. When the instance’s Overview page displays, scroll down to the Properties section.
  3. Locate the IPv4 address field, which is highlighted in the image below:

    Google CloudSQL PostgreSQL IPv4 address field, which contains the hostname info.

  4. Copy and paste the IPv4 address into a text file or leave this page open and open your Stitch account in another tab.

Step 5.2: Enter connection details into Stitch

  1. If you aren’t signed into your Stitch account, sign in now.
  2. Click the Destination tab.
  3. Click the PostgreSQL icon.
  4. Fill in the fields as follows:

    • Host (Endpoint): Paste the address that is in the IPv4 address field in the Google Console.

    • Port: Enter the port used by the PostgreSQL instance. The default is 5432.

    • Username: Enter the Stitch PostgreSQL user’s username.

    • Password: Enter the password associated with the Stitch PostgreSQL user.

    • Database: Enter the name of the PostgreSQL database that you created for Stitch.

    • Connect using SSL: Check to connect to PostgreSQL using SSL. Note: This is not required to connect Stitch.

    • SSL Certificate: Optional: Provide the certificate (typically a CA or server certificate) Stitch should verify the SSL connection against. The connection will succeed only if the server’s certificate verifies against the certificate provided here.

      Note: Providing a certificate isn’t required to use SSL. This is only if Stitch should verify the connection against a specific certificate.

When finished, click Update PostgreSQL Settings.

Stitch will perform a connection test to the PostgreSQL database; if successful, a Success! mesage will display at the top of the screen. Note: This test may take a few minutes to complete.



Questions? Feedback?

Did this article help? If you have questions or feedback, feel free to submit a pull request with your suggestions, open an issue on GitHub, or reach out to us.