Connecting a Self-Hosted PostgreSQL Data Warehouse to Stitch

An open-source relational database, PostgreSQL is a powerful and well-known system that has received recognition from both its users and the industry at large. Unlike some other database systems, PostgreSQL is completely customizable and yours to do with as you please (assuming, of course, that your instance is self-hosted).

Prerequisites

1. A bit of technical know-how. We recommend looping in a developer or a member of your technical team if you come across something you aren’t sure about.

2. An up-and-running Postgres instance. Instructions for installing PostgreSQL and creating an initial database are outside the scope of this tutorial; our instructions assume that you have a PostgreSQL instance up and running. For help installing and getting started with PostgreSQL, refer to the Postgres documentation.

3. A PostgreSQL instance running on or above. While this isn’t something that Stitch strictly enforces, we recommend keeping your version current as a best practice.

4. createdb permissions in your PostgreSQL instance. This is required to create a database for Stitch.

Step 1: Whitelist Stitch’s IP Addresses

For the connection to be successful, you’ll need to 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

Step 2: Create a Postgres Database

Next, you’ll create a database in your PostgreSQL instance for Stitch. This is where data replicated by Stitch will be stored.

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: Retrieve Your Public Key

If you aren’t using an SSH tunnel to connect, you can skip this step.

The Public Key is used to authorize the Stitch Linux user. If the key isn’t properly installed, Stitch will be unable to access your database.

To retrieve the key:

1. Click the Destination tab.
2. Click the 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.

Step 4: Create a Stitch Linux User

If you aren’t using an SSH tunnel to connect, you can skip this step.

Note that anything inside square brackets - [like this] - 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 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

In the next step, you’ll create a database user for Stitch.

Step 5: Create a PostgreSQL User for Stitch

Required PostgreSQL Permissions

To successfully pipe your data to your PostgreSQL data warehouse, the Stitch user must:

• Be able to CREATE schemas, tables, and views
• Be able to SELECT from systems tables
• Be the owner of all integration schemas and tables. We occasionally need to run COMMENT and ALTER TABLE commands to properly insert your data.

While you technically can manually create integration schemas in PostgreSQL and then grant Stitch access to those schemas, we don’t recommend it. We understand that security is a high priority and permissions should be carefully granted, but this approach will likely lead to headaches for you.

What do we mean? If you take this approach, you’ll need to first create the schema in your data warehouse and then grant the Stitch user the appropriate permissions to access it every single time a new integration is added in Stitch. If this isn’t done, Stitch won’t be able to load data into your data warehouse.

Additionally, if there are any differences between the name of the schema you create in your data warehouse and the schema name you enter in Stitch when setting up an integration, Stitch won’t be able to load data into your data warehouse.

Create the Stitch User

• Connection Type
• NEW Connections
• EXISTING Connections

create user [stitch_username] with password '[password]';

grant create on database [database_name] to [stitch_username];

grant select on all tables in schema information_schema to [stitch_username];

create user [stitch_username] with password '[password]';

grant create on database [database_name] to [stitch_username];

grant select on all tables in schema information_schema to [stitch_username];

select * from [stitch_username] pg_tables;

alter schema [schema_name] owner to [stitch_username];

alter table [table_name] owner to [stitch_username];

for table in `psql -qAt --host [your.postgres.amazonaws.com] --port [port] --user [admin_user] -c "select tablename from pg_tables where schemaname = '[schema_name]';" [database_name]` ; 
do psql -qAt --host [your.postgres.amazonaws.com] --port [port] --user [admin_user] -c "alter table \"[schema_name]\".\"$table\" owner to [stitch_username];" [database_name] ; done

Step 6: Connect Stitch

To wrap things up, you need to enter the PostgreSQL database connection and user info into Stitch.

1. Click the Destination tab.
2. Click the PostgreSQL icon.
3. Fill in the fields as follows:
   • Host: Enter the host address (endpoint) used by the PostgreSQL instance.
   • Port: Enter the port used by the PostgreSQL instance.
   • Username: Enter the Stitch PostgreSQL user’s username.
   • Password: Enter the password for the Stitch PostgreSQL user.
   • Database: Enter the name of the PostgreSQL database that you created for Stitch.
4. 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 that this test may take a few minutes to complete.

Troubleshooting Connection Errors

If you receive an error message, we recommend trying these troubleshooting steps for common destination connection problems before reaching out to support.

We’ve found that the majority of the time, these resources can resolve most issues.

Learning About Stitch & PostgreSQL

Now that your PostgreSQL data warehouse is up and running, we recommend learning about how Stitch will load your data into it.

• PostgreSQL Destination Overview: This overview will give you the high-level basics for working with your PostgreSQL data warehouse.
• Destination Data Loading Guide: This article covers many of the common scenarios Stitch will encounter when loading data into your PostgreSQL destination and the expected behavior for each scenario.

Did this article help? If you have questions or feedback, please reach out to us.