A function that lets you sign S3 urls for viewing from within your database queries

sign_s3_url.sql

-- This function is based on this description:
-- https://docs.aws.amazon.com/AmazonS3/latest/API/sigv4-query-string-auth.html

CREATE OR REPLACE FUNCTION
sign_s3_url(
  m_host text,
  m_verb text,
  m_resource text,
  m_region text,
  m_key text,
  m_secret text,
  m_time timestamptz)
RETURNS text AS $$
DECLARE
  m_time_utz timestamptz := m_time at time zone 'UTC';
  m_service text := 's3';
  m_date_str text := to_char(m_time_utz, 'yyyyMMdd');
  m_time_str text := to_char(m_time_utz, 'yyyyMMddThh24mmssZ');
  m_credential text := concat(
      m_key,
      '/', m_date_str,
      '/', m_region,
      '/', m_service,
      '/aws4_request'
    );
  -- Credential won't contain any other things we need to encode
  -- replace any / with %2F
  m_credential_url_encoded text := replace(m_credential, '/', '%2F');

  m_signed_headers text := 'host';
  -- The line break is not in error, required by spec for each header line
  m_headers text := concat('host:', m_host, E'\n'); 

  -- If you need query parameters, they should go here, sorted alphabetically (and URI encoded)
  m_query_str text := concat(
    'X-Amz-Algorithm=AWS4-HMAC-SHA256',
    '&X-Amz-Credential=', m_credential_url_encoded,
    '&X-Amz-Date=', m_time_str,
    '&X-Amz-Expires=', '3600', -- 1800 is desired, use this for testing
    '&X-Amz-SignedHeaders=', m_signed_headers
  );

  -- Build the canonical request
  m_canonical_request text := concat(
    m_verb, E'\n',
    m_resource, E'\n',
    m_query_str, E'\n',
    m_headers, E'\n',
    m_signed_headers, E'\n',
    'UNSIGNED-PAYLOAD'
  );
  m_hex_encoded_hash text := encode(digest(m_canonical_request, 'sha256'), 'hex');

  m_string_to_sign bytea := concat(
    'AWS4-HMAC-SHA256', E'\n',
    m_time_str, E'\n',
    concat(m_date_str, '/', m_region, '/', m_service, '/aws4_request'), E'\n',
    m_hex_encoded_hash
  );

  -- In the AWS documentation, format is HMAC-SHA256(key, data), but postgres is hmac(data, key, type)
  -- So, these look backwards, but they are not.
  m_date_key                 bytea := hmac(bytea(m_date_str), bytea(concat('AWS4', m_secret)), 'sha256');
  m_date_region_key          bytea := hmac(bytea(m_region), m_date_key, 'sha256');
  m_date_region_service_key  bytea := hmac(bytea(m_service), m_date_region_key, 'sha256');
  m_signing_key              bytea := hmac(bytea('aws4_request'), m_date_region_service_key, 'sha256');

  m_signature text := encode(hmac(m_string_to_sign, m_signing_key, 'sha256'), 'hex');

  m_result_url text := concat(
    'https://',
    m_host,
    m_resource,
    '?',
    m_query_str,
    '&X-Amz-Signature=',
    m_signature
  );

BEGIN
  RETURN m_result_url;
END;
$$
LANGUAGE plpgsql
COST 1000
RETURNS NULL ON NULL INPUT
IMMUTABLE;

Usage:

SELECT sign_s3_url(
  'mybucket.s3.amazonaws.com',
  'GET',
  '/my/path/to/image.png',
  'us-east-1',
  ACCESS_KEY_ID, 
  SECRET_ACCESS_KEY, -- you probably want to secure this
  now()
) as sig;

Issues:

• Document how to store your secrets for use in the DB
• You probably want to round now() to an interval around your expiration time so that clients get back the same URL for a while, enabling caching the URL response.
• Should be IMMUTABLE
• You can actually cache m_signing_key, since it doesn't change per date and region, which speeds this up a lot. In practice, this is so much faster than going out of the DB, that it hasn't been an issue yet.