sqlite3_opt_userauth_test.go 14 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619
  1. // Copyright (C) 2018 G.J.R. Timmer <gjr.timmer@gmail.com>.
  2. //
  3. // Use of this source code is governed by an MIT-style
  4. // license that can be found in the LICENSE file.
  5. // +build sqlite_userauth
  6. package sqlite3
  7. import (
  8. "database/sql"
  9. "fmt"
  10. "os"
  11. "testing"
  12. )
  13. var (
  14. conn *SQLiteConn
  15. create func(t *testing.T, username, password string) (file string, err error)
  16. createWithCrypt func(t *testing.T, username, password, crypt, salt string) (file string, err error)
  17. connect func(t *testing.T, f string, username, password string) (file string, db *sql.DB, c *SQLiteConn, err error)
  18. connectWithCrypt func(t *testing.T, f string, username, password string, crypt string, salt string) (file string, db *sql.DB, c *SQLiteConn, err error)
  19. authEnabled func(db *sql.DB) (exists bool, err error)
  20. addUser func(db *sql.DB, username, password string, admin int) (rv int, err error)
  21. userExists func(db *sql.DB, username string) (rv int, err error)
  22. isAdmin func(db *sql.DB, username string) (rv bool, err error)
  23. modifyUser func(db *sql.DB, username, password string, admin int) (rv int, err error)
  24. deleteUser func(db *sql.DB, username string) (rv int, err error)
  25. )
  26. func init() {
  27. // Create database connection
  28. sql.Register("sqlite3_with_conn",
  29. &SQLiteDriver{
  30. ConnectHook: func(c *SQLiteConn) error {
  31. conn = c
  32. return nil
  33. },
  34. })
  35. create = func(t *testing.T, username, password string) (file string, err error) {
  36. var db *sql.DB
  37. file, db, _, err = connect(t, "", username, password)
  38. db.Close()
  39. return
  40. }
  41. createWithCrypt = func(t *testing.T, username, password, crypt, salt string) (file string, err error) {
  42. var db *sql.DB
  43. file, db, _, err = connectWithCrypt(t, "", "admin", "admin", crypt, salt)
  44. db.Close()
  45. return
  46. }
  47. connect = func(t *testing.T, f string, username, password string) (file string, db *sql.DB, c *SQLiteConn, err error) {
  48. conn = nil // Clear connection
  49. file = f // Copy provided file (f) => file
  50. if file == "" {
  51. // Create dummy file
  52. file = TempFilename(t)
  53. }
  54. db, err = sql.Open("sqlite3_with_conn", "file:"+file+fmt.Sprintf("?_auth&_auth_user=%s&_auth_pass=%s", username, password))
  55. if err != nil {
  56. defer os.Remove(file)
  57. return file, nil, nil, err
  58. }
  59. // Dummy query to force connection and database creation
  60. // Will return ErrUnauthorized (SQLITE_AUTH) if user authentication fails
  61. if _, err = db.Exec("SELECT 1;"); err != nil {
  62. defer os.Remove(file)
  63. defer db.Close()
  64. return file, nil, nil, err
  65. }
  66. c = conn
  67. return
  68. }
  69. connectWithCrypt = func(t *testing.T, f string, username, password string, crypt string, salt string) (file string, db *sql.DB, c *SQLiteConn, err error) {
  70. conn = nil // Clear connection
  71. file = f // Copy provided file (f) => file
  72. if file == "" {
  73. // Create dummy file
  74. file = TempFilename(t)
  75. }
  76. db, err = sql.Open("sqlite3_with_conn", "file:"+file+fmt.Sprintf("?_auth&_auth_user=%s&_auth_pass=%s&_auth_crypt=%s&_auth_salt=%s", username, password, crypt, salt))
  77. if err != nil {
  78. defer os.Remove(file)
  79. return file, nil, nil, err
  80. }
  81. // Dummy query to force connection and database creation
  82. // Will return ErrUnauthorized (SQLITE_AUTH) if user authentication fails
  83. if _, err = db.Exec("SELECT 1;"); err != nil {
  84. defer os.Remove(file)
  85. defer db.Close()
  86. return file, nil, nil, err
  87. }
  88. c = conn
  89. return
  90. }
  91. authEnabled = func(db *sql.DB) (exists bool, err error) {
  92. err = db.QueryRow("select count(type) from sqlite_master WHERE type='table' and name='sqlite_user';").Scan(&exists)
  93. return
  94. }
  95. addUser = func(db *sql.DB, username, password string, admin int) (rv int, err error) {
  96. err = db.QueryRow("select auth_user_add(?, ?, ?);", username, password, admin).Scan(&rv)
  97. return
  98. }
  99. userExists = func(db *sql.DB, username string) (rv int, err error) {
  100. err = db.QueryRow("select count(uname) from sqlite_user where uname=?", username).Scan(&rv)
  101. return
  102. }
  103. isAdmin = func(db *sql.DB, username string) (rv bool, err error) {
  104. err = db.QueryRow("select isAdmin from sqlite_user where uname=?", username).Scan(&rv)
  105. return
  106. }
  107. modifyUser = func(db *sql.DB, username, password string, admin int) (rv int, err error) {
  108. err = db.QueryRow("select auth_user_change(?, ?, ?);", username, password, admin).Scan(&rv)
  109. return
  110. }
  111. deleteUser = func(db *sql.DB, username string) (rv int, err error) {
  112. err = db.QueryRow("select auth_user_delete(?);", username).Scan(&rv)
  113. return
  114. }
  115. }
  116. func TestUserAuthCreateDatabase(t *testing.T) {
  117. f, db, c, err := connect(t, "", "admin", "admin")
  118. if err != nil && c == nil && db == nil {
  119. t.Fatal(err)
  120. }
  121. defer db.Close()
  122. defer os.Remove(f)
  123. enabled, err := authEnabled(db)
  124. if err != nil || !enabled {
  125. t.Fatalf("UserAuth not enabled: %s", err)
  126. }
  127. e, err := userExists(db, "admin")
  128. if err != nil {
  129. t.Fatal(err)
  130. }
  131. if e != 1 {
  132. t.Fatal("UserAuth: admin does not exists")
  133. }
  134. a, err := isAdmin(db, "admin")
  135. if err != nil {
  136. t.Fatal(err)
  137. }
  138. if !a {
  139. t.Fatal("UserAuth: User is not administrator")
  140. }
  141. }
  142. func TestUserAuthLogin(t *testing.T) {
  143. f1, err := create(t, "admin", "admin")
  144. if err != nil {
  145. t.Fatal(err)
  146. }
  147. defer os.Remove(f1)
  148. f2, db2, c2, err := connect(t, f1, "admin", "admin")
  149. if err != nil {
  150. t.Fatal(err)
  151. }
  152. defer db2.Close()
  153. if f1 != f2 {
  154. t.Fatal("UserAuth: Database file mismatch")
  155. }
  156. // Test lower level authentication
  157. err = c2.Authenticate("admin", "admin")
  158. if err != nil {
  159. t.Fatalf("UserAuth: *SQLiteConn.Authenticate() Failed: %s", err)
  160. }
  161. // Test Login Failed
  162. _, _, _, err = connect(t, f1, "admin", "invalid")
  163. if err == nil {
  164. t.Fatal("Login successful while expecting to fail")
  165. }
  166. if err != ErrUnauthorized {
  167. t.Fatal(err)
  168. }
  169. err = c2.Authenticate("admin", "invalid")
  170. if err == nil {
  171. t.Fatal("Login successful while expecting to fail")
  172. }
  173. if err != ErrUnauthorized {
  174. t.Fatal(err)
  175. }
  176. }
  177. func TestUserAuthAddAdmin(t *testing.T) {
  178. f, db, c, err := connect(t, "", "admin", "admin")
  179. if err != nil && c == nil && db == nil {
  180. t.Fatal(err)
  181. }
  182. defer db.Close()
  183. defer os.Remove(f)
  184. // Add Admin User through SQL call
  185. rv, err := addUser(db, "admin2", "admin2", 1)
  186. if err != nil {
  187. t.Fatal(err)
  188. }
  189. if rv != 0 {
  190. t.Fatal("Failed to add user")
  191. }
  192. // Check if user was created
  193. exists, err := userExists(db, "admin2")
  194. if err != nil {
  195. t.Fatal(err)
  196. }
  197. if exists != 1 {
  198. t.Fatal("UserAuth: 'admin2' does not exists")
  199. }
  200. // Check if user was created as an Administrator
  201. admin, err := isAdmin(db, "admin2")
  202. if err != nil {
  203. t.Fatal(err)
  204. }
  205. if !admin {
  206. t.Fatal("UserAuth: 'admin2' is not administrator")
  207. }
  208. // Test *SQLiteConn
  209. err = c.AuthUserAdd("admin3", "admin3", true)
  210. if err != nil {
  211. t.Fatal(err)
  212. }
  213. // Check if user was created
  214. exists, err = userExists(db, "admin2")
  215. if err != nil {
  216. t.Fatal(err)
  217. }
  218. if exists != 1 {
  219. t.Fatal("UserAuth: 'admin3' does not exists")
  220. }
  221. // Check if the user was created as an Administrator
  222. admin, err = isAdmin(db, "admin3")
  223. if err != nil {
  224. t.Fatal(err)
  225. }
  226. if !admin {
  227. t.Fatal("UserAuth: 'admin3' is not administrator")
  228. }
  229. }
  230. func TestUserAuthAddUser(t *testing.T) {
  231. f1, db1, c, err := connect(t, "", "admin", "admin")
  232. if err != nil && c == nil && db == nil {
  233. t.Fatal(err)
  234. }
  235. defer os.Remove(f1)
  236. // Add user through SQL call
  237. rv, err := addUser(db1, "user", "user", 0)
  238. if err != nil {
  239. t.Fatal(err)
  240. }
  241. if rv != 0 {
  242. t.Fatal("Failed to add user")
  243. }
  244. // Check if user was created
  245. exists, err := userExists(db1, "user")
  246. if err != nil {
  247. t.Fatal(err)
  248. }
  249. if exists != 1 {
  250. t.Fatal("UserAuth: 'user' does not exists")
  251. }
  252. // Check if user was created as an Administrator
  253. admin, err := isAdmin(db1, "user")
  254. if err != nil {
  255. t.Fatal(err)
  256. }
  257. if admin {
  258. t.Fatal("UserAuth: 'user' is administrator")
  259. }
  260. // Test *SQLiteConn
  261. err = c.AuthUserAdd("user2", "user2", false)
  262. if err != nil {
  263. t.Fatal(err)
  264. }
  265. // Check if user was created
  266. exists, err = userExists(db1, "user2")
  267. if err != nil {
  268. t.Fatal(err)
  269. }
  270. if exists != 1 {
  271. t.Fatal("UserAuth: 'user2' does not exists")
  272. }
  273. // Check if the user was created as an Administrator
  274. admin, err = isAdmin(db1, "user2")
  275. if err != nil {
  276. t.Fatal(err)
  277. }
  278. if admin {
  279. t.Fatal("UserAuth: 'user2' is administrator")
  280. }
  281. // Reconnect as normal user
  282. db1.Close()
  283. _, db2, c2, err := connect(t, f1, "user", "user")
  284. if err != nil {
  285. t.Fatal(err)
  286. }
  287. defer db2.Close()
  288. // Try to create admin user while logged in as normal user
  289. rv, err = addUser(db2, "admin2", "admin2", 1)
  290. if err != nil {
  291. t.Fatal(err)
  292. }
  293. if rv != SQLITE_AUTH {
  294. t.Fatal("Created admin user while not allowed")
  295. }
  296. err = c2.AuthUserAdd("admin3", "admin3", true)
  297. if err != ErrAdminRequired {
  298. t.Fatal("Created admin user while not allowed")
  299. }
  300. // Try to create normal user while logged in as normal user
  301. rv, err = addUser(db2, "user3", "user3", 0)
  302. if err != nil {
  303. t.Fatal(err)
  304. }
  305. if rv != SQLITE_AUTH {
  306. t.Fatal("Created user while not allowed")
  307. }
  308. err = c2.AuthUserAdd("user4", "user4", false)
  309. if err != ErrAdminRequired {
  310. t.Fatal("Created user while not allowed")
  311. }
  312. }
  313. func TestUserAuthModifyUser(t *testing.T) {
  314. f1, db1, c1, err := connect(t, "", "admin", "admin")
  315. if err != nil && c1 == nil && db == nil {
  316. t.Fatal(err)
  317. }
  318. defer os.Remove(f1)
  319. // Modify Password for current logged in admin
  320. // through SQL
  321. rv, err := modifyUser(db1, "admin", "admin2", 1)
  322. if err != nil {
  323. t.Fatal(err)
  324. }
  325. if rv != 0 {
  326. t.Fatal("Failed to modify password for admin")
  327. }
  328. // Modify password for current logged in admin
  329. // through *SQLiteConn
  330. err = c1.AuthUserChange("admin", "admin3", true)
  331. if err != nil {
  332. t.Fatal(err)
  333. }
  334. // Modify Administrator Flag
  335. // Because we are current logged in as 'admin'
  336. // Changing our own admin flag should fail.
  337. rv, err = modifyUser(db1, "admin", "admin3", 0)
  338. if err != nil {
  339. t.Fatal(err)
  340. }
  341. if rv != SQLITE_AUTH {
  342. t.Fatal("Successfully changed admin flag while not allowed")
  343. }
  344. // Modify admin flag through (*SQLiteConn)
  345. // Because we are current logged in as 'admin'
  346. // Changing our own admin flag should fail.
  347. err = c1.AuthUserChange("admin", "admin3", false)
  348. if err != ErrAdminRequired {
  349. t.Fatal("Successfully changed admin flag while not allowed")
  350. }
  351. // Add normal user
  352. rv, err = addUser(db1, "user", "password", 0)
  353. if err != nil {
  354. t.Fatal(err)
  355. }
  356. if rv != 0 {
  357. t.Fatal("Failed to add user")
  358. }
  359. rv, err = addUser(db1, "user2", "user2", 0)
  360. if err != nil {
  361. t.Fatal(err)
  362. }
  363. if rv != 0 {
  364. t.Fatal("Failed to add user")
  365. }
  366. // Modify other user password and flag through SQL
  367. rv, err = modifyUser(db1, "user", "pass", 1)
  368. if err != nil {
  369. t.Fatal(err)
  370. }
  371. if rv != 0 {
  372. t.Fatal("Failed to modify password for user")
  373. }
  374. // Modify other user password and flag through *SQLiteConn
  375. err = c1.AuthUserChange("user", "newpass", false)
  376. if err != nil {
  377. t.Fatal(err)
  378. }
  379. // Disconnect database for reconnect
  380. db1.Close()
  381. _, db2, c2, err := connect(t, f1, "user", "newpass")
  382. if err != nil {
  383. t.Fatal(err)
  384. }
  385. defer db2.Close()
  386. // Modify other user password through SQL
  387. rv, err = modifyUser(db2, "user2", "newpass", 0)
  388. if err != nil {
  389. t.Fatal(err)
  390. }
  391. if rv != SQLITE_AUTH {
  392. t.Fatal("Password change successful while not allowed")
  393. }
  394. // Modify other user password and flag through *SQLiteConn
  395. err = c2.AuthUserChange("user2", "invalid", false)
  396. if err != ErrAdminRequired {
  397. t.Fatal("Password change successful while not allowed")
  398. }
  399. }
  400. func TestUserAuthDeleteUser(t *testing.T) {
  401. f1, db1, c, err := connect(t, "", "admin", "admin")
  402. if err != nil && c == nil && db == nil {
  403. t.Fatal(err)
  404. }
  405. defer os.Remove(f1)
  406. // Add Admin User 2
  407. rv, err := addUser(db1, "admin2", "admin2", 1)
  408. if err != nil {
  409. t.Fatal(err)
  410. }
  411. if rv != 0 {
  412. t.Fatal("Failed to add user")
  413. }
  414. rv, err = addUser(db1, "admin3", "admin3", 1)
  415. if err != nil {
  416. t.Fatal(err)
  417. }
  418. if rv != 0 {
  419. t.Fatal("Failed to add user")
  420. }
  421. // Check if user was created
  422. exists, err := userExists(db1, "admin2")
  423. if err != nil {
  424. t.Fatal(err)
  425. }
  426. if exists != 1 {
  427. t.Fatal("UserAuth: 'admin2' does not exists")
  428. }
  429. exists, err = userExists(db1, "admin3")
  430. if err != nil {
  431. t.Fatal(err)
  432. }
  433. if exists != 1 {
  434. t.Fatal("UserAuth: 'admin2' does not exists")
  435. }
  436. // Delete user through SQL
  437. rv, err = deleteUser(db1, "admin2")
  438. if err != nil {
  439. t.Fatal(err)
  440. }
  441. if rv != 0 {
  442. t.Fatal("Failed to delete admin2")
  443. }
  444. // Verify user admin2 deleted
  445. exists, err = userExists(db1, "admin2")
  446. if err != nil {
  447. t.Fatal(err)
  448. }
  449. if exists != 0 {
  450. t.Fatal("UserAuth: 'admin2' still exists")
  451. }
  452. // Delete user through *SQLiteConn
  453. rv, err = deleteUser(db1, "admin3")
  454. if err != nil {
  455. t.Fatal(err)
  456. }
  457. if rv != 0 {
  458. t.Fatal("Failed to delete admin3")
  459. }
  460. // Verify user admin3 deleted
  461. exists, err = userExists(db1, "admin3")
  462. if err != nil {
  463. t.Fatal(err)
  464. }
  465. if exists != 0 {
  466. t.Fatal("UserAuth: 'admin3' still exists")
  467. }
  468. // Add normal user for reconnect and privileges check
  469. rv, err = addUser(db1, "reconnect", "reconnect", 0)
  470. if err != nil {
  471. t.Fatal(err)
  472. }
  473. if rv != 0 {
  474. t.Fatal("Failed to add user")
  475. }
  476. // Add normal user for deletion through SQL
  477. rv, err = addUser(db1, "user", "user", 0)
  478. if err != nil {
  479. t.Fatal(err)
  480. }
  481. if rv != 0 {
  482. t.Fatal("Failed to add user")
  483. }
  484. rv, err = addUser(db1, "user2", "user2", 0)
  485. if err != nil {
  486. t.Fatal(err)
  487. }
  488. if rv != 0 {
  489. t.Fatal("Failed to add user")
  490. }
  491. // Close database for reconnect
  492. db1.Close()
  493. // Reconnect as normal user
  494. _, db2, c2, err := connect(t, f1, "reconnect", "reconnect")
  495. if err != nil {
  496. t.Fatal(err)
  497. }
  498. defer db2.Close()
  499. // Delete user while logged in as normal user
  500. // through SQL
  501. rv, err = deleteUser(db2, "user")
  502. if err != nil {
  503. t.Fatal(err)
  504. }
  505. if rv != SQLITE_AUTH {
  506. t.Fatal("Successfully deleted user wthout proper privileges")
  507. }
  508. // Delete user while logged in as normal user
  509. // through *SQLiteConn
  510. err = c2.AuthUserDelete("user2")
  511. if err != ErrAdminRequired {
  512. t.Fatal("Successfully deleted user wthout proper privileges")
  513. }
  514. }
  515. func TestUserAuthEncoders(t *testing.T) {
  516. cases := map[string]string{
  517. "sha1": "",
  518. "ssha1": "salted",
  519. "sha256": "",
  520. "ssha256": "salted",
  521. "sha384": "",
  522. "ssha384": "salted",
  523. "sha512": "",
  524. "ssha512": "salted",
  525. }
  526. for enc, salt := range cases {
  527. f, err := createWithCrypt(t, "admin", "admin", enc, salt)
  528. if err != nil {
  529. t.Fatal(err)
  530. }
  531. defer os.Remove(f)
  532. _, db, _, err := connectWithCrypt(t, f, "admin", "admin", enc, salt)
  533. if err != nil {
  534. t.Fatal(err)
  535. }
  536. defer db.Close()
  537. if e, err := authEnabled(db); err != nil && !e {
  538. t.Fatalf("UserAuth (%s) not enabled %s", enc, err)
  539. }
  540. }
  541. }