0067.yml

version: 67
description: add secure storage of object hashes
migrationScript: |-
  begin
    create table object_hashes (
      name text not null
        references objects (name)
        on delete cascade,
      algorithm text not null,
      hash text not null
    );

    alter table object_hashes add primary key (name, algorithm);

    grant select, insert, update, delete on object_hashes to $db_user_prefix$_object;
  end
downgradeScript: |-
  begin
    revoke select, insert, update, delete on object_hashes from $db_user_prefix$_object;
    drop table object_hashes;
  end
methods:
  add_object_hashes:
    description: |-
      Add the given hashes, of the form `{algorithm: hash}`, to the named
      object.  The named object must already exist.  If any of the given
      algorithms already exist in the table, then the hash must match exactly.
      This function raises a CHECK_VIOLATION if the object's upload has been
      finished (upload_id is null) or FOREIGN_KEY_VIOLATION if the object does
      not exist.
    mode: write
    serviceName: object
    args: name_in text, hashes_in jsonb
    returns: void
    body: |-
      declare
        item record;
        object record;
      begin
        select objects.name, objects.upload_id
          into object
          from objects
          where name = name_in;
        raise log 'object %', object;
        if not found then
          raise exception 'object does not exist' using errcode = 'foreign_key_violation';
        end if;
        if object.upload_id is null then
          raise exception 'object upload is already finished' using errcode = 'check_violation';
        end if;

        -- insert each hash individually; in this case at least all hashes
        -- end up inserted in the same transaction.  On conflict, we verify
        -- that the hash value matches.
        for item in
          select
            name_in as name,
            key as algorithm,
            value as hash
          from jsonb_each_text(hashes_in)
        loop
          begin
            insert
            into object_hashes (name, algorithm, hash)
            values (item.name, item.algorithm, item.hash);
          exception
            when UNIQUE_VIOLATION then
              perform 1
              from object_hashes
              where
                name = item.name and
                algorithm = item.algorithm and
                hash = item.hash;
              if not found then
                raise exception 'object hash already exists with different value' using errcode = 'unique_violation';
              end if;
          end;
        end loop;
      end
  get_object_hashes:
    description: |-
      Get all hashes for the named object.  If the given object has no hashes,
      or doesn't exist, this function returns an empty result.
    mode: read
    serviceName: object
    args: name_in text
    returns: table ( algorithm text, hash text )
    body: |-
      begin
        return query
          select
            object_hashes.algorithm,
            object_hashes.hash
          from object_hashes
          where name = name_in
          order by algorithm;
      end