codit/backend/internal/db/pki-client-profiles.go

package db

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

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

func (s *Store) ListPKIClientProfiles(targetType string, targetID string) ([]models.PKIClientProfile, error) {
	var rows *sql.Rows
	var err error
	var items []models.PKIClientProfile
	var item models.PKIClientProfile

	targetType = strings.TrimSpace(targetType)
	targetID = strings.TrimSpace(targetID)
	if targetType != "" && targetID != "" {
		rows, err = s.Query(`SELECT DISTINCT
			p.public_id,
			p.name,
			ca.public_id,
			ca.name,
			p.subject_organization,
			p.san_uri_prefix,
			p.allow_server_auth,
			p.authz_permissions,
			p.authz_scope,
			p.default_valid_seconds,
			p.max_valid_seconds,
			p.enabled,
			COALESCE(u.public_id, ''),
			COALESCE(u.username, ''),
			p.created_at,
			p.updated_at
			FROM pki_client_profiles p
			JOIN pki_cas ca ON ca.id = p.ca_id
			LEFT JOIN users u ON u.id = p.created_by_user_id
			JOIN pki_client_profile_targets t ON t.profile_id = p.id
			LEFT JOIN users tu ON t.target_type = 'user' AND tu.id = t.target_id
			LEFT JOIN user_groups tg ON t.target_type = 'group' AND tg.id = t.target_id
			WHERE t.target_type = ?
			  AND ((t.target_type = 'user' AND tu.public_id = ?) OR (t.target_type = 'group' AND tg.public_id = ?))
			ORDER BY p.name`, targetType, targetID, targetID)
	} else {
		rows, err = s.Query(`SELECT
			p.public_id,
			p.name,
			ca.public_id,
			ca.name,
			p.subject_organization,
			p.san_uri_prefix,
			p.allow_server_auth,
			p.authz_permissions,
			p.authz_scope,
			p.default_valid_seconds,
			p.max_valid_seconds,
			p.enabled,
			COALESCE(u.public_id, ''),
			COALESCE(u.username, ''),
			p.created_at,
			p.updated_at
			FROM pki_client_profiles p
			JOIN pki_cas ca ON ca.id = p.ca_id
			LEFT JOIN users u ON u.id = p.created_by_user_id
			ORDER BY p.name`)
	}
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(
			&item.ID,
			&item.Name,
			&item.CAID,
			&item.CAName,
			&item.SubjectOrganization,
			&item.SANURIPrefix,
			&item.AllowServerAuth,
			&item.AuthzPermissions,
			&item.AuthzScope,
			&item.DefaultValidSeconds,
			&item.MaxValidSeconds,
			&item.Enabled,
			&item.CreatedByUserID,
			&item.CreatedByUsername,
			&item.CreatedAt,
			&item.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		item.Targets, err = s.listPKIClientProfileTargets(item.ID)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) ListActivePKIClientProfilesForUser(userID string) ([]models.PKIClientProfile, error) {
	var rows *sql.Rows
	var err error
	var items []models.PKIClientProfile
	var item models.PKIClientProfile

	rows, err = s.Query(`SELECT DISTINCT
		p.public_id,
		p.name,
		ca.public_id,
		ca.name,
		p.subject_organization,
		p.san_uri_prefix,
		p.allow_server_auth,
		p.authz_permissions,
		p.authz_scope,
		p.default_valid_seconds,
		p.max_valid_seconds,
		p.enabled,
		COALESCE(u.public_id, ''),
		COALESCE(u.username, ''),
		p.created_at,
		p.updated_at
		FROM pki_client_profiles p
		JOIN pki_cas ca ON ca.id = p.ca_id
		LEFT JOIN users u ON u.id = p.created_by_user_id
		JOIN pki_client_profile_targets t ON t.profile_id = p.id
		LEFT JOIN user_groups ug ON t.target_type = 'group' AND ug.id = t.target_id
		LEFT JOIN user_group_members gm ON t.target_type = 'group' AND gm.group_id = ug.id
		LEFT JOIN users gu ON gm.user_id = gu.id
		LEFT JOIN users tu ON t.target_type = 'user' AND tu.id = t.target_id
		WHERE p.enabled = 1
			AND ca.status = ?
			AND (
				(t.target_type = 'user' AND tu.public_id = ?)
				OR
				(t.target_type = 'group' AND ug.disabled = 0 AND (ug.scope = 'all_users' OR gu.public_id = ?))
		)
		ORDER BY p.name`,
		models.PKIStatusActive,
		strings.TrimSpace(userID),
		strings.TrimSpace(userID),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(
			&item.ID,
			&item.Name,
			&item.CAID,
			&item.CAName,
			&item.SubjectOrganization,
			&item.SANURIPrefix,
			&item.AllowServerAuth,
			&item.AuthzPermissions,
			&item.AuthzScope,
			&item.DefaultValidSeconds,
			&item.MaxValidSeconds,
			&item.Enabled,
			&item.CreatedByUserID,
			&item.CreatedByUsername,
			&item.CreatedAt,
			&item.UpdatedAt,
		)
		if err != nil {
			return nil, err
		}
		item.Targets, err = s.listPKIClientProfileTargets(item.ID)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) GetPKIClientProfile(id string) (models.PKIClientProfile, error) {
	var row *sql.Row
	var item models.PKIClientProfile
	var err error

	row = s.QueryRow(`SELECT
		p.public_id,
		p.name,
		ca.public_id,
		ca.name,
		p.subject_organization,
		p.san_uri_prefix,
		p.allow_server_auth,
		p.authz_permissions,
		p.authz_scope,
		p.default_valid_seconds,
		p.max_valid_seconds,
		p.enabled,
		COALESCE(u.public_id, ''),
		COALESCE(u.username, ''),
		p.created_at,
		p.updated_at
		FROM pki_client_profiles p
		JOIN pki_cas ca ON ca.id = p.ca_id
		LEFT JOIN users u ON u.id = p.created_by_user_id
		WHERE p.public_id = ?`, strings.TrimSpace(id))
	err = row.Scan(
		&item.ID,
		&item.Name,
		&item.CAID,
		&item.CAName,
		&item.SubjectOrganization,
		&item.SANURIPrefix,
		&item.AllowServerAuth,
		&item.AuthzPermissions,
		&item.AuthzScope,
		&item.DefaultValidSeconds,
		&item.MaxValidSeconds,
		&item.Enabled,
		&item.CreatedByUserID,
		&item.CreatedByUsername,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if err != nil {
		return item, err
	}
	item.Targets, err = s.listPKIClientProfileTargets(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) GetActivePKIClientProfileForUser(profileID string, userID string) (models.PKIClientProfile, error) {
	var row *sql.Row
	var item models.PKIClientProfile
	var err error

	row = s.QueryRow(`SELECT DISTINCT
		p.public_id,
		p.name,
		ca.public_id,
		ca.name,
		p.subject_organization,
		p.san_uri_prefix,
		p.allow_server_auth,
		p.authz_permissions,
		p.authz_scope,
		p.default_valid_seconds,
		p.max_valid_seconds,
		p.enabled,
		COALESCE(u.public_id, ''),
		COALESCE(u.username, ''),
		p.created_at,
		p.updated_at
		FROM pki_client_profiles p
		JOIN pki_cas ca ON ca.id = p.ca_id
		LEFT JOIN users u ON u.id = p.created_by_user_id
		JOIN pki_client_profile_targets t ON t.profile_id = p.id
		LEFT JOIN user_groups ug ON t.target_type = 'group' AND ug.id = t.target_id
		LEFT JOIN user_group_members gm ON t.target_type = 'group' AND gm.group_id = ug.id
		LEFT JOIN users gu ON gm.user_id = gu.id
		LEFT JOIN users tu ON t.target_type = 'user' AND tu.id = t.target_id
		WHERE p.public_id = ?
			AND p.enabled = 1
			AND ca.status = ?
			AND (
				(t.target_type = 'user' AND tu.public_id = ?)
				OR
				(t.target_type = 'group' AND ug.disabled = 0 AND (ug.scope = 'all_users' OR gu.public_id = ?))
			)`,
		strings.TrimSpace(profileID),
		models.PKIStatusActive,
		strings.TrimSpace(userID),
		strings.TrimSpace(userID),
	)
	err = row.Scan(
		&item.ID,
		&item.Name,
		&item.CAID,
		&item.CAName,
		&item.SubjectOrganization,
		&item.SANURIPrefix,
		&item.AllowServerAuth,
		&item.AuthzPermissions,
		&item.AuthzScope,
		&item.DefaultValidSeconds,
		&item.MaxValidSeconds,
		&item.Enabled,
		&item.CreatedByUserID,
		&item.CreatedByUsername,
		&item.CreatedAt,
		&item.UpdatedAt,
	)
	if err != nil {
		return item, err
	}
	item.Targets, err = s.listPKIClientProfileTargets(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) CreatePKIClientProfile(ctx context.Context, item models.PKIClientProfile) (models.PKIClientProfile, error) {
	var tx txExecutor
	var owned bool
	var err error
	var now int64
	var i int
	var target models.PKIClientProfileTarget

	if strings.TrimSpace(item.Name) == "" {
		return item, errors.New("name is required")
	}
	if strings.TrimSpace(item.CAID) == "" {
		return item, errors.New("ca_id is required")
	}
	if len(item.Targets) == 0 {
		return item, errors.New("at least one target is required")
	}
	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	now = time.Now().UTC().Unix()
	item.CreatedAt = now
	item.UpdatedAt = now
	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return item, err
	}
	_, err = tx.Exec(`INSERT INTO pki_client_profiles (
		public_id,
		name,
		ca_id,
		subject_organization,
		san_uri_prefix,
		allow_server_auth,
		authz_permissions,
		authz_scope,
		default_valid_seconds,
		max_valid_seconds,
		enabled,
		created_by_user_id,
		created_at,
		updated_at
	) VALUES (
		?,
		?,
		(SELECT id FROM pki_cas WHERE public_id = ?),
		?,
		?,
		?,
		?,
		?,
		?,
		?,
		?,
		CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM users WHERE public_id = ?) END,
		?,
		?
	)`,
		strings.TrimSpace(item.ID),
		strings.TrimSpace(item.Name),
		strings.TrimSpace(item.CAID),
		strings.TrimSpace(item.SubjectOrganization),
		strings.TrimSpace(item.SANURIPrefix),
		item.AllowServerAuth,
		strings.TrimSpace(item.AuthzPermissions),
		strings.TrimSpace(item.AuthzScope),
		item.DefaultValidSeconds,
		item.MaxValidSeconds,
		item.Enabled,
		strings.TrimSpace(item.CreatedByUserID),
		strings.TrimSpace(item.CreatedByUserID),
		item.CreatedAt,
		item.UpdatedAt,
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	for i = 0; i < len(item.Targets); i++ {
		target = item.Targets[i]
		err = insertPKIClientProfileTargetTx(tx, item.ID, target.TargetType, target.TargetID, now)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return item, err
	}
	item, err = s.GetPKIClientProfile(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UpdatePKIClientProfile(ctx context.Context, item models.PKIClientProfile) (models.PKIClientProfile, error) {
	var tx txExecutor
	var owned bool
	var err error
	var now int64
	var i int
	var target models.PKIClientProfileTarget

	if strings.TrimSpace(item.ID) == "" {
		return item, errors.New("id is required")
	}
	if strings.TrimSpace(item.Name) == "" {
		return item, errors.New("name is required")
	}
	if strings.TrimSpace(item.CAID) == "" {
		return item, errors.New("ca_id is required")
	}
	if len(item.Targets) == 0 {
		return item, errors.New("at least one target is required")
	}
	now = time.Now().UTC().Unix()
	item.UpdatedAt = now
	tx, owned, err = s.beginContext(ctx)
	if err != nil {
		return item, err
	}
	_, err = tx.Exec(`UPDATE pki_client_profiles
		SET
			name = ?,
			ca_id = (SELECT id FROM pki_cas WHERE public_id = ?),
			subject_organization = ?,
			san_uri_prefix = ?,
			allow_server_auth = ?,
			authz_permissions = ?,
			authz_scope = ?,
			default_valid_seconds = ?,
			max_valid_seconds = ?,
			enabled = ?,
			updated_at = ?
		WHERE public_id = ?`,
		strings.TrimSpace(item.Name),
		strings.TrimSpace(item.CAID),
		strings.TrimSpace(item.SubjectOrganization),
		strings.TrimSpace(item.SANURIPrefix),
		item.AllowServerAuth,
		strings.TrimSpace(item.AuthzPermissions),
		strings.TrimSpace(item.AuthzScope),
		item.DefaultValidSeconds,
		item.MaxValidSeconds,
		item.Enabled,
		item.UpdatedAt,
		strings.TrimSpace(item.ID),
	)
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	_, err = tx.Exec(`DELETE FROM pki_client_profile_targets
		WHERE profile_id = (SELECT id FROM pki_client_profiles WHERE public_id = ?)`, strings.TrimSpace(item.ID))
	if err != nil {
		rollbackIfOwned(tx, owned)
		return item, err
	}
	for i = 0; i < len(item.Targets); i++ {
		target = item.Targets[i]
		err = insertPKIClientProfileTargetTx(tx, item.ID, target.TargetType, target.TargetID, now)
		if err != nil {
			rollbackIfOwned(tx, owned)
			return item, err
		}
	}
	err = commitIfOwned(tx, owned)
	if err != nil {
		return item, err
	}
	item, err = s.GetPKIClientProfile(item.ID)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) DeletePKIClientProfile(id string) error {
	var err error
	_, err = s.Exec(`DELETE FROM pki_client_profiles WHERE public_id = ?`, strings.TrimSpace(id))
	return err
}

func (s *Store) ListPKIClientIssuancesForUser(userID string) ([]models.PKIClientIssuance, error) {
	var rows *sql.Rows
	var err error
	var items []models.PKIClientIssuance
	var item models.PKIClientIssuance

	rows, err = s.Query(`SELECT
		i.public_id,
		COALESCE(c.public_id, ''),
		COALESCE(u.public_id, ''),
		i.username,
		COALESCE(p.public_id, ''),
		i.profile_name,
		i.serial_hex,
		i.common_name,
		i.san_uri,
		i.authz_permissions,
		i.authz_scope,
		i.not_before,
		i.not_after,
		COALESCE(c.status, ?),
		COALESCE(c.revoked_at, 0),
		COALESCE(c.revocation_reason, ''),
		i.created_at
		FROM pki_client_issuances i
		LEFT JOIN pki_certs c ON c.id = i.cert_id
		LEFT JOIN users u ON u.id = i.user_id
		LEFT JOIN pki_client_profiles p ON p.id = i.profile_id
		WHERE u.public_id = ?
		ORDER BY i.created_at DESC`, models.PKIStatusDeleted, strings.TrimSpace(userID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(
			&item.ID,
			&item.CertID,
			&item.UserID,
			&item.Username,
			&item.ProfileID,
			&item.ProfileName,
			&item.SerialHex,
			&item.CommonName,
			&item.SANURI,
			&item.AuthzPermissions,
			&item.AuthzScope,
			&item.NotBefore,
			&item.NotAfter,
			&item.Status,
			&item.RevokedAt,
			&item.RevocationReason,
			&item.CreatedAt,
		)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) CreatePKIClientIssuance(item models.PKIClientIssuance) (models.PKIClientIssuance, error) {
	var err error
	if strings.TrimSpace(item.ID) == "" {
		item.ID, err = util.NewID()
		if err != nil {
			return item, err
		}
	}
	if item.CreatedAt == 0 {
		item.CreatedAt = time.Now().UTC().Unix()
	}
	_, err = s.Exec(`INSERT INTO pki_client_issuances (
		public_id,
		cert_id,
		user_id,
		username,
		profile_id,
		profile_name,
		serial_hex,
		common_name,
		san_uri,
		authz_permissions,
		authz_scope,
		not_before,
		not_after,
		created_at
	) VALUES (?, (SELECT id FROM pki_certs WHERE public_id = ?), (SELECT id FROM users WHERE public_id = ?), ?, (SELECT id FROM pki_client_profiles WHERE public_id = ?), ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		strings.TrimSpace(item.ID),
		strings.TrimSpace(item.CertID),
		strings.TrimSpace(item.UserID),
		strings.TrimSpace(item.Username),
		strings.TrimSpace(item.ProfileID),
		strings.TrimSpace(item.ProfileName),
		strings.TrimSpace(item.SerialHex),
		strings.TrimSpace(item.CommonName),
		strings.TrimSpace(item.SANURI),
		strings.TrimSpace(item.AuthzPermissions),
		strings.TrimSpace(item.AuthzScope),
		item.NotBefore,
		item.NotAfter,
		item.CreatedAt,
	)
	if err != nil {
		return item, err
	}
	return item, nil
}

func (s *Store) UserOwnsPKIClientCert(userID string, certID string) (bool, error) {
	var row *sql.Row
	var count int
	var err error

	row = s.QueryRow(`SELECT COUNT(*) FROM pki_client_issuances
		WHERE user_id = (SELECT id FROM users WHERE public_id = ?)
		  AND cert_id = (SELECT id FROM pki_certs WHERE public_id = ?)`,
		strings.TrimSpace(userID),
		strings.TrimSpace(certID),
	)
	err = row.Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

func (s *Store) listPKIClientProfileTargets(profileID string) ([]models.PKIClientProfileTarget, error) {
	var rows *sql.Rows
	var err error
	var items []models.PKIClientProfileTarget
	var item models.PKIClientProfileTarget

	rows, err = s.Query(`SELECT
		p.public_id,
		t.target_type,
		CASE WHEN t.target_type = 'user' THEN COALESCE(u.public_id, '') ELSE COALESCE(g.public_id, '') END,
		CASE
			WHEN t.target_type = 'user' THEN COALESCE(CASE WHEN u.display_name != '' THEN u.display_name || ' (' || u.username || ')' ELSE u.username END, '')
			ELSE COALESCE(g.name, '')
		END,
		CASE
			WHEN t.target_type = 'user' THEN CASE WHEN u.public_id IS NOT NULL AND u.disabled = 0 THEN 1 ELSE 0 END
			ELSE CASE WHEN g.public_id IS NOT NULL AND g.disabled = 0 THEN 1 ELSE 0 END
		END,
		t.created_at
		FROM pki_client_profile_targets t
		JOIN pki_client_profiles p ON p.id = t.profile_id
		LEFT JOIN users u ON t.target_type = 'user' AND u.id = t.target_id
		LEFT JOIN user_groups g ON t.target_type = 'group' AND g.id = t.target_id
		WHERE p.public_id = ?
		ORDER BY t.target_type, target_id`, strings.TrimSpace(profileID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	for rows.Next() {
		err = rows.Scan(
			&item.ProfileID,
			&item.TargetType,
			&item.TargetID,
			&item.TargetName,
			&item.TargetActive,
			&item.CreatedAt,
		)
		if err != nil {
			return nil, err
		}
		items = append(items, item)
	}
	err = rows.Err()
	if err != nil {
		return nil, err
	}
	return items, nil
}

func insertPKIClientProfileTargetTx(tx txExecutor, profileID string, targetType string, targetID string, createdAt int64) error {
	var err error
	targetType = strings.ToLower(strings.TrimSpace(targetType))
	targetID = strings.TrimSpace(targetID)
	if targetType != "user" && targetType != "group" {
		return errors.New("target_type must be user or group")
	}
	if targetID == "" {
		return errors.New("target_id is required")
	}
	_, err = tx.Exec(`INSERT INTO pki_client_profile_targets (
		profile_id,
		target_type,
		target_id,
		created_at
	) VALUES (
		(SELECT id FROM pki_client_profiles WHERE public_id = ?),
		?,
		CASE
			WHEN ? = 'user' THEN (SELECT id FROM users WHERE public_id = ?)
			WHEN ? = 'group' THEN (SELECT id FROM user_groups WHERE public_id = ?)
			ELSE NULL
		END,
		?
	)`,
		strings.TrimSpace(profileID),
		targetType,
		targetType,
		targetID,
		targetType,
		targetID,
		createdAt,
	)
	return err
}