在Postgresql RDS/Amazon Aurora中签名S3 URL

Question

DB（PostgreSQL RDS/Amazon Aurora）返回了很多图像文件。我们需要签署URL。当前，用户定义的函数或视图返回记录。

我正在寻找一种将S3 URL直接在SQL中签名的方法，作为用户定义的功能。不幸的是，除了在用户定义的函数中使用Python语言外，似乎没有其他方法，而Python在PostgreSQL/Aurora中不支持Python。

有人知道我们可以直接签署URL作为SQL查询的一部分而在Postgresql RDS/Amazon Aurora中直接签名的方法？

Answer 1

是的，这是可能的，我已经做到了。

首先，让我们看看AWS SDK是如何做到的。在JavaScript AWS SDK中，我们调用 getsignedurlpromise 获取签名的URL。

import AWS from 'aws-sdk';
const s3 = new AWS.S3();
s3.getSignedUrlPromise('getObject', { Bucket, Key, Expires: 60 * 60});

从 aws.signers.presign.presign.presign（） 我们可以看到

  sign: function sign(request, expireTime, callback) {
    request.on('build', signedUrlBuilder);
    request.on('sign', signedUrlSigner);
// ...
      return AWS.util.urlFormat(request.httpRequest.endpoint);
  }

签名发生在 signedurlsigner 对于v3，

function signedUrlSigner(request) {
  var auth = request.httpRequest.headers['Authorization'].split(' ');
  if (auth[0] === 'AWS') {
    auth = auth[1].split(':');
    queryParams['Signature'] = auth.pop();
    queryParams['AWSAccessKeyId'] = auth.join(':');
  }

  endpoint.search = AWS.util.queryParamsToString(queryParams);
}

签名来自标题['授权'] ，但是授权设置在哪里？

它设置为 aws.signers.s3 .addauthorization

  addAuthorization: function addAuthorization(credentials, date) {
    var signature = this.sign(credentials.secretAccessKey, this.stringToSign());
    var auth = 'AWS ' + credentials.accessKeyId + ':' + signature;

    this.request.headers['Authorization'] = auth;
  },

sign 函数正在使用sha1 with base64摘要

  sign: function sign(secret, string) {
    return AWS.util.crypto.hmac(secret, string, 'base64', 'sha1');
  }

和 stringtosign 有点令人困惑，但是在我的实验中，结果是

const stringToSign = `GET\n\n\n${EXPIRES}\n/${BUCKET}/${KEY}`;

上述信息，我们可以轻松地轻松编写一个postgres函数

CREATE OR REPLACE FUNCTION image_s3_signed_url(image_row "Image")
RETURNS TEXT AS $
DECLARE
    newline text := E'\n';
    method text := 'GET';
    bucket text := 'my_bucket';
    access_key text := 'my_access_key';
    access_secret text := 'my_access_secret';
    filepath text;
    expires bigint;
    tosign text;
    raw_signature text;
    encoded_signatre text;
    result text;
BEGIN

  filepath := image_row.filepath;
  expires = EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) + 60 * 60;
  
  tosign := method || newline || newline || newline || expires || newline || '/' || bucket || filepath;
  raw_signature := encode(hmac(tosign, access_secret, 'sha1'), 'base64');
  -- encodeURIComponent:
  encoded_signatre := REPLACE(REPLACE(REPLACE(raw_signature, '+', '%2B'), '/', '%2F'), '=', '%3D');

  result = filepath || '?AWSAccessKeyId=' || access_key || '&Expires=' || expires || '&Signature=' || encoded_signatre;

  RETURN result;

END;
$ LANGUAGE plpgsql STABLE;

结果是

/KEY?AWSAccessKeyId=my_access_key&Expires=EXPIRES&Signature=signature

并添加您的S3主机 https://my_bucket.s3.us-west-1.amazonaws.com 在前端

https://my_bucket.s3.us-west-1.amazonaws.com/KEY?AWSAccessKeyId=my_access_key&Expires=EXPIRES&Signature=signature

Answer 2

我们可以通过参数（AWS签名版本4）

CREATE OR REPLACE FUNCTION public.get_signed_url(
    method text,
    bucket text,
    endpoint text,
    region text,
    key text,
    expires integer,
    access_key text,
    secret_key text)
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    host_url text;
    amz_date text;
    date_stamp text;
    canonical_headers text;
    credential_scope text;
    signed_headers text;
    algorithm text;
    canonical_request text;
    string_to_sign text;
    k_date bytea;
    k_region bytea;
    k_service bytea;
    k_signing bytea;
    signing_key bytea;
    signature text;
    presigned_url text;
BEGIN
    
    endpoint := replace(endpoint, 'https://', '');

    host_url :=  endpoint;
    key := bucket || '/' || key;

    -- Generate dates
    amz_date := to_char(now() at time zone 'utc', 'YYYYMMDD"T"HH24MISS"Z"');
    date_stamp := substring(amz_date FROM 1 FOR 8);

    -- Construct canonical headers and credential scope
    canonical_headers := 'host:' || host_url;
    credential_scope := date_stamp || '/' || region || '/s3/aws4_request';
    signed_headers := 'host';
    algorithm := 'AWS4-HMAC-SHA256';

    -- Construct canonical request
    canonical_request := method || E'\n' || '/' || key || E'\n' || 
        'X-Amz-Algorithm=' || urlencode(algorithm) || '&' ||
        'X-Amz-Credential=' || urlencode(access_key || '/' || credential_scope) || '&' ||
        'X-Amz-Date=' || urlencode(amz_date) || '&' ||
        'X-Amz-Expires=' || expires || '&' ||
        'X-Amz-SignedHeaders=' || urlencode(signed_headers) || E'\n' ||
        canonical_headers || E'\n\n' || signed_headers || E'\nUNSIGNED-PAYLOAD';

    -- Construct string to sign
    string_to_sign := algorithm || E'\n' || amz_date || E'\n' || credential_scope || E'\n' || 
        encode(digest(canonical_request, 'sha256'), 'hex');

    -- Calculate signing key
    k_date := hmac(date_stamp, ('AWS4' || secret_key), 'sha256');
    k_region := hmac(region::bytea, k_date, 'sha256');
    k_service := hmac('s3'::bytea, k_region, 'sha256');
    k_signing := hmac('aws4_request'::bytea, k_service, 'sha256');
    signing_key := k_signing;

    -- Calculate signature
    signature := encode(hmac(string_to_sign::bytea, signing_key, 'sha256'), 'hex');

    -- Construct presigned URL
    presigned_url := 'https://' || host_url || '/' || key || '?' ||
        'X-Amz-Algorithm=' || urlencode(algorithm) || '&' ||
        'X-Amz-Credential=' || urlencode(access_key || '/' || credential_scope) || '&' ||
        'X-Amz-Date=' || urlencode(amz_date) || '&' ||
        'X-Amz-Expires=' || expires || '&' ||
        'X-Amz-SignedHeaders=' || urlencode(signed_headers) || '&' ||
        'X-Amz-Signature=' || urlencode(signature);

    RETURN presigned_url;
END;
$BODY$;

依赖项

1. install pgcrypto extension =＆gt; 创建扩展名（如果不存在）pgcrypto;
2. create urlencode function =＆gt; https://stackoverflow.com/a/40762846/13423287

Answer 3

数据库不是执行此类操作的地方。
您应该考虑已经将签名的URL放入数据库中，或者如果不应该重新搜索您的应用程序。