Mercurial > gemma

changeset 343:5b03f420957d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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';