Amazon Aurora PostgreSQL Warehouse Setup Guide

Choose connection option

Decide whether to connect to your warehouse directly or connect using an SSH tunnel.

• To connect using an SSH tunnel, follow these instructions.
• To connect directly, configure your firewall and/or other access control systems to allow:
  • Incoming connections to your Postgres port (usually 5432) from Fivetran's IP
  • Outgoing connections to all ports (1024 to 65535) at Fivetran's IP

Allow Fivetran to access your database

The Fivetran data processing servers need access to your database.

• If your instance is in a VPC, you need to configure your VPC Security Groups and Network ACLs (Access Control Lists).

• If your instance is not in a VPC, then you only need to configure Security Groups.

Configure VPC security group

These instructions assume that your database is in a VPC (if not, you can still use these instructions as a guide because configuring a non-VPC security group is almost identical).

1. Select your Aurora PostgreSQL database by navigating to the databases panel on the AWS RDS service and clicking on your database from the list.

   

2. Go to the Connectivity & security panel. Write down the database's port number (you will need this later).

   

3. Click on the active VPC security group under the Security tab to view the current configuration of this group:

   

4. In the security group panel, select the Inbound tab:

   

5. Click Edit:

   

6. Click Add Rule. This will create a new Custom TCP Rule at the bottom of the list with a blank space for a Port Range and a Source IP address.

   

7. For the Port, enter your database's port number that you wrote down in step 2 (usually 5432).

8. For the Source, enter a Fivetran IP best suited for your project's location. You can always add rules with more than one Fivetran IP if required.

9. Save the newly created Inbound rule before closing the pop-up:

   

---

Configure Network ACLs

1. Select your Aurora PostgreSQL database by navigating to the databases panel on the AWS RDS service and clicking on your database from the list.

   

2. Go to the Connectivity & security panel. Write down the database's port number (you may need this later).

   

3. Click on the current VPC link under the Networking tab to view the current configuration of this VPC:

   

4. Click on the Network ACL link present in the Description panel to view it's current configuration, including the Inbound and Outbound rules:

   

5. If you have a default VPC that was automatically created by AWS, then the settings already allow all incoming and outgoing traffic. You don't need to take any action.

6. Select Inbound Rules to view your existing rules:

   

7. You can verify that your settings allow all incoming traffic because the the Source value is 0.0.0.0/0:

   

   If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, follow steps mentioned under Edit Inbound rules.

8. Select Outbound Rules to view your existing rules:

   

9. You can verify that your settings allow all outgoing traffic because the the Destination value is 0.0.0.0/0:

   

   If your outbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, follow steps mentioned under Edit Outbound rules.

Edit Inbound rules

1. Click Edit inbound rules:

   

2. Click Add Rule. This will create a new Custom TCP Rule at the bottom of the list.

   

3. For the Port range, enter your database's port number that you wrote down in step 2 (usually 5432).

4. For the Source, enter a Fivetran IP best suited for your project's location. You can always add rules with more than one Fivetran IP if required:

5. Save the newly created Inbound rule.

   

Edit Outbound rules

1. Click Edit outbound rules:

   

2. Click Add Rule. This will create a new TCP Rule at the bottom of the list.

   

3. For the Port range, enter 1024-65535 to allow outgoing traffic to these ports.

4. For the Destination, enter a Fivetran IP best suited for your project's location. You can always add rules with more than one Fivetran IP if required:

5. Save the newly created Outbound rule.

   

---

Create a Fivetran user

1. Connect to your Postgres database and run the following query to create a password:

   CREATE USER fivetran PASSWORD <password>;
   

2. Run the following query to grant the fivetran user the following privileges:

   • CREATE: Allows the user to create new schemas in the database
   • TEMPORARY: Allows the user to create temporary tables while using the database

   GRANT CREATE, TEMPORARY ON DATABASE <database> TO fivetran;