Newbie help - how to connect to AWS Redshift cluster (currently using Aginity)

Question

(I'm afraid I'm probably about to reveal myself as completely unfit for the task at hand!)

I'm trying to setup a Redshift cluster and database to help manage data for a class/group project. I have a dc2.large cluster running with either default options, or what looked like the most generic in the couple of place I was forced to make entries. I have downloaded Aginity (Win64) as it is described as being specialized for Redshift. That said, I can't find any instructions for connecting using it. The connection dialog requests the follwoing:

Server: using the endpoint for my cluster (less :57xx at the end).
UserID:  the Master username for the database defined for the cluster.
Password: to match the UserID
SSL Mode (Disable, Allow, Prefer, Require): trying various options
Database: as named in cluster setup
Port: as defined in cluster setup

I can't get it to connect ("failed to establish connection") and don't know if I'm entering something wrong in Aginity or if I haven't set up my cluster properly.

Message: Failed to establish a connection to 'abc1234-smtm.crone7m2jcwv.us-east-1.redshift.amazonaws.com'.
Type   : Npgsql.NpgsqlException
Source : Npgsql
Trace  :    at Npgsql.NpgsqlClosedState.Open(NpgsqlConnector context, Int32 timeout)
   at Npgsql.NpgsqlConnector.Open()
   at Npgsql.NpgsqlConnection.Open()
   at Aginity.MPP.Common.BaseDataProvider.get_Connection()
   at Aginity.MPP.Common.BaseDataProvider.CreateCommand(String commandText, CommandType commandType, IDataParameter[] commandParams)
   at Aginity.MPP.Common.BaseDataProvider.ExecuteReader(String commandText, CommandType commandType, IDataParameter[] commandParams)

--- Inner Exception: ---

......

It seems there is not enough information going into Aginity to authorize connection to my cluster - no account credential are supplied. For UserID, am I meant to enter the ID of a valid user? Can I use the root account? What would the ID look like? I have setup a User with FullAccess to S3 and Redshift, then entered the UserID in this format arn:aws:iam::600123456789:user/john along with the matching password, but that hasn't worked either.

The only training/tutorial I have been able to find/do on this is the Intro AWS direct you to, at https://qwiklabs.com/focuses/2366, which uses a web-based client that I can't find outside of the tutorial (pgweb).

Any advice what I am doing wrong, and how to do it right?

did you add your ip to the security group for redshift (port 5439) in the aws console? — Jon Scott
I can now connect and create/drop tables, but I think I need to have a "role" to copy data from my S3 - I'm having trouble figuring out how to do that, if anyone had any advice, please share! — James
follow steps 1 and 2 of instructions here but use awss3fullaccess instead of readonlyaccess docs.aws.amazon.com/redshift/latest/dg/… — Jon Scott

James James · Accepted Answer · 2018-03-22T23:08:44

Well, I think I got it working - I haven't had a chance to see if I can actually create table yet, but it seems to be connected. I had to allow inbound traffic from outside the VPC, as per the above snapshot.

I'm guessing there's a better way than opening it up to all IP addresses, but I don't know the users' (fellow team members) IPs, and aren't they all subject to change depending on the device they're using to connect?

How does one go about getting inside the VPC to connect that way, presumably more securely?

Newbie help - how to connect to AWS Redshift cluster (currently using Aginity)

1 Answers