Authenticating a scheduled query

When you schedule a query, you use one of the following authentication methods when the SQL runs. Each method requires a different combination of input on the query editor v2. These authentication methods are supported by the Data API which is used to run your SQL statements.

The database user or role that is used to run the query must have the necessary database privileges. For example, to grant IAMR:MyRedshiftQEv2Scheduler privileges to table mytable, run the following SQL command.

GRANT all ON TABLE mytable TO "IAMR:MyRedshiftQEv2Scheduler";

To view the list of database users in your cluster or workgroup, query the system view PG_USER_INFO.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "UseTemporaryCredentialsForAllServerlessWorkgroups",
            "Effect": "Allow",
            "Action": "redshift-serverless:GetCredentials",
            "Resource": [
                "arn:aws:redshift-serverless:*:*:workgroup/*"
            ]
        }
    ]
}

Amazon Secrets Manager

With this method, provide a secret value for secret-arn that is stored in Amazon Secrets Manager. This secret contains credentials to connect to your database. You might have created a secret with the proper credentials when you created your cluster or workgroup. The secret must be tagged with the key RedshiftDataFullAccess. If the tag key is not already present, use the Amazon Secrets Manager console to add it. For information about creating a secret, see Creating a secret for database connection credentials.

For more information about the minimum permissions, see Creating and Managing Secrets with Amazon Secrets Manager in the Amazon Secrets Manager User Guide.

Temporary credentials

With this method, provide your Database name and Database user values when connecting to a database in a cluster. You only need to provide your Database name when connecting to a database in a workgroup.

When connecting to a cluster, the AmazonRedshiftDataFullAccess policy allows the database user named redshift_data_api_user permission for redshift:GetClusterCredentials. If you want to use a different database user to run the SQL statement, then add a policy to the IAM role attached to your cluster to allow redshift:GetClusterCredentials. The following example policy allows database users awsuser and myuser.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "UseTemporaryCredentialsForAllDbUsers",
            "Effect": "Allow",
            "Action": "redshift:GetClusterCredentials",
            "Resource": [
                "arn:aws:redshift:*:*:dbuser:*/awsuser",
                "arn:aws:redshift:*:*:dbuser:*/myuser"
            ]
        }
    ]
}