Mercurial > gemma
changeset 207:88d21c29cf04
Care for the fact that role attributes are not inherited
Tests are now run with login roles instead of abstract base roles.
create_user has become a SECURITY DEFINER function, thus circumventing
RLS policies and that a sys_admin cannot CREATE ROLEs by himself. A test
has been added to showcase the intentional error in case the name of
an abstract base role is used as a new username.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 23 Jul 2018 11:29:41 +0200 |
| parents | cd6ad5eaef8d |
| children | 87f21b9a1292 |
| files | schema/auth_tests.sql schema/manage_users.sql schema/manage_users_tests.sql schema/roles.sql schema/run_tests.sh schema/tap_tests_data.sql |
| diffstat | 6 files changed, 25 insertions(+), 15 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth_tests.sql Sun Jul 22 10:40:17 2018 +0200 +++ b/schema/auth_tests.sql Mon Jul 23 11:29:41 2018 +0200 @@ -5,7 +5,7 @@ -- -- Run tests as unprivileged user -- -SET SESSION AUTHORIZATION waterway_user; +SET SESSION AUTHORIZATION user_at; SELECT throws_ok('CREATE TABLE test()', 42501, NULL, 'No objects can be created'); @@ -31,7 +31,7 @@ -- -- Run tests as waterway administrator -- -SET SESSION AUTHORIZATION waterway_admin; +SET SESSION AUTHORIZATION admin_at; PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS INSERT INTO waterway.bottlenecks ( @@ -66,7 +66,7 @@ SELECT lives_ok('INSERT INTO users.templates (template_name, template_data) VALUES (''New AT'', ''\x''); INSERT INTO users.user_templates - VALUES (''waterway_user'', ''New AT'')', + VALUES (''user_at'', ''New AT'')', 'Waterway admin can add templates for users in his country'); SELECT throws_ok('INSERT INTO users.user_templates
--- a/schema/manage_users.sql Sun Jul 22 10:40:17 2018 +0200 +++ b/schema/manage_users.sql Mon Jul 23 11:29:41 2018 +0200 @@ -20,4 +20,5 @@ 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); END; $$ -LANGUAGE plpgsql; + LANGUAGE plpgsql + SECURITY DEFINER;
--- a/schema/manage_users_tests.sql Sun Jul 22 10:40:17 2018 +0200 +++ b/schema/manage_users_tests.sql Mon Jul 23 11:29:41 2018 +0200 @@ -4,7 +4,7 @@ SET search_path TO public, gemma, gemma_waterway, gemma_fairway; -SET SESSION AUTHORIZATION waterway_admin; +SET SESSION AUTHORIZATION admin_at; SELECT throws_ok($$ SELECT sys_admin.create_user( @@ -13,7 +13,7 @@ 42501, NULL, 'Less privileged user cannot call function in schema sys_admin'); -SET SESSION AUTHORIZATION sys_admin; +SET SESSION AUTHORIZATION sys_admin1; SELECT lives_ok($$ SELECT sys_admin.create_user( @@ -40,6 +40,13 @@ SELECT sys_admin.create_user( 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4') $$, + 42710, NULL, + 'Reserved role names cannot be used as username'); + +SELECT throws_ok($$ + SELECT sys_admin.create_user( + 'waterway_user', 'user_at', 'secret', 'AT', NULL, 'test4') + $$, 23505, NULL, 'No duplicate user name is allowed');
--- a/schema/roles.sql Sun Jul 22 10:40:17 2018 +0200 +++ b/schema/roles.sql Mon Jul 23 11:29:41 2018 +0200 @@ -3,4 +3,4 @@ -- CREATE ROLE waterway_user; CREATE ROLE waterway_admin IN ROLE waterway_user; -CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin; +CREATE ROLE sys_admin IN ROLE waterway_admin;
--- a/schema/run_tests.sh Sun Jul 22 10:40:17 2018 +0200 +++ b/schema/run_tests.sh Mon Jul 23 11:29:41 2018 +0200 @@ -13,7 +13,7 @@ # in cluster, which would prevent tests from being repeatable psql -qXv ON_ERROR_STOP= -v AUTOCOMMIT=off -d gemma_test \ -f tap_tests_data.sql \ - -c 'SELECT plan(22)' \ + -c 'SELECT plan(23)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()' \
--- a/schema/tap_tests_data.sql Sun Jul 22 10:40:17 2018 +0200 +++ b/schema/tap_tests_data.sql Mon Jul 23 11:29:41 2018 +0200 @@ -7,12 +7,14 @@ INSERT INTO users.responsibility_areas VALUES ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)); -INSERT INTO users.user_profiles (username, country, email_adress) - VALUES - ('waterway_user', 'AT', 'xxx'), - ('waterway_user2', 'RO', 'xxy'), - ('waterway_admin', 'AT', 'yyy'), - ('sys_admin', 'AT', 'zzz'); +SELECT sys_admin.create_user( + 'waterway_user', 'user_at', 'user_at', 'AT', NULL, 'xxx'); +SELECT sys_admin.create_user( + 'waterway_user', 'user_ro', 'user_ro', 'RO', NULL, 'xxy'); +SELECT sys_admin.create_user( + 'waterway_admin', 'admin_at', 'admin_at', 'AT', NULL, 'yyy'); +SELECT sys_admin.create_user( + 'sys_admin', 'sys_admin1', 'sys_admin1', 'AT', NULL, 'zzz'); INSERT INTO limiting_factors VALUES ('depth'), ('width'); @@ -51,4 +53,4 @@ INSERT INTO users.templates (template_name, template_data) VALUES ('AT', '\x'), ('RO', '\x'); INSERT INTO users.user_templates - VALUES ('waterway_user', 'AT'), ('waterway_user2', 'RO'); + VALUES ('user_at', 'AT'), ('user_ro', 'RO');