255 lines
10 KiB
Go
255 lines
10 KiB
Go
package db
|
|
|
|
import "context"
|
|
import "database/sql"
|
|
import "strings"
|
|
import "time"
|
|
|
|
import "codit/internal/models"
|
|
import "codit/internal/util"
|
|
|
|
func (s *Store) CreateUser(user models.User, passwordHash string) (models.User, error) {
|
|
var id string
|
|
var err error
|
|
var nowUnix int64
|
|
if user.ID == "" {
|
|
id, err = util.NewID()
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
user.ID = id
|
|
}
|
|
nowUnix = time.Now().UTC().Unix()
|
|
user.CreatedAt = nowUnix
|
|
user.UpdatedAt = nowUnix
|
|
_, err = s.Exec(`
|
|
INSERT INTO users (public_id, username, display_name, email, password_hash, is_admin, disabled, auth_provider_id, totp_required, external_subject, created_at, updated_at)
|
|
VALUES (?, ?, ?, ?, ?, ?, ?, CASE WHEN ? = '' THEN NULL ELSE (SELECT id FROM auth_providers WHERE public_id = ?) END, ?, ?, ?, ?)
|
|
`, user.ID, user.Username, user.DisplayName, user.Email, passwordHash, user.IsAdmin, user.Disabled, user.AuthProviderID, user.AuthProviderID, user.TOTPRequired, user.ExternalSubject, nowUnix, nowUnix)
|
|
return user, err
|
|
}
|
|
|
|
func (s *Store) UpdateUser(user models.User) error {
|
|
var err error
|
|
var nowUnix int64
|
|
nowUnix = time.Now().UTC().Unix()
|
|
user.UpdatedAt = nowUnix
|
|
_, err = s.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, totp_required = ?, updated_at = ? WHERE public_id = ?`,
|
|
user.DisplayName, user.Email, user.IsAdmin, user.Disabled, user.TOTPRequired, nowUnix, user.ID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) UpdateUserWithPassword(ctx context.Context, user models.User, passwordHash string) error {
|
|
var err error
|
|
var nowUnix int64
|
|
var tx txExecutor
|
|
var owned bool
|
|
|
|
nowUnix = time.Now().UTC().Unix()
|
|
user.UpdatedAt = nowUnix
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`UPDATE users SET display_name = ?, email = ?, is_admin = ?, disabled = ?, totp_required = ?, password_hash = ?, updated_at = ? WHERE public_id = ?`,
|
|
user.DisplayName, user.Email, user.IsAdmin, user.Disabled, user.TOTPRequired, passwordHash, nowUnix, user.ID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return err
|
|
}
|
|
return nil
|
|
}
|
|
|
|
func (s *Store) SetUserPassword(userID, passwordHash string) error {
|
|
var err error
|
|
_, err = s.Exec(`UPDATE users SET password_hash = ?, updated_at = ? WHERE public_id = ?`, passwordHash, time.Now().UTC().Unix(), userID)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetUserByID(id string) (models.User, error) {
|
|
var user models.User
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.QueryRow(`SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), COALESCE(t.enabled, 0), u.totp_required, CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END, u.avatar_updated_at, u.created_at, u.updated_at FROM users u LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id LEFT JOIN user_totp t ON t.user_id = u.id WHERE u.public_id = ?`, id)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthProviderID, &user.TOTPEnabled, &user.TOTPRequired, &user.AvatarURL, &user.AvatarUpdatedAt, &user.CreatedAt, &user.UpdatedAt)
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
return user, nil
|
|
}
|
|
|
|
func (s *Store) GetUserByUsername(username string) (models.User, string, error) {
|
|
var user models.User
|
|
var passwordHash sql.NullString
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.QueryRow(`SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), u.password_hash, COALESCE(t.enabled, 0), u.totp_required, CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END, u.avatar_updated_at, u.created_at, u.updated_at FROM users u LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id LEFT JOIN user_totp t ON t.user_id = u.id WHERE u.username = ?`, username)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthProviderID, &passwordHash, &user.TOTPEnabled, &user.TOTPRequired, &user.AvatarURL, &user.AvatarUpdatedAt, &user.CreatedAt, &user.UpdatedAt)
|
|
if err != nil {
|
|
return user, passwordHash.String, err
|
|
}
|
|
return user, passwordHash.String, nil
|
|
}
|
|
|
|
func (s *Store) ListUsers() ([]models.User, error) {
|
|
var rows *sql.Rows
|
|
var err error
|
|
var users []models.User
|
|
var u models.User
|
|
rows, err = s.Query(`SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), COALESCE(t.enabled, 0), u.totp_required, CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END, u.avatar_updated_at, u.created_at, u.updated_at FROM users u LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id LEFT JOIN user_totp t ON t.user_id = u.id ORDER BY u.username`)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
for rows.Next() {
|
|
u = models.User{}
|
|
err = rows.Scan(&u.ID, &u.Username, &u.DisplayName, &u.Email, &u.IsAdmin, &u.Disabled, &u.AuthProviderID, &u.TOTPEnabled, &u.TOTPRequired, &u.AvatarURL, &u.AvatarUpdatedAt, &u.CreatedAt, &u.UpdatedAt)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
users = append(users, u)
|
|
}
|
|
return users, rows.Err()
|
|
}
|
|
|
|
func (s *Store) GetUserAvatar(id string) (models.UserAvatar, error) {
|
|
var avatar models.UserAvatar
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.QueryRow(`SELECT public_id, avatar_storage_path, avatar_content_type, avatar_updated_at FROM users WHERE public_id = ? AND avatar_storage_path != ''`, id)
|
|
err = row.Scan(&avatar.UserID, &avatar.StoragePath, &avatar.ContentType, &avatar.UpdatedAt)
|
|
if err != nil {
|
|
return avatar, err
|
|
}
|
|
return avatar, nil
|
|
}
|
|
|
|
func (s *Store) UpdateUserAvatar(id string, storagePath string, contentType string) error {
|
|
var err error
|
|
var nowUnix int64
|
|
nowUnix = time.Now().UTC().Unix()
|
|
_, err = s.Exec(`UPDATE users SET avatar_storage_path = ?, avatar_content_type = ?, avatar_updated_at = ?, updated_at = ? WHERE public_id = ?`,
|
|
storagePath, contentType, nowUnix, nowUnix, id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) ClearUserAvatar(id string) error {
|
|
var err error
|
|
_, err = s.Exec(`UPDATE users SET avatar_storage_path = '', avatar_content_type = '', avatar_updated_at = 0, updated_at = ? WHERE public_id = ?`,
|
|
time.Now().UTC().Unix(), id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) DeleteUser(ctx context.Context, id string) error {
|
|
var tx txExecutor
|
|
var owned bool
|
|
var err error
|
|
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil { return err }
|
|
|
|
_, err = tx.Exec(`DELETE FROM project_role_bindings WHERE subject_type = 'user' AND subject_id = (SELECT id FROM users WHERE public_id = ?)`, id)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`DELETE FROM ssh_principal_grant_targets WHERE target_type = 'user' AND target_id = (SELECT id FROM users WHERE public_id = ?)`, id)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`DELETE FROM subject_permissions WHERE subject_type = 'user' AND subject_id = (SELECT id FROM users WHERE public_id = ?)`, id)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
// Remove the user's personal signing keys (and their private material). Only
|
|
// personal keys carry owner_user_id; the gpg_keys FK also cascades, but this
|
|
// is explicit and runs while the user row still resolves.
|
|
_, err = tx.Exec(`DELETE FROM gpg_keys WHERE owner_user_id = (SELECT id FROM users WHERE public_id = ?)`, id)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
_, err = tx.Exec(`DELETE FROM users WHERE public_id = ?`, id)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetUserByProviderAndSub(providerPublicID string, sub string) (models.User, error) {
|
|
var user models.User
|
|
var row *sql.Row
|
|
var err error
|
|
row = s.QueryRow(`
|
|
SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled,
|
|
COALESCE(ap.public_id, ''), u.external_subject, COALESCE(t.enabled, 0), u.totp_required,
|
|
CASE WHEN u.avatar_storage_path != '' THEN '/api/users/' || u.public_id || '/avatar?v=' || u.avatar_updated_at ELSE '' END,
|
|
u.avatar_updated_at, u.created_at, u.updated_at
|
|
FROM users u
|
|
LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id
|
|
LEFT JOIN user_totp t ON t.user_id = u.id
|
|
WHERE ap.public_id = ? AND u.external_subject = ?
|
|
`, strings.TrimSpace(providerPublicID), strings.TrimSpace(sub))
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled,
|
|
&user.AuthProviderID, &user.ExternalSubject, &user.TOTPEnabled, &user.TOTPRequired,
|
|
&user.AvatarURL, &user.AvatarUpdatedAt, &user.CreatedAt, &user.UpdatedAt)
|
|
if err != nil { return user, err }
|
|
return user, nil
|
|
}
|
|
|
|
func (s *Store) SetUserDisabled(id string, disabled bool) error {
|
|
var err error
|
|
_, err = s.Exec(`UPDATE users SET disabled = ?, updated_at = ? WHERE public_id = ?`, disabled, time.Now().UTC().Unix(), id)
|
|
return err
|
|
}
|
|
|
|
func (s *Store) GetUserByAPIKeyHash(ctx context.Context, tokenHash string) (models.User, error) {
|
|
var user models.User
|
|
var tx txExecutor
|
|
var owned bool
|
|
var row *sql.Row
|
|
var keyID int64
|
|
var nowUnix int64
|
|
var err error
|
|
|
|
tx, owned, err = s.beginContext(ctx)
|
|
if err != nil { return user, err }
|
|
|
|
nowUnix = time.Now().UTC().Unix()
|
|
row = tx.QueryRow(`
|
|
SELECT u.public_id, u.username, u.display_name, u.email, u.is_admin, u.disabled, COALESCE(ap.public_id, ''), u.created_at, u.updated_at, k.id
|
|
FROM api_keys k
|
|
JOIN users u ON u.id = k.user_id
|
|
LEFT JOIN auth_providers ap ON ap.id = u.auth_provider_id
|
|
WHERE k.token_hash = ?
|
|
AND u.disabled = 0
|
|
AND k.disabled = 0
|
|
AND (k.expires_at = 0 OR k.expires_at > ?)
|
|
LIMIT 1
|
|
`, tokenHash, nowUnix)
|
|
err = row.Scan(&user.ID, &user.Username, &user.DisplayName, &user.Email, &user.IsAdmin, &user.Disabled, &user.AuthProviderID, &user.CreatedAt, &user.UpdatedAt, &keyID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return user, err
|
|
}
|
|
nowUnix = time.Now().UTC().Unix()
|
|
_, err = tx.Exec(`UPDATE api_keys SET last_used_at = ? WHERE id = ?`, nowUnix, keyID)
|
|
if err != nil {
|
|
rollbackIfOwned(tx, owned)
|
|
return user, err
|
|
}
|
|
err = commitIfOwned(tx, owned)
|
|
if err != nil {
|
|
return user, err
|
|
}
|
|
return user, nil
|
|
}
|