sslmode does not support allow, prefer, require, disable

[jiezhen-chen]

Driver version

2.0.910

Redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.49780

Client Operating System

macos monterey 12.6.2

Python version

3.11

Table schema

Does not apply

Problem description

1. Expected behaviour: In postgreSQL, these 5 parameters are allowed values for sslmode. However, redshift_connector only allows for verify-ca, and verify-full for this parameter. Redshift_connector also has ssl as a parameter.

2. Actual behaviour: There are a few problems with this difference between postgreSQL and redshift_connector: a. To disable ssl, users using redshift_connector has to set ssl = False. Simply setting sslmode = disable will not set ssl to false. Since disable is not a recognizable value of sslmode in redshift_connector, redshift_connector will use the default of 'verify-ca' to make the connection. b. According to the PostgreSQL doc, the accepted values of sslmode behave as below:

disable only try a non-SSL connection

allow first try a non-SSL connection; if that fails, try an SSL connection

prefer (default) first try an SSL connection; if that fails, try a non-SSL connection

require only try an SSL connection. If a root CA file is present, verify the certificate in the same way as if verify-ca was specified

verify-ca only try an SSL connection, and verify that the server certificate is issued by a trusted certificate authority (CA)

verify-full only try an SSL connection, verify that the server certificate is issued by a trusted CA and that the requested server host name matches that in the certificate

Redshift_connector should also increase the values accepted by sslmode to align with PostgreSQL docs

After some investigation, here is a detailed table on the behavior of sslmode of redshift_connector and psycopg2:

sslmode	behavior in redshift connector (ssl, sslmode)	behavior in psycopg2 connector (sslmode)
disable	ssl=defaulted to true, sslmode=verify-ca (sslmode of disable is not recognized by redshift_connector, therefore falling back to default of verify-ca)	sslmode=disable
allow	ssl=defaulted to true, sslmode=verify-ca	first try with sslmode=disable, if fails, try with sslmode=verify-ca
prefer	ssl=defaulted to true, sslmode=verify-ca	first try with sslmode=verify-ca, if fails, try with sslmode=disable
require	ssl=defaulted to true, sslmode=verify-ca	ssl=true, sslmode=verify-ca
verify-ca	ssl=defaulted to true, sslmode=verify-ca	ssl=true, sslmode=verify-ca
verify-full	ssl=defaulted to true, sslmode=verify-full	ssl=true, sslmode=verify-full

[Brooke-white]

Hi Jessie,

thanks for opening this issue. we are in discussions with the redshift driver team around next steps in addressing this. once we have determined next steps, I will provide an update here.