Cannot connect to Redshift due to hostname error

[petroswork]

(psycopg2.OperationalError) server certificate for "ec2-[IP address].compute-1.amazonaws.com" does not match host name "[Hostname].redshift.amazonaws.com"

The same connection URI was fine until last Friday. I've checked that both hostnames correspond to the same IP address. If I set 'sslmode': 'prefer' in dialect.py, the connection is successful.

According to https://docs.aws.amazon.com/redshift/latest/mgmt/connecting-ssl-support.html,

SSL support in Amazon Redshift is strictly for encrypting the connection between your client and your cluster; it should not be relied on for authenticating the server. To authenticate the server, install the public key (.pem file) for the SSL certificate on your client and use the key to connect to your clusters.

Perhaps sslmode: require should be the default?

[graingert]

it seems like you're trying to connect to an ec2 instance rather than your redshift host.

[graingert]

can you paste your connection configuration

[petroswork]

Which details do you need? Also sslmode: require works fine.

[graingert]

The stuff you set here:

>>> import sqlalchemy as sa
>>> sa.create_engine('redshift+psycopg2://username@host.amazonaws.com:5439/database')
Engine(redshift+psycopg2://username@host.amazonaws.com:5439/database)

redact your password though.

[petroswork]

redshift+psycopg2://petroswork:[password]@[hostname].redshift.amazonaws.com:5439/dev

[graingert]

@petroswork I'll need the hostname

[petroswork]

I'd rather not disclose it to the public. Is it really necessary in this case?

[graingert]

You can email me direct at sqlalchemy-redshift@graingert.co.uk

[gonzalodiaz]

+1 to this issue... I have the same problem. And I can't disclosure the server URL.

[graingert]

This is looking like a configuration problem on Amazon's end... Or you're
all getting MITMd!

On 18 Nov 2016 18:19, "Gonzalo Diaz" notifications@github.com wrote:

+1 to this issue... I have the same problem. And I can't disclosure the
server URL.

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#106 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAZQTPehL0kVupvgFMlr_IPdeGj2MMsNks5q_ewzgaJpZM4KxUXF
.

[gonzalodiaz]

If I try the following connection I have no problem to obtain results.:

self.rs_conn = psycopg2.connect(connection_string)
self.cursor = self.rs_conn.cursor()

To elaborate more my scenario, I'm creating an ssh tunnel to Redshift. Because we have an authentication server in premises.
Note that using psycopg2 I can connect with Redshift using the tunnel. While with sqlalchemy I'm not able to do it.

[graingert]

@gonzalodiaz you've got a different problem. (you've got a self inflicted MITM).

You should point the DNS of your redshift cluster (xyz.redshift.amazonaws.com) at your local SSH tunnel using /etc/hosts. Or you can download the specific certificate for your redshift node, verify the domain manually then and manually configure your TLS settings as described by SQLalchemy

[graingert]

Psychopg by default does not do TLS. This is why it works out of the box

[stephenpaulger]

I think I am having the same issue. I have two redshift databases that apart from running in different accounts are set up identically.

One connects fine without having to specify connect_args={'sslmode': 'prefer'} when calling create_engine. The other will fail with the message...

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server certificate for "IP ADDRESS" does not match host name "HOST.redshift.amazonaws.com"

but when I specify connect_args={'sslmode': 'prefer'} it connects.

[graingert]

@stephenpaulger are you using an explicit IP address to connect? {'sslmode': 'prefer'} is not safe.