Syncing to your database

When creating a new sync on Sequin, you’ll have two options for your destination:

1. Sync to a Sequin-hosted demo database
2. Sync to your self-hosted database

We provide Sequin-hosted demo databases so you can get up and running quickly. You’ll be provisioned a private database on a shared AWS RDS instance. You can connect to a Sequin hosted database from any standard SQL client. But since your database is on a shared instance, permissions and performance are limited.

After you’ve gotten a feel for Sequin and are ready to use your sync in production, configure us to sync right to your production database.

In this guide, you’ll connect your self-hosted database and set up your sync in just a couple minutes.

​

Self-hosted database setup

Step 1: Create a new account at https://console.sequin.io/signup.

Step 2: Select the Source (e.g. Salesforce, HubSpot, etc) you want to sync to your database and connect it to Sequin.

Step 3: In the Destination section, select New database connection to start setting up your database:

Step 4: Enter the hosting details for your database. Optionally setup a SSH Tunnel to connect to a Postgres instance in a private network.

If you’d like to connect to a local database, see our section on using localhost.

Step 5: Create a new user in your database that Sequin will use to run queries in your database. You’ll do this by first creating the user, granting the user permissions, and then extending those permissions to a new database role:

1. Create a Postgres user for Sequin:

create user sequin with encrypted password '▀▀▀▀▀▀';

1. Grant permissions:

grant connect, create on database YOUR_DBNAME to sequin;

Learn more about the permissions we request.

1. Create a Sequin read role: You’ll grant this read role to database users to grant access to Sequin tables.

create role sequin_read with admin sequin;

Step 6: Enter the database, schema, and Sequin user credentials you created.

• Database: The name of the database you are connecting to (sometimes called the ”dbname”).
• Schema to sync to: The name of the Postgres schema (or namespace) Sequin will create in your database. This schema will contain all the tables and data synced from your source. For example, if you’re syncing Stripe to your database, you might sync that data to a schema called stripe.

caution

If the schema already exists in your database, we may request you to grant the Sequin user USAGE and CREATE permissions to it:

grant usage, create on schema "stripe" to sequin;

To prevent data loss, we recommend not pointing your sync to an existing schema. Sequin will automatically create and manage your sync schema.

• Credentials: Enter the username and password for the new database user you just created for Sequin in the previous steps.

(For more on how permissions work in your database, read the permissions section below.)

Step 7: Once you’ve entered your database, schema, and user details, click Setup. Sequin will verify your database connection and check your permission grants. We’ll provide instructions if any additional database configuration is required.

Step 8: Select which Postgres users should be granted access to your new Sequin schema. Select as many as you need and then click Finish:

(If you need to grant a new user access to a Sequin schema, read the permissions section below.)

Step 9: When you click Finish, Sequin will create the new schemas in your database, then add the users you selected in Step 6 to the Sequin read role. Click Finish.

Step 10: Your self-hosted database is now connected to Sequin. Finish configuring your source and click the Create button to start your real-time sync.

​

Inbound IP addresses

Sequin’s sync workers will make inbound requests using the following two IP addresses:

• 54.245.7.80
• 35.155.134.189

Be sure inbound traffic to your database is allowed from these two IP addresses.

​

Regional Proxy

Sequin has several regional proxies you can use to connect to your database. If you’re using a proxy other than us-west-2, you’ll need to allow inbound connections from the IP addresses of that proxy region:

​

SSH tunnels

Your Postgres database may be hosted in a private VPC, inaccessible from the external internet. In that instance, you can use an SSH tunnel or bastion host to route traffic from the outside internet to your Postgres database.

Setup your SSH tunnel with Sequin when you are connecting a new database in the Sequin console. During this setup, download the id_rsa_sequin_tunnel.pub public key file shown in the setup modal:

Access your SSH server. This is either where your self-hosted database is located or a bastion server. Place the id_rsa_sequin_tunnel.pub in the home directory of the SSH tunnel user in the ~/.ssh/authorized_keys file:

# navigate to the ssh user's home directory
cd ~

# optionally create the .ssh directory if it does not exist
mkdir -p ~/.ssh

# open the authorized_keys for editing
nano ~/.ssh/authorized_keys

Paste the content of the id_rsa_sequin_tunnel.pub file into the authorized_keys file. Save the file and exit the text editor.

Make sure the authorized_keys file is secure. Run the following command:

chmod 600 ~/.ssh/authorized_keys

​

SSL Certificates

If you require client SSL certificates to connect to your database, send us a note. This is often the case if you’re using Google Cloud as your database provider.

​

SSL Modes

Postgres supports several different SSL modes, including disable. See this table for more information.

​

Syncing additional sources

You can sync multiple sources into unique schemas within your self-hosted database. This makes querying across your data easy. Here is how to setup multiple sources to sync to your self-hosted database:

Step 0: If you haven’t already, connect your self-hosted database to Sequin.

Step 1: Select the new Source you want to sync to your database and connect it to Sequin.

Step 2: In the Destination section, click the Edit button and select Choose Existing:

Step 3: Click the Select button next to your self-hosted database:

Step 4: Confirm the name for the new schema you’d like to create in your database and click the Confirm changes button:

Step 5: Your self-hosted database is now configured for an additional source. Finish configuring your source and click the Create button to start your real-time sync.

​

Changing schema names

You can change the name of the schema we sync to.

Step 1: Edit your resource by clicking the pencil icon and selecting Edit details:

Step 2: Expand the Destination section and click Change:

Step 3: Select Choose existing.

Step 4: In the modal that appears, you’ll see all the databases we are actively syncing to. Click the grey Configure button next to your database:

Step 5: Enter the new name for your schema. Then click Confirm changes:

Step 6: Click the Save button on your resource.

When you change the name of the schema associated to your sync, we’ll create a new schema in your database with the new name and then backfill your data into this new schema. We will not automatically drop the old schema and swap schema.

If you would like, you can delete the old schema and swap schema from your database:

drop schema {{schema_name}} cascade;

​

Permissions

PostgreSQL permissions define what a user can do. You can define what a user can access (like CONNECT to your database) and what actions they can take (like SELECT and CREATE). These permissions can be defined at the database, schema, table, and view level.

A role is a set of permissions. A user is a role that has login permissions (includes a password that someone can use to login to the database).

When creating a sync to your self-hosted database, you create a new user and a new role:

• The sync user (e.g. sequin): This is the user Sequin uses to connect to your database. This user only has permission to connect to your database and create the schemas, tables, and views associated to your sync. This user will need owner privileges on any table or view related to your sync.
• The read role (e.g. sequin_read): This role has the privilege to read Sequin tables and views in your database. Any user in your database that needs to read synced data should be granted the sequin_read role. It is provided for convenience and is not used by the Sequin sync process.

​

The Sequin user

The sequin user creates and updates the data we are syncing for you. When syncing to your database we first create the Sequin user:

create user sequin with encrypted password '▀▀▀▀';

Then we grant that user permissions to:

1. Connect to your database
2. Create new schemas

grant connect, create on database YOUR_DBNAME to sequin;

​

The Sequin read role

The sequin_read role is provided for your convenience. The role has permission to read all the Sequin data in your database. You can give or revoke the read role permission to your database users at any time.

You can manually create this read role during the database setup. If we detect a role named sequin_read, we’ll grant that role the appropriate permissions. The sequin user needs to be admin of the sequin_read role. The following command will grant the sequin user admin options of the role during creation:

create role sequin_read with admin sequin;

If you’ve already created the role, you can grant it the admin option like so:

grant sequin_read to sequin with admin true;

If you used our legacy automated database setup, your read role was automatically created and granted to the users you selected.

To find your read role:

• You can check your Sequin console.
• You can just let your SQL client auto-complete it after typing sequin_read + “tab.”
• You can run this query:

select * from pg_roles where rolname ilike 'sequin_read%';

You can then grant users the ability to read from Sequin-managed schemas via the Sequin read role:

-- sub-out the read role below with the one in your db
grant sequin_read to some_user;

To revoke the read role from a user:

revoke sequin_read from some_user;

​

Additional tables and views

​

Tables

You may want to create new tables in your Sequin schema. As a best practice, we encourage you to create any additional tables in a different schema.

But, in instances where the table needs to be in a Sequin-owned schema, make sure that the Sequin user does not own the table. Sequin won’t drop tables it doesn’t own. To verify the owner of a table, use this query:

select tableowner from pg_tables where tablename = 'my_table';

To set the table owner to your current user, run this command:

alter table stripe._sync_event owner to current_user;

​

Views

Postgres’ views let you turn a query into a table. This lets you customize the schema and format of the data that Sequin syncs. Because views are just queries under the hood, they’ll always return the latest data.

Treat views just like tables (above): make sure that Sequin is not the owner of the view.

​

Using localhost

We recommend using ngrok to create a secure tunnel from your localhost database connection to the outside internet. The process below is explained in more detail in ngrok’s getting started guide.

1. Ensure your local Postgres server is running
2. Install the ngrok agent. Instructions for how to do that can be found here.
3. Connect your ngrok agent to your ngrok account. Instructions for for how to do that can be found here.
4. Start ngrok ngrok http 5432 (assuming Postgres is running on port 5432). The Forwarding property in the ngrok UI in your terminal is the url you’ll use with Sequin’s host field. Ngrok’s detailed instructions for this step can be found here.

You should be ready to use your local database and an ngrok tunnel now.

​

Common connection issues

​

Connections timeout or hang

If you’re experiencing connection timeouts or our system can’t seem to reach the database server, it’s likely one of two problems:

• Firewall rules: It’s possible firewall rules are preventing the connection. To resolve this, adjust the firewall settings associated with your database server to allow traffic from our system’s IP addresses on your database’s port.
• Network routing issues: It’s possible your routing table is misconfigured or your database is not setup for outside access.

Verify that your database is reachable using tools like Telnet or Netcat. Using Telnet, you’ll see the following if you’re able to connect:

# using telnet
> telnet mydatabase-hostname.example.com 5432
Trying 127.0.0.1...
Connected to mydatabase-hostname.example.com.
Escape character is '^]'.

Using Netcat, nc will “hang open” if it’s able to connect, otherwise it will crash:

# using nc
> nc mydatabase-hostname.example.com 5432

​

Connection refused errors

These are likely due to a mismatch between the hostname and port of your database and those configured in Sequin.

​

SSL/TLS failures

There are four possible permutations for SSL/TLS configuration. When there is a mismatch, you’ll receive a SSL/TLS error:

• ❌ Sequin is configured to use TLS, but the database does not support it.
• ✅ Sequin is configured to use TLS, and the database supports it.
• ✅ Sequin is configured to not use TLS, and the database does not require it.
• ❌ Sequin is configured to not use TLS, but the database requires it.

If your database supports it, configure Sequin to use SSL/TLS. Otherwise, you can turn this off.

​

Common Authentication Issues

​

Invalid Authorization Specification

invalid_authorization_specification typically occurs when there is a problem with the way you’re specifying authentication information while trying to connect to a PostgreSQL database. The most common way this happens while using Sequin is when there is a mismatch between the authentication method specified by the database and how Sequin is trying to connect. If you encounter this error, either ensure you’re using a password authentication method, or you have Use SSL selected.