# HG changeset patch
# User Frank Koormann <frank@intevation.de>
# Date 1532701806 -7200
# Node ID d2fb83291c68213e5eed48ed3c316f95e4577a5b
# Parent  78f96f168b5988698f23b61398f9fb8c21ffe426# Parent  92470caf81fdf774bc57ac52ef0516bb54320190
Merge

diff -r 78f96f168b59 -r d2fb83291c68 controllers/json.go
--- 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
 	})
 
diff -r 78f96f168b59 -r d2fb83291c68 controllers/routes.go
--- 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)
diff -r 78f96f168b59 -r d2fb83291c68 controllers/user.go
--- 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
+}
diff -r 78f96f168b59 -r d2fb83291c68 schema/install-db.sh
--- 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
diff -r 78f96f168b59 -r d2fb83291c68 schema/manage_users.sql
--- 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;
 $$
diff -r 78f96f168b59 -r d2fb83291c68 schema/manage_users_tests.sql
--- 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');
diff -r 78f96f168b59 -r d2fb83291c68 schema/run_tests.sh
--- 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()'
diff -r 78f96f168b59 -r d2fb83291c68 schema/tap_tests_data.sql
--- 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');