How do I connect with my redshift database?

Question

I am setting up a redshift database on AWS and I've followed the instructions on this article - https://chartio.com/resources/tutorials/connecting-to-a-database-within-an-amazon-vpc/

I am unable to connect to the database.

Here's my setup -

I have a PostgreSQL database instance that I spun up with Amazon RDS. That is connected to an Amazon VPC with two subnets.

Subnet A is set in us-east-2c. It is associated with a Route Table that has two routes. The first has destination 10.0.0.0/16, target 'local', status 'active' and propogated 'no'. The second has destination 0.0.0.0/0 and is targeted to an Internet Gateway associated with the VPC.

Subnet B is set in us-east-2b. It has destination 10.0.0.0/16 and target 'local'.

The PostgreSQL db is associated with a Security Group with this inbound rule: Type: Custom TCP Rule, Protocol: TCP, Port Range: 5432 and Source: 10.0.0.0/32. There are no outbound rules.

Other details on the database:

-Publicly Accessible is set to No

-It is running in us-east-2b

Additionally, there is an instance on EC2. It is on us-east-2c.

It is associated with a Security Group with these inbound rules:

First- Type: Custom TCP Rule, Protocol: TCP, Port Range: 5432, Source: 10.0.0.0/32

Second- Type: SSH, Protocol: TCP, Port Range: 22, Source: (my-ip-address)/32

Third- Type: SSH, Protocol: TCP, Port Range: 22, Source: (group id for the security group)

Both of the Security Groups are associated with the same VPC that has the following settings: IPv4 CIDR: 10.0.0.0/16, IPv6 CIDR: (blank).

My understanding of the set up is that the EC2 instance is public and I can SSH into that from my SQL client (Postico). And then, the EC2 instance will connect privately to the Redshift Database.

Here's my problem-

a) I've never set this up before and I may have done something completely wrong without knowing it.

b) I am attempting to create an SSH connection from Postico. I do not know what value to fill in for 'Host' or 'Port'. Additionally, I do not know whether 'User' and 'Password' refer to the user and password for the account on my computer or whether it refers to something else altogether.

My goal is simply to be able to have a PostgreSQL database that is unavailable to the public, but allows me to access it from my SQL client (Postico).

I've attempted to research this problem, but there is a surprising lack of content that I was able to find to address these needs. I'm new to this, so if I'm missing required pieces to post this or if I've messed up in some way, please alert me and I will update accordingly.

Answer 1

Your inbound security group has "Source: 10.0.0.0/32" This means only 10.0.0.0 can connect to it, which is an invalid host address. Change the /32 to match your network (/16).

Redshift's port is usually 5439. You are referencing 5432.

I don't understand your "b" question. What are you trying to connect to?

[Update with new information]

I just realized an issue with what you are trying to do.

Your goal is to connect to EC2 from your desktop using SSH and then connect to RDS. This won’t work.

The solution is to setup a VPN such as OpenVPN that allows you to connect to your VPC in AWS and then OpenVPN will forward your client requests to RDS (VPN routing).

What I do is setup an EC2 instance using OpenVPN. I then turn on and off this instance when I need VPN access into AWS. I have batch scripts that do this from my desktop (start and stop an EC2 instance).

The other choice is to allow Internet access to RDS. You can use Security Groups to lock down Internet access to only your home/work IP address. Depending on your Internet provider your IP may change which means updating your security group with the new IP address, but this is simple to do.

This page will show you your public IP address that is put into the Security Group: What is my IP