How to access Amazon Redshift by @aws-sdk/client-redshift-data

Question

I developed sample apps which backend DB is Redshift and try to execute query by following SDK code.

import { RedshiftDataClient, ExecuteStatementCommand } from '@aws-sdk/client-redshift-data';


export const resolvers: IResolvers = {
  Query: {
    user: (parent, args, context): User => ({ login: context.login }),
    region: (): string => getRegion(),
    getData: async () => {
      const redshift_client = new RedshiftDataClient({});
      
      const request = new ExecuteStatementCommand({
        ClusterIdentifier: 'testrs',
        Sql: `select * from test`,
        SecretArn: 'arn:aws:secretsmanager:us-east-1:12345561:secret:test-HYRSWs',
        Database: 'test',
      });

      try {
        const data = await redshift_client.send(request);
        console.log('data', data);
        return data;
      } catch (error) {
        console.error(error);
        throw new Error('Failed fetching data to Redshift');
      } finally {
        // execute regardless of error state
      }
    },
  },
};

It returned following error

ERROR   AccessDeniedException: 
User: arn:aws:sts::12345561:assumed-role/WebsiteStack-Beta-US-EAST-GraphQLLambdaServiceRole1BCPB5P3Q4IS9/GraphQLLambda 
is not authorized to perform: redshift-data:ExecuteStatement on resource: arn:aws:redshift:us-east-1:12345561:cluster:testrs 
because no identity-based policy allows the redshift-data:ExecuteStatement action

Must I use sdk package like STS ?

If someone has opinion,or materials. will you please let me know Thanks

user_1435543 · Answer

I know when using the AWS SDK for Java V2 for the exact same use case, you can successfully query data by setting the ExecuteStatementRequest object and passing it to the Data Client's executeStatement like this:

if (num ==5)
       sqlStatement = "SELECT TOP 5 * FROM blog ORDER BY date DESC";
 else if (num ==10)
       sqlStatement = "SELECT TOP 10 * FROM blog ORDER BY date DESC";
  else
       sqlStatement = "SELECT * FROM blog ORDER BY date DESC" ;

   ExecuteStatementRequest statementRequest = ExecuteStatementRequest.builder()
                .clusterIdentifier(clusterId)
                .database(database)
                .dbUser(dbUser)
                .sql(sqlStatement)
                .build();

   ExecuteStatementResponse response = redshiftDataClient.executeStatement(statementRequest);

As shown here -- the required values are clusterId, database, and dbUser.

I would assume the AWS SDK for JavaScript would work the same way. (I have not tried using that SDK however).

The reference docs confirm this...

Temporary credentials - when connecting to a cluster, specify the cluster identifier, the database name, and the database user name. Also, permission to call the redshift:GetClusterCredentials operation is required. When connecting to a serverless endpoint, specify the database name.

https://docs.aws.amazon.com/AWSJavaScriptSDK/v3/latest/clients/client-redshift-data/classes/executestatementcommand.html

How to access Amazon Redshift by @aws-sdk/client-redshift-data

1 Answers