PostgreSQL

​

Step 1: Create a read replica (optional)

• AWS RDS
• AWS Aurora

1

Create the read replica

In your Amazon RDS dashboard, click the PostgreSQL instance you want to replicate. On the database page, click Actions, then select Create read replica from the drop-down.

2

Choose an instance size

In the Instance specifications section, specify the instance type for the read replica. It can be smaller than the main instance.

3

Make the replica reachable

In the Network & Security section, under Publicly accessible, select Yes so that the read replica is reachable from outside your VPC. It remains accessible only through whitelisted IPs. If you plan to connect through an SSH tunnel, you can leave this set to No.

4

Name and create the replica

In the Settings section, enter a DB instance identifier, such as source-read-replica, then click Create read replica.

1

Add a reader

In your Amazon RDS dashboard, click the Aurora PostgreSQL instance you want to add a reader to. On the database page, click Actions, then select Add reader from the drop-down.

2

Name and size the reader

In the Settings section, enter a DB instance identifier, such as source-reader. In the DB instance class section, specify the instance type. It can be smaller than the main instance, though AWS may set a lower bound.

3

Make the reader reachable

In the Connectivity section, select Publicly accessible so that the reader is reachable from outside your VPC. It remains accessible only through whitelisted IPs. If you plan to connect through an SSH tunnel, you can leave this set to Not publicly accessible. Click Add reader.

​

Step 2: Allow network access

• AWS RDS or Aurora
• Other environments

​

Configure the security group

1

Note the endpoint and port

In your Amazon RDS > Databases list, click the PostgreSQL instance you want to connect. In the Connectivity & security tab, make a note of the Endpoint and the Port number.

2

Open the VPC security group

Click one of the VPC security groups (usually default). VPC groups are permissive rather than restrictive, so for instances with multiple security groups, only one needs the new inbound rule.

3

Add an inbound rule

Select the Inbound rules tab, click Edit inbound rules, then click Add rule. Set the rule type to Custom TCP, set the Port range to the port you noted (usually 5432), and set a Custom Source value that includes the static IP. You need to add /32 to the end of the IP to express it in CIDR notation. Click Save rules.

​

Configure the network ACL

For database instances in a VPC, you also need to allow traffic at the network ACL level.

1

Open the VPC

In your RDS dashboard, select the PostgreSQL instance, then click the link to the instance’s VPC.

2

Open the main network ACL

Click the VPC ID. In the Details section, click the link under Main network ACL, then click the network ACL ID.

3

Edit the inbound rules

Click the Inbound rules tab and check for an existing rule with a Source of 0.0.0.0/0 set to Allow. This is a default rule created by AWS, and if it already exists, you can skip to the outbound rules. Otherwise, click Edit inbound rules, add a rule allowing access to your database port (usually 5432) from the static IP, and click Save changes.

4

Edit the outbound rules

Select the Outbound rules tab and check for an existing rule with a Destination of 0.0.0.0/0 set to Allow. This is a default rule created by AWS, and if it already exists, you are done. Otherwise, click Edit outbound rules and add a rule allowing outbound traffic to ports 1024 to 65535 for Destination 0.0.0.0/0.

In your firewall or security group, create a rule that allows:

• Incoming connections to your host and port (usually 5432) from the static IP.
• Outgoing connections from ports 1024 to 65535 to the static IP.

If your database is not reachable over the public internet, you can connect through an SSH tunnel instead. Provide the bastion host details in Step 4, and add the public key from your account representative to the bastion’s authorized_keys file.

​

Step 3: Create a read-only user

CREATE USER <username> PASSWORD '<password>' NOSUPERUSER NOCREATEDB NOCREATEROLE;

GRANT USAGE ON SCHEMA "<schema_name>" TO <username>;
GRANT SELECT ON "<schema_name>"."<table_name_a>" TO <username>;
GRANT SELECT ON "<schema_name>"."<table_name_b>" TO <username>;

GRANT USAGE ON SCHEMA "<schema_name>" TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA "<schema_name>" TO <username>;

​

Step 4: Submit your connection details

1. The name is a descriptive name of the source.
2. The host (for example, your-db.sd8jekhrlkhla.us-east-1.rds.amazonaws.com).
3. The port (most likely 5432).
4. The database you want to read from.
5. The schema from Step 3.
6. The username from Step 3.
7. The password from Step 3.