Amazon Redshift Connection Error will prevent us from connecting to the Amazon Redshift cluster.

Here, at Bobcares, we assist our customers with several AWS queries as part of our AWS Support Services.

Today, let us see how we can fix this error.

Amazon Redshift Connection Error

In this article, let us see how our Support Techs troubleshoot the connections errors in Amazon Redshift.

Recently resized or restored from a snapshot

Suppose we resize or restore the Amazon Redshift cluster from a snapshot. Then we need to check the cluster’s subnet.

The cluster must launch in the same subnet from before the snapshot resize or restore.

On the other hand, if we didn’t resize or restore we need to verify the network configurations.

It must be the same for both the old and new subnets.

Resides in a public subnet

Suppose we try to connect to an Amazon Redshift cluster in a public subnet, we need to check:

• If the Amazon Redshift cluster is set to “Publicly Accessible.”
• If we have an internet gateway attached to the route table.

We can use telnet to test connectivity to the Amazon Redshift cluster:

telnet <cluster endpoint> <cluster port>

Resides in a private subnet

If the Amazon Redshift cluster resides in a private subnet, we need to:

• Attach the NAT Gateway to the public subnet’s route table.
• Make sure that your cluster isn’t set to “Publicly Accessible”.

In order to confirm that the  client can reach the private IP address of the Amazon Redshift cluster’s leader node, we use the dig command:

dig <cluster endpoint>

To test the connection to the Amazon Redshift cluster, we use the telnet command:

telnet <cluster endpoint><cluster port>

Telnet is unsuccessful or the cluster remains inaccessible

However, if the telnet indicates “unsuccessful”, we verify that the following conditions are true:

• The Amazon Redshift port (default 5439) of type TCP is allowed in the Security Group’s inbound rule.
• The CIDR range or IP we connect to the Amazon Redshift cluster from is added in the Security Group’s ingress rule.

On the other hand, if it indicates “successful” but the cluster remains inaccessible from the client, we need to check the network’s firewall.

In this case, the firewall might be blocking the Amazon Redshift port.

Invalid operation connection error

Generally, if the Amazon Redshift cluster requires to enable SSL for any connection, we may receive the following error:

Error: [Amazon](500310) Invalid operation: no pg_hba.conf entry for host "::ffff:205.xxx.xxx.xxx", user "username", database "dbname", SSL off;

We need to confirm whether the require_ssl parameter is set to “true”.

1. To do so, we open the Amazon Redshift console.

2. Then we select the Config tab.

3. Here, we modify the parameter group that associates with the Amazon Redshift cluster.

4. If necessary, we verify the SSL configuration using the AWS Command Line Interface (AWS CLI):

describe-cluster-parameters

Once done, if the configuration is set to “true”, our Support Techs recommend updating the require_ssl parameter back to “false”.

Fatal connection error

In case we have too many open connections in the Amazon Redshift cluster we receive the error:

Error: FATAL: connection limit [500]/[2000] exceeded for user

In order to find the number of open connections our Support Techs recommend the steps below:

1. Initially, we open the Amazon CloudWatch console.

2. Then we look for the DatabaseConnections metric in the Amazon CloudWatch Logs entries.

3. Here, we use the STL_CONNECTION_LOG table to check the open connections:

select recordtime, username, dbname, remotehost, remoteport
from stl_connection_log
where event = 'initiating session'
and pid not in
(select pid from stl_connection_log
where event = 'disconnecting session')
order by 1 desc;

4. After that, we use the STV_SESSIONS table to view information about the active user sessions for Amazon Redshift:

select * from stv_sessions;

Suppose we have a large number of open connections. In such a case, we use the PG_TERMINATE_BACKEND command to shut down any idle sessions.

Connection refused error

If the Amazon Redshift cluster fails to establish a connection, it is likely to show the error:

Error: amazon 500150 error setting/closing connection: operation timed out/ connection refused.

This indicates a permissions issue with accessing the Amazon Redshift cluster.

To resolve this, we do the following:

• For cluster in an Amazon VPC, we add the client CIDR/IP address to the VPC security group.
• For clusters outside of a VPC, we add the client CIDR/IP address to the cluster security group.

[Stuck with any of the above errors? We are here to assist you]

Conclusion

In short, we saw how our Support Techs go about Amazon Redshift Connection Error.