PostgreSQL Destination Setup Guide

To use username and password authentication, create a dedicated database user for Fivetran.

1. Connect to your PostgreSQL database and execute the following command. Replace <username> with a username of your choice (for example, fivetran) and <password> with a password of your choice.

   CREATE USER <username> PASSWORD '<password>';
   

2. Grant the following database privileges to the user:

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

   Replace <database> with the name of your database, and then execute the following command:

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

3. Make a note of the username and password. You will need them to configure your destination in Fivetran.

To use IAM authentication, configure your Aurora PostgreSQL cluster or Amazon RDS for PostgreSQL instance and create the AWS and PostgreSQL resources that Fivetran requires.

Find External ID

1. Log in to your Fivetran account.
2. On the navigation menu, click Destinations.
3. Click + Add destination.
4. Enter a Destination name of your choice, and then click Add.
5. Select PostgreSQL as the destination type.
6. In the Authentication Method drop-down menu, select IAM.
7. Make a note of the External ID. You will need it to create an IAM role for Fivetran.

Enable IAM authentication

1. Log in to the Amazon RDS console.

2. On the navigation menu, click Databases.

3. Select your Aurora PostgreSQL cluster or Amazon RDS for PostgreSQL instance, and then click Modify.

4. Expand Additional credentials settings, and then choose the appropriate option:

   • Aurora PostgreSQL: Choose IAM database authentication.
   • Amazon RDS for PostgreSQL: Choose Password and IAM database authentication.

5. Click Continue.

6. In the Schedule modifications section, choose Apply immediately.

7. Complete the modification:

   • Aurora PostgreSQL: Click Modify cluster.
   • Amazon RDS for PostgreSQL: Click Modify DB instance.

Create database user

Create a dedicated database user for Fivetran. You will need this username when you create the IAM policy.

1. Connect to your PostgreSQL database and execute the following command to create a dedicated database user for Fivetran:

   CREATE USER <username>;
   

   Replace <username> with a username of your choice (for example, fivetran).

2. Grant the rds_iam role to the user:

   GRANT rds_iam TO <username>;
   

3. Grant the CREATE and TEMPORARY database privileges to the user. Replace <database> with the name of your database:

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

4. Make a note of the username. You will need it when you create the IAM policy and configure your destination in Fivetran.

Create IAM policy

1. Log in to the IAM console.

2. On the navigation menu, click Policies.

3. Click Create policy.

4. Go to the JSON tab.

5. Copy the following policy and paste it into the JSON editor.

   {
     "Version": "2012-10-17",
     "Statement": [
       {
         "Effect": "Allow",
         "Action": [
           "rds-db:connect"
         ],
         "Resource": "arn:aws:rds-db:REGION:ACCOUNT_ID:dbuser:RESOURCE_ID/DB_USERNAME"
       }
     ]
   }
   

6. Replace the following values in the Resource ARN:

   • REGION with the AWS region of your cluster or instance (for example, us-east-1)
   • ACCOUNT_ID with your AWS account ID
   • RESOURCE_ID with the DB cluster resource ID for Aurora PostgreSQL or the DB instance resource ID for Amazon RDS for PostgreSQL
   • DB_USERNAME with the username of the PostgreSQL user you created for Fivetran

7. Click Next.

8. In the Policy name field, enter a name for the policy, and then click Create policy.

Create IAM role

1. On the navigation menu, click Roles.
2. Click Create role.
3. Choose AWS account as the Trusted entity type.
4. Under An AWS account, choose Another AWS account.
5. In the Account ID field, enter the Fivetran AWS account ID (834469178297).
6. Select the Require external ID checkbox.
7. In the External ID field, enter the external ID you found.
8. Click Next.
9. Select the checkbox for the IAM policy you created, and then click Next.
10. In the Role name field, enter a name for the role, and then click Create role.
11. In the Roles page, select the role you created.
12. Make a note of the ARN. You will need it to configure your destination in Fivetran.

A direct connection allows Fivetran to connect directly to your PostgreSQL database over the public internet.

Configure your database firewall, security group, or other network access controls to allow the following traffic:

• Inbound: Allow connections to your PostgreSQL host and port (the default port is 5432) from the Fivetran IP addresses for your destination region.
• Outbound: Allow connections from ports 1024–65535 to the Fivetran IP addresses for your destination region.

An SSH tunnel allows Fivetran to connect to your PostgreSQL database through an SSH server in your network. Use this connection method when Fivetran cannot connect directly to your database host.

To configure an SSH tunnel:

1. In the destination setup form, select Connect via an SSH tunnel from the Connection method drop-down menu.

2. In the SSH Key Type drop-down menu, select the SSH key type you want to use:

   • RSA
   • ECDSA

3. Copy the Public Key provided by Fivetran.

4. Add the public SSH key to the authorized_keys file on your SSH server.

   Add the entire public SSH key on a single line. Do not add line breaks when copying the key.

For additional SSH server configuration requirements, see our SSH tunnel documentation.

Private networking allows Fivetran to connect to your PostgreSQL database without sending traffic over the public internet.

Fivetran supports the following private networking options:

• AWS PrivateLink: Used for AWS VPCs and AWS-hosted or on-premises services. For more information, see our AWS PrivateLink documentation.
• Azure Private Link: Used for Azure virtual networks (VNets) and Azure-hosted or on-premises services. For more information, see our Azure Private Link documentation.
• Google Cloud Private Service Connect: Used for Google Cloud VPC networks and Google Cloud-hosted or on-premises services. For more information, see our Google Cloud Private Service Connect documentation.

Proxy Agent allows Fivetran to connect to your PostgreSQL database through an agent installed in your network. The agent establishes an outbound connection to Fivetran, enabling secure communication between Fivetran and your destination.

For more information, see our Proxy Agent documentation.

If Fivetran connects directly to your database endpoint, configure the VPC security group associated with your database instance to allow access from Fivetran. If your database subnets use custom or restrictive network access control lists (ACLs), make sure the ACL rules also allow traffic between Fivetran and your database.

Configure security group

1. Log in to the Amazon RDS console.

2. On the navigation menu, click Databases.

3. Select your database:

   • Amazon Aurora PostgreSQL: Expand your database cluster and select the database instance you want to use as your destination.
   • Amazon RDS for PostgreSQL: Select the database instance you want to use as your destination.

4. Go to the Connectivity & security tab.

5. Make a note of the database Port. You will need it to complete the security group configuration.

6. Expand the Additional configurations section.

7. In the Security section, click the link displayed in the VPC security groups field.

8. Click the link in the Security group ID column.

9. Go to the Inbound rules tab and click Edit inbound rules.

10. Click Add rule.

11. In the Port range field, enter the port number you found earlier.

12. In the Source drop-down menu, select Custom, and then enter the Fivetran IP addresses.

13. Click Save rules.

Configure network ACLs

Configure network ACLs only if your database subnets use custom or restrictive ACLs. The default network ACL typically allows all inbound and outbound traffic and does not require modification.

1. Log in to the Amazon RDS console.

2. On the navigation menu, click Databases.

3. Expand your database cluster and select the database instance you want to use as your destination.

4. Go to the Connectivity & security tab.

5. In the Connect using section, select Endpoints.

6. Expand the Additional configurations section.

7. In the Networking section, click the link displayed in the VPC field.

8. Click the link in the VPC ID column.

9. In the Details section, click the link displayed in the Main network ACL field.

10. Click the link in the Network ACL ID column.

11. Go to the Inbound rules tab.

    • If you use the default VPC that AWS automatically created, the inbound rules may already allow all incoming traffic. This is indicated by an ALLOW rule with the source 0.0.0.0/0 listed above the DENY rule.
    • If your inbound rules do not include an ALL - 0.0.0.0/0 - ALLOW rule, edit the rules to allow the Fivetran IP addresses to access your database port.
    For more information, see Amazon documentation.

12. Go to the Outbound rules tab. If your outbound rules do not include an ALL - 0.0.0.0/0 - ALLOW rule, update the rules to allow outbound traffic to the Fivetran IP addresses on ephemeral ports 1024–65535.

If you connect to your Azure Database for PostgreSQL flexible server using its public endpoint, configure firewall rules to allow the Fivetran IP addresses.

1. Log in to the Azure portal.

2. Go to your Azure Database for PostgreSQL flexible server.

3. On the navigation menu, go to Settings > Networking.

4. Under Firewall rules, add a firewall rule for each Fivetran IP address.

5. For each rule:

   i. Enter a name for the rule.

   ii. Enter the Fivetran IP address in both the Start IP address and End IP address fields.

6. Click Save.

Enable native Postgres password authentication so that Fivetran can connect using a PostgreSQL username and password.

For more information about Lakebase authentication methods, see Databricks documentation.

To enable native Postgres password authentication:

1. Log in to your Databricks workspace.
2. From the app switcher, select Lakebase Postgres.
3. Select your project.
4. Go to Settings > Database connections.
5. Select the Password (Native Postgres roles) checkbox.
6. Click Save.

If you connect to your Cloud SQL for PostgreSQL instance using its public IP address, configure an authorized network for the Fivetran IP addresses.

1. Log in to the Google Cloud console.
2. On the navigation menu, click Cloud SQL.
3. Select the Cloud SQL for PostgreSQL instance that you want to connect to Fivetran.
4. On the SQL navigation menu, click Connections.
5. Go to the Networking tab.
6. Select the Public IP checkbox.
7. In the Authorized networks section, click Add a network or expand New IP range.
8. In the Name field, enter a name for the authorized network rule (for example, fivetran).
9. In the IP range field, enter a Fivetran IP address or CIDR range. Add a separate rule for each additional Fivetran IP address.
10. Click Done.
11. Click Save.