355 lines
10 KiB
Go
355 lines
10 KiB
Go
package db
|
|
|
|
import "database/sql"
|
|
import "strings"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
import "codit/internal/util"
|
|
|
|
func (s *Store) ListPKICAs() ([]models.PKICA, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var items []models.PKICA
|
|
var item models.PKICA
|
|
rows, err = s.DB.Query(`SELECT c.public_id, c.name, COALESCE(p.public_id, ''), c.is_root, c.cert_pem, c.key_pem, c.serial_counter, c.status, c.created_at, c.updated_at
|
|
FROM pki_cas c
|
|
LEFT JOIN pki_cas p ON p.id = c.parent_ca_id
|
|
ORDER BY c.name`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&item.ID, &item.Name, &item.ParentCAID, &item.IsRoot, &item.CertPEM, &item.KeyPEM, &item.SerialCounter, &item.Status, &item.CreatedAt, &item.UpdatedAt)
|
|
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) GetPKICA(id string) (models.PKICA, error) {
|
|
var row *sql.Row
|
|
var item models.PKICA
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT c.public_id, c.name, COALESCE(p.public_id, ''), c.is_root, c.cert_pem, c.key_pem, c.serial_counter, c.status, c.created_at, c.updated_at
|
|
FROM pki_cas c
|
|
LEFT JOIN pki_cas p ON p.id = c.parent_ca_id
|
|
WHERE c.public_id = ?`, id)
|
|
err = row.Scan(&item.ID, &item.Name, &item.ParentCAID, &item.IsRoot, &item.CertPEM, &item.KeyPEM, &item.SerialCounter, &item.Status, &item.CreatedAt, &item.UpdatedAt)
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
return item, nil
|
|
}
|
|
|
|
func (s *Store) UpdatePKICAName(id string, name string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE pki_cas SET name = ?, updated_at = ? WHERE public_id = ?`, name, time.Now().UTC().Unix(), id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) CreatePKICA(item models.PKICA) (models.PKICA, error) {
|
|
var id string
|
|
var now int64
|
|
var err error
|
|
if item.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
item.ID = id
|
|
}
|
|
if item.SerialCounter <= 0 {
|
|
item.SerialCounter = 1
|
|
}
|
|
if item.Status == "" {
|
|
item.Status = "active"
|
|
}
|
|
now = time.Now().UTC().Unix()
|
|
item.CreatedAt = now
|
|
item.UpdatedAt = now
|
|
_, err = s.DB.Exec(`INSERT INTO pki_cas (public_id, name, parent_ca_id, is_root, cert_pem, key_pem, serial_counter, status, created_at, updated_at)
|
|
VALUES (?, ?, CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM pki_cas WHERE public_id = ?) END, ?, ?, ?, ?, ?, ?, ?)`,
|
|
item.ID, item.Name, item.ParentCAID, item.ParentCAID, item.IsRoot, item.CertPEM, item.KeyPEM, item.SerialCounter, item.Status, item.CreatedAt, item.UpdatedAt)
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
return item, nil
|
|
}
|
|
|
|
func (s *Store) CountPKICAChildren(id string) (int, error) {
|
|
var row *sql.Row
|
|
var count int
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT COUNT(*)
|
|
FROM pki_cas c
|
|
JOIN pki_cas p ON p.id = c.parent_ca_id
|
|
WHERE p.public_id = ?`, id)
|
|
err = row.Scan(&count)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
return count, nil
|
|
}
|
|
|
|
func (s *Store) CountPKICertsByCA(id string) (int, error) {
|
|
var row *sql.Row
|
|
var count int
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT COUNT(*)
|
|
FROM pki_certs c
|
|
JOIN pki_cas ca ON ca.id = c.ca_id
|
|
WHERE ca.public_id = ?`, id)
|
|
err = row.Scan(&count)
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
return count, nil
|
|
}
|
|
|
|
func (s *Store) DeletePKICA(id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM pki_cas WHERE public_id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeletePKICASubtree(id string) error {
|
|
var tx *sql.Tx
|
|
var rows *sql.Rows
|
|
var err error
|
|
var itemID string
|
|
var parentID string
|
|
var parentByID map[string]string
|
|
var pending []string
|
|
var current string
|
|
var i int
|
|
var j int
|
|
var target string
|
|
var toDelete []string
|
|
var contains bool
|
|
parentByID = map[string]string{}
|
|
tx, err = s.DB.Begin()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
rows, err = tx.Query(`SELECT c.public_id, COALESCE(p.public_id, '')
|
|
FROM pki_cas c
|
|
LEFT JOIN pki_cas p ON p.id = c.parent_ca_id`)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&itemID, &parentID)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
parentByID[itemID] = parentID
|
|
}
|
|
err = rows.Err()
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
pending = append(pending, id)
|
|
for len(pending) > 0 {
|
|
current = pending[0]
|
|
pending = pending[1:]
|
|
contains = false
|
|
for i = 0; i < len(toDelete); i++ {
|
|
if toDelete[i] == current {
|
|
contains = true
|
|
break
|
|
}
|
|
}
|
|
if !contains {
|
|
toDelete = append(toDelete, current)
|
|
}
|
|
for target = range parentByID {
|
|
if parentByID[target] == current {
|
|
pending = append(pending, target)
|
|
}
|
|
}
|
|
}
|
|
for i = len(toDelete) - 1; i >= 0; i-- {
|
|
j = i
|
|
_ = j
|
|
_, err = tx.Exec(`DELETE FROM pki_cas WHERE public_id = ?`, toDelete[i])
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return err
|
|
}
|
|
}
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (s *Store) NextPKICASerial(caID string) (int64, error) {
|
|
var tx *sql.Tx
|
|
var err error
|
|
var row *sql.Row
|
|
var serial int64
|
|
tx, err = s.DB.Begin()
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
row = tx.QueryRow(`SELECT serial_counter FROM pki_cas WHERE public_id = ?`, caID)
|
|
err = row.Scan(&serial)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return 0, err
|
|
}
|
|
_, err = tx.Exec(`UPDATE pki_cas SET serial_counter = ?, updated_at = ? WHERE public_id = ?`, serial+1, time.Now().UTC().Unix(), caID)
|
|
if err != nil {
|
|
_ = tx.Rollback()
|
|
return 0, err
|
|
}
|
|
err = tx.Commit()
|
|
if err != nil {
|
|
return 0, err
|
|
}
|
|
return serial, nil
|
|
}
|
|
|
|
func (s *Store) ListPKICerts(caID string) ([]models.PKICert, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var items []models.PKICert
|
|
var item models.PKICert
|
|
if caID == "" {
|
|
rows, err = s.DB.Query(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
|
|
FROM pki_certs c
|
|
LEFT JOIN pki_cas ca ON ca.id = c.ca_id
|
|
ORDER BY c.created_at DESC`)
|
|
} else if caID == "standalone" {
|
|
rows, err = s.DB.Query(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
|
|
FROM pki_certs c
|
|
LEFT JOIN pki_cas ca ON ca.id = c.ca_id
|
|
WHERE c.ca_id IS NULL
|
|
ORDER BY c.created_at DESC`)
|
|
} else {
|
|
rows, err = s.DB.Query(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
|
|
FROM pki_certs c
|
|
LEFT JOIN pki_cas ca ON ca.id = c.ca_id
|
|
WHERE c.ca_id = (SELECT id FROM pki_cas WHERE public_id = ?)
|
|
ORDER BY c.created_at DESC`, caID)
|
|
}
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&item.ID, &item.CAID, &item.SerialHex, &item.CommonName, &item.SANDNS, &item.SANIPs, &item.IsCA, &item.CertPEM, &item.KeyPEM, &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) GetPKICert(id string) (models.PKICert, error) {
|
|
var row *sql.Row
|
|
var item models.PKICert
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT c.public_id, COALESCE(ca.public_id, ''), c.serial_hex, c.common_name, c.san_dns, c.san_ips, c.is_ca, c.cert_pem, c.key_pem, c.not_before, c.not_after, c.status, c.revoked_at, c.revocation_reason, c.created_at
|
|
FROM pki_certs c
|
|
LEFT JOIN pki_cas ca ON ca.id = c.ca_id
|
|
WHERE c.public_id = ?`, id)
|
|
err = row.Scan(&item.ID, &item.CAID, &item.SerialHex, &item.CommonName, &item.SANDNS, &item.SANIPs, &item.IsCA, &item.CertPEM, &item.KeyPEM, &item.NotBefore, &item.NotAfter, &item.Status, &item.RevokedAt, &item.RevocationReason, &item.CreatedAt)
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
return item, nil
|
|
}
|
|
|
|
func (s *Store) CreatePKICert(item models.PKICert) (models.PKICert, error) {
|
|
var id string
|
|
var now int64
|
|
var err error
|
|
if item.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
item.ID = id
|
|
}
|
|
if item.Status == "" {
|
|
item.Status = "active"
|
|
}
|
|
now = time.Now().UTC().Unix()
|
|
item.CreatedAt = now
|
|
item.CAID = strings.TrimSpace(item.CAID)
|
|
_, err = s.DB.Exec(`INSERT INTO pki_certs (public_id, ca_id, serial_hex, common_name, san_dns, san_ips, is_ca, cert_pem, key_pem, not_before, not_after, status, revoked_at, revocation_reason, created_at)
|
|
VALUES (?, CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM pki_cas WHERE public_id = ?) END, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
|
|
item.ID, item.CAID, item.CAID, item.SerialHex, item.CommonName, item.SANDNS, item.SANIPs, item.IsCA, item.CertPEM, item.KeyPEM, item.NotBefore, item.NotAfter, item.Status, item.RevokedAt, item.RevocationReason, item.CreatedAt)
|
|
if err != nil {
|
|
return item, err
|
|
}
|
|
return item, nil
|
|
}
|
|
|
|
func (s *Store) RevokePKICert(id string, reason string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`UPDATE pki_certs SET status = 'revoked', revoked_at = ?, revocation_reason = ? WHERE public_id = ?`, time.Now().UTC().Unix(), reason, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeletePKICert(id string) error {
|
|
var err error
|
|
_, err = s.DB.Exec(`DELETE FROM pki_certs WHERE public_id = ?`, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) CountTLSServerCertReferences(certID string) (int, int, error) {
|
|
var row *sql.Row
|
|
var appCount int
|
|
var listenerCount int
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT COUNT(*) FROM app_settings WHERE key = 'tls.pki_server_cert_id' AND value = ?`, certID)
|
|
err = row.Scan(&appCount)
|
|
if err != nil {
|
|
return 0, 0, err
|
|
}
|
|
row = s.DB.QueryRow(`SELECT COUNT(*) FROM tls_listeners WHERE tls_pki_server_cert_id = ?`, certID)
|
|
err = row.Scan(&listenerCount)
|
|
if err != nil {
|
|
return 0, 0, err
|
|
}
|
|
return appCount, listenerCount, nil
|
|
}
|
|
|
|
func (s *Store) CountTLSClientCAReferences(caID string) (int, int, error) {
|
|
var row *sql.Row
|
|
var appCount int
|
|
var listenerCount int
|
|
var err error
|
|
row = s.DB.QueryRow(`SELECT COUNT(*) FROM app_settings WHERE key = 'tls.pki_client_ca_id' AND value = ?`, caID)
|
|
err = row.Scan(&appCount)
|
|
if err != nil {
|
|
return 0, 0, err
|
|
}
|
|
row = s.DB.QueryRow(`SELECT COUNT(*) FROM tls_listeners WHERE tls_pki_client_ca_id = ?`, caID)
|
|
err = row.Scan(&listenerCount)
|
|
if err != nil {
|
|
return 0, 0, err
|
|
}
|
|
return appCount, listenerCount, nil
|
|
}
|