user.go 8.5 KB

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