1

Background

I have a Spectrum schema referencing a Glue Data Catalog (my_spectrum_schema). The external schema was created with an IAM role (s3_glue_role) with AWSGlueServiceRole and AmazonS3ReadOnlyAccess and a trust relationship like this:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "ForAnyValue:StringEquals": {
          "sts:ExternalId": [
            "arn:aws:redshift:<region>:<acct>:dbuser:<cluster>/<admin_user>"
          ]
        }
      }
    }
  ]
}

The role is attached to the cluster, and admin_user is able to query tables in my_spectrum_schema both from the AWS Console and also from a local SQL client.

Issue

I want to allow members of an existing Redshift group to query tables in the schema.

Based on syntax I found here in the AWS docs, I expected that adding the group data_group to the trust policy would allow members of the group to query the data:

          "sts:ExternalId": [
            "arn:aws:redshift:<region>:<acct>:dbuser:<cluster>/<admin_user>",
            "arn:aws:redshift:<region>:<acct>:dbgroup:<cluster>/<data_group>"
          ]

However, queries under any user in data_group throw this error:

Query 1 ERROR: ERROR:  
  -----------------------------------------------
  error:     Not authorized to get credentials of role arn:aws:iam::<acct>:role/<s3_glue_role>
  code:      30000
  context:   
  query:     0
  location:  xen_aws_credentials_mgr.cpp:402
  process:   padbmaster [pid=6826]
  -----------------------------------------------

Attempted resolutions

I tried dropping and re-creating the schema after the trust policy had been updated: no effect.

I tried changing dbgroup to dbuser for data_group: (predictably) didn't work.

Searched for similar SO issues. Allow AWS Redshift Cluster DB user group to assume role is related but doesn't have any answers.

I added a user from data_group explicitly to the trust relationship, which resolved the error and allows the user to query tables in the schema:

          "sts:ExternalId": [
            "arn:aws:redshift:<region>:<acct>:dbuser:<cluster>/<admin_user>",
            "arn:aws:redshift:<region>:<acct>:dbgroup:<cluster>/<data_group>",
            "arn:aws:redshift:<region>:<acct>:dbuser:<cluster>/<single_user>"

The workaround isn't ideal because it requires me to manage the data_group and the trust policy separately. When users and added or removed from the group, the trust policy should reflect that.

Question

Is there a different way to specify a Redshift group in the trust policy that will behave the way I'm expecting, or is there another approach altogether to accomplish what I'm trying to do that doesn't feel like an anti-pattern?

Joseph H
  • 33
  • 5

0 Answers0