user.go 9.2 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362
  1. // Copyright 2017 Frédéric Guillot. All rights reserved.
  2. // Use of this source code is governed by the Apache 2.0
  3. // license that can be found in the LICENSE file.
  4. package storage // import "miniflux.app/storage"
  5. import (
  6. "database/sql"
  7. "errors"
  8. "fmt"
  9. "strings"
  10. "time"
  11. "miniflux.app/model"
  12. "miniflux.app/timer"
  13. "github.com/lib/pq/hstore"
  14. "golang.org/x/crypto/bcrypt"
  15. )
  16. // SetLastLogin updates the last login date of a user.
  17. func (s *Storage) SetLastLogin(userID int64) error {
  18. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:SetLastLogin] userID=%d", userID))
  19. query := "UPDATE users SET last_login_at=now() WHERE id=$1"
  20. _, err := s.db.Exec(query, userID)
  21. if err != nil {
  22. return fmt.Errorf("unable to update last login date: %v", err)
  23. }
  24. return nil
  25. }
  26. // UserExists checks if a user exists by using the given username.
  27. func (s *Storage) UserExists(username string) bool {
  28. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UserExists] username=%s", username))
  29. var result int
  30. s.db.QueryRow(`SELECT count(*) as c FROM users WHERE username=LOWER($1)`, username).Scan(&result)
  31. return result >= 1
  32. }
  33. // AnotherUserExists checks if another user exists with the given username.
  34. func (s *Storage) AnotherUserExists(userID int64, username string) bool {
  35. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:AnotherUserExists] userID=%d, username=%s", userID, username))
  36. var result int
  37. s.db.QueryRow(`SELECT count(*) as c FROM users WHERE id != $1 AND username=LOWER($2)`, userID, username).Scan(&result)
  38. return result >= 1
  39. }
  40. // CreateUser creates a new user.
  41. func (s *Storage) CreateUser(user *model.User) (err error) {
  42. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:CreateUser] username=%s", user.Username))
  43. password := ""
  44. extra := hstore.Hstore{Map: make(map[string]sql.NullString)}
  45. if user.Password != "" {
  46. password, err = hashPassword(user.Password)
  47. if err != nil {
  48. return err
  49. }
  50. }
  51. if len(user.Extra) > 0 {
  52. for key, value := range user.Extra {
  53. extra.Map[key] = sql.NullString{String: value, Valid: true}
  54. }
  55. }
  56. query := `INSERT INTO users
  57. (username, password, is_admin, extra)
  58. VALUES
  59. (LOWER($1), $2, $3, $4)
  60. RETURNING id, username, is_admin, language, theme, timezone, entry_direction`
  61. err = s.db.QueryRow(query, user.Username, password, user.IsAdmin, extra).Scan(
  62. &user.ID,
  63. &user.Username,
  64. &user.IsAdmin,
  65. &user.Language,
  66. &user.Theme,
  67. &user.Timezone,
  68. &user.EntryDirection,
  69. )
  70. if err != nil {
  71. return fmt.Errorf("unable to create user: %v", err)
  72. }
  73. s.CreateCategory(&model.Category{Title: "All", UserID: user.ID})
  74. s.CreateIntegration(user.ID)
  75. return nil
  76. }
  77. // UpdateExtraField updates an extra field of the given user.
  78. func (s *Storage) UpdateExtraField(userID int64, field, value string) error {
  79. query := fmt.Sprintf(`UPDATE users SET extra = hstore('%s', $1) WHERE id=$2`, field)
  80. _, err := s.db.Exec(query, value, userID)
  81. if err != nil {
  82. return fmt.Errorf("unable to update user extra field: %v", err)
  83. }
  84. return nil
  85. }
  86. // RemoveExtraField deletes an extra field for the given user.
  87. func (s *Storage) RemoveExtraField(userID int64, field string) error {
  88. query := `UPDATE users SET extra = delete(extra, $1) WHERE id=$2`
  89. _, err := s.db.Exec(query, field, userID)
  90. if err != nil {
  91. return fmt.Errorf("unable to remove user extra field: %v", err)
  92. }
  93. return nil
  94. }
  95. // UpdateUser updates a user.
  96. func (s *Storage) UpdateUser(user *model.User) error {
  97. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UpdateUser] userID=%d", user.ID))
  98. if user.Password != "" {
  99. hashedPassword, err := hashPassword(user.Password)
  100. if err != nil {
  101. return err
  102. }
  103. query := `UPDATE users SET
  104. username=LOWER($1),
  105. password=$2,
  106. is_admin=$3,
  107. theme=$4,
  108. language=$5,
  109. timezone=$6,
  110. entry_direction=$7
  111. WHERE id=$8`
  112. _, err = s.db.Exec(
  113. query,
  114. user.Username,
  115. hashedPassword,
  116. user.IsAdmin,
  117. user.Theme,
  118. user.Language,
  119. user.Timezone,
  120. user.EntryDirection,
  121. user.ID,
  122. )
  123. if err != nil {
  124. return fmt.Errorf("unable to update user: %v", err)
  125. }
  126. } else {
  127. query := `UPDATE users SET
  128. username=LOWER($1),
  129. is_admin=$2,
  130. theme=$3,
  131. language=$4,
  132. timezone=$5,
  133. entry_direction=$6
  134. WHERE id=$7`
  135. _, err := s.db.Exec(
  136. query,
  137. user.Username,
  138. user.IsAdmin,
  139. user.Theme,
  140. user.Language,
  141. user.Timezone,
  142. user.EntryDirection,
  143. user.ID,
  144. )
  145. if err != nil {
  146. return fmt.Errorf("unable to update user: %v", err)
  147. }
  148. }
  149. return nil
  150. }
  151. // UserLanguage returns the language of the given user.
  152. func (s *Storage) UserLanguage(userID int64) (language string) {
  153. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UserLanguage] userID=%d", userID))
  154. err := s.db.QueryRow(`SELECT language FROM users WHERE id = $1`, userID).Scan(&language)
  155. if err != nil {
  156. return "en_US"
  157. }
  158. return language
  159. }
  160. // UserByID finds a user by the ID.
  161. func (s *Storage) UserByID(userID int64) (*model.User, error) {
  162. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UserByID] userID=%d", userID))
  163. query := `SELECT
  164. id, username, is_admin, theme, language, timezone, entry_direction, last_login_at, extra
  165. FROM users
  166. WHERE id = $1`
  167. return s.fetchUser(query, userID)
  168. }
  169. // UserByUsername finds a user by the username.
  170. func (s *Storage) UserByUsername(username string) (*model.User, error) {
  171. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UserByUsername] username=%s", username))
  172. query := `SELECT
  173. id, username, is_admin, theme, language, timezone, entry_direction, last_login_at, extra
  174. FROM users
  175. WHERE username=LOWER($1)`
  176. return s.fetchUser(query, username)
  177. }
  178. // UserByExtraField finds a user by an extra field value.
  179. func (s *Storage) UserByExtraField(field, value string) (*model.User, error) {
  180. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:UserByExtraField] field=%s", field))
  181. query := `SELECT
  182. id, username, is_admin, theme, language, timezone, entry_direction, last_login_at, extra
  183. FROM users
  184. WHERE extra->$1=$2`
  185. return s.fetchUser(query, field, value)
  186. }
  187. func (s *Storage) fetchUser(query string, args ...interface{}) (*model.User, error) {
  188. var extra hstore.Hstore
  189. user := model.NewUser()
  190. err := s.db.QueryRow(query, args...).Scan(
  191. &user.ID,
  192. &user.Username,
  193. &user.IsAdmin,
  194. &user.Theme,
  195. &user.Language,
  196. &user.Timezone,
  197. &user.EntryDirection,
  198. &user.LastLoginAt,
  199. &extra,
  200. )
  201. if err == sql.ErrNoRows {
  202. return nil, nil
  203. } else if err != nil {
  204. return nil, fmt.Errorf("unable to fetch user: %v", err)
  205. }
  206. for key, value := range extra.Map {
  207. if value.Valid {
  208. user.Extra[key] = value.String
  209. }
  210. }
  211. return user, nil
  212. }
  213. // RemoveUser deletes a user.
  214. func (s *Storage) RemoveUser(userID int64) error {
  215. defer timer.ExecutionTime(time.Now(), fmt.Sprintf("[Storage:RemoveUser] userID=%d", userID))
  216. result, err := s.db.Exec("DELETE FROM users WHERE id = $1", userID)
  217. if err != nil {
  218. return fmt.Errorf("unable to remove this user: %v", err)
  219. }
  220. count, err := result.RowsAffected()
  221. if err != nil {
  222. return fmt.Errorf("unable to remove this user: %v", err)
  223. }
  224. if count == 0 {
  225. return errors.New("nothing has been removed")
  226. }
  227. return nil
  228. }
  229. // Users returns all users.
  230. func (s *Storage) Users() (model.Users, error) {
  231. defer timer.ExecutionTime(time.Now(), "[Storage:Users]")
  232. query := `
  233. SELECT
  234. id, username, is_admin, theme, language, timezone, entry_direction, last_login_at, extra
  235. FROM users
  236. ORDER BY username ASC`
  237. rows, err := s.db.Query(query)
  238. if err != nil {
  239. return nil, fmt.Errorf("unable to fetch users: %v", err)
  240. }
  241. defer rows.Close()
  242. var users model.Users
  243. for rows.Next() {
  244. var extra hstore.Hstore
  245. user := model.NewUser()
  246. err := rows.Scan(
  247. &user.ID,
  248. &user.Username,
  249. &user.IsAdmin,
  250. &user.Theme,
  251. &user.Language,
  252. &user.Timezone,
  253. &user.EntryDirection,
  254. &user.LastLoginAt,
  255. &extra,
  256. )
  257. if err != nil {
  258. return nil, fmt.Errorf("unable to fetch users row: %v", err)
  259. }
  260. for key, value := range extra.Map {
  261. if value.Valid {
  262. user.Extra[key] = value.String
  263. }
  264. }
  265. users = append(users, user)
  266. }
  267. return users, nil
  268. }
  269. // CheckPassword validate the hashed password.
  270. func (s *Storage) CheckPassword(username, password string) error {
  271. defer timer.ExecutionTime(time.Now(), "[Storage:CheckPassword]")
  272. var hash string
  273. username = strings.ToLower(username)
  274. err := s.db.QueryRow("SELECT password FROM users WHERE username=$1", username).Scan(&hash)
  275. if err == sql.ErrNoRows {
  276. return fmt.Errorf("unable to find this user: %s", username)
  277. } else if err != nil {
  278. return fmt.Errorf("unable to fetch user: %v", err)
  279. }
  280. if err := bcrypt.CompareHashAndPassword([]byte(hash), []byte(password)); err != nil {
  281. return fmt.Errorf(`invalid password for "%s" (%v)`, username, err)
  282. }
  283. return nil
  284. }
  285. // HasPassword returns true if the given user has a password defined.
  286. func (s *Storage) HasPassword(userID int64) (bool, error) {
  287. var result bool
  288. query := `SELECT true FROM users WHERE id=$1 AND password <> ''`
  289. err := s.db.QueryRow(query, userID).Scan(&result)
  290. if err == sql.ErrNoRows {
  291. return false, nil
  292. } else if err != nil {
  293. return false, fmt.Errorf("unable to execute query: %v", err)
  294. }
  295. if result {
  296. return true, nil
  297. }
  298. return false, nil
  299. }
  300. func hashPassword(password string) (string, error) {
  301. bytes, err := bcrypt.GenerateFromPassword([]byte(password), bcrypt.DefaultCost)
  302. return string(bytes), err
  303. }