Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Connecting Google CloudSQL (Postgres) via SSH Tunnel

Important!
This article only applies to Postgres-based CloudSQL databases.

If you want to connect a MySQL-based CloudSQL instance, use these instructions.

In this article, we'll walk you through connecting your Google CloudSQL (Postgres) database to Stitch via an SSH Tunnel.

Connecting Google CloudSQL is a seven-step process:

  1. Retrieve the Stitch public key
  2. Whitelist the Stitch IP addresses
  3. Create a Linux user for Stitch
  4. Create a Stitch Google CloudSQL user
  5. Enter the connection info in Stitch
  6. Define the Replication Frequency
  7. Select tables and columns to sync

Retrieving the Stitch Public Key

The Public Key is used to authorize the Stitch Linux user. In the next step, we'll create the user and import the key.

To retrieve the key:

  1. On the Stitch dashboard page, click the Add an Integration button.
  2. Click the Google CloudSQL PostgreSQL icon.
  3. When the credentials page displays, click the Encryption Type menu and select the SSH Tunnel option.
  4. The Public Key will display, along with the other SSH fields.

Leave this page open throughout the tutorial - you'll need it in the next section and at the end.

Whitelisting the Stitch IP Addresses

For the connection to be successful, you must configure your firewall to allow access from our IP addresses. Whitelist the following IPs before continuing onto the next step:

  • 52.23.137.21/32
  • 52.204.223.208/32
  • 52.204.228.32/32
  • 52.204.230.227/32

Creating a Linux User for Stitch

Important!
If the sshd_config file associated with the server is not set to the default option, only certain users will have server access - this will prevent a successful connection to Stitch. In these cases, it's necessary to run a command like AllowUsers to allow the Stitch user access to the server.

This can be a production or slave machine, as long as it contains real-time (or frequently updated) data. You may restrict this user any way you like as long as it retains the right to connect to the Google CloudSQL server.

Note that anything inside square brackets - [like this], for example - is something you need to define when running the commands yourself.

To create the new user, run the following commands as root on your Linux server:

adduser [stitch username] -p
mkdir /home/[stitch username]
mkdir /home/[stitch username]/.ssh

To ensure the user has access to the database, we need to import the Public Key from the first step into authorized_keys. Copy the entire key into the authorized_keys file as follows:

touch /home/[stitch username]/.ssh/authorized_keys
"< [PASTE KEY HERE] >" >> /home/[stitch username]/.ssh/authorized_keys

To finish creating the user, alter the permissions on the /home/[stitch_username] directory to allow access via SSH:

chown -R [stitch username]:[stitch username] /home/[stitch username]
chmod -R 700 /home/[stitch username]/.ssh

 

Creating a Stitch Postgres-CloudSQL User

These instructions only apply to Postgres-based CloudSQL databases.

If you want to connect a MySQL-based CloudSQL instance, use these instructions.

Your organization may require a different process, but the simplest way to create this user is to execute the following query when logged into Postgres as a user with the right to grant privileges. This user should also own the schema that Stitch is being granted access to.

CREATE USER [stitch username] WITH ENCRYPTED PASSWORD '[secure password]';
GRANT CONNECT ON DATABASE [database name] TO [stitch username];
GRANT USAGE ON SCHEMA [schema name] TO [stitch username];
GRANT SELECT ON ALL TABLES IN SCHEMA [schema name] TO [stitch username];
ALTER DEFAULT PRIVILEGES IN SCHEMA [schema name] GRANT SELECT ON TABLES TO [stitch username];

Replace [secure password] with a secure password, which can be different than the SSH password. Additionally, make sure you replace [database name] and [schema name] with the appropriate names in your database.

If you want to connect multiple databases or schemas, repeat this process as necessary.


Entering the Connection Info into Stitch

To wrap things up, we need to enter the connection and user info into Stitch. Did you leave the Google CloudSQL credentials page open? If not, click the Add an Integration button on the dashboard and then click the Google CloudSQL PostgreSQL icon. Don't forget to select the SSH Tunnel option from the Encryption Type menu.

  • Integration name: This is the name that will display on the Stitch dashboard for the integration; it’ll also be used to create the schema in your data warehouse.

    For example, the name “CloudSQL Postgres” would create a schema called cloudsql_postgres in the data warehouse.
  • Host: By default, this will be localhost. In general, it will be the bind-address value for your Google CloudSQL server, which by default is 127.0.0.1 (localhost), but could also be some local network address (e.g. 192.168.0.1) or your server's public IP address.
  • Port: This is Google CloudSQL's port on your server (5432 by default)
  • Username: This is the username of the Postgres-CloudSQL Stitch user
  • Password: This is the password of the Postgres-CloudSQL Stitch user
  • Remote Address: This is the IP address or hostname of the server we will SSH into
  • SSH Port: This is the SSH port on your server (22 by default)
  • SSH User: This is the username of the Linux (SSH) Stitch user

In the next step, you'll define the integration's replication frequency and save the connection.

Defining the Replication Frequency

The Replication Frequency controls how often Stitch will attempt to replicate data from your data warehouse. By default, the frequency is set to 30 minutes, but you can change it to better suit your needs.

When you're finished, click the Save Integration button to complete the setup.

Selecting Tables and Columns to Sync

Now that your Google CloudSQL database is connected to Stitch, the next step is selecting the tables and fields you want to sync.

If you're missing tables or some have a Sync Status of Unsupported, try these troubleshooting tips.

Related:

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!