For user access to Redshift, we are using federated security between ADFS and IAM. We are able to successfully connect and interact with Redshift via dBeaver and other tools using a JDBC connection string, as well as with Python using a user on the database - not a federated user. However psycopg2, pg8000, and sqlalchemy fail due to the additional connection options required for this model.
Our connection string:
jdbc:redshift:iam://{host}:{port}/{database}?plugin_name=com.amazon.redshift.plugin.AdfsCredentialsProvider&preferred_role=arn:aws:iam::{RoleID}:role/{Group}&idp_host=sts.{Domain}.com&idp_port=443&autocreate=true&user={Domain}\username&password={Password}
psycopg2, pg8000, and sqlalchemy all fail with some version of the below
invalid connection option "plugin_name"
or
Could not parse rfc1738 URL from string 'redshift+psycopg2:iam://
Presumably, because of the Redshift and AWS specific options and IAM protocol.
The only solution that works is using JayDeBeAPI, as it will take a raw JDBC connection string - no matter how specialized. This issue with this is that JayDeBeAPI is significantly slower than the other libraries - e.g. 26 seconds for a 70k row query vs 1.2 in psycopg2.
Is there any possible way to pass arbitrary connection options to psycopg2 or pg8000? Or any other Python libraries that will accomplish this?
