Skip to content

Add models for SSH signed commits

Brian Williams requested to merge bwill/ssh-signatures/models into master

What does this MR do and why?

Describe in detail what your merge request does and why.

This is part one of a multi-part implementation to support validation of git commits signed by SSH keys (#343879 (closed)). This adds the database tables and models for SSH commit signatures, using the same pattern as the existing gpg_signatures and x509_signatures tables. These will be used to show the verification status on commits which are signed. Existing commit signature specs have also been refactored to use shared examples.

💾 Migrations

⤴ Up

./bin/rails db:migrate RAILS_ENV=test
== 20220518183504 CreateSshSignatures: migrating ==============================
-- create_table(:ssh_signatures, {})
   -> 0.0120s
== 20220518183504 CreateSshSignatures: migrated (0.0121s) =====================

== 20220518183548 AddProjectsRelationToSshSignatures: migrating ===============
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ssh_signatures)
   -> 0.0048s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE ssh_signatures\nADD CONSTRAINT fk_7d2f93996c\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0024s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- execute("ALTER TABLE ssh_signatures VALIDATE CONSTRAINT fk_7d2f93996c;")
   -> 0.0068s
-- execute("RESET statement_timeout")
   -> 0.0011s
== 20220518183548 AddProjectsRelationToSshSignatures: migrated (0.0238s) ======

== 20220520143105 AddKeysRelationToSshSignatures: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ssh_signatures)
   -> 0.0035s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE ssh_signatures\nADD CONSTRAINT fk_f177ea6aa5\nFOREIGN KEY (key_id)\nREFERENCES keys (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0014s
-- execute("ALTER TABLE ssh_signatures VALIDATE CONSTRAINT fk_f177ea6aa5;")
   -> 0.0019s
== 20220520143105 AddKeysRelationToSshSignatures: migrated (0.0122s) ==========

== 20220518183504 CreateSshSignatures: migrating ==============================
-- create_table(:ssh_signatures, {})
   -> 0.0128s
== 20220518183504 CreateSshSignatures: migrated (0.0129s) =====================

== 20220518183548 AddProjectsRelationToSshSignatures: migrating ===============
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ssh_signatures)
   -> 0.0038s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE ssh_signatures\nADD CONSTRAINT fk_7d2f93996c\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0029s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- execute("ALTER TABLE ssh_signatures VALIDATE CONSTRAINT fk_7d2f93996c;")
   -> 0.0063s
-- execute("RESET statement_timeout")
   -> 0.0008s
== 20220518183548 AddProjectsRelationToSshSignatures: migrated (0.0193s) ======

== 20220520143105 AddKeysRelationToSshSignatures: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:ssh_signatures)
   -> 0.0033s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE ssh_signatures\nADD CONSTRAINT fk_f177ea6aa5\nFOREIGN KEY (key_id)\nREFERENCES keys (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0015s
-- execute("ALTER TABLE ssh_signatures VALIDATE CONSTRAINT fk_f177ea6aa5;")
   -> 0.0021s
== 20220520143105 AddKeysRelationToSshSignatures: migrated (0.0117s) ==========

⤵ Down

bin/rails db:migrate:down:main VERSION=20220520143105 RAILS_ENV=test
bin/rails db:migrate:down:main VERSION=20220518183548 RAILS_ENV=test
bin/rails db:migrate:down:main VERSION=20220518183504 RAILS_ENV=test

== 20220520143105 AddKeysRelationToSshSignatures: reverting ===================
-- transaction_open?()
   -> 0.0000s
-- remove_foreign_key(:ssh_signatures, {:column=>:key_id})
   -> 0.0071s
== 20220520143105 AddKeysRelationToSshSignatures: reverted (0.0169s) ==========

== 20220518183548 AddProjectsRelationToSshSignatures: reverting ===============
-- transaction_open?()
   -> 0.0000s
-- remove_foreign_key(:ssh_signatures, {:column=>:project_id})
   -> 0.0090s
== 20220518183548 AddProjectsRelationToSshSignatures: reverted (0.0186s) ======

== 20220518183504 CreateSshSignatures: reverting ==============================
-- drop_table(:ssh_signatures, {})
   -> 0.0043s
== 20220518183504 CreateSshSignatures: reverted (0.0069s) =====================

Table Description ┬─┬ノ( º _ ºノ)

                                           Table "public.ssh_signatures"
       Column        |           Type           | Collation | Nullable |                  Default                   
---------------------+--------------------------+-----------+----------+--------------------------------------------
 id                  | bigint                   |           | not null | nextval('ssh_signatures_id_seq'::regclass)
 created_at          | timestamp with time zone |           | not null | 
 updated_at          | timestamp with time zone |           | not null | 
 project_id          | bigint                   |           | not null | 
 key_id              | bigint                   |           | not null | 
 verification_status | smallint                 |           | not null | 0
 commit_sha          | bytea                    |           | not null | 
Indexes:
    "ssh_signatures_pkey" PRIMARY KEY, btree (id)
    "index_ssh_signatures_on_commit_sha" UNIQUE, btree (commit_sha)
    "index_ssh_signatures_on_key_id" btree (key_id)
    "index_ssh_signatures_on_project_id" btree (project_id)
Foreign-key constraints:
    "fk_7d2f93996c" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_f177ea6aa5" FOREIGN KEY (key_id) REFERENCES keys(id) ON DELETE CASCADE

Compare with the gpg_signatures description, for reference:

                                            Table "public.gpg_signatures"
        Column         |           Type           | Collation | Nullable |                  Default                   
-----------------------+--------------------------+-----------+----------+--------------------------------------------
 id                    | integer                  |           | not null | nextval('gpg_signatures_id_seq'::regclass)
 created_at            | timestamp with time zone |           | not null | 
 updated_at            | timestamp with time zone |           | not null | 
 project_id            | integer                  |           |          | 
 gpg_key_id            | integer                  |           |          | 
 commit_sha            | bytea                    |           |          | 
 gpg_key_primary_keyid | bytea                    |           |          | 
 gpg_key_user_name     | text                     |           |          | 
 gpg_key_user_email    | text                     |           |          | 
 verification_status   | smallint                 |           | not null | 0
 gpg_key_subkey_id     | integer                  |           |          | 
Indexes:
    "gpg_signatures_pkey" PRIMARY KEY, btree (id)
    "index_gpg_signatures_on_commit_sha" UNIQUE, btree (commit_sha)
    "index_gpg_signatures_on_gpg_key_id" btree (gpg_key_id)
    "index_gpg_signatures_on_gpg_key_primary_keyid" btree (gpg_key_primary_keyid)
    "index_gpg_signatures_on_gpg_key_subkey_id" btree (gpg_key_subkey_id)
    "index_gpg_signatures_on_project_id" btree (project_id)
Foreign-key constraints:
    "fk_rails_11ae8cb9a7" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    "fk_rails_19d4f1c6f9" FOREIGN KEY (gpg_key_subkey_id) REFERENCES gpg_key_subkeys(id) ON DELETE SET NULL
    "fk_rails_c97176f5f7" FOREIGN KEY (gpg_key_id) REFERENCES gpg_keys(id) ON DELETE SET NULL

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Brian Williams

Merge request reports