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 }