TLS defaults to 'verify-full' and so rejects tunneled hostnames

[whitish]

I m using tunneling for my EC2 RedShift DB to my localhost e.g.: ssh -L 5439:<my_database_host>.redshift.amazonaws.com:5439 username@tunnel_host

So now I can access my DB from my sql client using as it is deployed on my localhost, e.g.: jdbc:redshift://localhost:5439/mydb

But when I m trying to access it from python code:

engine = create_engine("redshift+psycopg2://dbuser:pass@127.0.0.1:5439/mydb")
res = conn.execute("select count(1) from myschema.mytable").fetchall()

I receive error next error:

...
...
File "/home/aleksei/projects/venv/lib/python3.4/site-packages/psycopg2/__init__.py", line 164, in connect
    conn = _connect(dsn, connection_factory=connection_factory, async=async)
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) server common name "<my_database_host>.redshift.amazonaws.com" does not match host name "127.0.0.1"

Will be appreciate for any help on this.

[graingert]

try adding:

127.0.0.1 <my_database_host>.redshift.amazonaws.com

to /etc/hosts

then connect to:

create_engine("redshift+psycopg2://dbuser:pass@<my_database_host>.redshift.amazonaws.com:5439/mydb")

[graingert]

The reason for this error is because I enable TLS by default using the bundled RDS certificate. You can manually override this if you wish, but you can be MITMd and someone can use that to steal your password and data

[whitish]

Thank you, @graingert , works good now.

[daveauerbach]

Can you give details about where the TLS overrides be set?

[daveauerbach]

In case anyone else finds this issue, the solution to over-ride involved passing sslmode connection arguments to the create_engine command e.g.: sqlalchemy.create_engine(url, connect_args={'sslmode': 'prefer'}) Prefer appears to over-ride the strict checking of SSL certs that is build into redshift_sqlalchemy