Mercurial > gemma
changeset 342:c6bd6ed18942
Use INSTEAD OF trigger for user deletion
As we already have this for updating, it's more symmetric to make
the whole thing look like a real table. TODO: make it happen also
for user creation.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 06 Aug 2018 12:37:06 +0200 |
| parents | 889517f254f5 |
| children | 5b03f420957d |
| files | controllers/user.go schema/manage_users.sql schema/manage_users_tests.sql schema/run_tests.sh |
| diffstat | 4 files changed, 25 insertions(+), 18 deletions(-) [+] |
line wrap: on
line diff
--- a/controllers/user.go Mon Aug 06 12:36:20 2018 +0200 +++ b/controllers/user.go Mon Aug 06 12:37:06 2018 +0200 @@ -28,7 +28,7 @@ = ($2, $3, $4, $5, ST_MakeBox2D(ST_Point($6, $7), ST_Point($8, $9)), $10) WHERE username = $1` - deleteUserSQL = `SELECT sys_admin.delete_user($1)` + deleteUserSQL = `DELETE FROM users.list_users WHERE username = $1` listUsersSQL = `SELECT rolname, @@ -66,7 +66,17 @@ return } - if _, err = db.Exec(deleteUserSQL, user); err != nil { + var res sql.Result + + if res, err = db.Exec(deleteUserSQL, user); err != nil { + return + } + + if n, err2 := res.RowsAffected(); err2 == nil && n == 0 { + err = JSONError{ + Code: http.StatusNotFound, + Message: fmt.Sprintf("Cannot find user %s.", user), + } return }
--- a/schema/manage_users.sql Mon Aug 06 12:36:20 2018 +0200 +++ b/schema/manage_users.sql Mon Aug 06 12:37:06 2018 +0200 @@ -141,16 +141,14 @@ EXECUTE PROCEDURE internal.update_user(); -CREATE OR REPLACE FUNCTION sys_admin.delete_user( - username internal.user_profiles.username%TYPE - ) - RETURNS void +CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger AS $$ DECLARE bid int; BEGIN -- Terminate the users backends started before the current transaction - FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = username LOOP + FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username + LOOP PERFORM pg_terminate_backend(bid); END LOOP; -- Note that any backend that might be started during the transaction @@ -158,14 +156,19 @@ -- without any privileges after commiting this transaction -- Delete user - EXECUTE format('DROP ROLE %I', username); + EXECUTE format('DROP ROLE %I', OLD.username); DELETE FROM internal.user_profiles p - WHERE p.username = delete_user.username; + WHERE p.username = OLD.username; + + RETURN OLD; END; $$ LANGUAGE plpgsql SECURITY DEFINER; +CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW + EXECUTE PROCEDURE internal.delete_user(); + CREATE OR REPLACE VIEW pw_reset.list_users AS SELECT username, pw, email_address FROM users.list_users;
--- a/schema/manage_users_tests.sql Mon Aug 06 12:36:20 2018 +0200 +++ b/schema/manage_users_tests.sql Mon Aug 06 12:37:06 2018 +0200 @@ -278,18 +278,12 @@ SELECT lives_ok($$ SELECT sys_admin.create_user( 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3'); - SELECT sys_admin.delete_user('test3') + DELETE FROM users.list_users WHERE username = 'test3' $$, 'Existing user can be deleted'); SELECT throws_ok($$ - SELECT sys_admin.delete_user('test_non_existent') - $$, - 42704, NULL, - 'Non-existent user cannot be deleted'); - -SELECT throws_ok($$ - SELECT sys_admin.delete_user(CAST(current_user AS varchar)) + DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar) $$, 55006, NULL, 'Current user cannot be deleted');
--- a/schema/run_tests.sh Mon Aug 06 12:36:20 2018 +0200 +++ b/schema/run_tests.sh Mon Aug 06 12:37:06 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(45)' \ + -c 'SELECT plan(44)' \ -f auth_tests.sql \ -f manage_users_tests.sql \ -c 'SELECT * FROM finish()'