Mercurial > gemma
changeset 185:a9d9c2b1d08c
Add database function to create role and user profile
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 19 Jul 2018 16:37:52 +0200 |
| parents | bc7829defa99 |
| children | fe3a88f00b0a |
| files | schema/Dockerfile schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh |
| diffstat | 4 files changed, 85 insertions(+), 4 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/Dockerfile Thu Jul 19 16:32:27 2018 +0200 +++ b/schema/Dockerfile Thu Jul 19 16:37:52 2018 +0200 @@ -31,8 +31,7 @@ RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \ psql -f roles.sql && \ createdb gemma && \ - psql -f gemma.sql -d gemma && \ - psql -f auth.sql -d gemma && \ + psql -f gemma.sql -f auth.sql -f manage_users.sql -d gemma && \ psql -f demo-data/roles.sql -f demo-data/users.sql -d gemma && \ psql -f demo-data/responsibility_areas.sql -d gemma && \ $PGBIN/pg_ctl stop -m smart
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/manage_users.sql Thu Jul 19 16:37:52 2018 +0200 @@ -0,0 +1,23 @@ +-- +-- Functions encapsulating user management functionality and +-- exposing it to privileged users +-- + +CREATE OR REPLACE FUNCTION create_user( + userrole varchar, + username user_profiles.username%TYPE, + pw varchar, + country user_profiles.country%TYPE, + map_extent user_profiles.map_extent%TYPE, + email_adress user_profiles.email_adress%TYPE + ) + RETURNS void +AS $$ +BEGIN + INSERT INTO user_profiles VALUES ( + username, country, map_extent, email_adress); + EXECUTE format( + 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); +END; +$$ +LANGUAGE plpgsql;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/manage_users_tests.sql Thu Jul 19 16:37:52 2018 +0200 @@ -0,0 +1,56 @@ +BEGIN; +-- +-- pgTAP test script for user management functions +-- +SELECT plan(5); -- Give number of tests that have to be run + +SET search_path TO public, gemma, gemma_waterway, gemma_fairway; + +-- +-- Run tests as system_admin +-- +SET SESSION AUTHORIZATION sys_admin; + +SELECT lives_ok($$ + SELECT create_user( + 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1') + $$, + 'New waterway user can be added'); + +SELECT throws_ok($$ + SELECT create_user( + 'invalid', 'test2', 'secret', 'AT', NULL, 'test2') + $$, + 42704, NULL, + 'Valid role name has to be provided'); + +SELECT throws_ok($$ + SELECT create_user( + 'waterway_user', NULL, 'secret', 'AT', NULL, 'test3') + $$, + 23502, NULL, + 'username is mandatory'); +-- Though other arguments are mandatory, too, there are no explicit tests + +SELECT throws_ok($$ + SELECT create_user( + 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4') + $$, + 23505, NULL, + 'No duplicate user name is allowed'); + +SELECT throws_ok($$ + SELECT create_user( + 'waterway_user', 'test2', 'secret', 'AT', NULL, 'xxx') + $$, + 23505, NULL, + 'No duplicate e-mail adress is allowed'); + +-- +-- finish tests +-- +SELECT * FROM finish(); + +-- Rollback because we don't want test roles to stay in the cluster, which +-- would make tests not repeatable +ROLLBACK;
--- a/schema/run_tests.sh Thu Jul 19 16:32:27 2018 +0200 +++ b/schema/run_tests.sh Thu Jul 19 16:37:52 2018 +0200 @@ -4,9 +4,12 @@ createdb gemma_test psql -qv ON_ERROR_STOP= -c 'CREATE EXTENSION pgtap' -d gemma_test -psql -qv ON_ERROR_STOP= -f gemma.sql -d gemma_test -psql -qv ON_ERROR_STOP= -f auth.sql -d gemma_test +psql -qv ON_ERROR_STOP= -d gemma_test \ + -f gemma.sql \ + -f auth.sql \ + -f manage_users.sql psql -qv ON_ERROR_STOP= -f tap_tests_data.sql -d gemma_test psql -Xf auth_tests.sql -d gemma_test +psql -Xf manage_users_tests.sql -d gemma_test