Aurora PostgreSQL Configuration
First, you'll need to decide whether to connect your PostgreSQL database directly or via an SSH tunnel. Security group configuration in subsequent steps will differ depending on this decision.
If you connect directly, you will need to create a rule in security group that allows us to access your database instance.
If you connect via a SSH tunnel, Fivetran will connect to a separate server in your network which provides an SSH tunnel to your database. This method is necessary if your database is in an inaccessible subnet. You will then configure your tunnel server's security group to allow us access and then configure the database instance's security to allow access from the tunnel.
If you have an SSH connection, please follow these instructions before proceeding to the next step.
Allow access to read replica
Creating a read replica is recommended but not necessary. Using a read replica will reduce the load on the master of Fivetran's queries. In practice, this load is negligible unless your database has a >100 million row table.
If you already have a read replica, you can skip to the next section Enable access.
In your RDS Dashboard, select the Aurora Postgres instance you would like to integrate:
Create aurora replica.
Make sure the replica is accessible from outside your VPC.
DB instance class for the replica. It need not to be as large as your master instance.
DB instance identifier.
DB Parameter group and write down the aurora replica's database
port number (you will need this later).
Then click on
Create Aurora replica.
Your RDS dashboard should now show the status of the replica as
It will take a few minutes for the Aurora replica to finish being created. The status will show
available when it is done.
The Fivetran data processing servers will need access to your Aurora PostgreSQL replica/master you want to integrate. Assuming your instance is in a VPC, access is controlled by two mechanisms. 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 security group
These instructions assume that your read replica 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).
Click on your Aurora replica.
Go to your replica
Details and make it
Then click its
In the Security Group panel, select
Inbound tab then click
Add Rule to add a new
Custom TCP rule.
Enter your replica's port number (default 5432) in
Enter Fivetran's IP in
Description to your rule and click
Configure network ACLs
Click on your
VPC in Aurora replica
VPC. In the
Summary tab, select
You will see tabs for Inbound Rules and Outbound Rules. We will need to edit both.
Edit Inbound Rules
If you have a default VPC that was automatically created by AWS;
then it would have the setting, allow all incoming traffic as indicated by the Source value
and the fact that the
ALLOW entry is listed above the
If your inbound rules don't include
ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow inbound traffic to all ports
Edit Outbound Rules
Select "Outbound Rules":
If your outbound rules don't include an
ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow outbound traffic to all ports
Help on ACL configuration can be found here.
Open a connection to your master Aurora PostgreSQL database and execute the following SQL command to create
CREATE USER fivetran PASSWORD 'some-password';
some-password with a password of your choice.
Grant read-only access to Fivetran
Then grant the
fivetran user read-only access to all tables:
GRANT USAGE ON SCHEMA "public" TO fivetran; GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO fivetran; ALTER DEFAULT PRIVILEGES IN SCHEMA "public" GRANT SELECT ON TABLES TO fivetran;
The last command makes sure that any future tables will be accessible to fivetran. To grant access to schemas other
than the Postgres' default
public schema, simply replace
public with your other schema name. If you have several
schemas and you wish to grant access to all of them, you will need to repeat these three commands for each schema.
Restricted permissions (optional)
How to allow only a subset of tables
To grant access only to some tables in a schema, first make sure
fivetran has access to the schema itself:
GRANT USAGE ON SCHEMA "some_schema" TO fivetran;
Make sure you have removed any previously given permission to all tables in that schema.
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" REVOKE SELECT ON TABLES FROM fivetran; REVOKE SELECT ON ALL TABLES IN SCHEMA "some_schema" FROM fivetran;
Then repeat this command for each table you wish to include.
GRANT SELECT ON "some_schema"."some_table" TO fivetran;
Any tables created in the future will be excluded from access by default. To include them run,
ALTER DEFAULT PRIVILEGES IN SCHEMA "some_schema" GRANT SELECT ON TABLES TO fivetran;
To grant access to all tables except a few, unfortunately there is no way to do that directly in Postgres. You will just have to positively include all the tables you do want. Note that it is not possible to achieve exclusion by granting access to all tables and then revoking access for a subset of tables.
How to allow only a subset of columns
To grant access only to some columns in a table, first make sure you have removed any previously given permission to read all columns in the table:
REVOKE SELECT ON "some_schema"."some_table" FROM fivetran;
Then give permission to the specific columns, e.g.
GRANT SELECT (xmin, "some_column", "other_column") ON "some_schema"."some_table" TO fivetran;
Access to the hidden system column
xmin is required for incremental updates. Any new columns added to the table
in the future will not be accessible to
fivetran unless you rerun this command with the new column included.
To grant access to all columns except one, you would have to positively grant access to all the other columns.
Incremental update Configuration
Note - Logical Replication is not supported by Aurora PostgreSQL Database so we use XMIN system column for incremental updates. As a result, our Aurora Postgres connection does NOT support replicating deleted data.