Mercurial > gemma
view schema/updates/1450/01.report_reciever.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | 5b2faff9ec81 |
children |
line wrap: on
line source
ALTER TABLE internal.user_profiles ADD COLUMN report_reciever boolean NOT NULL DEFAULT false; 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 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) = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever) 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) VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address, NEW.report_reciever); -- Do not leak new password NEW.pw = ''; RETURN NEW; END; $$ LANGUAGE plpgsql SECURITY DEFINER;