Amazon Redshift Data API REST HTTP endpoint returns <UnknownOperationException/> from Salesforce Apex callouts

Question

I work for a consulting firm. We are trying to authenticate outgoing Apex calls to the Amazon Redshift Data API from the Salesforce platform. This will ultimately allow us to execute SQL calls and pull the results back through the Valence app in Salesforce.

I am looking for clarification and hopefully a basic example of the request I'm trying to construct, just so we can find a jumping off point.

Specifically - we are trying to submit a basic POST request with the DescribeTable action to get some response back, but all we can get is an error in the response body saying "UnknownOperationException" in XML format. We would be happy to get a different error at this point just so we can have some direction.

It's not clear as to what we need to include -

• Are "Action" and "Version" supposed to be included in the query string instead of going into the body for a POST request?
• Are any additional parameters needed for the query string to properly route the request?
• Should I be using a GET request even though there's a character limit on URLs that may be an issue?
• Does the Content-Type header need to be deliberately set and / or included in the canonical request? Some examples include it and others don't

The sprawling Amazon docs recommend repeatedly that you either use their CLI tool to access the Redshift data (which isn't applicable here), OR to use their numerous SDKs to code authentication into your app... but because we are using Salesforce Apex, we are not able to take advantage of either of those.

So, we have 2 options -

1. Use the Named / External Credentials in Salesforce to authenticate for us, using the AWS Signature Version 4 protocol. Our company doesn't have experience with this and it's not clear as to how this interacts with additional query (URL), body, or header parameters included in the request.

OR

2. Code the authentication protocol ourselves, which we have done, but we're getting the same issue using both our code and the Named Credential callout, so it's not clear what the connection issue is.

For the sake of brevity, here are some links to documentation I've reviewed. I'm HOPING I missed a small detail somewhere. Basic request code is at the bottom and is what returns the error for the Redshift callout request -

Description of the action and parameters to include in the body - this is from the Redshift Data API docs specifically

https://docs.aws.amazon.com/redshift-data/latest/APIReference/API_DescribeTable.html

"You can use a GET or POST request to send requests to Amazon Redshift. The difference between the two is that for the GET request your parameters are sent as query string parameters. For the POST request they are included in the body of the request. " - Redshift management guide

https://docs.aws.amazon.com/redshift/latest/mgmt/amazon-redshift-signing-requests.html

Example of the signing process which involves constructing a canonical request. From the AWS general reference. The named credential / signing would need to carry out this same process

https://docs.aws.amazon.com/general/latest/gr/create-signed-request.html

I've tried following the documentation examples as closely as possible, but I'm not getting an error related to the specifics of the request, so it's hard to understand where the issue is.

I've tried both basic GET and POST requests for the DescribeTable action, to no avail. I've wanted to stick with POST if possible because body JSON is preferred over query parameters IMO.

In the common parameters section of the docs, it indicates the Action and Version params are necessary for each call, but the rest depend on the context and only seem necessary for GET requests.

https://docs.aws.amazon.com/redshift-data/latest/APIReference/CommonParameters.html

The list of common errors does not describe UnknownOperationException.

https://docs.aws.amazon.com/redshift-data/latest/APIReference/CommonErrors.html

Identifying info is dummied in for this post.

global class JPTestClass {
    public static void jpTestMethod(){
        String POSTMAN_CALLOUT = 'callout:Testing_Postman_API';
        // This points to https://redshift-data.us-east-1.amazonaws.com
        String REDSHIFT_CALLOUT = 'callout:Redshift_Data_API';
        
        send(POSTMAN_CALLOUT);
        send(REDSHIFT_CALLOUT);
    }
    
    public static void send(String callout){
        HttpRequest request = new HttpRequest();

        // parameters in the POST request body
        String payloadString = JSON.serialize(new Map<String, Object>{
                    'Action' => 'DescribeTable',
                    'Version' => '2010-05-08',
                    'ClusterIdentifier' => '<<<XXX>>>', 
                    'ConnectedDatabase' => '<<<XXX>>>',
                    'Database' => '<<<XXX>>>',
                    'MaxResults' => 1000,
                    'NextToken' => '',
                    'Schema' => '<<<XXX>>>',
                    'SecretArn' => '<<<XXX>>>',
                    'Table' => 'salesforce_contacts',
                    'WorkgroupName' => ''
                });
        
        System.debug(payloadString);
        
        request.setEndpoint(callout + '?Action=DescribeTable&Version=2010-05-08');
        request.setMethod('POST');
        
        request.setBody(payloadString);
        
        HttpResponse response = new Http().send(request);
        
        System.debug(response.getBody());
    }
}

These are the headers received in the request by Postman. Authorization header generated by Salesforce.

x-forwarded-for: "xxxxxxxxx"
x-forwarded-proto: "xxxxxxxxx"
x-forwarded-port: "xxxxxxxxx"
host: "36b0a2b5-79a5-471e-ac92-06122c76dad2.mock.pstmn.io"
x-amzn-trace-id: "Root=1-639b9d8c-64700ff707de06582eafc6ce"
content-length: "263"
user-agent: "SFDC-Callout/56.0"
sfdc_stack_depth: "1"
accept: "text/html, image/gif, image/jpeg, *; q=.2, */*; q=.2"
authorization: "AWS4-HMAC-SHA256 Credential=<<<ACCESS KEY>>>/20221215/us-east-1/redshift-data/aws4_request, SignedHeaders=host;x-amz-content-sha256;x-amz-date, Signature=0be75a778aa72003cd29140ea52b3db74b6d559078efa382697f8d78b5460b7f"
x-amz-date: "20221215T221956Z"
x-amz-content-sha256: "ec4ae9564eca8bf406807d8ec6305ddbf8435095b269db6f3162b5cfcac4cad8"
accept-encoding: "gzip,deflate"

Answer 1

To anyone curious - the requests succeeded after including these headers, which are NOT included with either 1) Salesforce AWS Signature 4 named credentials, or 2) generated Postman authorization headers.

I also could not locate these via the Redshift Data API docs, but they are described in other API docs.

'X-Amz-Target' => 'RedshiftData.' + action, e.g. 'RedshiftData.DescribeTable'
'X-Requested-With' => 'XMLHttpRequest'
'Content-Type' => 'application/x-amz-json-1.1'