I am trying to connect to the AWS Redshift Data API via Apps Script in a Google Sheet. I created the request signature according to Amazon's documentation but I am always receiving this response from Amazon's API: The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details.
So I guess I am doing something wrong.
Below is my code (credentials and project specific stuff redacted and sources of the documentation in every step.
function awsRedshiftDataExecuteStatement() {
let bodyPayload = {
"ClusterIdentifier": "<cluster_identifier>",
"Database": "<database>",
"DbUser": "<db_user>",
"MaxResults": 1,
"Sql": "select 1;",
"NextToken": ""
};
let result = awsSendRequest(bodyPayload, "RedshiftData.ExecuteStatement");
Logger.log(result);
}
function awsSendRequest(bodyPayload, headerXAmzTarget){
let accessKeyId = "<access_key>";
let secretKey = "<secret_key>";
let regionName = "<region>"
let serviceName = "redshift-data";
let awsAlgorithm = "AWS4-HMAC-SHA256";
let requestDate = Utilities.formatDate(today,'GTM','YYYYMMdd');
//let requestDateTime = today.toISOString();
let requestDateTime = Utilities.formatDate(today,'GTM','YYYYMMdd\'T\'HHmmss\'Z\'');
let hashedPayload = Sha256Hash(String(bodyPayload));
let headerArray = [
[{'name':'Host','value':'redshift-data.<region>.amazonaws.com'}],
[{'name':'X-Amz-Target','value':headerXAmzTarget}],
[{'name':'X-Requested-With','value':'XMLHttpRequest'}],
[{'name':'Content-Type','value':'application/x-amz-json-1.1'}],
[{'name':'X-Amz-Content-Sha256','value':hashedPayload}],
[{'name':'X-Amz-Date','value':requestDateTime}]
];
// Step 1: Create a canonical request > https://docs.aws.amazon.com/IAM/latest/UserGuide/create-signed-request.html#create-canonical-request
let httpMethod = 'POST';
let canonicalURI = encodeURI('https://redshift-data.eu-central-1.amazonaws.com');
let canonicalQueryString = "";
let canonicalHeaders = awsCreateHeaders(headerArray, 'canonicalHeaders');
let signedHeaders = awsCreateHeaders(headerArray, 'signedHeaders');
let canonicalRequest = awsCreateCanonicalRequest(httpMethod, canonicalURI, canonicalQueryString, canonicalHeaders, signedHeaders, hashedPayload);
Logger.log("Step 1: canonicalRequest: %s", canonicalRequest);
// Step 2: Create a hash of the canonical request > https://docs.aws.amazon.com/IAM/latest/UserGuide/create-signed-request.html#create-canonical-request-hash
let hashedCanonicalRequest = Sha256Hash(canonicalRequest);
Logger.log("Step 2: hashedCanonicalRequest: %s", hashedCanonicalRequest);
// Step 3: Create a string to sign > https://docs.aws.amazon.com/IAM/latest/UserGuide/create-signed-request.html#create-string-to-sign
let stringToSign = awsCreateStringToSign(awsAlgorithm, hashedCanonicalRequest, requestDateTime, requestDate, regionName, serviceName);
Logger.log("Step 3: stringToSign: %s", stringToSign);
// Step 4: Calculate the signature > https://docs.aws.amazon.com/IAM/latest/UserGuide/create-signed-request.html#calculate-signature
let signatureKey = awsGetSignatureKey(secretKey, requestDate, regionName, serviceName, stringToSign);
Logger.log("Step 4: signatureKey: %s", signatureKey);
// Step 5: Add the signature to the request > https://docs.aws.amazon.com/IAM/latest/UserGuide/create-signed-request.html#add-signature-to-request
let headerRequestObject = awsCreateHeaders(headerArray, 'requestOptionsHeader');
headerRequestObject["Authorization"] = awsAlgorithm+" Credential="+ awsCreatCredentialString(accessKeyId, requestDate, regionName, serviceName) +", SignedHeaders="+signedHeaders+", Signature="+signatureKey;;
Logger.log("Step 5: headerRequestObject['Authorization']: %s", headerRequestObject["Authorization"]);
// remove header "host" (Apps Script would throw an error otherwise)
delete headerRequestObject['Host'];
var requestOptions = {
method: httpMethod,
headers: headerRequestObject,
body: bodyPayload,
redirect: 'follow',
muteHttpExceptions: true
};
Logger.log(requestOptions);
var response;
try {
response = UrlFetchApp.fetch(canonicalURI, requestOptions);
} catch (e) {
throw (e);
}
var json = JSON.parse(response);
return json;
}
function Sha256Hash(value) {
return BytesToHex(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, value));
}
function BytesToHex(bytes) {
let hex = [];
for (let i = 0; i < bytes.length; i++) {
let b = parseInt(bytes[i]);
if (b < 0) {
c = (256+b).toString(16);
} else {
c = b.toString(16);
}
if (c.length == 1) {
hex.push("0" + c);
} else {
hex.push(c);
}
}
return hex.join("");
}
// Google Apps Script version of https://docs.aws.amazon.com/general/latest/gr/signature-v4-examples.html#signature-v4-examples-javascript
function awsGetSignatureKey(key, requestDate, regionName, serviceName, stringToSign) {
const kDate = Utilities.computeHmacSha256Signature(requestDate, "AWS4" + key);
const kRegion = Utilities.computeHmacSha256Signature(Utilities.newBlob(regionName).getBytes(), kDate);
const kService = Utilities.computeHmacSha256Signature(Utilities.newBlob(serviceName).getBytes(), kRegion);
const kSigning = Utilities.computeHmacSha256Signature(Utilities.newBlob("aws4_request").getBytes(), kService);
const kSignature = Utilities.computeHmacSha256Signature(Utilities.newBlob(stringToSign).getBytes(), kSigning);
return BytesToHex(kSignature);
}
function awsCreateCanonicalRequest(httpMethod, canonicalURI, canonicalQueryString, canonicalHeaders, signedHeaders, hashedPayload){
let canonicalRequest = httpMethod +"\n"+canonicalURI+"\n"+canonicalQueryString+"\n"+canonicalHeaders+"\n"+signedHeaders+"\n"+hashedPayload;
return canonicalRequest;
}
function awsCreateHeaders(headerArray, type){
switch(type){
case 'canonicalHeaders':
var returnValue = new String();
for(let i=0; i < headerArray.length; i++ ){
returnValue = returnValue + headerArray[i][0].name.toLowerCase() +":" + headerArray[i][0].value.trim() +"\n";
}
break;
case 'signedHeaders':
var returnValue = new Array();
for(let i=0; i < headerArray.length; i++ ){
returnValue.push(headerArray[i][0].name.toLowerCase());
}
//returnValue.push('host');
returnValue = returnValue.sort().join(";");
break;
case 'requestOptionsHeader':
var returnValue = {};
for(let i=0; i < headerArray.length; i++ ){
returnValue[headerArray[i][0].name] = headerArray[i][0].value;
}
break;
}
return returnValue;
}
function awsCreateStringToSign(awsAlgorithm, hashedCanonicalRequest, requestDateTime, requestDate, region, service){
let returnValue = awsAlgorithm + "\n" + requestDateTime + "\n" + requestDate+"/"+region+"/"+service+"/aws4_request" + "\n" + hashedCanonicalRequest;
return returnValue;
}
function awsCreatCredentialString(accessKeyId, requestDate, region, service){
let returnValue = accessKeyId+"/"+ requestDate+"/"+region+"/"+service+"/aws4_request";
return returnValue;
}
I couldn't find any other topics about connecting to AWS via Apps Script apart from Passing hashed data as key to hash again returns incorrect results. If there's any easier way to execute statements and retrieve their results I am happy to use those alternatives!
Using this code I was able to connect: https://github.com/smithy545/aws-apps-scripts/blob/master/aws.js