Mercurial > gemma
changeset 343:5b03f420957d
Use INSTEAD OF trigger for user creation
Now make the whole thing look like a real table.
There is no more function in schema sys_admin, thus remove respective
privilege test.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 06 Aug 2018 13:25:18 +0200 |
parents | c6bd6ed18942 |
children | e98033e3683a |
files | controllers/user.go schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh schema/tap_tests_data.sql |
diffstat | 5 files changed, 34 insertions(+), 42 deletions(-) [+] |
line wrap: on
line diff
--- a/controllers/user.go Mon Aug 06 12:37:06 2018 +0200 +++ b/controllers/user.go Mon Aug 06 13:25:18 2018 +0200 @@ -11,8 +11,10 @@ ) const ( - createUserSQL = `SELECT sys_admin.create_user($1, $2, $3, $4, NULL, $5)` - createUserExtentSQL = `SELECT sys_admin.create_user($1, $2, $3, $4, + createUserSQL = `INSERT INTO users.list_users + VALUES ($1, $2, $3, $4, NULL, $5)` + createUserExtentSQL = `INSERT INTO users.list_users + VALUES ($1, $2, $3, $4, ST_MakeBox2D(ST_Point($5, $6), ST_Point($7, $8)), $9)` updateUserUnprivSQL = `UPDATE users.list_users
--- a/schema/manage_users.sql Mon Aug 06 12:37:06 2018 +0200 +++ b/schema/manage_users.sql Mon Aug 06 13:25:18 2018 +0200 @@ -56,35 +56,34 @@ OR pg_has_role('sys_admin', 'MEMBER'); -CREATE OR REPLACE FUNCTION sys_admin.create_user( - userrole varchar, - username internal.user_profiles.username%TYPE, - pw varchar, - country internal.user_profiles.country%TYPE, - map_extent internal.user_profiles.map_extent%TYPE, - email_address internal.user_profiles.email_address%TYPE - ) - RETURNS void +CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger AS $$ BEGIN - IF map_extent IS NULL + IF NEW.map_extent IS NULL THEN - map_extent = ST_Extent(area) FROM users.responsibility_areas ra - WHERE ra.country = create_user.country; + NEW.map_extent = ST_Extent(area) FROM users.responsibility_areas ra + WHERE ra.country = NEW.country; END IF; INSERT INTO internal.user_profiles ( username, country, map_extent, email_address) - VALUES (username, country, map_extent, email_address); + VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address); EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', - username, - userrole, - internal.check_password(pw)); + NEW.username, + NEW.rolname, + internal.check_password(NEW.pw)); + + -- Do not leak new password + NEW.pw = ''; + RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER; +CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW + EXECUTE PROCEDURE internal.create_user(); + CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger AS $$
--- a/schema/manage_users_tests.sql Mon Aug 06 12:37:06 2018 +0200 +++ b/schema/manage_users_tests.sql Mon Aug 06 13:25:18 2018 +0200 @@ -4,15 +4,6 @@ SET search_path TO public, gemma, gemma_waterway, gemma_fairway; -SET SESSION AUTHORIZATION test_admin_at; - -SELECT throws_ok($$ - SELECT sys_admin.create_user( - 'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0') - $$, - 42501, NULL, - 'Less privileged user cannot call function in schema sys_admin'); - -- -- Role listing -- @@ -43,20 +34,20 @@ -- Role creation -- SELECT lives_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1') $$, 'New waterway user can be added'); SELECT throws_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3') $$, 23502, NULL, @@ -64,14 +55,14 @@ -- Though other arguments are mandatory, too, there are no explicit tests SELECT throws_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( '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( + INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4') $$, 23505, NULL, @@ -79,21 +70,21 @@ -- Test password policy SELECT throws_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( '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( + INSERT INTO users.list_users VALUES ( '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( + INSERT INTO users.list_users VALUES ( 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2') $$, '28P01', NULL, @@ -141,7 +132,7 @@ SET SESSION AUTHORIZATION test_sys_admin1; SELECT lives_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); UPDATE users.list_users SET (rolname, username, pw, country, map_extent, email_address) @@ -276,7 +267,7 @@ -- Note: backend termination is not tested in the following. -- See also comments in function definition. SELECT lives_ok($$ - SELECT sys_admin.create_user( + INSERT INTO users.list_users VALUES ( 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3'); DELETE FROM users.list_users WHERE username = 'test3' $$,
--- a/schema/run_tests.sh Mon Aug 06 12:37:06 2018 +0200 +++ b/schema/run_tests.sh Mon Aug 06 13:25:18 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(44)' \ + -c 'SELECT plan(43)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'
--- a/schema/tap_tests_data.sql Mon Aug 06 12:37:06 2018 +0200 +++ b/schema/tap_tests_data.sql Mon Aug 06 13:25:18 2018 +0200 @@ -8,13 +8,13 @@ ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)), ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)); -SELECT sys_admin.create_user( +INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx'); -SELECT sys_admin.create_user( +INSERT INTO users.list_users VALUES ( 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy'); -SELECT sys_admin.create_user( +INSERT INTO users.list_users VALUES ( 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy'); -SELECT sys_admin.create_user( +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';