google-storage-url-signer.sql

PostgreSQL - Google Storage URL Signer

create or replace function qx.create_google_storage_url(
  file_path text,
  bucket text default 'qx-media-unstable',
  request_location text default 'auto',
  request_service text default 'storage',
  request_type text default 'goog4_request'
)
  returns text
  volatile
  language plpgsql
as $$
declare
  host text;
  access_key text;
  hash_algorithm text;
  credential_scope text;
  string_to_sign text;
  request_date text;
  request_date_time text;
  path_to_resource text;
  canonical_request text;
  canonical_query_string text;
begin
  -- https://cloud.google.com/storage/docs/authentication/canonical-requests#required-headers
  host = 'storage.googleapis.com';

  -- https://cloud.google.com/storage/docs/authentication/signatures#credential-scope
  request_date = to_char(now(), 'YYYYMMDD');

  -- https://cloud.google.com/storage/docs/authentication/signatures#active_datetime
  request_date_time = to_char(now(), 'YYYYMMDD"T"HH24MISS"Z"');

  -- https://cloud.google.com/storage/docs/authentication/canonical-requests#about-resource-path
  path_to_resource = concat('/', bucket, '/',  file_path);

  -- https://cloud.google.com/storage/docs/authentication/signatures#credential-scope
  credential_scope = concat(request_date, '/', request_location, '/', request_service, '/', request_type);

  -- https://cloud.google.com/storage/docs/authentication/canonical-requests#required-query-parameters
  hash_algorithm = 'GOOG4-HMAC-SHA256';
  -- todo : we need to not store this in plain text
  access_key = qx.current_setting('qx.google_storage_access_key');
  canonical_query_string = concat(
    concat('X-Goog-Algorithm', '=', hash_algorithm),
    concat('&', 'X-Goog-Credential', '=', replace(concat(access_key, '/', credential_scope), '/', '%2F')),
    concat('&', 'X-Goog-Date', '=', request_date_time),
    concat('&', 'X-Goog-Expires', '=', 60 * 60),
    concat('&', 'X-Goog-SignedHeaders', '=', 'host')
  );

  -- https://cloud.google.com/storage/docs/authentication/canonical-requests#request-structure
  canonical_request = concat(
    -- https://cloud.google.com/storage/docs/authentication/canonical-requests#verbs
    'GET',
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/canonical-requests#about-resource-path
    qx.google_file_path_encode(path_to_resource),
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/canonical-requests#about-query-strings
    canonical_query_string,
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/canonical-requests#about-headers
    concat('host:', host),
    e'\n',
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/canonical-requests#signed_headers
    'host',
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/canonical-requests#payload
    'UNSIGNED-PAYLOAD'
  );

  -- https://cloud.google.com/storage/docs/authentication/signatures#string-to-sign
  string_to_sign = concat(
    -- https://cloud.google.com/storage/docs/authentication/signatures#signing_algorithm
    hash_algorithm,
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/signatures#active_datetime
    request_date_time,
    e'\n',
    -- https://cloud.google.com/storage/docs/authentication/signatures#credential_scope
    credential_scope,
    e'\n',
  -- https://cloud.google.com/storage/docs/authentication/signatures#hash_of_the_canonical_request
    encode(digest(canonical_request, 'sha256'), 'hex')
  );

  -- https://cloud.google.com/storage/docs/access-control/signing-urls-manually#algorithm
  return concat('https://', host, path_to_resource, '?', canonical_query_string, '&', 'X-Goog-Signature', '=', qx.create_google_storage_signature(string_to_sign, request_date, request_location, request_service, request_type));
end
$$;

create or replace function qx.create_google_storage_signature(
  string_to_sign text,
  request_date text,
  request_location text,
  request_service text,
  request_type text
)
  returns text
  volatile
  language plpgsql
as $$
declare
  hmac_secret text;
  message_digest text;
begin
  -- todo : we need to not store this in plain text
  hmac_secret = qx.current_setting('qx.google_storage_hmac_secret');

  -- https://cloud.google.com/storage/docs/authentication/signatures#derive-key
  message_digest = hmac(string_to_sign::text::bytea, hmac(request_type::text::bytea, hmac(request_service::text::bytea, hmac(request_location::text::bytea, hmac(request_date, concat('GOOG4', hmac_secret), 'sha256'), 'sha256'), 'sha256'), 'sha256'), 'sha256');

  -- https://cloud.google.com/storage/docs/authentication/signatures#after_signing
  return encode(message_digest::text::bytea, 'hex');
end
$$;

-- todo - im sure there is a better way of handling this
-- https://cloud.google.com/storage/docs/authentication/canonical-requests#about-resource-path
create or replace function qx.google_file_path_encode(
  plaintext text
)
  returns text
  stable
  language plpgsql
as $$
declare
begin
  plaintext = replace(plaintext, '?', '%3F');
  plaintext = replace(plaintext, '=', '%3D');
  plaintext = replace(plaintext, '!', '%21');
  plaintext = replace(plaintext, '#', '%23');
  plaintext = replace(plaintext, '$', '%24');
  plaintext = replace(plaintext, '&', '%26');
  plaintext = replace(plaintext, '''', '%27');
  plaintext = replace(plaintext, '(', '%28');
  plaintext = replace(plaintext, ')', '%29');
  plaintext = replace(plaintext, '*', '%2A');
  plaintext = replace(plaintext, '+', '%2B');
  plaintext = replace(plaintext, ',', '%2C');
  plaintext = replace(plaintext, ';', '%3B');
  plaintext = replace(plaintext, ';', '%40');
  plaintext = replace(plaintext, '[', '%5B');
  plaintext = replace(plaintext, ']', '%5C');
--   plaintext = replace(plaintext, '.', '%2E'); -- their docs say to do this, but leaving it works
  plaintext = replace(plaintext, '"', '%22');
  return plaintext;
end
$$;

Updated to correct the chaining of the hmac