[ADAP-1064] [Bug] AWS IAM Assumed Role Authentication Issue in dbt-redshift with Serverless Redshift

[nbphuoc]

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

When attempting to use an AWS IAM Assumed Role for authentication in dbt-redshift, the process fails. This issue does not occur when using AWS IAM User credentials. The expected behavior is for dbt-redshift to successfully authenticate using both IAM User and Assumed Role credentials. The failure with Assumed Role presents an obstacle in environments where Assumed Roles are a mandatory security practice.

Expected Behavior

Expected dbt-redshift to authenticate seamlessly with AWS Redshift using IAM Assumed Role credentials, similar to its functioning with IAM User credentials

Steps To Reproduce

Steps To Reproduce

1. AWS Configuration:

   • Set up the AWS configuration file (~/.aws/config):

     [profile myprofile]
     region = us-west-2
     role_arn = arn:aws:iam::123456789012:role/myredshiftrole
     source_profile = mysourceprofile
     

2. dbt Profile Setup:

   • Configure the dbt profile in ~/.dbt/profiles.yml:

     dbt_project:
       outputs:
         dev:
           host: "serverless-redshift-cluster-host"
           user: "db_user"
           dbname: "db_name"
           port: 5439
           schema: public
           threads: 1
           type: redshift
           method: iam
       target: dev

3. Executing dbt Command:

   • Run the command: dbt build, targeting a model that connects to a serverless Redshift.

It is important to note that this issue specifically occurs with serverless Redshift.

Relevant log output

15:40:04  Running with dbt=1.7.3
15:40:04  Registered adapter: redshift=1.7.0
15:40:04  Unable to do partial parsing because env vars used in profiles.yml have changed
15:40:05  Found 1 model, 1 source, 0 exposures, 0 metrics, 455 macros, 0 groups, 0 semantic models
15:40:05  
15:40:17  Concurrency: 1 threads (target='dev')
15:40:17  
15:40:17  1 of 1 START sql table model public.last_100_users ............................. [RUN]
15:40:19  1 of 1 ERROR creating sql table model public.last_100_users .................... [ERROR in 2.91s]
15:40:22  
15:40:22  Finished running 1 table model in 0 hours 0 minutes and 16.74 seconds (16.74s).
15:40:22  
15:40:22  Completed with 1 error and 0 warnings:
15:40:22  
15:40:22    Database Error in model model (models/staging/model.sql)
  permission denied for relation raw_model
  compiled Code at target/run/dbt_transformation/models/staging/model.sql

Environment

- OS:Mac OS 14.1.2
- Python: 3.9.16
- dbt-core: 1.7.3
- dbt-redshift: 1.7.0

Additional Context

No response

[sgahr]

Are there any news so far?
I am facing exactly the same problem. It would be great if someone could fix it otherwise I can not use it due to mandatory security requirements that require getting user credentials via the assumed role.

[Fleid]

Moving the ticket up the queue!

[dataders]

@nbphuoc @sgahr sorry that you're not getting what you want from the new IAM auth options. To clarify, does assumed role auth work today for non-serverless clusters?

@jiezhen-chen pointed us to aws/amazon-redshift-python-driver#192 in which someone was able to get assumed role working by calling sts_client.assume_role() before connecting. Ostensibly, something like this would need to happen within dbt-redshift to officially support assumed role auth. For that reason, we've changed this issue a "bug" to an "enhancement".

I'm also going to add the "help wanted" label, because while we'd love to support this feature, we don't currently have the bandwidth to address this.

[timesking]

when you run select * from pg_user;, you should see some user name like IAMR:myredshiftrole.
Then you should do GRANT permission to user IAMR:myredshiftrole.

The error you preset here like 'permission denied for relation raw_model'. It just saying your user in redshift no permission to that table.

[Fleid]

This was added via #781
Can be tested locally via install from source, should land in 1.9