sonda/internal/storage/users.go
tgiachi a880b87a9e chore: move the module path to git.kmalloc.org/squid/sonda
Migrating hosting from Codeberg to git.kmalloc.org. Renames the
module path in go.mod, every internal import, and the OCI image
source label in the Dockerfile. Build and full test suite verified
green after the rename.
2026-11-15 17:30:29 +01:00

192 lines
6.4 KiB
Go

package storage
import (
"context"
"database/sql"
"errors"
"fmt"
"time"
"github.com/jackc/pgx/v5/pgconn"
"github.com/uptrace/bun"
msqlite "modernc.org/sqlite"
"git.kmalloc.org/squid/sonda/internal/auth"
)
// sqliteConstraintUnique is the extended result code SQLITE_CONSTRAINT_UNIQUE.
const sqliteConstraintUnique = 2067
type userRow struct {
bun.BaseModel `bun:"table:users"`
ID int64 `bun:"id,pk,autoincrement"`
Email string `bun:"email,notnull"`
PasswordHash string `bun:"password_hash,notnull"`
IsAdmin bool `bun:"is_admin,notnull"`
CreatedAt int64 `bun:"created_at,notnull"`
}
func (r userRow) toUser() auth.User {
return auth.User{ID: r.ID, Email: r.Email, IsAdmin: r.IsAdmin, CreatedAt: time.Unix(r.CreatedAt, 0)}
}
// UserRepo stores users. It implements auth.UserStore.
type UserRepo struct{ db *bun.DB }
// NewUserRepo builds a UserRepo.
func NewUserRepo(db *bun.DB) *UserRepo { return &UserRepo{db: db} }
// CountUsers returns how many users exist.
func (r *UserRepo) CountUsers(ctx context.Context) (int, error) {
n, err := r.db.NewSelect().Model((*userRow)(nil)).Count(ctx)
return int(n), err
}
// CountAdmins returns how many users are admins.
func (r *UserRepo) CountAdmins(ctx context.Context) (int, error) {
n, err := r.db.NewSelect().Model((*userRow)(nil)).Where("is_admin = ?", true).Count(ctx)
return int(n), err
}
// CreateUser inserts a user. Emails are unique case-insensitively.
func (r *UserRepo) CreateUser(ctx context.Context, email, passwordHash string, isAdmin bool) (auth.User, error) {
row := &userRow{Email: email, PasswordHash: passwordHash, IsAdmin: isAdmin, CreatedAt: time.Now().Unix()}
if _, err := r.db.NewInsert().Model(row).Exec(ctx); err != nil {
if isUniqueViolation(err) {
return auth.User{}, auth.ErrEmailTaken
}
return auth.User{}, fmt.Errorf("insert user: %w", err)
}
return row.toUser(), nil
}
// UserByEmail finds a user (case-insensitive) and returns their password hash.
func (r *UserRepo) UserByEmail(ctx context.Context, email string) (auth.User, string, error) {
var row userRow
err := r.db.NewSelect().Model(&row).Where("lower(email) = lower(?)", email).Limit(1).Scan(ctx)
if errors.Is(err, sql.ErrNoRows) {
return auth.User{}, "", auth.ErrUserNotFound
}
if err != nil {
return auth.User{}, "", fmt.Errorf("select user: %w", err)
}
return row.toUser(), row.PasswordHash, nil
}
// postgresUniqueViolation is the SQLSTATE for unique_violation.
const postgresUniqueViolation = "23505"
// UserByID finds a user by id and returns their password hash.
func (r *UserRepo) UserByID(ctx context.Context, id int64) (auth.User, string, error) {
var row userRow
err := r.db.NewSelect().Model(&row).Where("id = ?", id).Limit(1).Scan(ctx)
if errors.Is(err, sql.ErrNoRows) {
return auth.User{}, "", auth.ErrUserNotFound
}
if err != nil {
return auth.User{}, "", fmt.Errorf("select user: %w", err)
}
return row.toUser(), row.PasswordHash, nil
}
// ListUsers returns every user, oldest first.
func (r *UserRepo) ListUsers(ctx context.Context) ([]auth.User, error) {
var rows []userRow
if err := r.db.NewSelect().Model(&rows).OrderExpr("id").Scan(ctx); err != nil {
return nil, fmt.Errorf("list users: %w", err)
}
out := make([]auth.User, len(rows))
for i, row := range rows {
out[i] = row.toUser()
}
return out, nil
}
// SetAdmin sets the admin flag.
func (r *UserRepo) SetAdmin(ctx context.Context, id int64, isAdmin bool) error {
res, err := r.db.NewUpdate().Model((*userRow)(nil)).
Set("is_admin = ?", isAdmin).Where("id = ?", id).Exec(ctx)
return affectedOrNotFound(res, err, "set admin")
}
// SetPasswordHash replaces the password hash and revokes every refresh token of
// the user in the same transaction. Updating the user row first also takes its
// lock, so the change is serialised with any refresh in flight for that user.
func (r *UserRepo) SetPasswordHash(ctx context.Context, id int64, passwordHash string, now time.Time) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
res, err := tx.NewUpdate().Model((*userRow)(nil)).
Set("password_hash = ?", passwordHash).Where("id = ?", id).Exec(ctx)
if err := affectedOrNotFound(res, err, "set password"); err != nil {
return err
}
return revokeUserTokens(ctx, tx, id, now)
})
}
// DeleteUser removes a user; their refresh tokens and listening events go with them.
func (r *UserRepo) DeleteUser(ctx context.Context, id int64) error {
return r.db.RunInTx(ctx, nil, func(ctx context.Context, tx bun.Tx) error {
// The user's marks can be all that keeps an artist a provider named: take the
// turn artists are removed in, and clean up after the marks are gone.
if err := lockArtistWrites(ctx, tx); err != nil {
return err
}
var marked []int64
if err := tx.NewRaw("SELECT artist_id FROM artist_feedback WHERE user_id = ?", id).Scan(ctx, &marked); err != nil {
return fmt.Errorf("read the user's marks: %w", err)
}
res, err := tx.NewDelete().Model((*userRow)(nil)).Where("id = ?", id).Exec(ctx)
if err := affectedOrNotFound(res, err, "delete user"); err != nil {
return err
}
const chunk = 500
for start := 0; start < len(marked); start += chunk {
end := min(start+chunk, len(marked))
if _, err := removeUnnamedArtists(ctx, tx, true, "id IN (?) AND ", bun.In(marked[start:end])); err != nil {
return err
}
}
return nil
})
}
func affectedOrNotFound(res sql.Result, err error, what string) error {
if err != nil {
return fmt.Errorf("%s: %w", what, err)
}
n, err := res.RowsAffected()
if err != nil {
return fmt.Errorf("%s: %w", what, err)
}
if n == 0 {
return auth.ErrUserNotFound
}
return nil
}
// SQLSTATE and SQLite extended result code of a foreign key violation.
const (
postgresForeignKeyViolation = "23503"
sqliteConstraintForeignKey = 787
)
// isForeignKeyViolation says whether err is a row refused because it points at
// one that does not exist (for example settings for a user deleted a moment ago).
func isForeignKeyViolation(err error) bool {
var se *msqlite.Error
if errors.As(err, &se) {
return se.Code() == sqliteConstraintForeignKey
}
var pe *pgconn.PgError
return errors.As(err, &pe) && pe.Code == postgresForeignKeyViolation
}
func isUniqueViolation(err error) bool {
var se *msqlite.Error
if errors.As(err, &se) {
return se.Code() == sqliteConstraintUnique
}
var pe *pgconn.PgError
return errors.As(err, &pe) && pe.Code == postgresUniqueViolation
}