bin/return-assignment: save signing key in database, too

1 files changed, 56 insertions, 6 deletions

diff --git a/bin/return-assignment b/bin/return-assignment
index 8242215..4efe11a 100755
--- a/bin/return-assignment
+++ b/bin/return-assignment
@@ -724,6 +724,49 @@ if [ -z "$(
   exit 3
 fi
 
+# get the fingerprints of the signing keys for the sent packages
+printf '%s\n' "${signatures}" \
+| sed -n '
+  s/^\S\+ //
+  /^file /,/^TRUST_FULLY / {
+    /^file / p
+    /^KEY_CONSIDERED / p
+  }
+' \
+| sed '
+  /^file / {
+    N
+    s/^file \(\S\+\) KEY_CONSIDERED \([0-9A-F]\{40\}\) .*$/\1\t\2/
+    t
+  }
+  d
+' \
+| sort -k2,2 \
+> "${tmp_dir}/signing-keys"
+
+# shellcheck disable=SC2016
+{
+  printf 'SELECT '
+  printf '`gpg_keys`.`id`,'
+  printf '`gpg_keys`.`fingerprint`n'
+  printf ' FROM `gpg_keys`;\n'
+} \
+| sort -k2,2 \
+| join -1 2 -2 2 -o 1.1,2.1 -a 2 -e 'NULL' - "${tmp_dir}/signing-keys" \
+| sort -k2,2 \
+| sponge "${tmp_dir}/signing-keys"
+
+if grep -q '^NULL ' "${tmp_dir}/signing-keys"; then
+  >&2 echo 'Signing key is unknown to the buildmaster'"'"'s mysql database:'
+  printf 'Your buildslave "%s" uploaded a package with a signature of a key unknown to the mysql database:\n' \
+    "${slave}" | \
+    irc_say "${operator}"
+  irc_say "${operator}" 'copy' \
+  <"${tmp_dir}/signing-keys" \
+  >&2
+  exit 3
+fi
+
 # check if the package maintainer is set
 errors=$(
   find . -maxdepth 1 -regextype sed \
@@ -874,17 +917,23 @@ if [ -n "${errors}" ]; then
   exit 1
 fi
 
+join -1 2 -2 2 -o 1.1,1.2,2.1 "${tmp_dir}/package-ids" "${tmp_dir}/signing-keys" \
+| sponge "${tmp_dir}/package-ids"
+
 mysql_load_min_and_max_versions
 
-while read -r package_id package_name; do
+while read -r package_id package_name key_id; do
   # move namcap.logs
   mv \
     "${tmp_dir}/${package_name}-namcap.log.gz" \
     "${build_log_directory}/success/"
   # generate checksum
-  sha512sum "${tmp_dir}/${package_name}" | \
-    awk '{print "'"${package_id}"'\t" $1}' >> \
-    "${tmp_dir}/sha512sums"
+  sha512sum "${tmp_dir}/${package_name}" \
+  | awk '{print "'"${package_id}"'\t" $1}' \
+  | sed '
+    s/$/\t'"${key_id}"'/
+  ' \
+  >> "${tmp_dir}/sha512sums"
   # generate list of required/provided libraries
   for lib in 'provides' 'needs'; do
     zcat "${tmp_dir}/${package_name}.so.${lib}.gz" | \
@@ -1045,13 +1094,14 @@ cut -d' ' -f4,5 "${tmp_dir}/repository-ids" | \
   printf '} <<END_OF_MYSQL_QUERY\n'
 
   # insert checksums into database
-  printf 'CREATE TEMPORARY TABLE `pkg_hashes` (`pkgid` BIGINT, `sha512sum` VARCHAR(128));\n'
+  printf 'CREATE TEMPORARY TABLE `pkg_hashes` (`pkgid` BIGINT, `sha512sum` VARCHAR(128), `key_ids` BIGINT);\n'
   printf 'LOAD DATA LOCAL INFILE "%s" INTO TABLE `pkg_hashes`;\n' \
     "${tmp_dir}/sha512sums"
   printf 'UPDATE `binary_packages`'
   printf ' JOIN `pkg_hashes`'
   printf ' ON `pkg_hashes`.`pkgid`=`binary_packages`.`id`'
-  printf ' SET `binary_packages`.`sha512sum`=`pkg_hashes`.`sha512sum`;\n'
+  printf ' SET `binary_packages`.`sha512sum`=`pkg_hashes`.`sha512sum`,'
+  printf '`binary_packages`.`signing_key`=`pkg_hashes`.`key_id`;\n'
   printf 'COMMIT;\n'
 
   # insert provided/needed libraries into database