Mercurial > gemma
changeset 319:ac760b0f22a9
Add special role for password reset
As password reset is exposed without requiring a login, let this role
have privileges limited to reseting passwords, and only reseting passwords.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 02 Aug 2018 13:06:39 +0200 |
| parents | 1a2dfd9351e9 |
| children | e4bf72cda62e |
| files | controllers/pwreset.go schema/auth.sql schema/demo-data/roles.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/tap_tests_data.sql |
| diffstat | 10 files changed, 70 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/controllers/pwreset.go Thu Aug 02 12:48:59 2018 +0200 +++ b/controllers/pwreset.go Thu Aug 02 13:06:39 2018 +0200 @@ -23,9 +23,9 @@ const ( userExistsSQL = `SELECT email_address - FROM users.list_users WHERE username = $1` + FROM pw_reset.list_users WHERE username = $1` - updatePasswordSQL = `UPDATE users.list_users + updatePasswordSQL = `UPDATE pw_reset.list_users SET pw = $1 WHERE username = $2` )
--- a/schema/auth.sql Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/auth.sql Thu Aug 02 13:06:39 2018 +0200 @@ -30,6 +30,15 @@ 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; + + -- -- RLS policies for waterway_user --
--- a/schema/demo-data/roles.sql Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/demo-data/roles.sql Thu Aug 02 13:06:39 2018 +0200 @@ -11,4 +11,7 @@ CREATE ROLE oana IN ROLE waterway_user LOGIN PASSWORD 'oa2Na2'; CREATE ROLE vanja IN ROLE waterway_user LOGIN PASSWORD 'va2Nja3'; +-- User for password reset +CREATE ROLE paul IN ROLE pw_reset LOGIN PASSWORD 'pw2Reset4'; + COMMIT;
--- a/schema/gemma.sql Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/gemma.sql Thu Aug 02 13:06:39 2018 +0200 @@ -164,6 +164,11 @@ ALTER TABLE internal.user_profiles ADD 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; + + -- Namespace for waterway data that can change in a running system CREATE SCHEMA waterway
--- a/schema/install-db.sh Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/install-db.sh Thu Aug 02 13:06:39 2018 +0200 @@ -98,7 +98,7 @@ if [[ $a == "yes" ]] ; then dropdb -p "$port" "$db" for r in `psql -p $port -t -c '\du' | awk -F '|' \ - '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin/ \ + '$1 "." $3 ~ /waterway_user|waterway_admin|sys_admin|pw_reset/ \ {print $1}'` do dropuser -p "$port" "$r"
--- a/schema/manage_users.sql Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/manage_users.sql Thu Aug 02 13:06:39 2018 +0200 @@ -52,6 +52,7 @@ WHERE p.username = current_user OR pg_has_role('waterway_admin', 'MEMBER') AND p.country = users.current_user_country() + OR pg_has_role('pw_reset', 'MEMBER') OR pg_has_role('sys_admin', 'MEMBER'); @@ -156,3 +157,7 @@ $$ LANGUAGE plpgsql SECURITY DEFINER; + + +CREATE OR REPLACE VIEW pw_reset.list_users AS + SELECT username, pw, email_address FROM users.list_users;
--- a/schema/manage_users_tests.sql Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/manage_users_tests.sql Thu Aug 02 13:06:39 2018 +0200 @@ -255,3 +255,38 @@ $$, 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 02 12:48:59 2018 +0200 +++ b/schema/roles.sql Thu Aug 02 13:06:39 2018 +0200 @@ -4,3 +4,10 @@ CREATE ROLE waterway_user; CREATE ROLE waterway_admin IN ROLE waterway_user; CREATE ROLE sys_admin IN ROLE waterway_admin; + +-- +-- Special roles +-- + +-- A role that is intended to be used for password reset only +CREATE ROLE pw_reset;
--- a/schema/run_tests.sh Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/run_tests.sh Thu Aug 02 13:06:39 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(39)' \ + -c 'SELECT plan(42)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'
--- a/schema/tap_tests_data.sql Thu Aug 02 12:48:59 2018 +0200 +++ b/schema/tap_tests_data.sql Thu Aug 02 13:06:39 2018 +0200 @@ -17,6 +17,8 @@ SELECT sys_admin.create_user( '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 (