Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A function that lets you sign S3 urls for viewing from within your database queries
-- 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_resource text,
m_region text,
m_key text,
m_secret text,
m_time timestamptz)
RETURNS text AS $$
DECLARE
m_verb text := 'GET';
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;
@darknoon

This comment has been minimized.

Copy link
Owner Author

@darknoon darknoon commented Dec 17, 2019

Usage:

SELECT sign_s3_url(
  'mybucket.s3.amazonaws.com',
  '/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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment