[CT-1504] [Feature] Replace psycopg2 with redshift_connector

[dbeatty10]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-redshift functionality, rather than a Big Idea better suited to a discussion

Describe the feature

Currently, dbt-redshift inherits the DB API 2.0 Python connector from dbt-postgres (which defaults to psycopg2-binary).

redshift_connector is the Amazon Redshift connector for Python that implements Python Database API Specification 2.0 (PEP 249).

Implementing redshift_connector within dbt-redshift may unlock or ease other open issues like:

• [CT-1484] [Feature] SSO for Redshift #968
• [CT-1340] [Feature] Support Python models (dbt-py) on Redshift/AWS #204

Describe alternatives you've considered

Leaving the psycopg2 is an option, but it might not support Redshift-specific functionality like mentioned above.

Who will this benefit?

No response

Are you interested in contributing this feature?

No response

Anything else?

This was discussed in a video call with @jtcohen6 and @Fleid earlier today, and it was affirmed that it might be time to take on this implementation.

[iklose]

This could also solve problems regarding Redshift Serverless connections with IAM authentication: #138