Mercurial > gemma

changeset 478:3af7ca761f6a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Purge password reset role The risk of SQL-injections and thus privilege escalation via the metamorphic user was estimated not high enough to justify the extra role. Thus, bring database back in line with rev. ffdb507d5b42 and re-enable password reset.
author Tom Gottfried <tom@intevation.de>
date Thu, 23 Aug 2018 16:41:44 +0200
parents 00b52d653039
children fce16726f51d
files example_conf.toml pkg/controllers/pwreset.go schema/Dockerfile schema/auth.sql schema/gemma.sql schema/install-db.sh schema/manage_users.sql schema/manage_users_tests.sql schema/roles.sql schema/run_tests.sh schema/std_login_roles.sql schema/tap_tests_data.sql
diffstat 12 files changed, 26 insertions(+), 96 deletions(-) [+]
line wrap: on
line diff
--- a/example_conf.toml	Thu Aug 23 16:18:07 2018 +0200
+++ b/example_conf.toml	Thu Aug 23 16:41:44 2018 +0200
@@ -1,6 +1,4 @@
 dbhost = "gemma_db"
-service-user = "gemma_service"
-service-password = "pw2Reset4"
 host = "0.0.0.0"
 sessions = "/tmp/gemma_session.data"
 web = "./web"
--- a/pkg/controllers/pwreset.go	Thu Aug 23 16:18:07 2018 +0200
+++ b/pkg/controllers/pwreset.go	Thu Aug 23 16:41:44 2018 +0200
@@ -20,29 +20,29 @@
 )
 
 const (
-	insertRequestSQL = `INSERT INTO pw_reset.password_reset_requests
+	insertRequestSQL = `INSERT INTO sys_admin.password_reset_requests
     (hash, username) VALUES ($1, $2)`
 
-	countRequestsSQL = `SELECT count(*) FROM pw_reset.password_reset_requests`
+	countRequestsSQL = `SELECT count(*) FROM sys_admin.password_reset_requests`
 
-	countRequestsUserSQL = `SELECT count(*) FROM pw_reset.password_reset_requests
+	countRequestsUserSQL = `SELECT count(*) FROM sys_admin.password_reset_requests
     WHERE username = $1`
 
-	deleteRequestSQL = `DELETE FROM pw_reset.password_reset_requests
+	deleteRequestSQL = `DELETE FROM sys_admin.password_reset_requests
     WHERE hash = $1`
 
 	findRequestSQL = `SELECT lu.email_address, lu.username
-    FROM pw_reset.password_reset_requests prr
-    JOIN pw_reset.list_users lu on prr.username = lu.username
+    FROM sys_admin.password_reset_requests prr
+    JOIN users.list_users lu on prr.username = lu.username
     WHERE prr.hash = $1`
 
-	cleanupRequestsSQL = `DELETE FROM pw_reset.password_reset_requests
+	cleanupRequestsSQL = `DELETE FROM sys_admin.password_reset_requests
     WHERE issued < $1`
 
 	userExistsSQL = `SELECT email_address
-    FROM pw_reset.list_users WHERE username = $1`
+    FROM users.list_users WHERE username = $1`
 
-	updatePasswordSQL = `UPDATE pw_reset.list_users
+	updatePasswordSQL = `UPDATE users.list_users
     SET pw = $1 WHERE username = $2`
 )
 
@@ -55,7 +55,7 @@
 	cleanupPause               = 15 * time.Minute
 )
 
-const pwResetRole = "pw_reset"
+const pwResetRole = "sys_admin"
 
 var (
 	passwordResetRequestMailTmpl = template.Must(
--- a/schema/Dockerfile	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/Dockerfile	Thu Aug 23 16:41:44 2018 +0200
@@ -31,7 +31,7 @@
 COPY *.sql *.sh ./
 COPY demo-data ./demo-data/
 RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \
-    ./install-db.sh --demo --servicepw "pw2Reset4" --metapw "geo2Serv" && \
+    ./install-db.sh --demo --metapw "geo2Serv" && \
     $PGBIN/pg_ctl stop -m smart
 
 # Set the default command to run when starting the container
--- a/schema/auth.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/auth.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -31,15 +31,7 @@
 GRANT USAGE ON SCHEMA sys_admin TO sys_admin;
 GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin;
 GRANT UPDATE ON sys_admin.system_config TO sys_admin;
-
-
---
--- Special privileges for pw_reset
---
-GRANT USAGE ON SCHEMA pw_reset TO pw_reset;
-GRANT SELECT (username, email_address) ON pw_reset.list_users TO pw_reset;
-GRANT UPDATE (pw) ON pw_reset.list_users TO pw_reset;
-GRANT INSERT, SELECT, DELETE ON pw_reset.password_reset_requests TO pw_reset;
+GRANT INSERT, DELETE ON sys_admin.password_reset_requests TO sys_admin;
 
 --
 -- Privileges assigned directly to metamorph
--- a/schema/gemma.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/gemma.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -56,6 +56,14 @@
         config_val varchar
     )
 
+    CREATE TABLE password_reset_requests (
+        hash varchar(32) PRIMARY KEY,
+        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        username varchar NOT NULL
+            REFERENCES internal.user_profiles(username)
+                ON DELETE CASCADE ON UPDATE CASCADE
+    )
+
     CREATE TABLE external_services (
         local_name varchar PRIMARY KEY,
         remote_url varchar NOT NULL,
@@ -182,18 +190,6 @@
     country char(2) NOT NULL REFERENCES users.responsibility_areas;
 
 
--- Namespace intended to be the only one that pw_reset can access
-CREATE SCHEMA pw_reset
-    CREATE TABLE password_reset_requests (
-        hash varchar(32) PRIMARY KEY,
-        issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-        username varchar NOT NULL
-            REFERENCES internal.user_profiles(username)
-                ON DELETE CASCADE ON UPDATE CASCADE
-    )
-;
-
-
 -- Namespace for waterway data that can change in a running system
 CREATE SCHEMA waterway
 
--- a/schema/install-db.sh	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/install-db.sh	Thu Aug 23 16:41:44 2018 +0200
@@ -17,8 +17,6 @@
   -D, --demo       also install demo accounts and data
       --adminpw    set the password to use for the "sysadmin" account.
                    Default is a random password.
-      --servicepw  set the password to use for the "gemma_service" account.
-                   Default is a random password.
       --metapw     set the password to use for the "meta_login" account.
                    Default is a random password.
       --drop       drop database and all roles
@@ -47,13 +45,12 @@
 demo=0
 drop=0
 adminpw=`genpw 15`
-servicepw=`genpw 15`
 metapw=`genpw 15`
 
 # Parse options:
 
 OPTS=`getopt \
-      -l help,demo,db:,port:,drop,adminpw:,servicepw:,metapw: \
+      -l help,demo,db:,port:,drop,adminpw:,metapw: \
       -o Dd:p: -n "$ME" -- "$@"`
 [ $? -eq 0 ] || { usage ; exit 1 ; }
 
@@ -73,10 +70,6 @@
       adminpw="$2"
       shift 2
       ;;
-    --servicepw)
-      servicepw="$2"
-      shift 2
-      ;;
     --metapw)
       metapw="$2"
       shift 2
@@ -114,7 +107,7 @@
 
   # setup initial login roles with given passwords:
   psql -qt -p "$port" -d "$db" \
-       -v adminpw="$adminpw" -v servicepw="$servicepw" -v metapw="$metapw" \
+       -v adminpw="$adminpw" -v metapw="$metapw" \
        -f "$BASEDIR/std_login_roles.sql"
 
   if [[ $demo -eq 1 ]] ; then
@@ -133,7 +126,7 @@
   if [[ $a == "yes" ]] ; then
     dropdb -p "$port" "$db"
     psql -p $port -A -t -c '\du' | awk -F '|' -v port=$port \
-        '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|pw_reset|metamorph/ \
+        '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|metamorph/ \
 	    { system("dropuser -p " port " \"" $1 "\"") }'
   else
     echo "No harm done."
--- a/schema/manage_users.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/manage_users.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -41,7 +41,6 @@
                 AND p.country = (
                     SELECT country FROM internal.user_profiles
                         WHERE username = current_user)
-            OR pg_has_role('pw_reset', 'MEMBER')
             OR pg_has_role('sys_admin', 'MEMBER');
 
 
@@ -101,14 +100,13 @@
     EXECUTE PROCEDURE internal.update_metamorph();
 
 
--- Prevent roles other than sys_admin and pw_reset to update any user but
+-- Prevent roles other than sys_admin to update any user but
 -- themselves (affects waterway_admin)
 CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger
 AS $$
 BEGIN
     IF OLD.username <> current_user
-        AND NOT (pg_has_role('sys_admin', 'MEMBER')
-            OR pg_has_role('pw_reset', 'MEMBER'))
+        AND NOT pg_has_role('sys_admin', 'MEMBER')
     THEN
         RETURN NULL;
     ELSE
@@ -202,9 +200,6 @@
     EXECUTE PROCEDURE internal.delete_user();
 
 
-CREATE OR REPLACE VIEW pw_reset.list_users AS
-    SELECT username, pw, email_address FROM users.list_users;
-
 -- To set a role from a hex-encoded user name (which is save from SQL injections).
 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
 AS $$
--- a/schema/manage_users_tests.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/manage_users_tests.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -314,38 +314,3 @@
     $$,
     55006, NULL,
     'Current user cannot be deleted');
-
-
---
--- Password reset
---
-
--- Workaround broken relocatability of pgtap (otherwise we could
--- put pgtap in its own schema and GRANT USAGE to PUBLIC on it)
-RESET SESSION AUTHORIZATION;
-GRANT USAGE ON SCHEMA public TO pw_reset;
-
-SET SESSION AUTHORIZATION test_pw_reset;
-
-SELECT isnt_empty($$
-    SELECT username, email_address FROM pw_reset.list_users
-    $$,
-    'Special role can see users with their email addresses');
-
-SELECT results_eq($$
-    UPDATE pw_reset.list_users
-        SET pw = 'user_at2!' WHERE username = 'test_user_at'
-        RETURNING email_address
-    $$,
-    $$
-    SELECT email_address FROM pw_reset.list_users
-        WHERE username = 'test_user_at'
-    $$,
-    'Special role can update password');
-
-SELECT throws_ok($$
-    UPDATE pw_reset.list_users
-        SET username = 'test_rename', email_address = 'test'
-    $$,
-    42501, NULL,
-    'Special role cannot update arbitrary user attributes');
--- a/schema/roles.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/roles.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -9,9 +9,6 @@
 -- Special roles
 --
 
--- A role that is intended to be used for password reset only
-CREATE ROLE pw_reset;
-
 -- A role that is intended to be used for backend- or
 -- GeoServer-connections on which SET ROLE has to be used to
 -- gain privileges of a specific role
--- a/schema/run_tests.sh	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/run_tests.sh	Thu Aug 23 16:41:44 2018 +0200
@@ -16,7 +16,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c 'SELECT plan(46)' \
+    -c 'SELECT plan(43)' \
     -f auth_tests.sql \
     -f manage_users_tests.sql \
     -c 'SELECT * FROM finish()'
--- a/schema/std_login_roles.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/std_login_roles.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -30,15 +30,11 @@
 -- Functional Users
 --
 
--- Used by the back end (gemma)
-CREATE ROLE gemma_service IN ROLE pw_reset LOGIN PASSWORD :'servicepw';
-
 -- Used by GeoServer and backend
 CREATE ROLE meta_login IN ROLE metamorph LOGIN PASSWORD :'metapw';
 
 -- Emit messages to the client if everything went ok
 SELECT 'Default admin user ''sysadmin'' created with password ' || :'adminpw';
-SELECT 'Backend user ''gemma_service'' created with password ' || :'servicepw';
 SELECT 'Backend user ''meta_login'' created with password ' || :'metapw';
 
 COMMIT;
--- a/schema/tap_tests_data.sql	Thu Aug 23 16:18:07 2018 +0200
+++ b/schema/tap_tests_data.sql	Thu Aug 23 16:41:44 2018 +0200
@@ -17,8 +17,6 @@
 INSERT INTO users.list_users VALUES (
     'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz');
 
-CREATE ROLE test_pw_reset IN ROLE pw_reset LOGIN PASSWORD 'ppp';
-
 INSERT INTO limiting_factors VALUES ('depth'), ('width');
 
 INSERT INTO waterway.gauges (