# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1532338181 -7200
# Node ID 88d21c29cf04cc695d83deafe570a8f9ea1f7637
# Parent  cd6ad5eaef8dedc67ed620fa3d125f078b521741
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.

diff -r cd6ad5eaef8d -r 88d21c29cf04 schema/auth_tests.sql
--- 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
diff -r cd6ad5eaef8d -r 88d21c29cf04 schema/manage_users.sql
--- 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;
diff -r cd6ad5eaef8d -r 88d21c29cf04 schema/manage_users_tests.sql
--- 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');
 
diff -r cd6ad5eaef8d -r 88d21c29cf04 schema/roles.sql
--- 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;
diff -r cd6ad5eaef8d -r 88d21c29cf04 schema/run_tests.sh
--- 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()' \
diff -r cd6ad5eaef8d -r 88d21c29cf04 schema/tap_tests_data.sql
--- 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');