From 6ae7f98274c6e254f84a9b8349b4a4eed0bee8a5 Mon Sep 17 00:00:00 2001 From: Gert-Jan Timmer Date: Wed, 30 May 2018 22:36:49 +0200 Subject: ADD: User authentication * User Authentication Implementation * Rename file to conform to fileformat `sqlite3_*_omit.go` * Updated sqlite3-binding.* with new upgrade tool * Add: callbackRetNil required for error type return because of adding `RegisterFunc`s directly on the connection. * Add: TestCreateAuthDatabase --- sqlite3.go | 129 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++- 1 file changed, 127 insertions(+), 2 deletions(-) (limited to 'sqlite3.go') diff --git a/sqlite3.go b/sqlite3.go index 75a5ee8..8a15696 100644 --- a/sqlite3.go +++ b/sqlite3.go @@ -891,6 +891,9 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // Options var loc *time.Location + authCreate := false + authUser := "" + authPass := "" mutex := C.int(C.SQLITE_OPEN_FULLMUTEX) txlock := "BEGIN" @@ -916,6 +919,17 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { return nil, err } + // Authentication + if _, ok := params["_auth"]; ok { + authCreate = true + } + if val := params.Get("_auth_user"); val != "" { + authUser = val + } + if val := params.Get("_auth_pass"); val != "" { + authPass = val + } + // _loc if val := params.Get("_loc"); val != "" { switch strings.ToLower(val) { @@ -1248,7 +1262,93 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { return nil } + // USER AUTHENTICATION + // + // User Authentication is always performed even when + // sqlite_userauth is not compiled in, because without user authentication + // the authentication is a no-op. + // + // Workflow + // - Authenticate + // ON::SUCCESS => Continue + // ON::SQLITE_AUTH => Return error and exit Open(...) + // + // - Activate User Authentication + // Check if the user wants to activate User Authentication. + // If so then first create a temporary AuthConn to the database + // This is possible because we are already succesfully authenticated. + // + // - Check if `sqlite_user`` table exists + // YES => Add the provided user from DSN as Admin User and + // activate user authentication. + // NO => Continue + // + + // Create connection to SQLite + conn := &SQLiteConn{db: db, loc: loc, txlock: txlock} + + // Preform Authentication + if err := conn.Authenticate(authUser, authPass); err != nil { + return nil, err + } + + // Register Authentication Functions into connection + // + // Register Authentication function + // Authenticate will perform an authentication of the provided username + // and password against the database. + // + // If a database contains the SQLITE_USER table, then the + // call to Authenticate must be invoked with an + // appropriate username and password prior to enable read and write + //access to the database. + // + // Return SQLITE_OK on success or SQLITE_ERROR if the username/password + // combination is incorrect or unknown. + // + // If the SQLITE_USER table is not present in the database file, then + // this interface is a harmless no-op returnning SQLITE_OK. + if err := conn.RegisterFunc("authenticate", conn.Authenticate, true); err != nil { + return nil, err + } + // + // Register AuthUserAdd + // AuthUserAdd can be used (by an admin user only) + // to create a new user. When called on a no-authentication-required + // database, this routine converts the database into an authentication- + // required database, automatically makes the added user an + // administrator, and logs in the current connection as that user. + // The AuthUserAdd only works for the "main" database, not + // for any ATTACH-ed databases. Any call to AuthUserAdd by a + // non-admin user results in an error. + if err := conn.RegisterFunc("auth_user_add", conn.AuthUserAdd, true); err != nil { + return nil, err + } + // + // AuthUserChange can be used to change a users + // login credentials or admin privilege. Any user can change their own + // login credentials. Only an admin user can change another users login + // credentials or admin privilege setting. No user may change their own + // admin privilege setting. + if err := conn.RegisterFunc("auth_user_change", conn.AuthUserChange, true); err != nil { + return nil, err + } + // + // AuthUserDelete can be used (by an admin user only) + // to delete a user. The currently logged-in user cannot be deleted, + // which guarantees that there is always an admin user and hence that + // the database cannot be converted into a no-authentication-required + // database. + if err := conn.RegisterFunc("auth_user_delete", conn.AuthUserDelete, true); err != nil { + return nil, err + } + // Auto Vacuum + // Moved auto_vacuum command, the user preference for auto_vacuum needs to be implemented directly after + // the authentication and before the sqlite_user table gets created if the user + // decides to activate User Authentication because + // auto_vacuum needs to be set before any tables are created + // and activating user authentication creates the internal table `sqlite_user`. if autoVacuum > -1 { if err := exec(fmt.Sprintf("PRAGMA auto_vacuum = %d;", autoVacuum)); err != nil { C.sqlite3_close_v2(db) @@ -1256,6 +1356,33 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { } } + // Check if user wants to activate User Authentication + if authCreate { + // Before going any further, we need to check that the user + // has provided an username and password within the DSN. + // We are not allowed to continue. + if len(authUser) < 0 { + return nil, fmt.Errorf("Missing '_auth_user' while user authentication was requested with '_auth'") + } + if len(authPass) < 0 { + return nil, fmt.Errorf("Missing '_auth_pass' while user authentication was requested with '_auth'") + } + + // TODO: Table exists check for table 'sqlite_user' + // replace 'authExists := false' with return value of table exists check + // + // REPLACE BY RESULT FROM TABLE EXISTS + // SELECT count(type) as exists FROM sqlite_master WHERE type='table' AND name='sqlite_user'; + // Scan result 'exists' and use it instead of boolean below. + authExists := false + + if !authExists { + if err := conn.AuthUserAdd(authUser, authPass, true); err != nil { + return nil, err + } + } + } + // Case Sensitive LIKE if caseSensitiveLike > -1 { if err := exec(fmt.Sprintf("PRAGMA case_sensitive_like = %d;", caseSensitiveLike)); err != nil { @@ -1347,8 +1474,6 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { } } - conn := &SQLiteConn{db: db, loc: loc, txlock: txlock} - if len(d.Extensions) > 0 { if err := conn.loadExtensions(d.Extensions); err != nil { conn.Close() -- cgit v1.2.3 From 183e7d61d12d767363bf91073584300a2d470507 Mon Sep 17 00:00:00 2001 From: Gert-Jan Timmer Date: Thu, 31 May 2018 14:55:22 +0200 Subject: UPD: User Authentication Implemented table check; only activate User Authentication on a database which has no UA enabled. Closes #582 --- sqlite3.go | 10 ++-------- sqlite3_opt_userauth.go | 27 +++++++++++++++++++++++++++ 2 files changed, 29 insertions(+), 8 deletions(-) (limited to 'sqlite3.go') diff --git a/sqlite3.go b/sqlite3.go index 8a15696..56cb262 100644 --- a/sqlite3.go +++ b/sqlite3.go @@ -1368,14 +1368,8 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { return nil, fmt.Errorf("Missing '_auth_pass' while user authentication was requested with '_auth'") } - // TODO: Table exists check for table 'sqlite_user' - // replace 'authExists := false' with return value of table exists check - // - // REPLACE BY RESULT FROM TABLE EXISTS - // SELECT count(type) as exists FROM sqlite_master WHERE type='table' AND name='sqlite_user'; - // Scan result 'exists' and use it instead of boolean below. - authExists := false - + // Check if User Authentication is Enabled + authExists := conn.AuthIsEnabled() if !authExists { if err := conn.AuthUserAdd(authUser, authPass, true); err != nil { return nil, err diff --git a/sqlite3_opt_userauth.go b/sqlite3_opt_userauth.go index 2892dd0..2f5da0e 100644 --- a/sqlite3_opt_userauth.go +++ b/sqlite3_opt_userauth.go @@ -40,6 +40,23 @@ _sqlite3_user_delete(sqlite3* db, const char* zUsername) { return sqlite3_user_delete(db, zUsername); } + +static int +_sqlite3_auth_is_enabled(sqlite3* db) +{ + int exists = -1; + + sqlite3_stmt *stmt; + sqlite3_prepare_v2(db, "select count(type) from sqlite_master WHERE type='table' and name='sqlite_user';", -1, &stmt, NULL); + + while ( sqlite3_step(stmt) == SQLITE_ROW) { + exists = sqlite3_column_int(stmt, 0); + } + + sqlite3_finalize(stmt); + + return exists; +} */ import "C" import ( @@ -165,4 +182,14 @@ func (c *SQLiteConn) AuthUserDelete(username string) error { return nil } +// Check is database is protected by user authentication +func (c *SQLiteConn) AuthIsEnabled() (exists bool) { + rv := C._sqlite3_auth_is_enabled(c.db) + if rv == 1 { + exists = true + } + + return +} + // EOF -- cgit v1.2.3 From 4a33fcc1d2d467144ec063576131d346efb1f345 Mon Sep 17 00:00:00 2001 From: Gert-Jan Timmer Date: Thu, 31 May 2018 16:42:03 +0200 Subject: Stash [ci skip] --- sqlite3.go | 8 +++---- sqlite3_opt_userauth.go | 34 +++++++++++++++++++++++----- sqlite3_opt_userauth_omit.go | 5 ++++ sqlite3_opt_userauth_test.go | 54 ++++++++++++++++++++++++++++++++++++++++---- 4 files changed, 86 insertions(+), 15 deletions(-) (limited to 'sqlite3.go') diff --git a/sqlite3.go b/sqlite3.go index 56cb262..79b96ab 100644 --- a/sqlite3.go +++ b/sqlite3.go @@ -1308,7 +1308,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // // If the SQLITE_USER table is not present in the database file, then // this interface is a harmless no-op returnning SQLITE_OK. - if err := conn.RegisterFunc("authenticate", conn.Authenticate, true); err != nil { + if err := conn.RegisterFunc("authenticate", conn.Authenticate, false); err != nil { return nil, err } // @@ -1321,7 +1321,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // The AuthUserAdd only works for the "main" database, not // for any ATTACH-ed databases. Any call to AuthUserAdd by a // non-admin user results in an error. - if err := conn.RegisterFunc("auth_user_add", conn.AuthUserAdd, true); err != nil { + if err := conn.RegisterFunc("auth_user_add", conn.AuthUserAdd, false); err != nil { return nil, err } // @@ -1330,7 +1330,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // login credentials. Only an admin user can change another users login // credentials or admin privilege setting. No user may change their own // admin privilege setting. - if err := conn.RegisterFunc("auth_user_change", conn.AuthUserChange, true); err != nil { + if err := conn.RegisterFunc("auth_user_change", conn.AuthUserChange, false); err != nil { return nil, err } // @@ -1339,7 +1339,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // which guarantees that there is always an admin user and hence that // the database cannot be converted into a no-authentication-required // database. - if err := conn.RegisterFunc("auth_user_delete", conn.AuthUserDelete, true); err != nil { + if err := conn.RegisterFunc("auth_user_delete", conn.AuthUserDelete, false); err != nil { return nil, err } diff --git a/sqlite3_opt_userauth.go b/sqlite3_opt_userauth.go index 2f5da0e..197938b 100644 --- a/sqlite3_opt_userauth.go +++ b/sqlite3_opt_userauth.go @@ -60,6 +60,7 @@ _sqlite3_auth_is_enabled(sqlite3* db) */ import "C" import ( + "errors" "unsafe" ) @@ -67,6 +68,11 @@ const ( SQLITE_AUTH = C.SQLITE_AUTH ) +var ( + ErrUnauthorized = errors.New("SQLITE_AUTH: Unauthorized") + ErrAdminRequired = errors.New("SQLITE_AUTH: Unauthorized; Admin Privileges Required") +) + // Authenticate will perform an authentication of the provided username // and password against the database. // @@ -92,6 +98,9 @@ func (c *SQLiteConn) Authenticate(username, password string) error { }() rv := C._sqlite3_user_authenticate(c.db, cuser, cpass, C.int(len(password))) + if rv == C.SQLITE_AUTH { + return ErrUnauthorized + } if rv != C.SQLITE_OK { return c.lastError() } @@ -113,6 +122,18 @@ func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error { isAdmin = 1 } + rv := c.authUserAdd(username, password, isAdmin) + switch rv { + case C.SQLITE_AUTH: + return ErrAdminRequired + case C.SQLITE_OK: + return nil + default: + return c.lastError() + } +} + +func (c *SQLiteConn) authUserAdd(username, password string, admin int) int { // Allocate C Variables cuser := C.CString(username) cpass := C.CString(password) @@ -123,12 +144,7 @@ func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error { C.free(unsafe.Pointer(cpass)) }() - rv := C._sqlite3_user_add(c.db, cuser, cpass, C.int(len(password)), C.int(isAdmin)) - if rv != C.SQLITE_OK { - return c.lastError() - } - - return nil + return int(C._sqlite3_user_add(c.db, cuser, cpass, C.int(len(password)), C.int(admin))) } // AuthUserChange can be used to change a users @@ -153,6 +169,9 @@ func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error }() rv := C._sqlite3_user_change(c.db, cuser, cpass, C.int(len(password)), C.int(isAdmin)) + if rv == C.SQLITE_AUTH { + return ErrAdminRequired + } if rv != C.SQLITE_OK { return c.lastError() } @@ -175,6 +194,9 @@ func (c *SQLiteConn) AuthUserDelete(username string) error { }() rv := C._sqlite3_user_delete(c.db, cuser) + if rv == SQLITE_AUTH { + return ErrAdminRequired + } if rv != C.SQLITE_OK { return c.lastError() } diff --git a/sqlite3_opt_userauth_omit.go b/sqlite3_opt_userauth_omit.go index 0ae92da..3d1c758 100644 --- a/sqlite3_opt_userauth_omit.go +++ b/sqlite3_opt_userauth_omit.go @@ -62,4 +62,9 @@ func (c *SQLiteConn) AuthUserDelete(username string) error { return nil } +// Check is database is protected by user authentication +func (c *SQLiteConn) AuthIsEnabled() (exists bool) { + return +} + // EOF diff --git a/sqlite3_opt_userauth_test.go b/sqlite3_opt_userauth_test.go index 649d10e..fcbcd56 100644 --- a/sqlite3_opt_userauth_test.go +++ b/sqlite3_opt_userauth_test.go @@ -9,6 +9,7 @@ package sqlite3 import ( "database/sql" + "fmt" "os" "testing" ) @@ -23,11 +24,6 @@ func TestAuthCreateDatabase(t *testing.T) { } defer db.Close() - // Ping database - if err := db.Ping(); err != nil { - t.Fatal(err) - } - var exists bool err = db.QueryRow("select count(type) from sqlite_master WHERE type='table' and name='sqlite_user';").Scan(&exists) if err != nil { @@ -38,3 +34,51 @@ func TestAuthCreateDatabase(t *testing.T) { t.Fatal("failed to enable User Authentication") } } + +func TestAuthorization(t *testing.T) { + tempFilename := TempFilename(t) + fmt.Println(tempFilename) + //defer os.Remove(tempFilename) + + db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin") + if err != nil { + t.Fatal("Failed to open database:", err) + } + + if _, err := db.Exec("select auth_user_add('user', 'user', false);"); err != nil { + t.Fatal(err) + } + + var uname string + if err := db.QueryRow("select uname from sqlite_user where uname = 'user';").Scan(&uname); err != nil { + t.Fatal(err) + } + + if uname != "user" { + t.Fatal("Failed to create normal user") + } + db.Close() + + // Re-Open Database as User + // Add User should now fail because we are not admin + db, err = sql.Open("sqlite3", "file:"+tempFilename+"?_auth_user=user&_auth_pass=user") + if err != nil { + t.Fatal("Failed to open database:", err) + } + defer db.Close() + + // Try to create normal user + var rv string + if err := db.QueryRow("select auth_user_add('user2', 'user2', false);").Scan(&rv); err != nil { + t.Fatal(err) + } + fmt.Printf("RV: %v\n", rv) + // if rv != SQLITE_AUTH { + // t.Fatal("Succeeded creating user while not admin") + // } + + // // Try to create admin user + // if _, err := db.Exec("select auth_user_add('admin2', 'admin2', true);"); err != nil { + // t.Fatal(err) + // } +} -- cgit v1.2.3 From 0e289439a27f126dcc2fa96b2dd2d4cc9ecbbf7c Mon Sep 17 00:00:00 2001 From: Gert-Jan Timmer Date: Fri, 1 Jun 2018 11:28:04 +0200 Subject: Update User Authentication * Update bindings * Add user authentication sql functions Reference #579 --- sqlite3.go | 30 ++++++---- sqlite3_opt_userauth.go | 136 +++++++++++++++++++++++++++++++++---------- sqlite3_opt_userauth_omit.go | 88 +++++++++++++++++++++++++++- 3 files changed, 207 insertions(+), 47 deletions(-) (limited to 'sqlite3.go') diff --git a/sqlite3.go b/sqlite3.go index 79b96ab..4b33726 100644 --- a/sqlite3.go +++ b/sqlite3.go @@ -1292,9 +1292,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { return nil, err } - // Register Authentication Functions into connection - // - // Register Authentication function + // Register: authenticate // Authenticate will perform an authentication of the provided username // and password against the database. // @@ -1308,12 +1306,12 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // // If the SQLITE_USER table is not present in the database file, then // this interface is a harmless no-op returnning SQLITE_OK. - if err := conn.RegisterFunc("authenticate", conn.Authenticate, false); err != nil { + if err := conn.RegisterFunc("authenticate", conn.authenticate, false); err != nil { return nil, err } // - // Register AuthUserAdd - // AuthUserAdd can be used (by an admin user only) + // Register: auth_user_add + // auth_user_add can be used (by an admin user only) // to create a new user. When called on a no-authentication-required // database, this routine converts the database into an authentication- // required database, automatically makes the added user an @@ -1321,25 +1319,33 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // The AuthUserAdd only works for the "main" database, not // for any ATTACH-ed databases. Any call to AuthUserAdd by a // non-admin user results in an error. - if err := conn.RegisterFunc("auth_user_add", conn.AuthUserAdd, false); err != nil { + if err := conn.RegisterFunc("auth_user_add", conn.authUserAdd, false); err != nil { return nil, err } // - // AuthUserChange can be used to change a users + // Register: auth_user_change + // auth_user_change can be used to change a users // login credentials or admin privilege. Any user can change their own // login credentials. Only an admin user can change another users login // credentials or admin privilege setting. No user may change their own // admin privilege setting. - if err := conn.RegisterFunc("auth_user_change", conn.AuthUserChange, false); err != nil { + if err := conn.RegisterFunc("auth_user_change", conn.authUserChange, false); err != nil { return nil, err } // - // AuthUserDelete can be used (by an admin user only) + // Register: auth_user_delete + // auth_user_delete can be used (by an admin user only) // to delete a user. The currently logged-in user cannot be deleted, // which guarantees that there is always an admin user and hence that // the database cannot be converted into a no-authentication-required // database. - if err := conn.RegisterFunc("auth_user_delete", conn.AuthUserDelete, false); err != nil { + if err := conn.RegisterFunc("auth_user_delete", conn.authUserDelete, false); err != nil { + return nil, err + } + + // Register: auth_enabled + // auth_enabled can be used to check if user authentication is enabled + if err := conn.RegisterFunc("auth_enabled", conn.authEnabled, false); err != nil { return nil, err } @@ -1369,7 +1375,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { } // Check if User Authentication is Enabled - authExists := conn.AuthIsEnabled() + authExists := conn.AuthEnabled() if !authExists { if err := conn.AuthUserAdd(authUser, authPass, true); err != nil { return nil, err diff --git a/sqlite3_opt_userauth.go b/sqlite3_opt_userauth.go index 197938b..94203b3 100644 --- a/sqlite3_opt_userauth.go +++ b/sqlite3_opt_userauth.go @@ -42,7 +42,7 @@ _sqlite3_user_delete(sqlite3* db, const char* zUsername) } static int -_sqlite3_auth_is_enabled(sqlite3* db) +_sqlite3_auth_enabled(sqlite3* db) { int exists = -1; @@ -87,6 +87,26 @@ var ( // If the SQLITE_USER table is not present in the database file, then // this interface is a harmless no-op returnning SQLITE_OK. func (c *SQLiteConn) Authenticate(username, password string) error { + rv := c.authenticate(username, password) + switch rv { + case C.SQLITE_ERROR, C.SQLITE_AUTH: + return ErrUnauthorized + case C.SQLITE_OK: + return nil + default: + return c.lastError() + } +} + +// authenticate provides the actual authentication to SQLite. +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authenticate(username, password string) int { // Allocate C Variables cuser := C.CString(username) cpass := C.CString(password) @@ -97,15 +117,7 @@ func (c *SQLiteConn) Authenticate(username, password string) error { C.free(unsafe.Pointer(cpass)) }() - rv := C._sqlite3_user_authenticate(c.db, cuser, cpass, C.int(len(password))) - if rv == C.SQLITE_AUTH { - return ErrUnauthorized - } - if rv != C.SQLITE_OK { - return c.lastError() - } - - return nil + return int(C._sqlite3_user_authenticate(c.db, cuser, cpass, C.int(len(password)))) } // AuthUserAdd can be used (by an admin user only) @@ -124,7 +136,7 @@ func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error { rv := c.authUserAdd(username, password, isAdmin) switch rv { - case C.SQLITE_AUTH: + case C.SQLITE_ERROR, C.SQLITE_AUTH: return ErrAdminRequired case C.SQLITE_OK: return nil @@ -133,6 +145,19 @@ func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error { } } +// authUserAdd enables the User Authentication if not enabled. +// Otherwise it will add a user. +// +// When user authentication is already enabled then this function +// can only be called by an admin. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) func (c *SQLiteConn) authUserAdd(username, password string, admin int) int { // Allocate C Variables cuser := C.CString(username) @@ -158,6 +183,34 @@ func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error isAdmin = 1 } + rv := c.authUserChange(username, password, isAdmin) + switch rv { + case C.SQLITE_ERROR, C.SQLITE_AUTH: + return ErrAdminRequired + case C.SQLITE_OK: + return nil + default: + return c.lastError() + } +} + +// authUserChange allows to modify a user. +// Users can change their own password. +// +// Only admins can change passwords for other users +// and modify the admin flag. +// +// The admin flag of the current logged in user cannot be changed. +// THis ensures that their is always an admin. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authUserChange(username, password string, admin int) int { // Allocate C Variables cuser := C.CString(username) cpass := C.CString(password) @@ -168,15 +221,7 @@ func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error C.free(unsafe.Pointer(cpass)) }() - rv := C._sqlite3_user_change(c.db, cuser, cpass, C.int(len(password)), C.int(isAdmin)) - if rv == C.SQLITE_AUTH { - return ErrAdminRequired - } - if rv != C.SQLITE_OK { - return c.lastError() - } - - return nil + return int(C._sqlite3_user_change(c.db, cuser, cpass, C.int(len(password)), C.int(admin))) } // AuthUserDelete can be used (by an admin user only) @@ -185,6 +230,29 @@ func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error // the database cannot be converted into a no-authentication-required // database. func (c *SQLiteConn) AuthUserDelete(username string) error { + rv := c.authUserDelete(username) + switch rv { + case C.SQLITE_ERROR, C.SQLITE_AUTH: + return ErrAdminRequired + case C.SQLITE_OK: + return nil + default: + return c.lastError() + } +} + +// authUserDelete can be used to delete a user. +// +// This function can only be executed by an admin. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authUserDelete(username string) int { // Allocate C Variables cuser := C.CString(username) @@ -193,20 +261,12 @@ func (c *SQLiteConn) AuthUserDelete(username string) error { C.free(unsafe.Pointer(cuser)) }() - rv := C._sqlite3_user_delete(c.db, cuser) - if rv == SQLITE_AUTH { - return ErrAdminRequired - } - if rv != C.SQLITE_OK { - return c.lastError() - } - - return nil + return int(C._sqlite3_user_delete(c.db, cuser)) } -// Check is database is protected by user authentication -func (c *SQLiteConn) AuthIsEnabled() (exists bool) { - rv := C._sqlite3_auth_is_enabled(c.db) +// AuthEnabled checks if the database is protected by user authentication +func (c *SQLiteConn) AuthEnabled() (exists bool) { + rv := c.authEnabled() if rv == 1 { exists = true } @@ -214,4 +274,16 @@ func (c *SQLiteConn) AuthIsEnabled() (exists bool) { return } +// authEnabled perform the actual check for user authentication. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// 0 - Disabled +// 1 - Enabled +func (c *SQLiteConn) authEnabled() int { + return int(C._sqlite3_auth_enabled(c.db)) +} + // EOF diff --git a/sqlite3_opt_userauth_omit.go b/sqlite3_opt_userauth_omit.go index 3d1c758..302cd57 100644 --- a/sqlite3_opt_userauth_omit.go +++ b/sqlite3_opt_userauth_omit.go @@ -29,6 +29,19 @@ func (c *SQLiteConn) Authenticate(username, password string) error { return nil } +// authenticate provides the actual authentication to SQLite. +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authenticate(username, password string) int { + // NOOP + return 0 +} + // AuthUserAdd can be used (by an admin user only) // to create a new user. When called on a no-authentication-required // database, this routine converts the database into an authentication- @@ -42,6 +55,24 @@ func (c *SQLiteConn) AuthUserAdd(username, password string, admin bool) error { return nil } +// authUserAdd enables the User Authentication if not enabled. +// Otherwise it will add a user. +// +// When user authentication is already enabled then this function +// can only be called by an admin. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authUserAdd(username, password string, admin int) int { + // NOOP + return 0 +} + // AuthUserChange can be used to change a users // login credentials or admin privilege. Any user can change their own // login credentials. Only an admin user can change another users login @@ -52,6 +83,27 @@ func (c *SQLiteConn) AuthUserChange(username, password string, admin bool) error return nil } +// authUserChange allows to modify a user. +// Users can change their own password. +// +// Only admins can change passwords for other users +// and modify the admin flag. +// +// The admin flag of the current logged in user cannot be changed. +// THis ensures that their is always an admin. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authUserChange(username, password string, admin int) int { + // NOOP + return 0 +} + // AuthUserDelete can be used (by an admin user only) // to delete a user. The currently logged-in user cannot be deleted, // which guarantees that there is always an admin user and hence that @@ -62,9 +114,39 @@ func (c *SQLiteConn) AuthUserDelete(username string) error { return nil } -// Check is database is protected by user authentication -func (c *SQLiteConn) AuthIsEnabled() (exists bool) { - return +// authUserDelete can be used to delete a user. +// +// This function can only be executed by an admin. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// C.SQLITE_OK (0) +// C.SQLITE_ERROR (1) +// C.SQLITE_AUTH (23) +func (c *SQLiteConn) authUserDelete(username string) int { + // NOOP + return 0 +} + +// AuthEnabled checks if the database is protected by user authentication +func (c *SQLiteConn) AuthEnabled() (exists bool) { + // NOOP + return false +} + +// authEnabled perform the actual check for user authentication. +// +// This is not exported for usage in Go. +// It is however exported for usage within SQL by the user. +// +// Returns: +// 0 - Disabled +// 1 - Enabled +func (c *SQLiteConn) authEnabled() int { + // NOOP + return 0 } // EOF -- cgit v1.2.3 From 11627e448371b6f065fbba2d297cc7f622f5eeac Mon Sep 17 00:00:00 2001 From: Gert-Jan Timmer Date: Fri, 1 Jun 2018 17:59:42 +0200 Subject: Implemented goconvey for User Authentication Tests Reference #580 --- .travis.yml | 1 + sqlite3.go | 10 +- sqlite3.goconvey | 11 + sqlite3_opt_userauth_test.go | 1193 ++++++++++++++++++++++++++++++++++++------ upgrade/package.go | 7 + upgrade/profile.goconvey | 2 + 6 files changed, 1066 insertions(+), 158 deletions(-) create mode 100644 sqlite3.goconvey create mode 100644 upgrade/package.go create mode 100644 upgrade/profile.goconvey (limited to 'sqlite3.go') diff --git a/.travis.yml b/.travis.yml index ba8c891..2dca239 100644 --- a/.travis.yml +++ b/.travis.yml @@ -88,6 +88,7 @@ before_install: fi - | if [[ "${GOOS}" != "windows" ]]; then + go get github.com/smartystreets/goconvey go get github.com/mattn/goveralls go get golang.org/x/tools/cmd/cover fi diff --git a/sqlite3.go b/sqlite3.go index 4b33726..e6f8c55 100644 --- a/sqlite3.go +++ b/sqlite3.go @@ -1306,7 +1306,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // // If the SQLITE_USER table is not present in the database file, then // this interface is a harmless no-op returnning SQLITE_OK. - if err := conn.RegisterFunc("authenticate", conn.authenticate, false); err != nil { + if err := conn.RegisterFunc("authenticate", conn.authenticate, true); err != nil { return nil, err } // @@ -1319,7 +1319,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // The AuthUserAdd only works for the "main" database, not // for any ATTACH-ed databases. Any call to AuthUserAdd by a // non-admin user results in an error. - if err := conn.RegisterFunc("auth_user_add", conn.authUserAdd, false); err != nil { + if err := conn.RegisterFunc("auth_user_add", conn.authUserAdd, true); err != nil { return nil, err } // @@ -1329,7 +1329,7 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // login credentials. Only an admin user can change another users login // credentials or admin privilege setting. No user may change their own // admin privilege setting. - if err := conn.RegisterFunc("auth_user_change", conn.authUserChange, false); err != nil { + if err := conn.RegisterFunc("auth_user_change", conn.authUserChange, true); err != nil { return nil, err } // @@ -1339,13 +1339,13 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // which guarantees that there is always an admin user and hence that // the database cannot be converted into a no-authentication-required // database. - if err := conn.RegisterFunc("auth_user_delete", conn.authUserDelete, false); err != nil { + if err := conn.RegisterFunc("auth_user_delete", conn.authUserDelete, true); err != nil { return nil, err } // Register: auth_enabled // auth_enabled can be used to check if user authentication is enabled - if err := conn.RegisterFunc("auth_enabled", conn.authEnabled, false); err != nil { + if err := conn.RegisterFunc("auth_enabled", conn.authEnabled, true); err != nil { return nil, err } diff --git a/sqlite3.goconvey b/sqlite3.goconvey new file mode 100644 index 0000000..73c2bb6 --- /dev/null +++ b/sqlite3.goconvey @@ -0,0 +1,11 @@ +// GoConvey Test Profile + +// Activate Coverage +-cover + +-run=TestUserAuthentication + +// Test Flags +-tags=sqlite_userauth + +// EOF \ No newline at end of file diff --git a/sqlite3_opt_userauth_test.go b/sqlite3_opt_userauth_test.go index 4755550..fbe1b92 100644 --- a/sqlite3_opt_userauth_test.go +++ b/sqlite3_opt_userauth_test.go @@ -9,201 +9,1088 @@ package sqlite3 import ( "database/sql" + "fmt" "os" "testing" + + . "github.com/smartystreets/goconvey/convey" ) -func TestAuthCreateDatabase(t *testing.T) { - tempFilename := TempFilename(t) - defer os.Remove(tempFilename) +func init() { - db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin") - if err != nil { - t.Fatal("Failed to open database:", err) - } - defer db.Close() +} - var exists bool - err = db.QueryRow("select count(type) from sqlite_master WHERE type='table' and name='sqlite_user';").Scan(&exists) - if err != nil { - t.Fatal(err) - } +func TestUserAuthentication(t *testing.T) { + // Create database connection + var conn *SQLiteConn + sql.Register("sqlite3_with_conn", + &SQLiteDriver{ + ConnectHook: func(c *SQLiteConn) error { + conn = c + return nil + }, + }) - if !exists { - t.Fatal("failed to enable User Authentication") - } -} + connect := func(f string, username, password string) (file string, db *sql.DB, c *SQLiteConn, err error) { + conn = nil // Clear connection + file = f // Copy provided file (f) => file + if file == "" { + // Create dummy file + file = TempFilename(t) + } -func TestAuthorization(t *testing.T) { - tempFilename := TempFilename(t) - defer os.Remove(tempFilename) + db, err = sql.Open("sqlite3_with_conn", "file:"+file+fmt.Sprintf("?_auth&_auth_user=%s&_auth_pass=%s", username, password)) + if err != nil { + defer os.Remove(file) + return file, nil, nil, err + } - db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin") - if err != nil { - t.Fatal("Failed to open database:", err) - } + // Dummy query to force connection and database creation + // Will return ErrUnauthorized (SQLITE_AUTH) if user authentication fails + if _, err = db.Exec("SELECT 1;"); err != nil { + defer os.Remove(file) + defer db.Close() + return file, nil, nil, err + } + c = conn - // Dummy Query to force connection - if _, err := db.Exec("SELECT 1;"); err != nil { - t.Fatalf("Failed to connect: %s", err) + return } - // Add normal user to database - if _, err := db.Exec("select auth_user_add('user', 'user', false);"); err != nil { - t.Fatal(err) + authEnabled := func(db *sql.DB) (exists bool, err error) { + err = db.QueryRow("select count(type) from sqlite_master WHERE type='table' and name='sqlite_user';").Scan(&exists) + return } - var uname string - if err := db.QueryRow("select uname from sqlite_user where uname = 'user';").Scan(&uname); err != nil { - t.Fatal(err) + addUser := func(db *sql.DB, username, password string, admin int) (rv int, err error) { + err = db.QueryRow("select auth_user_add(?, ?, ?);", username, password, admin).Scan(&rv) + return } - if uname != "user" { - t.Fatal("Failed to create normal user") + + userExists := func(db *sql.DB, username string) (rv int, err error) { + err = db.QueryRow("select count(uname) from sqlite_user where uname=?", username).Scan(&rv) + return } - db.Close() - // Re-Open Database as User - db, err = sql.Open("sqlite3", "file:"+tempFilename+"?_auth_user=user&_auth_pass=user") - if err != nil { - t.Fatal("Failed to open database:", err) + isAdmin := func(db *sql.DB, username string) (rv bool, err error) { + err = db.QueryRow("select isAdmin from sqlite_user where uname=?", username).Scan(&rv) + return } - defer db.Close() - // Add User should now fail because we are not admin - var rv int - if err := db.QueryRow("select auth_user_add('user2', 'user2', false);").Scan(&rv); err != nil || rv == 0 { - if err != nil { - t.Fatal(err) - } - t.Fatal("Succeeded creating user, while not being admin, this is not supposed to work") + modifyUser := func(db *sql.DB, username, password string, admin int) (rv int, err error) { + err = db.QueryRow("select auth_user_change(?, ?, ?);", username, password, admin).Scan(&rv) + return } - // Try to create admin user - // Should also fail because we are not admin - if err := db.QueryRow("select auth_user_add('admin2', 'admin2', true);").Scan(&rv); err != nil || rv == 0 { - if err != nil { - t.Fatal(err) - } - t.Fatal("Succeeded creating admin, while not being admin, this is not supposed to work") + deleteUser := func(db *sql.DB, username string) (rv int, err error) { + err = db.QueryRow("select auth_user_delete(?);", username).Scan(&rv) + return } -} -func TestAuthorizationFailed(t *testing.T) { - tempFilename := TempFilename(t) - defer os.Remove(tempFilename) + Convey("Create Database", t, func() { + _, db, c, err := connect("", "admin", "admin") + So(db, ShouldNotBeNil) + So(c, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db.Close() - db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin") - if err != nil { - t.Fatal("Failed to open database:", err) - } + b, err := authEnabled(db) + So(b, ShouldEqual, true) + So(err, ShouldBeNil) - // Dummy Query to force connection - if _, err := db.Exec("SELECT 1;"); err != nil { - t.Fatalf("Failed to connect: %s", err) - } - db.Close() + e, err := userExists(db, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) - db, err = sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=invalid") - if err != nil { - t.Fatal("Failed to open database:", err) - } - defer db.Close() + a, err := isAdmin(db, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + }) - // Dummy Query to issue connection - if _, err := db.Exec("SELECT 1;"); err != nil && err != ErrUnauthorized { - t.Fatalf("Failed to connect: %s", err) - } -} + Convey("Authorization Success", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) -func TestAuthUserModify(t *testing.T) { - tempFilename := TempFilename(t) - defer os.Remove(tempFilename) + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) - var rv int + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() - db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin") - if err != nil { - t.Fatal("Failed to open database:", err) - } + // Preform authentication + f2, db2, c2, err := connect(f1, "admin", "admin") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) - // Dummy Query to force connection - if _, err := db.Exec("SELECT 1;"); err != nil { - t.Fatalf("Failed to connect: %s", err) - } + Convey("Authorization Success (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db1.Close() - if err := db.QueryRow("select auth_user_add('user', 'user', false);").Scan(&rv); err != nil || rv != 0 { - if err != nil { - t.Fatal(err) - } - t.Fatal("Failed to create normal user") - } + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) - if err := db.QueryRow("select auth_user_change('admin', 'nimda', true);").Scan(&rv); err != nil || rv != 0 { - if err != nil { - t.Fatal(err) - } - t.Fatal("Failed to change password") - } - db.Close() + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) - // Re-Connect with new credentials - db, err = sql.Open("sqlite3", "file:"+tempFilename+"?_auth_user=admin&_auth_pass=nimda") - if err != nil { - t.Fatal("Failed to open database:", err) - } + // Test lower level authentication + err = c1.Authenticate("admin", "admin") + So(err, ShouldBeNil) + }) - if err := db.QueryRow("select count(uname) from sqlite_user where uname = 'admin';").Scan(&rv); err != nil { - t.Fatal(err) - } - defer db.Close() + Convey("Authorization Failed", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) - // Dummy Query to force connection to test authorization - if _, err := db.Exec("SELECT 1;"); err != nil && err != ErrUnauthorized { - t.Fatalf("Failed to connect: %s", err) - } -} + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) -func TestAuthUserDelete(t *testing.T) { - tempFilename := TempFilename(t) - defer os.Remove(tempFilename) + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) - var rv int + // Perform Invalid Authentication when we connect + // to a database + f2, db2, c2, err := connect(f1, "admin", "invalid") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldBeNil) + So(c2, ShouldBeNil) + So(err, ShouldEqual, ErrUnauthorized) + }) - db, err := sql.Open("sqlite3", "file:"+tempFilename+"?_auth&_auth_user=admin&_auth_pass=admin") - if err != nil { - t.Fatal("Failed to open database:", err) - } - defer db.Close() + Convey("Authorization Failed (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db1.Close() - // Dummy Query to force connection to test authorization - if _, err := db.Exec("SELECT 1;"); err != nil { - t.Fatalf("Failed to connect: %s", err) - } + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) - // Add User - if _, err := db.Exec("select auth_user_add('user', 'user', false);"); err != nil { - t.Fatal(err) - } + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) - // Verify, their should be now 2 users - var users int - if err := db.QueryRow("select count(uname) from sqlite_user;").Scan(&users); err != nil { - t.Fatal(err) - } - if users != 2 { - t.Fatal("Failed to add user") - } + // Test lower level authentication + // We require a successful *SQLiteConn to test this. + err = c1.Authenticate("admin", "invalid") + So(err, ShouldNotBeNil) + So(err, ShouldEqual, ErrUnauthorized) + }) - // Delete User - if _, err := db.Exec("select auth_user_delete('user');"); err != nil { - t.Fatal(err) - } + Convey("Add Admin User", t, func() { + _, db, c, err := connect("", "admin", "admin") + So(db, ShouldNotBeNil) + So(c, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db.Close() - // Verify their should now only be 1 user remaining, the current logged in admin user - if err := db.QueryRow("select count(uname) from sqlite_user;").Scan(&users); err != nil { - t.Fatal(err) - } - if users != 1 { - t.Fatal("Failed to delete user") - } + e, err := userExists(db, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Admin User + rv, err := addUser(db, "admin2", "admin2", 1) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + e, err = userExists(db, "admin2") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db, "admin2") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + }) + + Convey("Add Admin User (*SQLiteConn)", t, func() { + _, db, c, err := connect("", "admin", "admin") + So(db, ShouldNotBeNil) + So(c, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db.Close() + + e, err := userExists(db, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Test lower level AuthUserAdd + err = c.AuthUserAdd("admin2", "admin2", true) + So(err, ShouldBeNil) + + e, err = userExists(db, "admin2") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db, "admin2") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + }) + + Convey("Add Normal User", t, func() { + _, db, c, err := connect("", "admin", "admin") + So(db, ShouldNotBeNil) + So(c, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db.Close() + + e, err := userExists(db, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + e, err = userExists(db, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + }) + + Convey("Add Normal User (*SQLiteConn)", t, func() { + _, db, c, err := connect("", "admin", "admin") + So(db, ShouldNotBeNil) + So(c, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db.Close() + + e, err := userExists(db, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Test lower level AuthUserAdd + err = c.AuthUserAdd("user", "user", false) + So(err, ShouldBeNil) + + e, err = userExists(db, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + }) + + Convey("Add Admin User Insufficient Privileges", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + // Add Admin User + // Because 'user' is not admin + // Adding an admin user should now fail + // because we have insufficient privileges + rv, err = addUser(db2, "admin2", "admin2", 1) + So(rv, ShouldEqual, SQLITE_AUTH) + So(err, ShouldBeNil) + }) + + Convey("Add Admin User Insufficient Privileges (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + // Add Admin User + // Because 'user' is not admin + // Adding an admin user should now fail + // because we have insufficient privileges + err = c2.AuthUserAdd("admin2", "admin2", true) + So(err, ShouldNotBeNil) + So(err, ShouldEqual, ErrAdminRequired) + }) + + Convey("Add Normal User Insufficient Privileges", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + // Add Normal User + // Because 'user' is not admin + // Adding an normal user should now fail + // because we have insufficient privileges + rv, err = addUser(db2, "user2", "user2", 0) + So(rv, ShouldEqual, SQLITE_AUTH) + So(err, ShouldBeNil) + }) + + Convey("Add Normal User Insufficient Privileges (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + // Add Normal User + // Because 'user' is not admin + // Adding an normal user should now fail + // because we have insufficient privileges + // Test lower level AuthUserAdd + err = c2.AuthUserAdd("user2", "user2", false) + So(err, ShouldNotBeNil) + So(err, ShouldEqual, ErrAdminRequired) + }) + + Convey("Modify Current Connection Password", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Modify Password + rv, err := modifyUser(db1, "admin", "admin2", 1) + So(err, ShouldBeNil) + So(rv, ShouldEqual, 0) + db1.Close() + + // Reconnect with new password + f2, db2, c2, err := connect(f1, "admin", "admin2") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("Modify Current Connection Password (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db1.Close() + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Modify password through (*SQLiteConn) + err = c1.AuthUserChange("admin", "admin2", true) + So(err, ShouldBeNil) + + // Reconnect with new password + f2, db2, c2, err := connect(f1, "admin", "admin2") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("Modify Current Connection Admin Flag", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db1.Close() + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Modify Administrator Flag + // Because we are current logged in as 'admin' + // Changing our own admin flag should fail. + rv, err := modifyUser(db1, "admin", "admin", 0) + So(err, ShouldBeNil) + So(rv, ShouldEqual, SQLITE_AUTH) + }) + + Convey("Modify Current Connection Admin Flag (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db1.Close() + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Modify admin flag through (*SQLiteConn) + // Because we are current logged in as 'admin' + // Changing our own admin flag should fail. + err = c1.AuthUserChange("admin", "admin", false) + So(err, ShouldNotBeNil) + So(err, ShouldEqual, ErrAdminRequired) + }) + + Convey("Modify Other User Password", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify User + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Modify Password for user + rv, err = modifyUser(db1, "user", "user2", 0) + So(err, ShouldBeNil) + So(rv, ShouldEqual, 0) + db1.Close() + + // Reconnect as normal user with new password + f2, db2, c2, err := connect(f1, "user", "user2") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("Modify Other User Password (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify User + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Modify user password through (*SQLiteConn) + // Because we are still logged in as admin + // this should succeed. + err = c1.AuthUserChange("admin", "admin", false) + So(err, ShouldNotBeNil) + }) + + Convey("Modify Other User Admin Flag", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify User + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Modify Password for user + // Because we are logged in as admin + // This call should succeed. + rv, err = modifyUser(db1, "user", "user", 1) + So(err, ShouldBeNil) + So(rv, ShouldEqual, 0) + db1.Close() + + // Reconnect as normal user with new password + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("Modify Other User Admin Flag (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify User + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Modify user password through (*SQLiteConn) + // Because we are still logged in as admin + // this should succeed. + err = c1.AuthUserChange("user", "user", true) + So(err, ShouldBeNil) + }) + + Convey("Modify Other User Password as Non-Admin", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Add Normal User + rv, err = addUser(db1, "user2", "user2", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify 'user' + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Verify 'user2' + e, err = userExists(db1, "user2") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user2") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + // Modify password for user as normal user + // Because 'user' is not admin + // Modifying password as a normal user should now fail + // because we have insufficient privileges + rv, err = modifyUser(db2, "user2", "invalid", 0) + So(err, ShouldBeNil) + So(rv, ShouldEqual, SQLITE_AUTH) + }) + + Convey("Modify Other User Password as Non-Admin", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Add Normal User + rv, err = addUser(db1, "user2", "user2", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify 'user' + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Verify 'user2' + e, err = userExists(db1, "user2") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user2") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + // Modify user password through (*SQLiteConn) + // for 'user2' + // Because 'user' is not admin + // Modifying password as a normal user should now fail + // because we have insufficient privileges + err = c2.AuthUserChange("user2", "invalid", false) + So(err, ShouldNotBeNil) + So(err, ShouldEqual, ErrAdminRequired) + }) + + Convey("Delete User as Admin", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify 'user' + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + rv, err = deleteUser(db1, "user") + So(err, ShouldBeNil) + So(rv, ShouldEqual, 0) + + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 0) + }) + + Convey("Delete User as Admin (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify 'user' + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + err = c1.AuthUserDelete("user") + So(err, ShouldBeNil) + + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 0) + }) + + Convey("Delete User as Non-Admin", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Add Normal User + rv, err = addUser(db1, "user2", "user2", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify 'user' + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Verify 'user2' + e, err = userExists(db1, "user2") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user2") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + rv, err = deleteUser(db2, "user2") + So(err, ShouldBeNil) + So(rv, ShouldEqual, SQLITE_AUTH) + }) + + Convey("Delete User as Non-Admin (*SQLiteConn)", t, func() { + f1, db1, c1, err := connect("", "admin", "admin") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + + // Add Normal User + rv, err := addUser(db1, "user", "user", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Add Normal User + rv, err = addUser(db1, "user2", "user2", 0) + So(rv, ShouldEqual, 0) // 0 == C.SQLITE_OK + So(err, ShouldBeNil) + + // Verify 'user' + e, err = userExists(db1, "user") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + + // Verify 'user2' + e, err = userExists(db1, "user2") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err = isAdmin(db1, "user2") + So(err, ShouldBeNil) + So(a, ShouldEqual, false) + db1.Close() + + // Reconnect as normal user + f2, db2, c2, err := connect(f1, "user", "user") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + + err = c2.AuthUserDelete("user2") + So(err, ShouldNotBeNil) + So(err, ShouldEqual, ErrAdminRequired) + }) } diff --git a/upgrade/package.go b/upgrade/package.go new file mode 100644 index 0000000..2b12a77 --- /dev/null +++ b/upgrade/package.go @@ -0,0 +1,7 @@ +// Package upgrade +// +// Dummy to ensure package can be loaded +// +// This file is to avoid the following error: +// can't load package: package go-sqlite3/upgrade: build constraints exclude all Go files in go-sqlite3\upgrade +package upgrade diff --git a/upgrade/profile.goconvey b/upgrade/profile.goconvey new file mode 100644 index 0000000..55049ab --- /dev/null +++ b/upgrade/profile.goconvey @@ -0,0 +1,2 @@ +// Goconvey Profile +IGNORE -- cgit v1.2.3 From 7337e65c27313aec52f96e6da520acd2fe48c00f Mon Sep 17 00:00:00 2001 From: Gert-Jan Timmer Date: Tue, 5 Jun 2018 13:45:32 +0200 Subject: ADD: User Authentication Password Encoders Allow user to choose how to encode passwords with connection string overrides of embedded `sqlite_crypt` function. --- sqlite3.go | 58 ++++++++++ sqlite3_func_crypt.go | 120 +++++++++++++++++++++ sqlite3_opt_userauth_test.go | 244 +++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 422 insertions(+) create mode 100644 sqlite3_func_crypt.go (limited to 'sqlite3.go') diff --git a/sqlite3.go b/sqlite3.go index e6f8c55..979aedc 100644 --- a/sqlite3.go +++ b/sqlite3.go @@ -894,6 +894,8 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { authCreate := false authUser := "" authPass := "" + authCrypt := "" + authSalt := "" mutex := C.int(C.SQLITE_OPEN_FULLMUTEX) txlock := "BEGIN" @@ -929,6 +931,12 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { if val := params.Get("_auth_pass"); val != "" { authPass = val } + if val := params.Get("_auth_crypt"); val != "" { + authCrypt = val + } + if val := params.Get("_auth_salt"); val != "" { + authSalt = val + } // _loc if val := params.Get("_loc"); val != "" { @@ -1287,6 +1295,56 @@ func (d *SQLiteDriver) Open(dsn string) (driver.Conn, error) { // Create connection to SQLite conn := &SQLiteConn{db: db, loc: loc, txlock: txlock} + // Password Cipher has to be registerd before authentication + if len(authCrypt) > 0 { + switch strings.ToUpper(authCrypt) { + case "SHA1": + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA1, true); err != nil { + return nil, fmt.Errorf("CryptEncoderSHA1: %s", err) + } + case "SSHA1": + if len(authSalt) == 0 { + return nil, fmt.Errorf("_auth_crypt=ssha1, requires _auth_salt") + } + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA1(authSalt), true); err != nil { + return nil, fmt.Errorf("CryptEncoderSSHA1: %s", err) + } + case "SHA256": + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA256, true); err != nil { + return nil, fmt.Errorf("CryptEncoderSHA256: %s", err) + } + case "SSHA256": + if len(authSalt) == 0 { + return nil, fmt.Errorf("_auth_crypt=ssha256, requires _auth_salt") + } + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA256(authSalt), true); err != nil { + return nil, fmt.Errorf("CryptEncoderSSHA256: %s", err) + } + case "SHA384": + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA384, true); err != nil { + return nil, fmt.Errorf("CryptEncoderSHA384: %s", err) + } + case "SSHA384": + if len(authSalt) == 0 { + return nil, fmt.Errorf("_auth_crypt=ssha384, requires _auth_salt") + } + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA384(authSalt), true); err != nil { + return nil, fmt.Errorf("CryptEncoderSSHA384: %s", err) + } + case "SHA512": + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSHA512, true); err != nil { + return nil, fmt.Errorf("CryptEncoderSHA512: %s", err) + } + case "SSHA512": + if len(authSalt) == 0 { + return nil, fmt.Errorf("_auth_crypt=ssha512, requires _auth_salt") + } + if err := conn.RegisterFunc("sqlite_crypt", CryptEncoderSSHA512(authSalt), true); err != nil { + return nil, fmt.Errorf("CryptEncoderSSHA512: %s", err) + } + } + } + // Preform Authentication if err := conn.Authenticate(authUser, authPass); err != nil { return nil, err diff --git a/sqlite3_func_crypt.go b/sqlite3_func_crypt.go new file mode 100644 index 0000000..3774a97 --- /dev/null +++ b/sqlite3_func_crypt.go @@ -0,0 +1,120 @@ +// Copyright (C) 2018 G.J.R. Timmer . +// +// Use of this source code is governed by an MIT-style +// license that can be found in the LICENSE file. + +package sqlite3 + +import ( + "crypto/sha1" + "crypto/sha256" + "crypto/sha512" +) + +// This file provides several different implementations for the +// default embedded sqlite_crypt function. +// This function is uses a ceasar-cypher by default +// and is used within the UserAuthentication module to encode +// the password. +// +// The provided functions can be used as an overload to the sqlite_crypt +// function through the use of the RegisterFunc on the connection. +// +// Because the functions can serv a purpose to an end-user +// without using the UserAuthentication module +// the functions are default compiled in. +// +// From SQLITE3 - user-auth.txt +// The sqlite_user.pw field is encoded by a built-in SQL function +// "sqlite_crypt(X,Y)". The two arguments are both BLOBs. The first argument +// is the plaintext password supplied to the sqlite3_user_authenticate() +// interface. The second argument is the sqlite_user.pw value and is supplied +// so that the function can extract the "salt" used by the password encoder. +// The result of sqlite_crypt(X,Y) is another blob which is the value that +// ends up being stored in sqlite_user.pw. To verify credentials X supplied +// by the sqlite3_user_authenticate() routine, SQLite runs: +// +// sqlite_user.pw == sqlite_crypt(X, sqlite_user.pw) +// +// To compute an appropriate sqlite_user.pw value from a new or modified +// password X, sqlite_crypt(X,NULL) is run. A new random salt is selected +// when the second argument is NULL. +// +// The built-in version of of sqlite_crypt() uses a simple Ceasar-cypher +// which prevents passwords from being revealed by searching the raw database +// for ASCII text, but is otherwise trivally broken. For better password +// security, the database should be encrypted using the SQLite Encryption +// Extension or similar technology. Or, the application can use the +// sqlite3_create_function() interface to provide an alternative +// implementation of sqlite_crypt() that computes a stronger password hash, +// perhaps using a cryptographic hash function like SHA1. + +// CryptEncoderSHA1 encodes a password with SHA1 +func CryptEncoderSHA1(pass []byte, hash interface{}) []byte { + h := sha1.Sum(pass) + return h[:] +} + +// CryptEncoderSSHA1 encodes a password with SHA1 with the +// configured salt. +func CryptEncoderSSHA1(salt string) func(pass []byte, hash interface{}) []byte { + return func(pass []byte, hash interface{}) []byte { + s := []byte(salt) + p := append(pass, s...) + h := sha1.Sum(p) + return h[:] + } +} + +// CryptEncoderSHA256 encodes a password with SHA256 +func CryptEncoderSHA256(pass []byte, hash interface{}) []byte { + h := sha256.Sum256(pass) + return h[:] +} + +// CryptEncoderSSHA256 encodes a password with SHA256 +// with the configured salt +func CryptEncoderSSHA256(salt string) func(pass []byte, hash interface{}) []byte { + return func(pass []byte, hash interface{}) []byte { + s := []byte(salt) + p := append(pass, s...) + h := sha256.Sum256(p) + return h[:] + } +} + +// CryptEncoderSHA384 encodes a password with SHA256 +func CryptEncoderSHA384(pass []byte, hash interface{}) []byte { + h := sha512.Sum384(pass) + return h[:] +} + +// CryptEncoderSSHA384 encodes a password with SHA256 +// with the configured salt +func CryptEncoderSSHA384(salt string) func(pass []byte, hash interface{}) []byte { + return func(pass []byte, hash interface{}) []byte { + s := []byte(salt) + p := append(pass, s...) + h := sha512.Sum384(p) + return h[:] + } +} + +// CryptEncoderSHA512 encodes a password with SHA256 +func CryptEncoderSHA512(pass []byte, hash interface{}) []byte { + h := sha512.Sum512(pass) + return h[:] +} + +// CryptEncoderSSHA512 encodes a password with SHA256 +// with the configured salt +func CryptEncoderSSHA512(salt string) func(pass []byte, hash interface{}) []byte { + return func(pass []byte, hash interface{}) []byte { + s := []byte(salt) + p := append(pass, s...) + h := sha512.Sum512(p) + return h[:] + } +} + +// EOF diff --git a/sqlite3_opt_userauth_test.go b/sqlite3_opt_userauth_test.go index a71716c..cd05b05 100644 --- a/sqlite3_opt_userauth_test.go +++ b/sqlite3_opt_userauth_test.go @@ -1108,3 +1108,247 @@ func TestUserAuthenticationDeleteUser(t *testing.T) { So(err, ShouldEqual, ErrAdminRequired) }) } + +func TestUserAuthenticationEncoder(t *testing.T) { + connectWithCrypt := func(t *testing.T, f string, username, password string, crypt string, salt string) (file string, db *sql.DB, c *SQLiteConn, err error) { + conn = nil // Clear connection + file = f // Copy provided file (f) => file + if file == "" { + // Create dummy file + file = TempFilename(t) + } + + 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)) + if err != nil { + defer os.Remove(file) + return file, nil, nil, err + } + + // Dummy query to force connection and database creation + // Will return ErrUnauthorized (SQLITE_AUTH) if user authentication fails + if _, err = db.Exec("SELECT 1;"); err != nil { + defer os.Remove(file) + defer db.Close() + return file, nil, nil, err + } + c = conn + + return + } + + Convey("SHA1 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "sha1", "") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "sha1", "") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SSHA1 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "ssha1", "salted") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "ssha1", "salted") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SHA256 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "sha256", "") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "sha256", "") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SSHA256 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "ssha256", "salted") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "ssha256", "salted") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SHA384 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "sha384", "") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "sha384", "") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SSHA384 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "ssha384", "salted") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "ssha384", "salted") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SHA512 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "sha512", "") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "sha512", "") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) + + Convey("SSHA512 Encoder", t, func() { + f1, db1, c1, err := connectWithCrypt(t, "", "admin", "admin", "ssha512", "salted") + So(f1, ShouldNotBeBlank) + So(db1, ShouldNotBeNil) + So(c1, ShouldNotBeNil) + So(err, ShouldBeNil) + defer os.Remove(f1) + + e, err := userExists(db1, "admin") + So(err, ShouldBeNil) + So(e, ShouldEqual, 1) + + a, err := isAdmin(db1, "admin") + So(err, ShouldBeNil) + So(a, ShouldEqual, true) + db1.Close() + + // Preform authentication + f2, db2, c2, err := connectWithCrypt(t, f1, "admin", "admin", "ssha512", "salted") + So(f2, ShouldNotBeBlank) + So(f1, ShouldEqual, f2) + So(db2, ShouldNotBeNil) + So(c2, ShouldNotBeNil) + So(err, ShouldBeNil) + defer db2.Close() + }) +} -- cgit v1.2.3