AWS: Unable to connect to Redshift

[dhackney]

This is a new instance of AWS JRS.

The JRS instance was created via the EC2 console due to VPC

The JRS instance can connect to an RDS MySQL instance in this VPC with no problems.

The JRS instance is unable to connect to a Redshift instance in this VPC.

The JRS data source connection test errors out.

The summary error is: 

Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections. There may be a problem with the JDBC URL, AWS Security Settings or the AWS Data Source recovery process, which can take up to 2 minutes.

***

Verbose version of the error is attached.

 

Environment: 

VPC

JRS and Redshift are in the same subnet

JRS and Redshift are in seperate security groups

JRS security group and Redshift security group are set to allow "all traffic" from the other security group

The JRS instance was created with the following role:

{"Statement":

[

{"Resource":"*",

"Action":

["rds:Describe*",

"rds:AuthorizeDBSecurityGroupIngress",

"rds:CreateDBSecurityGroup",

"rds:RevokeDBSecurityGroupIngress",

"rds:ModifyDBInstance",

"redshift:Describe*",

"redshift:AuthorizeClusterSecurityGroupIngress",

"redshift:CreateClusterSecurityGroup",

"redshift:RevokeClusterSecurityGroupIngress",

"redshift:ModifyCluster",

"ec2:DescribeSecurityGroups",

"ec2:AuthorizeSecurityGroupIngress",

"ec2:CreateSecurityGroup",

"ec2:RevokeSecurityGroupIngress],

"Effect":"Allow"

}

]
}

The JRS instance has a public DNS

The JRS instance security group includes:

Inbound: 

80 0.0.0.0/0

22 0.0.0.0/0

All (Redshift security group)

 

Outbound:

All 0.0.0.0/0

All (Redshift security group)

 

The Redshift security group includes:

Inbound:

5439 (JRS security group)

 

Outbound:

5439 (JRS security group)

 

 

The Redshift instance is also assigned to the security group creatd by the JRS test proceedure. That security group includes:

 

Inbound: 

0 - 65535 (JRS IP address/32)

 

Outbound:

All 0.0.0.0/0

 

 

The connection was also tested and failed with user credentials (access key and secret key) with a policy of: 

{"Statement":[{"Resource":["*"],"Action":["rds:Describe*","rds:AuthorizeDBSecurityGroupIngress","rds:CreateDBSecurityGroup","rds:RevokeDBSecurityGroupIngress","rds:ModifyDBInstance","redshift:Describe*","redshift:CreateClusterSecurityGroup","redshift:RevokeClusterSecurityGroupIngress","redshift:AuthorizeClusterSecurityGroupIngress","redshift:ModifyCluster","ec2:DescribeSecurityGroups","ec2:RevokeSecurityGroupIngress","ec2:CreateSecurityGroup","ec2:AuthorizeSecurityGroupIngress"],"Effect":"Allow"}]}

 

***********************

Any input or suggestions would be greatly appreciated. 

 

Thanks

 

 

 

[dhackney]

 

I should add that the data source URL was tried with both the auto-provided by JRS URL and manually copy-and-pasted from the Redshift instance info page.

 

[abroitman]

Hi, 

We have escalated your issue to our technical team and will be responsing as soon as possible. We apologize for the issue you're experiencing.

[dhackney]

Thank you for the update and the escalation. 

 

Additional information: 

I have tested the Redshift user ID and password and it is valid and can connect to Redshift.

 

This is an installation of the AWS Marketplace Jaspersoft product.

AMI: Jaspersoft BI Professional for AWS v5.1-855efcad-bd21-459e-8a9b-6d0068a0b46c-ami-2fa4c946.1 (ami-333a4e5a)

EC2 instance IAM role: JaspersoftReportServerRole-06-JRSInstanceProfile-xxxxxxxxxxxxx

( this role was created for this instance as per the instructions in "Jaspersoft Professional For AWS Guide Release 5.0.3")

 

Both Redshift and the JSR EC2 instance are in the same subnet: "public-dw-redshift" 10.0.6.0

Redshift and the JSR EC2 instance are each in their own Security Group: 

Redshift: "public-dw-redshift"

JSR: "public_jaspersoft_report_server"    

Note that the JSR "public_jaspersoft_report_server" security group  is seperate from the Security Group automatically created during the installation. 

The JSR auto-created security group is: JRSSecurityGroup_i-XXXXXXXX-XX    

Both the Redshft and the JSR security groups have "allow all traffic" inbound and outbound rules to each other.  

 

The subnet "public-dw-redshift" 10.0.6.0 has its own routing table that routes external traffic directly to the internet gateway: 

10.0.0.0/16   local

0.0.0.0/0      igw-XXXXXXXX

 

 

 

 

 

[ogavavka]

Hi

 

let me clarify little bit parts which are involved in AWS Datasource setup

 

there are 3 parts:

1 - AWS RDS discovery (Add functionality to discover RDS Databases, endpoints, main databases, master user names )

2 - AWS RDS Security Setup/Recovery (Add fuctionality to automaticaly setup AWS Security layer or in case of instance IP change - recovery it)

3 - DataSource Connection setup (JDBC URL) (use AWS RDS discovery to generate JDBC URL)

 

ROLE or AWS Keys are used only for Discovery and Automatic Security setuprecovery

and they can be disabled or manualy controlled in ManageServer SettingsAWS Settings

 

i see in your setup you was trying to setup security manualy and automaticaly by JRS.

 

I look at you log and main cause is : Caused by: org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

 

so JRS cannot connect to RedShift endpoint - connection refused

 

possible causes:

1 - Error in AWS VPC Security Layer setup

2 - JRS instance use incorrect instance IP Address to setup security automaticaly

3 - JRS cannot resolve RedShift Endpoint (or resolving it incorrectly)

4 - RedShift instance is dead or malfunction

 

how to detect problem:

you must login to JRS instance using ssh.

 

easiest way to check if you can connect to your database from JRS instance is to use "telnet"

by default telnet is not present in AWS JRS instance to install it excute "sudo yum -y install telnet"

execute command "telnet database_dns_name port_number", if you get black screen in responce - connection was successfull

so if this test is successfull - JRS must connect to database

 

if not then try to check every possible cause

cause 1 - only manualy

cause 2 - you can check Security group which was created by JRS - it should contain in your case JRS internal VPC ip address

cause 3 - try to execute command "nslookup database_dns_name" - it should be resolved to Database internal VPC address

cause 4 - check connection from other tool, client, etc ...

 

BTW you was mentioning your JRS use public DNS

that could cause problems because you instances inside of VPC 

usualy instance in VPC have 1 internal IP 

or 2 IP addresses - one for ussage in VPC another for ussage outside of VPC

and if you use AWS DNS service - endpoint will be determined correctly 

inside of vpc it will resolve to internal VPC address, 

outside of VPC to Public IP address - if it`s assigned to instance,

but if you use public DNS service then endpoint will always resolve as Public IP if instance have one

if instance inside VPC don`t have Public IP then Public DNS will not resolve endpoint, or resolve it incorrectly.

to workaround this problem try substitute database_dns_name with internal database IP in VPC.

 

i hope this instruction will help you - if not, send more details about:

VPC setup, ElasticIPs ussage, VPC Network configuration, internal IP`s, resolved database endpoint etc

but keep in mind this conversation is publicaly available - so no sensitive data :-)

[ogavavka]

didn`t see extra details - was writing answer

[dhackney]

 

[RESOLVED]

Thank you for the detailed answer and suggestions. 

The background information on the discovery/security/connection and the corresponding use of the roles and keys was helpful to understand the overall architecture. 

 

 

In this particular case, the solution was in the Redshift security group: 

Security Group: 

Redshift: "public-dw-redshift"

 

Added: 

Allow

Port: ZZZZ

Inbound: XX.XX.XX.XX/32

Port: ZZZZ

Outbound: XX.XX.XX.XX/32

Where

ZZZZ = Redshift port

XX.XX.XX.XX = JRS EC2 instance public IP address

 

 

RE: Public DNS

Sorry for the confusion.

That is the EC2 "Step 3: Configure Instance Details" launch option: "Public IP  - Automatically assign a public IP address to your instances"

In this case, it appears that if you launch the JRS EC2 instance with that option you must explicitly enable traffic to and from that public IP address with the Redshift port in the Redshift security group.

 

 

 

[ogavavka]

Something is wrong with your setup !

i just test your setup (With Public IP, in same VPC)- JRS successfully configure Security automaticaly and connects to Redshift using VPC internal IPs.

This is prefered setup.

If you will be using Public IP to communicate with Redshift - you will be charged extra by AWS for traffic leaving EC2 using Public IP.

[ogavavka]

Why you use AMI from 5.1 Version ??

5.2 is available on marketplace from 07/30/2013

and in near feature 5.5 will be released