Mercurial > gemma
changeset 5496:8797274e2739 deactivate-users
Added a active column to users.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 21 Sep 2021 15:31:26 +0200 |
| parents | 0766dcb7e7f8 |
| children | 1e6053a4ed98 |
| files | schema/gemma.sql schema/updates/1464/01.active.sql schema/version.sql |
| diffstat | 3 files changed, 105 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Tue Sep 21 12:49:30 2021 +0200 +++ b/schema/gemma.sql Tue Sep 21 15:31:26 2021 +0200 @@ -385,7 +385,8 @@ country char(2) NOT NULL REFERENCES countries, map_extent box2d NOT NULL, email_address varchar NOT NULL, - report_reciever boolean NOT NULL DEFAULT false + report_reciever boolean NOT NULL DEFAULT false, + active boolean NOT NULL DEFAULT true ) ; @@ -500,7 +501,8 @@ p.country, p.map_extent, p.email_address, - p.report_reciever + p.report_reciever, + p.active FROM internal.user_profiles p JOIN pg_roles u ON p.username = u.rolname JOIN pg_auth_members a ON u.oid = a.member
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1464/01.active.sql Tue Sep 21 15:31:26 2021 +0200 @@ -0,0 +1,100 @@ +ALTER TABLE internal.user_profiles + ADD COLUMN active boolean NOT NULL DEFAULT true; + +CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS + SELECT + r.rolname, + p.username, + CAST('' AS varchar) AS pw, + p.country, + p.map_extent, + p.email_address, + p.report_reciever, + p.active + FROM internal.user_profiles p + JOIN pg_roles u ON p.username = u.rolname + JOIN pg_auth_members a ON u.oid = a.member + JOIN pg_roles r ON a.roleid = r.oid + WHERE p.username = current_user + OR pg_has_role('waterway_admin', 'MEMBER') + AND p.country = ( + SELECT country FROM internal.user_profiles + WHERE username = current_user) + AND r.rolname <> 'sys_admin' + OR pg_has_role('sys_admin', 'MEMBER'); + +CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger +AS $$ +DECLARE + cur_username varchar; +BEGIN + cur_username = OLD.username; + + IF NEW.username <> cur_username + THEN + EXECUTE format( + 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username); + cur_username = NEW.username; + END IF; + + UPDATE internal.user_profiles p + SET (username, country, map_extent, email_address, report_reciever, active) + = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever, NEW.active) + WHERE p.username = cur_username; + + IF NEW.rolname <> OLD.rolname + THEN + EXECUTE format( + 'REVOKE %I FROM %I', OLD.rolname, cur_username); + EXECUTE format( + 'GRANT %I TO %I', NEW.rolname, cur_username); + END IF; + + IF NEW.pw IS NOT NULL AND NEW.pw <> '' + THEN + EXECUTE format( + 'ALTER ROLE %I PASSWORD %L', + cur_username, + internal.check_password(NEW.pw)); + END IF; + + -- Do not leak new password + NEW.pw = ''; + RETURN NEW; +END; +$$ + LANGUAGE plpgsql + SECURITY DEFINER; + +CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger +AS $$ +BEGIN + IF NEW.map_extent IS NULL + THEN + NEW.map_extent = ST_Extent(CAST(area AS geometry)) + FROM users.stretches st + JOIN users.stretch_countries stc ON stc.stretch_id = st.id + WHERE stc.country = NEW.country; + END IF; + + IF NEW.username IS NOT NULL + -- otherwise let the constraint on user_profiles speak + THEN + EXECUTE format( + 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', + NEW.username, + NEW.rolname, + internal.check_password(NEW.pw)); + END IF; + + INSERT INTO internal.user_profiles ( + username, country, map_extent, email_address, report_reciever, active) + VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever, NEW.active); + + -- Do not leak new password + NEW.pw = ''; + RETURN NEW; +END; +$$ + LANGUAGE plpgsql + SECURITY DEFINER;