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?