Mercurial > gemma
changeset 265:d2fb83291c68
Merge
author | Frank Koormann <frank@intevation.de> |
---|---|
date | Fri, 27 Jul 2018 16:30:06 +0200 |
parents | 78f96f168b59 (current diff) 92470caf81fd (diff) |
children | a2f7049daf7f |
files | |
diffstat | 8 files changed, 193 insertions(+), 51 deletions(-) [+] |
line wrap: on
line diff
--- a/controllers/json.go Fri Jul 27 16:29:39 2018 +0200 +++ b/controllers/json.go Fri Jul 27 16:30:06 2018 +0200 @@ -19,7 +19,7 @@ type JSONHandler struct { Input func() interface{} - Handle func(http.ResponseWriter, *http.Request, interface{}, *sql.DB) (JSONResult, error) + Handle func(interface{}, *http.Request, *sql.DB) (JSONResult, error) } type JSONError struct { @@ -46,7 +46,7 @@ token, _ := auth.GetToken(req) var jr JSONResult err := auth.ConnPool.Do(token, func(db *sql.DB) (err error) { - jr, err = j.Handle(rw, req, input, db) + jr, err = j.Handle(input, req, db) return err })
--- a/controllers/routes.go Fri Jul 27 16:29:39 2018 +0200 +++ b/controllers/routes.go Fri Jul 27 16:30:06 2018 +0200 @@ -24,6 +24,10 @@ })).Methods(http.MethodPost) api.Handle("/users/{user}", sysAdmin(&JSONHandler{ + Handle: listUser, + })).Methods(http.MethodGet) + + api.Handle("/users/{user}", sysAdmin(&JSONHandler{ Input: func() interface{} { return new(User) }, Handle: updateUser, })).Methods(http.MethodPut)
--- a/controllers/user.go Fri Jul 27 16:29:39 2018 +0200 +++ b/controllers/user.go Fri Jul 27 16:30:06 2018 +0200 @@ -5,6 +5,7 @@ "database/sql/driver" "encoding/json" "errors" + "fmt" "net/http" "regexp" "strings" @@ -48,13 +49,22 @@ deleteUserSQL = `SELECT sys_admin.delete_user($1)` listUsersSQL = `SELECT - rolename, + rolname, username, country, email_address, ST_XMin(map_extent), ST_YMin(map_extent), ST_XMax(map_extent), ST_YMax(map_extent) FROM sys_admin.list_users` + + listUserSQL = `SELECT + rolname, + country, + email_address, + ST_XMin(map_extent), ST_YMin(map_extent), + ST_XMax(map_extent), ST_YMax(map_extent) +FROM sys_admin.list_users +WHERE username = $1` ) var errNoString = errors.New("Not a string") @@ -173,8 +183,8 @@ } func deleteUser( - rw http.ResponseWriter, req *http.Request, - input interface{}, db *sql.DB, + input interface{}, req *http.Request, + db *sql.DB, ) (jr JSONResult, err error) { user := mux.Vars(req)["user"] @@ -201,8 +211,8 @@ } func updateUser( - rw http.ResponseWriter, req *http.Request, - input interface{}, db *sql.DB, + input interface{}, req *http.Request, + db *sql.DB, ) (jr JSONResult, err error) { user := mux.Vars(req)["user"] @@ -241,6 +251,11 @@ return } + if user != newUser.User { + // Running in a go routine should not be necessary. + go func() { auth.ConnPool.Logout(user) }() + } + jr = JSONResult{ Code: http.StatusCreated, Result: struct { @@ -253,8 +268,8 @@ } func createUser( - rw http.ResponseWriter, req *http.Request, - input interface{}, db *sql.DB, + input interface{}, req *http.Request, + db *sql.DB, ) (jr JSONResult, err error) { user := input.(*User) @@ -297,8 +312,8 @@ } func listUsers( - rw http.ResponseWriter, req *http.Request, - input interface{}, db *sql.DB, + input interface{}, req *http.Request, + db *sql.DB, ) (jr JSONResult, err error) { var rows *sql.Rows @@ -335,3 +350,42 @@ } return } + +func listUser( + input interface{}, req *http.Request, + db *sql.DB, +) (jr JSONResult, err error) { + + user := mux.Vars(req)["user"] + if user == "" { + err = JSONError{http.StatusBadRequest, "error: user empty"} + return + } + + result := &User{ + User: user, + Extent: &BoundingBox{}, + } + + err = db.QueryRow(listUserSQL, user).Scan( + &result.Role, + &result.Country, + &result.Email, + &result.Extent.X1, &result.Extent.Y1, + &result.Extent.X2, &result.Extent.Y2, + ) + + switch { + case err == sql.ErrNoRows: + err = JSONError{ + Code: http.StatusNotFound, + Message: fmt.Sprintf("Cannot find user %s.", user), + } + return + case err != nil: + return + } + + jr.Result = result + return +}
--- a/schema/install-db.sh Fri Jul 27 16:29:39 2018 +0200 +++ b/schema/install-db.sh Fri Jul 27 16:30:06 2018 +0200 @@ -8,12 +8,14 @@ usage() { cat <<EOF -$ME [OPION]... +$ME [OPTION]... Options: - -d, --db=NAME connect to the database NAME - -p, --port=PORT connect do the postgresql cluster at PORT - -D, --demo install demo accounts and data + -d, --db=NAME create the database NAME. Default: "gemma" + -p, --port=PORT connect do the postgresql cluster at PORT. + Default is the postgresql standard port 5432 + -D, --demo also install demo accounts and data + --drop drop database and all roles --help display this help and exit EOF @@ -31,12 +33,12 @@ db=gemma port=5432 demo=0 - +drop=0 # Parse options: OPTS=`getopt \ - -l help,demo,db:,port: \ + -l help,demo,db:,port:,drop \ -o Dd:p: -n "$ME" -- "$@"` [ $? -eq 0 ] || { usage ; exit 1 ; } @@ -56,6 +58,10 @@ demo=1 shift 1 ;; + --drop) + drop=1 + shift 1 + ;; --help) { usage ; exit 0 ; } ;; @@ -69,16 +75,36 @@ # Main ------------------------------------------------------------ -psql -p "$port" -f "$BASEDIR/roles.sql" -createdb -p "$port" "$db" -psql -p "$port" -d "$db" \ - -f "$BASEDIR/gemma.sql" \ - -f "$BASEDIR/manage_users.sql" \ - -f "$BASEDIR/auth.sql" +if [[ drop -eq 0 ]] ; then + # Default operation: create schema + psql -q -p "$port" -f "$BASEDIR/roles.sql" + createdb -p "$port" "$db" + psql -qt -p "$port" -d "$db" \ + -c "SET client_min_messages TO WARNING;" \ + -f "$BASEDIR/gemma.sql" \ + -f "$BASEDIR/manage_users.sql" \ + -f "$BASEDIR/auth.sql" -if [[ $demo -eq 1 ]] ; then - psql -p "$port" -f "$BASEDIR/demo-data/responsibility_areas.sql" \ - -d "$db" - psql -p "$port" -f "$BASEDIR/demo-data/roles.sql" \ - -f "$BASEDIR/demo-data/users.sql" -d "$db" + if [[ $demo -eq 1 ]] ; then + psql -q -p "$port" -f "$BASEDIR/demo-data/responsibility_areas.sql" \ + -d "$db" + psql -q -p "$port" -f "$BASEDIR/demo-data/roles.sql" \ + -f "$BASEDIR/demo-data/users.sql" -d "$db" + fi +else + # Evil mode: drop everything gemma + echo "Really drop database '$db' and alle gemma roles? [type 'yes']: " + read a + if [[ $a == "yes" ]] ; then + dropdb -p "$port" "$db" + for r in `psql -p 5433 -t -c '\du' | awk -F '|' \ + '$3 ~/waterway_user|waterway_admin|sys_admin/ \ + || $1 ~/waterway_user|waterway_admin|sys_admin/ \ + {print $1}'` + do + dropuser -p "$port" "$r" + done + else + echo "No harm done." + fi fi
--- a/schema/manage_users.sql Fri Jul 27 16:29:39 2018 +0200 +++ b/schema/manage_users.sql Fri Jul 27 16:30:06 2018 +0200 @@ -1,8 +1,29 @@ -- -- Functions encapsulating user management functionality and --- exposing it to privileged users +-- exposing it to appropriately privileged users -- +CREATE OR REPLACE FUNCTION users.check_password( + pw varchar + ) + RETURNS varchar +AS $$ +DECLARE + min_len CONSTANT int = 8; +BEGIN + IF char_length(pw) < min_len + OR pw NOT SIMILAR TO '%[^[:alnum:]]%' + OR pw NOT SIMILAR TO '%[[:digit:]]%' + THEN + RAISE invalid_password USING MESSAGE = 'Invalid password'; + ELSE + RETURN pw; + END IF; +END; +$$ + LANGUAGE plpgsql; + + CREATE OR REPLACE VIEW sys_admin.list_users AS SELECT r.rolname, p.* FROM users.user_profiles p @@ -30,7 +51,10 @@ INSERT INTO users.user_profiles VALUES ( username, country, map_extent, email_address); EXECUTE format( - 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); + 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', + username, + userrole, + users.check_password(pw)); END; $$ LANGUAGE plpgsql @@ -83,7 +107,9 @@ IF new_pw IS NOT NULL AND new_pw <> '' THEN EXECUTE format( - 'ALTER ROLE %I PASSWORD %L', cur_username, new_pw); + 'ALTER ROLE %I PASSWORD %L', + cur_username, + users.check_password(new_pw)); END IF; END; $$
--- a/schema/manage_users_tests.sql Fri Jul 27 16:29:39 2018 +0200 +++ b/schema/manage_users_tests.sql Fri Jul 27 16:30:06 2018 +0200 @@ -8,7 +8,7 @@ SELECT throws_ok($$ SELECT sys_admin.create_user( - 'waterway_user', 'test0', 'secret', 'AT', NULL, 'test0') + 'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0') $$, 42501, NULL, 'Less privileged user cannot call function in schema sys_admin'); @@ -28,20 +28,20 @@ -- SELECT lives_ok($$ SELECT sys_admin.create_user( - 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1') + 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1') $$, 'New waterway user can be added'); SELECT throws_ok($$ SELECT sys_admin.create_user( - 'invalid', 'test2', 'secret', 'AT', NULL, 'test2') + 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ SELECT sys_admin.create_user( - 'waterway_user', NULL, 'secret', 'AT', NULL, 'test3') + 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3') $$, 23502, NULL, 'username is mandatory'); @@ -49,26 +49,48 @@ SELECT throws_ok($$ SELECT sys_admin.create_user( - 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4') + 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4') $$, 42710, NULL, 'Reserved role names cannot be used as username'); SELECT throws_ok($$ SELECT sys_admin.create_user( - 'waterway_user', 'test_user_at', 'secret', 'AT', NULL, 'test4') + 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); +-- Test password policy +SELECT throws_ok($$ + SELECT sys_admin.create_user( + 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2') + $$, + '28P01', NULL, + 'Password with less than 8 characters is not accepted'); + +SELECT throws_ok($$ + SELECT sys_admin.create_user( + 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2') + $$, + '28P01', NULL, + 'Password without non-alphanumeric character is not accepted'); + +SELECT throws_ok($$ + SELECT sys_admin.create_user( + 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2') + $$, + '28P01', NULL, + 'Password without digit is not accepted'); + -- -- Role update -- SELECT lives_ok($$ SELECT sys_admin.create_user( - 'waterway_user', 'test2', 'secret', 'AT', NULL, 'test2'); + 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); SELECT sys_admin.update_user('test2', - 'waterway_user', 'test2_new', 'new_secret', 'AT', + 'waterway_user', 'test2_new', 'new_secret1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test5') $$, @@ -85,7 +107,7 @@ SELECT throws_ok($$ SELECT sys_admin.update_user(CAST(current_user AS varchar), - 'waterway_user', 'test_new_name', 'secret', 'AT', + 'waterway_user', 'test_new_name', 'secret1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test6') $$, @@ -94,7 +116,7 @@ SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', - 'invalid', 'test2', 'secret', 'AT', + 'invalid', 'test2', 'secret1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test2') $$, @@ -103,7 +125,7 @@ SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', - 'waterway_user', NULL, 'secret', 'AT', + 'waterway_user', NULL, 'secret1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test3') $$, @@ -113,7 +135,7 @@ SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', - 'waterway_user', 'waterway_user', 'secret', 'AT', + 'waterway_user', 'waterway_user', 'secret1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test4') $$, @@ -122,13 +144,23 @@ SELECT throws_ok($$ SELECT sys_admin.update_user('test_user_at', - 'waterway_user', 'test_user_ro', 'secret', 'AT', + 'waterway_user', 'test_user_ro', 'secret1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); +-- Test password policy (only one rule to ensure it's also used on update) +SELECT throws_ok($$ + SELECT sys_admin.update_user('test_user_at', + 'waterway_user', 'test_user_at', 'secret', 'AT', + (SELECT map_extent FROM users.user_profiles + WHERE username = 'test_user_at'), 'test4') + $$, + '28P01', NULL, + 'Non-compliant password is not accepted'); + -- To compare passwords, we need to run the following tests as superuser RESET SESSION AUTHORIZATION; @@ -160,7 +192,7 @@ 'Giving empty string as password does not change password'); SELECT sys_admin.update_user('test_user_at', - 'waterway_user', 'test_user_at', 'new_pw', 'AT', + 'waterway_user', 'test_user_at', 'new_pw1$', 'AT', (SELECT map_extent FROM users.user_profiles WHERE username = 'test_user_at'), 'xxx'); SELECT set_ne($$ @@ -180,7 +212,7 @@ -- See also comments in function definition. SELECT lives_ok($$ SELECT sys_admin.create_user( - 'waterway_user', 'test3', 'secret', 'AT', NULL, 'test3'); + 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3'); SELECT sys_admin.delete_user('test3') $$, 'Existing user can be deleted');
--- a/schema/run_tests.sh Fri Jul 27 16:29:39 2018 +0200 +++ b/schema/run_tests.sh Fri Jul 27 16:30:06 2018 +0200 @@ -15,7 +15,7 @@ psql -qXv ON_ERROR_STOP= -v -d gemma_test \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(36)' \ + -c 'SELECT plan(40)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'
--- a/schema/tap_tests_data.sql Fri Jul 27 16:29:39 2018 +0200 +++ b/schema/tap_tests_data.sql Fri Jul 27 16:30:06 2018 +0200 @@ -9,13 +9,13 @@ ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)); SELECT sys_admin.create_user( - 'waterway_user', 'test_user_at', 'user_at', 'AT', NULL, 'xxx'); + 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx'); SELECT sys_admin.create_user( - 'waterway_user', 'test_user_ro', 'user_ro', 'RO', NULL, 'xxy'); + 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy'); SELECT sys_admin.create_user( - 'waterway_admin', 'test_admin_at', 'admin_at', 'AT', NULL, 'yyy'); + 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy'); SELECT sys_admin.create_user( - 'sys_admin', 'test_sys_admin1', 'sys_admin1', 'AT', NULL, 'zzz'); + 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz'); INSERT INTO limiting_factors VALUES ('depth'), ('width');