codit/backend/internal/db/gpg-keys.go

package db

import "context"
import "database/sql"
import "errors"
import "strings"
import "time"

import "codit/internal/models"
import "codit/internal/util"

const MaxPersonalGPGKeysPerUser = 10

var ErrGPGKeyAlreadyRegistered = errors.New("gpg key already registered")
var ErrPersonalGPGKeyLimitReached = errors.New("personal gpg key limit reached")

// CreateGPGKey inserts a key with the given scope. For scope 'personal',
// ownerPublicID is the owning user; for 'project', ownerProjectPublicID is the
// owning project; for 'global', both are empty.
func (s *Store) CreateGPGKey(ctx context.Context, item models.GPGKey, ownerPublicID string, ownerProjectPublicID string) (models.GPGKey, error) {
	var now int64
	var tx txExecutor
	var owned bool
	var exists bool
	var personalCount int
	var internalProjectID sql.NullInt64 // nullable numeric project id
	var internalUserID sql.NullInt64 // nullable numeric user id
	var err error

	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil { return item, err }
	}

	if strings.TrimSpace(item.Scope) == "" { item.Scope = models.GPGKeyScopeGlobal }

	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now

	tx, owned, err = s.beginImmediateContext(ctx)
	if err != nil { return item, err }

	if item.Scope == models.GPGKeyScopeGlobal {
		internalProjectID = sql.NullInt64{ Valid: false }
		internalUserID = sql.NullInt64{ Valid: false }
	} else if item.Scope == models.GPGKeyScopePersonal {
		var tmpid int64

		ownerPublicID = strings.TrimSpace(ownerPublicID)
		if ownerPublicID == "" {
			rollbackIfOwned(tx, owned)
			return item, errors.New("owner user is required for personal gpg key")
		}

		err = tx.QueryRow(`SELECT id FROM users WHERE public_id = ?`, ownerPublicID).Scan(&tmpid)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
		internalUserID = sql.NullInt64{ Int64: tmpid, Valid: true }
		internalProjectID = sql.NullInt64{ Valid: false }

		personalCount, err = countPersonalGPGKeys(tx, tmpid)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
		if personalCount >= MaxPersonalGPGKeysPerUser {
			rollbackIfOwned(tx, owned)
			return item, ErrPersonalGPGKeyLimitReached
		}

		exists, err = personalGPGKeyFingerprintExists(tx, item.Fingerprint)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
		if exists {
			rollbackIfOwned(tx, owned)
			return item, ErrGPGKeyAlreadyRegistered
		}
	} else if item.Scope == models.GPGKeyScopeProject {
		var tmpid int64
		ownerProjectPublicID = strings.TrimSpace(ownerProjectPublicID)
		if ownerProjectPublicID == "" {
			rollbackIfOwned(tx, owned)
			return item, errors.New("owner project is required for project gpg key")
		}
		err = tx.QueryRow(`SELECT id FROM projects WHERE public_id = ?`, ownerProjectPublicID).Scan(&tmpid)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
		internalProjectID = sql.NullInt64{ Int64: tmpid, Valid: true }
		internalUserID = sql.NullInt64{ Valid: false }
	} else {
		rollbackIfOwned(tx, owned)
		return item, errors.New("invalid gpg key scope")
	}

	_, err = tx.Exec(`INSERT INTO gpg_keys (public_id, name, fingerprint, key_id, public_key, private_key, scope, owner_user_id, owner_project_id, created_at, updated_at)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		item.ID, strings.TrimSpace(item.Name), item.Fingerprint, item.KeyID, item.PublicKey, item.PrivateKey, item.Scope,
		internalUserID, internalProjectID, item.CreatedAt, item.UpdatedAt)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}

	err = commitIfOwned(tx, owned)
	if err != nil { return item, err }

	return item, nil
}

func scanGPGKey(rows *sql.Rows) (models.GPGKey, error) {
	var item models.GPGKey
	var err error
	err = rows.Scan(&item.ID, &item.Name, &item.Fingerprint, &item.KeyID, &item.PublicKey, &item.Scope, &item.OwnerUserID, &item.OwnerProjectID, &item.OwnerName, &item.OwnerProjectName, &item.OwnerProjectPublicID, &item.UsedByCount, &item.CreatedAt, &item.UpdatedAt)
	return item, err
}

const gpgKeySelectColumns = `k.public_id, k.name, k.fingerprint, k.key_id, k.public_key, k.scope, COALESCE(k.owner_user_id, 0), COALESCE(k.owner_project_id, 0),
	COALESCE(u.username, ''), COALESCE(p.name, ''), COALESCE(p.public_id, ''),
	(SELECT COUNT(*) FROM repos r WHERE r.signing_key_id = k.id), k.created_at, k.updated_at`

// ListReposUsingGPGKey returns the repositories that reference the given key as
// their signing key. Used to block deletion of an in-use key and to show usage.
func (s *Store) ListReposUsingGPGKey(keyPublicID string) ([]models.GPGKeyRepoUsage, error) {
	var rows *sql.Rows
	var items []models.GPGKeyRepoUsage
	var err error

	rows, err = s.Query(`SELECT r.public_id, r.name, p.public_id, p.name
		FROM repos r
		JOIN projects p ON p.id = r.project_id
		WHERE r.signing_key_id = (SELECT id FROM gpg_keys WHERE public_id = ?)
		ORDER BY p.name COLLATE NOCASE, r.name COLLATE NOCASE`, strings.TrimSpace(keyPublicID))
	if err != nil { return nil, err }
	defer rows.Close()

	for rows.Next() {
		var item models.GPGKeyRepoUsage
		err = rows.Scan(&item.RepoID, &item.RepoName, &item.ProjectID, &item.ProjectName)
		if err != nil { return nil, err }
		items = append(items, item)
	}
	return items, rows.Err()
}

// ListGPGKeys returns every key (admin view).
func (s *Store) ListGPGKeys() ([]models.GPGKey, error) {
	var rows *sql.Rows
	var items []models.GPGKey
	var err error

	rows, err = s.Query(`SELECT ` + gpgKeySelectColumns + `
		FROM gpg_keys k LEFT JOIN users u ON u.id = k.owner_user_id LEFT JOIN projects p ON p.id = k.owner_project_id
		ORDER BY k.name COLLATE NOCASE`)
	if err != nil { return nil, err }
	defer rows.Close()

	for rows.Next() {
		var item models.GPGKey
		item, err = scanGPGKey(rows)
		if err != nil { return nil, err }
		items = append(items, item)
	}
	return items, nil
}

// ListPersonalGPGKeys returns the personal keys owned by the given user.
func (s *Store) ListPersonalGPGKeys(userPublicID string) ([]models.GPGKey, error) {
	var rows *sql.Rows
	var items []models.GPGKey
	var err error

	// [NOTE]
	// we may have to add the "u.disabled = 0" to the WHERE clause if disabling a user	should
	// remove the trust from the system.
	rows, err = s.Query(`SELECT `+gpgKeySelectColumns+`
		FROM gpg_keys k LEFT JOIN users u ON u.id = k.owner_user_id LEFT JOIN projects p ON p.id = k.owner_project_id
		WHERE k.scope = ? AND k.owner_user_id = (SELECT id FROM users WHERE public_id = ?)
		ORDER BY k.name COLLATE NOCASE`, models.GPGKeyScopePersonal, strings.TrimSpace(userPublicID))
	if err != nil { return nil, err }
	defer rows.Close()

	for rows.Next() {
		var item models.GPGKey
		item, err = scanGPGKey(rows)
		if err != nil { return nil, err }
		items = append(items, item)
	}
	return items, nil
}

// PersonalGPGKeyFingerprintExists reports whether any personal key already
// carries the given fingerprint. Personal fingerprints are globally unique so a
// key cannot be registered by multiple users.
func (s *Store) PersonalGPGKeyFingerprintExists(fingerprint string) (bool, error) {
	return personalGPGKeyFingerprintExists(s, fingerprint)
}

func countPersonalGPGKeys(exec sqlExecutor, userInternalID int64) (int, error) {
	var n int
	var err error
	err = exec.QueryRow(`SELECT COUNT(*) FROM gpg_keys WHERE scope = ? AND owner_user_id = ?`,
		models.GPGKeyScopePersonal, userInternalID).Scan(&n)
	if err != nil {
		return 0, err
	}
	return n, nil
}

func personalGPGKeyFingerprintExists(exec sqlExecutor, fingerprint string) (bool, error) {
	var n int
	var err error

	err = exec.QueryRow(`SELECT COUNT(*) FROM gpg_keys WHERE scope = ? AND fingerprint = ?`,
		models.GPGKeyScopePersonal, strings.TrimSpace(fingerprint)).Scan(&n)
	if err != nil { return false, err }

	return n > 0, nil
}

// ListSelectableGPGKeys returns keys a project may use to sign: the project's
// own keys plus all global keys.
func (s *Store) ListSelectableGPGKeys(projectPublicID string) ([]models.GPGKey, error) {
	var rows *sql.Rows
	var items []models.GPGKey
	var err error
	rows, err = s.Query(`SELECT `+gpgKeySelectColumns+`
		FROM gpg_keys k LEFT JOIN users u ON u.id = k.owner_user_id LEFT JOIN projects p ON p.id = k.owner_project_id
		WHERE k.scope = ?
			OR (k.scope = ? AND k.owner_project_id = (SELECT id FROM projects WHERE public_id = ?))
		ORDER BY k.scope, k.name COLLATE NOCASE`, models.GPGKeyScopeGlobal, models.GPGKeyScopeProject, strings.TrimSpace(projectPublicID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var item models.GPGKey
		item, err = scanGPGKey(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, nil
}

// ListProjectGPGKeys returns the keys owned by the given project.
func (s *Store) ListProjectGPGKeys(projectPublicID string) ([]models.GPGKey, error) {
	var rows *sql.Rows
	var items []models.GPGKey
	var err error
	rows, err = s.Query(`SELECT `+gpgKeySelectColumns+`
		FROM gpg_keys k LEFT JOIN users u ON u.id = k.owner_user_id LEFT JOIN projects p ON p.id = k.owner_project_id
		WHERE k.scope = ? AND k.owner_project_id = (SELECT id FROM projects WHERE public_id = ?)
		ORDER BY k.name COLLATE NOCASE`, models.GPGKeyScopeProject, strings.TrimSpace(projectPublicID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var item models.GPGKey
		item, err = scanGPGKey(rows)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, nil
}

// GPGKeySelectableByProject reports whether the project may sign with the key:
// the key is either a global key or a project key owned by that project.
func (s *Store) GPGKeySelectableByProject(keyPublicID string, projectPublicID string) (bool, error) {
	var n int
	var err error
	err = s.QueryRow(`SELECT COUNT(*) FROM gpg_keys k
		WHERE k.public_id = ?
		AND (k.scope = ?
			OR (k.scope = ? AND k.owner_project_id = (SELECT id FROM projects WHERE public_id = ?)))`,
		strings.TrimSpace(keyPublicID), models.GPGKeyScopeGlobal, models.GPGKeyScopeProject, strings.TrimSpace(projectPublicID)).Scan(&n)
	if err != nil {
		return false, err
	}
	return n > 0, nil
}

// ListPersonalVerificationKeys returns every registered personal public key
// with its owner's account identity, for verifying commit signatures.
func (s *Store) ListPersonalVerificationKeys() ([]models.PersonalVerificationKey, error) {
	var rows *sql.Rows
	var items []models.PersonalVerificationKey
	var err error
	rows, err = s.Query(`SELECT k.fingerprint, k.public_key, COALESCE(u.username, ''), COALESCE(u.email, '')
		FROM gpg_keys k JOIN users u ON u.id = k.owner_user_id
		WHERE k.scope = ?`, models.GPGKeyScopePersonal)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var item models.PersonalVerificationKey
		err = rows.Scan(&item.Fingerprint, &item.PublicKey, &item.OwnerName, &item.OwnerEmail)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

// ListPersonalVerificationKeysByIssuer returns registered personal keys whose
// primary fingerprint or key ID matches the detached signature issuer metadata.
func (s *Store) ListPersonalVerificationKeysByIssuer(fingerprint string, keyID string) ([]models.PersonalVerificationKey, error) {
	var rows *sql.Rows
	var items []models.PersonalVerificationKey
	var err error

	fingerprint = strings.ToUpper(strings.TrimSpace(fingerprint))
	keyID = strings.ToUpper(strings.TrimSpace(keyID))
	if fingerprint == "" && keyID == "" {
		return nil, nil
	}
	rows, err = s.Query(`SELECT k.fingerprint, k.public_key, COALESCE(u.username, ''), COALESCE(u.email, '')
		FROM gpg_keys k JOIN users u ON u.id = k.owner_user_id
		WHERE k.scope = ?
		AND ((? != '' AND UPPER(k.fingerprint) = ?) OR (? != '' AND UPPER(k.key_id) = ?))`,
		models.GPGKeyScopePersonal, fingerprint, fingerprint, keyID, keyID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var item models.PersonalVerificationKey
		err = rows.Scan(&item.Fingerprint, &item.PublicKey, &item.OwnerName, &item.OwnerEmail)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	return items, rows.Err()
}

// GPGKeyBelongsToProject reports whether the key is a project key owned by the
// given project.
func (s *Store) GPGKeyBelongsToProject(keyPublicID string, projectPublicID string) (bool, error) {
	var n int
	var err error
	err = s.QueryRow(`SELECT COUNT(*) FROM gpg_keys k
		WHERE k.public_id = ? AND k.scope = ?
		AND k.owner_project_id = (SELECT id FROM projects WHERE public_id = ?) AND k.owner_project_id != 0`,
		strings.TrimSpace(keyPublicID), models.GPGKeyScopeProject, strings.TrimSpace(projectPublicID)).Scan(&n)
	if err != nil {
		return false, err
	}
	return n > 0, nil
}

// GetRepoSigningMaterial returns the armored private and public key a repo is
// configured to sign with (via repos.signing_key_id). ok is false when the
// repo has no signing key. repoInternalID is the numeric repos.id.
func (s *Store) GetRepoSigningMaterial(repoInternalID int64) (string, string, bool, error) {
	var priv string
	var pub string
	var err error
	err = s.QueryRow(`SELECT COALESCE(g.private_key, ''), COALESCE(g.public_key, '')
		FROM repos r LEFT JOIN gpg_keys g ON g.id = r.signing_key_id
		WHERE r.id = ?`, repoInternalID).Scan(&priv, &pub)
	if err != nil {
		if err == sql.ErrNoRows {
			return "", "", false, nil
		}
		return "", "", false, err
	}
	return priv, pub, strings.TrimSpace(priv) != "", nil
}

func (s *Store) GetGPGKey(id string) (models.GPGKey, error) {
	var row *sql.Row
	var item models.GPGKey
	var err error
	row = s.QueryRow(`SELECT k.public_id, k.name, k.fingerprint, k.key_id, k.public_key, k.private_key, k.scope, COALESCE(k.owner_user_id, 0), COALESCE(k.owner_project_id, 0),
		COALESCE(u.username, ''), COALESCE(p.name, ''), COALESCE(p.public_id, ''), k.created_at, k.updated_at
		FROM gpg_keys k LEFT JOIN users u ON u.id = k.owner_user_id LEFT JOIN projects p ON p.id = k.owner_project_id
		WHERE k.public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(&item.ID, &item.Name, &item.Fingerprint, &item.KeyID, &item.PublicKey, &item.PrivateKey, &item.Scope, &item.OwnerUserID, &item.OwnerProjectID, &item.OwnerName, &item.OwnerProjectName, &item.OwnerProjectPublicID, &item.CreatedAt, &item.UpdatedAt)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeleteGPGKey(id string) error {
	var err error
	var result sql.Result
	var rowsAffected int64

	result, err = s.Exec(`DELETE FROM gpg_keys WHERE public_id = ?`, strings.TrimSpace(id))
	if err != nil { return err }
	rowsAffected, err = result.RowsAffected()
	if err != nil { return err }
	if rowsAffected <= 0 { return sql.ErrNoRows }
	return err
}

// GPGKeyPersonalOwner reports whether the personal key is owned by the user.
func (s *Store) GPGKeyPersonalOwner(keyPublicID string, userPublicID string) (bool, error) {
	var n int
	var err error
	err = s.QueryRow(`SELECT COUNT(*) FROM gpg_keys k
		WHERE k.public_id = ? AND k.scope = ?
		AND k.owner_user_id = (SELECT id FROM users WHERE public_id = ?) AND k.owner_user_id != 0`,
		strings.TrimSpace(keyPublicID), models.GPGKeyScopePersonal, strings.TrimSpace(userPublicID)).Scan(&n)
	if err != nil {
		return false, err
	}
	return n > 0, nil
}