hyung-hwan
12994b2dfa
instead added a gpg key page under a personal account, not stroing the private key, but stroing public key only
37 lines
2.0 KiB
SQL
37 lines
2.0 KiB
SQL
-- GPG signing keys. Each key is scoped as one of:
|
|
-- 'personal' — owned by a user (owner_user_id); for verifying that user's work
|
|
-- 'project' — owned by a project (owner_project_id); signs that project's repos
|
|
-- 'global' — organization-wide; selectable by any project
|
|
-- owner_user_id / owner_project_id are NULL when not applicable (global keys
|
|
-- have both NULL). They are FKs with ON DELETE CASCADE so deleting the owning
|
|
-- user or project removes the key (and its private material) automatically.
|
|
CREATE TABLE IF NOT EXISTS gpg_keys (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
public_id TEXT NOT NULL UNIQUE,
|
|
name TEXT NOT NULL,
|
|
fingerprint TEXT NOT NULL DEFAULT '',
|
|
key_id TEXT NOT NULL DEFAULT '',
|
|
public_key TEXT NOT NULL DEFAULT '',
|
|
private_key TEXT NOT NULL DEFAULT '',
|
|
scope TEXT NOT NULL DEFAULT 'global',
|
|
owner_user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
owner_project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
|
|
created_at INTEGER NOT NULL DEFAULT 0,
|
|
updated_at INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
-- Lookup indexes. The composite (scope, owner_*) indexes also serve scope-only
|
|
-- lookups via their leftmost column:
|
|
-- scope='global' -> either index's prefix
|
|
-- scope='personal' AND owner_user_id=? -> idx_gpg_keys_scope_user
|
|
-- scope='project' AND owner_project_id=? -> idx_gpg_keys_scope_project
|
|
CREATE INDEX IF NOT EXISTS idx_gpg_keys_scope_user ON gpg_keys(scope, owner_user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_gpg_keys_scope_project ON gpg_keys(scope, owner_project_id);
|
|
-- Fast verification lookup by fingerprint, and dedup, for personal public keys.
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_gpg_keys_personal_fingerprint ON gpg_keys(fingerprint) WHERE scope = 'personal';
|
|
|
|
-- A repo may reference a signing key used to sign its commits/tags (git) or
|
|
-- packages/metadata (rpm). NULL means no signing key; the reference is cleared
|
|
-- automatically if the referenced key is deleted.
|
|
ALTER TABLE repos ADD COLUMN signing_key_id INTEGER REFERENCES gpg_keys(id) ON DELETE SET NULL;
|