229 lines
8.7 KiB
Go
229 lines
8.7 KiB
Go
package db
|
|
|
|
import "context"
|
|
import "database/sql"
|
|
import "errors"
|
|
import "strings"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
import "codit/internal/util"
|
|
|
|
func scanSSHServerGroup(row interface{ Scan(dest ...any) error }, item *models.SSHServerGroup) error {
|
|
var tagsJSON string
|
|
var err error
|
|
err = row.Scan(&item.ID, &item.Name, &item.Description, &item.Enabled, &tagsJSON, &item.CreatedByKind, &item.CreatedBySubjectID, &item.CreatedBySubjectName, &item.CreatedAt, &item.UpdatedAt)
|
|
if err != nil { return err }
|
|
item.Tags, err = decodeStringList(tagsJSON)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) listSSHServerGroupMemberIDs(groupID string) ([]string, error) {
|
|
var rows *sql.Rows
|
|
var items []string
|
|
var id string
|
|
var err error
|
|
|
|
rows, err = s.Query(`SELECT s.public_id
|
|
FROM ssh_server_group_members gm
|
|
JOIN ssh_servers s ON s.id = gm.server_id
|
|
JOIN ssh_server_groups g ON g.id = gm.group_id
|
|
WHERE g.public_id = ?
|
|
ORDER BY s.public_id`, strings.TrimSpace(groupID))
|
|
if err != nil { return nil, err }
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&id)
|
|
if err != nil { return nil, err }
|
|
items = append(items, id)
|
|
}
|
|
err = rows.Err()
|
|
if err != nil { return nil, err }
|
|
return items, nil
|
|
}
|
|
|
|
func (s *Store) ListSSHServerGroups() ([]models.SSHServerGroup, error) {
|
|
var rows *sql.Rows
|
|
var items []models.SSHServerGroup
|
|
var item models.SSHServerGroup
|
|
var err error
|
|
|
|
rows, err = s.Query(`SELECT g.public_id, g.name, g.description, g.enabled, g.tags_json, g.created_by_kind, COALESCE(cbu.public_id, cbp.public_id, ''), g.created_by_subject_name, g.created_at, g.updated_at
|
|
FROM ssh_server_groups g
|
|
LEFT JOIN users cbu ON cbu.id = g.created_by_user_id
|
|
LEFT JOIN service_principals cbp ON cbp.id = g.created_by_principal_id
|
|
ORDER BY g.name`)
|
|
if err != nil { return nil, err }
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = scanSSHServerGroup(rows, &item)
|
|
if err != nil { return nil, err }
|
|
item.ServerIDs, err = s.listSSHServerGroupMemberIDs(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) GetSSHServerGroup(id string) (models.SSHServerGroup, error) {
|
|
var row *sql.Row
|
|
var item models.SSHServerGroup
|
|
var err error
|
|
|
|
row = s.QueryRow(`SELECT g.public_id, g.name, g.description, g.enabled, g.tags_json, g.created_by_kind, COALESCE(cbu.public_id, cbp.public_id, ''), g.created_by_subject_name, g.created_at, g.updated_at
|
|
FROM ssh_server_groups g
|
|
LEFT JOIN users cbu ON cbu.id = g.created_by_user_id
|
|
LEFT JOIN service_principals cbp ON cbp.id = g.created_by_principal_id
|
|
WHERE g.public_id = ?`, strings.TrimSpace(id))
|
|
err = scanSSHServerGroup(row, &item)
|
|
if err != nil { return item, err }
|
|
item.ServerIDs, err = s.listSSHServerGroupMemberIDs(item.ID)
|
|
if err != nil { return item, err }
|
|
return item, nil
|
|
}
|
|
|
|
func (s *Store) ListSSHServersForGroup(groupID string) ([]models.SSHServer, error) {
|
|
var rows *sql.Rows
|
|
var items []models.SSHServer
|
|
var item models.SSHServer
|
|
var tagsJSON string
|
|
var err error
|
|
|
|
rows, err = s.Query(`SELECT s.public_id, s.name, s.host, s.port, s.description, s.tags_json, s.enabled, s.host_key_policy, s.owner_scope, COALESCE(u.public_id, ''), s.created_by_kind, COALESCE(sbu.public_id, sbp.public_id, ''), s.created_by_subject_name, s.created_at, s.updated_at
|
|
FROM ssh_server_group_members gm
|
|
JOIN ssh_servers s ON s.id = gm.server_id
|
|
JOIN ssh_server_groups g ON g.id = gm.group_id
|
|
LEFT JOIN users u ON u.id = s.owner_user_id
|
|
LEFT JOIN users sbu ON sbu.id = s.created_by_user_id
|
|
LEFT JOIN service_principals sbp ON sbp.id = s.created_by_principal_id
|
|
WHERE g.public_id = ?
|
|
ORDER BY s.name`, strings.TrimSpace(groupID))
|
|
if err != nil { return nil, err }
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
err = rows.Scan(&item.ID, &item.Name, &item.Host, &item.Port, &item.Description, &tagsJSON, &item.Enabled, &item.HostKeyPolicy, &item.OwnerScope, &item.OwnerUserID, &item.CreatedByKind, &item.CreatedBySubjectID, &item.CreatedBySubjectName, &item.CreatedAt, &item.UpdatedAt)
|
|
if err != nil { return nil, err }
|
|
item.Tags, err = decodeStringList(tagsJSON)
|
|
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) CreateSSHServerGroup(ctx context.Context, item models.SSHServerGroup) (models.SSHServerGroup, error) {
|
|
var tx txExecutor
|
|
var owned bool
|
|
var err error
|
|
var now int64
|
|
|
|
var tagsJSON string
|
|
if strings.TrimSpace(item.Name) == "" { return item, errors.New("name is required") }
|
|
if strings.TrimSpace(item.ID) == "" {
|
|
item.ID, err = util.NewID()
|
|
if err != nil { return item, err }
|
|
}
|
|
item.Tags = normalizeStringList(item.Tags)
|
|
tagsJSON, err = encodeStringList(item.Tags)
|
|
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 ssh_server_groups (public_id, name, description, enabled, tags_json, created_by_kind, created_by_user_id, created_by_principal_id, created_by_subject_name, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, (SELECT id FROM users WHERE public_id = ? AND ? = 'user'), (SELECT id FROM service_principals WHERE public_id = ? AND ? = 'service_principal'), ?, ?, ?)`, item.ID, strings.TrimSpace(item.Name), strings.TrimSpace(item.Description), item.Enabled, tagsJSON, strings.TrimSpace(item.CreatedByKind), strings.TrimSpace(item.CreatedBySubjectID), strings.TrimSpace(item.CreatedByKind), strings.TrimSpace(item.CreatedBySubjectID), strings.TrimSpace(item.CreatedByKind), strings.TrimSpace(item.CreatedBySubjectName), item.CreatedAt, item.UpdatedAt)
|
|
if err != nil { rollbackIfOwned(tx, owned); return item, err }
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil { return item, err }
|
|
return s.GetSSHServerGroup(item.ID)
|
|
}
|
|
|
|
func (s *Store) UpdateSSHServerGroup(ctx context.Context, item models.SSHServerGroup) (models.SSHServerGroup, error) {
|
|
var tx txExecutor
|
|
var owned bool
|
|
var err error
|
|
var now int64
|
|
|
|
var tagsJSON string
|
|
if strings.TrimSpace(item.Name) == "" { return item, errors.New("name is required") }
|
|
item.Tags = normalizeStringList(item.Tags)
|
|
tagsJSON, err = encodeStringList(item.Tags)
|
|
if err != nil { return item, err }
|
|
now = time.Now().UTC().Unix()
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil { return item, err }
|
|
_, err = tx.Exec(`UPDATE ssh_server_groups SET name = ?, description = ?, enabled = ?, tags_json = ?, updated_at = ? WHERE public_id = ?`, strings.TrimSpace(item.Name), strings.TrimSpace(item.Description), item.Enabled, tagsJSON, now, strings.TrimSpace(item.ID))
|
|
if err != nil { rollbackIfOwned(tx, owned); return item, err }
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil { return item, err }
|
|
return s.GetSSHServerGroup(item.ID)
|
|
}
|
|
|
|
func (s *Store) DeleteSSHServerGroup(ctx context.Context, id string) error {
|
|
var tx txExecutor
|
|
var owned bool
|
|
var count int
|
|
var err error
|
|
|
|
id = strings.TrimSpace(id)
|
|
tx, owned, err = s.beginImmediateContext(ctx)
|
|
if err != nil { return err }
|
|
|
|
err = tx.QueryRow(`SELECT COUNT(*)
|
|
FROM ssh_access_profiles p
|
|
JOIN ssh_server_groups g ON g.id = p.server_group_id
|
|
WHERE g.public_id = ?`, id).Scan(&count)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
if count > 0 {
|
|
rollbackIfOwned(tx, owned)
|
|
return errors.New("ssh server group is used by access profiles")
|
|
}
|
|
_, err = tx.Exec(`DELETE FROM ssh_server_groups WHERE public_id = ?`, id)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
return commitIfOwned(tx, owned)
|
|
}
|
|
|
|
func (s *Store) AddSSHServerGroupMember(groupID string, serverID string) error {
|
|
var now int64
|
|
var err error
|
|
|
|
now = time.Now().UTC().Unix()
|
|
_, err = s.Exec(`INSERT OR IGNORE INTO ssh_server_group_members (group_id, server_id, created_at)
|
|
SELECT g.id, srv.id, ?
|
|
FROM ssh_server_groups g
|
|
JOIN ssh_servers srv ON srv.public_id = ?
|
|
WHERE g.public_id = ?`, now, strings.TrimSpace(serverID), strings.TrimSpace(groupID))
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeleteSSHServerGroupMember(groupID string, serverID string) error {
|
|
var err error
|
|
|
|
_, err = s.Exec(`DELETE FROM ssh_server_group_members
|
|
WHERE group_id = (SELECT id FROM ssh_server_groups WHERE public_id = ?)
|
|
AND server_id = (SELECT id FROM ssh_servers WHERE public_id = ?)`, strings.TrimSpace(groupID), strings.TrimSpace(serverID))
|
|
return err
|
|
}
|
|
|
|
func (s *Store) SSHServerBelongsToGroup(groupID string, serverID string) (bool, error) {
|
|
var row *sql.Row
|
|
var count int
|
|
var err error
|
|
|
|
row = s.QueryRow(`SELECT COUNT(*) FROM ssh_server_group_members
|
|
WHERE group_id = (SELECT id FROM ssh_server_groups WHERE public_id = ?)
|
|
AND server_id = (SELECT id FROM ssh_servers WHERE public_id = ?)`, strings.TrimSpace(groupID), strings.TrimSpace(serverID))
|
|
err = row.Scan(&count)
|
|
if err != nil { return false, err }
|
|
return count > 0, nil
|
|
}
|