Google Apps Script连接AWS Redshift Data API签名不匹配问题排查

我在Google表格的Apps Script中尝试连接AWS Redshift Data API，已按照亚马逊文档生成请求签名，但始终收到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."

推测签名生成逻辑存在问题，以下是脱敏后的代码，同时希望了解更简便的执行语句及获取结果的方法。

现有代码 #

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
  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
  let hashedCanonicalRequest = Sha256Hash(canonicalRequest);
  Logger.log("Step 2: hashedCanonicalRequest: %s", hashedCanonicalRequest);

  // Step 3: Create a string to sign
  let stringToSign = awsCreateStringToSign(awsAlgorithm, hashedCanonicalRequest, requestDateTime, requestDate, regionName, serviceName);
  Logger.log("Step 3: stringToSign: %s", stringToSign);

  // Step 4: Calculate the signature
  let signatureKey = awsGetSignatureKey(secretKey, requestDate, regionName, serviceName, stringToSign);
  Logger.log("Step 4: signatureKey: %s", signatureKey);

  // Step 5: Add the signature to the 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 AWS signature v4 example
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;
}

签名错误的核心问题及修正 #

1. 未定义日期变量 #

代码中使用了today但未初始化，在awsSendRequest函数开头添加：

let today = new Date();

2. Canonical URI错误 #

Canonical URI仅需路径部分，不是完整URL，修改为：

let canonicalURI = '/';

注意：实际请求的URL还是完整的https://redshift-data.<region>.amazonaws.com，但签名用的Canonical URI必须是/

3. Payload哈希计算错误 #

String(bodyPayload)会将对象转为[object Object]，导致哈希值完全错误，需序列化JSON：

let hashedPayload = Sha256Hash(JSON.stringify(bodyPayload));

4. HMAC签名输入格式错误 #

Utilities.computeHmacSha256Signature第一个参数直接传字符串即可，无需转Blob字节，修正awsGetSignatureKey：

function awsGetSignatureKey(key, requestDate, regionName, serviceName, stringToSign) {
  const kDate = Utilities.computeHmacSha256Signature(requestDate, "AWS4" + key);
  const kRegion = Utilities.computeHmacSha256Signature(regionName, kDate);
  const kService = Utilities.computeHmacSha256Signature(serviceName, kRegion);
  const kSigning = Utilities.computeHmacSha256Signature("aws4_request", kService);
  const kSignature = Utilities.computeHmacSha256Signature(stringToSign, kSigning);
  return BytesToHex(kSignature);
}

5. 请求Body格式错误 #

Apps Script的UrlFetchApp发送JSON时，需将body转为字符串，修改requestOptions：

var requestOptions = {
  method: httpMethod,
  headers: headerRequestObject,
  body: JSON.stringify(bodyPayload), // 序列化body
  contentType: 'application/x-amz-json-1.1', // 显式指定Content-Type
  redirect: 'follow',
  muteHttpExceptions: true
};

更简便的实现方式 #

方式1：使用AWS Apps Script库 #

直接使用第三方封装的AWS签名库，无需手动实现签名逻辑，只需配置凭证和调用API即可。

方式2：通过Lambda中间层 #

在AWS Lambda中编写调用Redshift Data API的代码，Apps Script只需通过HTTP请求调用Lambda函数，避免在前端处理复杂的签名逻辑，同时更安全（不用暴露AWS凭证在Apps Script中）。

方式3：使用IAM角色临时凭证 #

通过AWS STS服务获取临时凭证，减少长期凭证暴露风险，不过仍需处理签名，但安全性更高。

内容的提问来源于stack exchange，提问作者legrand