[Bug] Connection Pooling May be selecting the Wrong Connection Profile

[kumarshlok]

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

In order to have better fine-grained access control, we have created a new profile target in our profile.yml that has all values (like schema_name, user, password) different except for dbname with the other profile target. This is to restrict users to access tables in the new schema. In simpler terms, this new target is used to create models in a diff. schema with a diff. user (with required grants) in the same database.

During the execution of the task, I also see that the dbt run cmd has --target param appended and has the correct target name (from profiles.yml file)

Unfortunately when Airflow tasks execute the dbt-run cmd, they seem to select the wrong connection from the pool and selects the one that does not have the required permissions. This leads to the DAG failing. Has anyone faced this issue? Is there a way to restrict/specify in Airflow-dbt setup which Connection to select?

dbt-redshift version: 1.7.0
Airflow MWAA version: 2.4.3

I believe for every task execution in MWAA, all connections are re-opened. Hence why we see a connection pool. Is anyone familiar with the selection strategy of a connection from the pool?

Expected Behavior

Defining a new target should allow the task to select the connection profile that has the correct/required permissions granted to it.

Steps To Reproduce

1. Define a new profile target with all different values (schema name, user, password) except for host, and dbname. Pls not these values are passed as env_var and are set in the overridden pre-execute method.
2. Compile and push the changes to MWAA env.
3. Execute the DAG

Relevant log output

No response

Environment

- OS: MWAA env
- Python:3.8
- dbt-redshift: 1.7.0
- MWAA: 2.4.3

Additional Context

No response

[amychen1776]

@kumarshlok what is the command you're running in MWAA? Just to be clear - are you passing in the target name of the correct connection?