Configuring Amazon Redshift with IAM Authentication

This article demonstrates how AWS-based customers can set up their worker node configuration so that the worker node can access Redshift databases using IAM-based authentication.

AWS Configuration

Cluster ARN

  1. In the Amazon Redshift console, navigate to Clusters to select your desired cluster.

  2. The details page for the given cluster will contain all the necessary data to form the Redshift ARN.

  3. Choose the format option, db-user, in order to create a temporary credential using the AutoCreate option.

  4. Update the region, account-id, and cluster-name with your respective values.

  5. For the dbuser-name, specify the user name that is used to log into the cluster database.

    CODE 
    arn:aws:redshift:<region>:<account-id>:dbuser:<cluster-name>/<dbuser-name>

  6. Additionally, you will need the ARN for the given database you wish to scan in the format:

    CODE 
    arn:aws:redshift:<region>:<account-id>:dbname:<cluster-name>/<database-name>

Create Policy

  1. In the Amazon Redshift console, navigate to IAM > Policies > Create Policy.

  2. Use the following JSON and provide your respective account values:

    Note:

    DescribeCluster is also needed for scan process but this doesn't allow to have a different resource than "*"

    JSON 
    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "redshift:GetClusterCredentials",
                "redshift:CreateClusterUser"
            ],
            "Resource": [
                "arn:aws:redshift:<region>:<account-id>:dbuser:<cluster-name>/ot-service",
                "arn:aws:redshift:<region>:<account-id>:dbname:<cluster-name>/<database-name>"
            ]
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": "redshift:DescribeClusters",
            "Resource": "*"
        }
    ]
}

  3. Select the cluster where your OneTrust

Attach Policy to Role

  1. In the Amazon Elastic Kubernetes Service console, navigate to Clusters.

  2. Select the cluster where your OneTrust worker node is deployed.

  3. Go to the Compute tab to select the cluster's Node Groups.

  4. In this Node Group details screen, go to the Details section to select the Node IAM Role ARN. You will be redirected to the IAM role page.

  5. Click the Add Permissions button, then select Attach Policies.

  6. Attach the policy you created in previous section. This worker node is now ready to scan the database.

OneTrust Vault Configuration

  1. Navigate to the Redshift Vault Entry for your credential JSON.

  2. Add the following JSON and update cluster-name, region, and database-name with the values for your account.

    Note:

    The AutoCreate=True property is required for the java JDBC to be able to create the temporary credentials.

    JSON 
    {
  "connectionUrl": "jdbc:redshift:iam://<cluster-name>:<region>/<database-name>?AutoCreate=true"
}

Redshift Configuration

  1. In the OneTrust application, run a catalog scan in the Data Discovery module. This will create the user "IAM:ot-service" on the Redshift database.

  2. In the Amazon Redshift console, navigate to Clusters.

  3. Select the cluster to which you are connecting.

  4. Click the Query data button, then select Query in query editor.

  5. Confirm the user was created using:

    CODE 
    SELECT * FROM pg_user WHERE usename='IAM:ot-service';

  6. Run the following command. Replace schema-name with the one you wish to scan. This will need to be performed for each schema on which you want to perform a catalog scan.

    CODE 
    GRANT ALL ON ALL TABLES IN SCHEMA <schema_name> to "IAM:ot-service";

Troubleshooting

Resource Error

This error may show if the resource for DescribeClusters is different to a wildcard ("*")

CODE 
2023-01-11 03:07:58.228 ERROR --- [  rdbms1-P-1] c.o.d.s.c.e.DataDiscoveryExceptionWrapper jobId=93b335b5-bd04-4a50-84b8-667e8512766d : Source:RDBMS Scanner ErrorCode:Connection Error SQL error code:0 state:99999 Exception:
java.sql.SQLException: IAM error retrieving temp credentials: User: arn:aws:sts::295935724398:assumed-role/autobots-nodegroup-role/i-078199dfa3cf998fc is not authorized to perform: redshift:DescribeClusters on resource: arn:aws:redshift:us-east-1:295935724398:cluster:redshift-cluster-dev because no identity-based policy allows the redshift:DescribeClusters action (Service: AmazonRedshift; Status Code: 403; Error Code: AccessDenied; Request ID: b74b28b0-281d-4112-b900-426c5df99cc1; Proxy: null)

Auth Error #1

Shows up when the dbuser ARN has an error for the redshift:CreateClusterUser permission

CODE 
2023-01-11 15:42:19.350 ERROR --- [  rdbms1-P-1] c.o.d.s.c.e.DataDiscoveryExceptionWrapper jobId=c0a67d16-1fea-427e-a23c-dd9485b9d180 : Source:RDBMS Scanner ErrorCode:Connection Error SQL error code:0 state:99999 Exception:
java.sql.SQLException: IAM error retrieving temp credentials: User: arn:aws:sts::295935724398:assumed-role/autobots-nodegroup-role/i-078199dfa3cf998fc is not authorized to perform: redshift:CreateClusterUser on resource: arn:aws:redshift:us-east-1:295935724398:dbuser:redshift-cluster-dev/ot-service because no identity-based policy allows the redshift:CreateClusterUser action (Service: AmazonRedshift; Status Code: 403; Error Code: AccessDenied; Request ID: 16f5f316-5d9f-44c5-92fe-7f6f8c719e96; Proxy: null)

Auth Error #2

Similar to error #1, this appears when the ARN has an error resulting from the redshift:GetClusterCredentials permission.

CODE 
2023-01-11 15:37:33.893 ERROR --- [  rdbms1-P-1] c.o.d.s.c.e.DataDiscoveryExceptionWrapper jobId=d9315883-1ab6-4dc6-b7d6-0a30cfe2ca09 : Source:RDBMS Scanner ErrorCode:Connection Error SQL error code:0 state:99999 Exception:
java.sql.SQLException: IAM error retrieving temp credentials: User: arn:aws:sts::295935724398:assumed-role/autobots-nodegroup-role/i-078199dfa3cf998fc is not authorized to perform: redshift:GetClusterCredentials on resource: arn:aws:redshift:us-east-1:295935724398:dbname:redshift-cluster-dev/test because no identity-based policy allows the redshift:GetClusterCredentials action (Service: AmazonRedshift; Status Code: 403; Error Code: AccessDenied; Request ID: 8a78880a-ec8c-4ac9-823d-394c2cd79c81; Proxy: null)

Connection URL Error

Usually because the connection URL has a syntax error.

CODE 
2023-01-11 13:54:47.750 ERROR 78733 --- [     rdbms1-P-1] .o.d.s.c.e.DataDiscoveryExceptionWrapper : Source:RDBMS Scanner ErrorCode:Connection Error SQL error code:0 state:08001 Exception:

java.sql.SQLException: Cannot determine dialect. Please check the 'connectionUrl' property in credentials.

User Error

AutoCreate=true property is missing from the connection URL.

CODE 
2023-01-11 13:57:00.732 ERROR 78861 --- [     rdbms1-P-1] c.o.d.s.r.c.s.p.ScanJobEventProcessor    : JobId: 1 Failed processing. Code:[Connection Error] Source:[RDBMS Scanner] Message:[SQLException: FATAL: user "IAM:erikaalmeida" does not exist] Arguments:[[Dialect: redshift]] Date:[2023-01-11T16:57]

java.util.concurrent.CompletionException: com.onetrust.discovery.scanner.common.exceptions.DataDiscoveryException: SQLException: FATAL: user "IAM:erikaalmeida" does not exist