gemma: schema/manage_users.sql annotate

annotate schema/manage_users.sql @ 3678:8f58851927c0

client: make layer factory only return new layer config for individual maps instead of each time it is invoked. The purpose of the factory was to support multiple maps with individual layers. But returning a new config each time it is invoked leads to bugs that rely on the layer's state. Now this factory reuses the same objects it created before, per map.

author	Markus Kottlaender <markus@intevation.de>
date	Mon, 17 Jun 2019 17:31:35 +0200
parents	69292eb68984
children	5466562cca60

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	6
2912 93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	7 -- Copyright (C) 2018, 2019 by via donau
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	11 -- Author(s):
1301 2304778c4432 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1298 diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
1336 f65d1767452c add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1301 diff changeset	13 -- * Sacha Teichmann <sascha.teichmann@intevation.de>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	14
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	15 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	16 -- Functions encapsulating user management functionality and
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	17 -- exposing it to appropriately privileged users
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	18 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	19
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	20 CREATE OR REPLACE FUNCTION internal.check_password(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	21 pw varchar
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	22 )
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	23 RETURNS varchar
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	24 AS $$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	25 DECLARE
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	26 min_len CONSTANT int = 8;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	27 BEGIN
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	28 IF char_length(pw) < min_len
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	29 OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	30 OR pw NOT SIMILAR TO '%[[:digit:]]%'
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	31 THEN
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	32 RAISE invalid_password USING MESSAGE = 'Invalid password';
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	33 ELSE
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	34 RETURN pw;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	35 END IF;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	36 END;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	37 $$
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	38 LANGUAGE plpgsql;
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	39
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	40
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	41 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	42 SELECT
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	43 r.rolname,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	44 p.username,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	45 CAST('' AS varchar) AS pw,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	46 p.country,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	47 p.map_extent,
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	48 p.email_address
269 0b2d9f96ddb8 Replace CTE with access privilege inquiry function call. Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	49 FROM internal.user_profiles p
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	50 JOIN pg_roles u ON p.username = u.rolname
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	51 JOIN pg_auth_members a ON u.oid = a.member
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	52 JOIN pg_roles r ON a.roleid = r.oid
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	53 WHERE p.username = current_user
269 0b2d9f96ddb8 Replace CTE with access privilege inquiry function call. Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	54 OR pg_has_role('waterway_admin', 'MEMBER')
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	55 AND p.country = (
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	56 SELECT country FROM internal.user_profiles
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	57 WHERE username = current_user)
269 0b2d9f96ddb8 Replace CTE with access privilege inquiry function call. Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	58 OR pg_has_role('sys_admin', 'MEMBER');
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	59
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 233 diff changeset	60
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	61 CREATE OR REPLACE FUNCTION users.current_user_country()
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	62 RETURNS internal.user_profiles.country%TYPE
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	63 AS $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	64 SELECT country FROM users.list_users
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	65 WHERE username = current_user
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	66 $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	67 LANGUAGE SQL
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	68 STABLE PARALLEL SAFE;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	69
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	70
2912 93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	71 CREATE OR REPLACE FUNCTION users.current_user_area_utm()
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	72 RETURNS geometry
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	73 AS $$
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	74 DECLARE utm_area geometry;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	75 BEGIN
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	76 SELECT ST_Transform(area::geometry, best_utm(area))
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	77 INTO STRICT utm_area
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	78 FROM users.responsibility_areas
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	79 WHERE country = users.current_user_country();
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	80 RETURN utm_area;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	81 END;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	82 $$
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	83 LANGUAGE plpgsql
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	84 STABLE PARALLEL SAFE;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	85
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	86
2915 69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	87 CREATE OR REPLACE FUNCTION users.utm_covers(g geography) RETURNS boolean AS
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	88 $$
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	89 SELECT ST_Covers(a, ST_Transform(g::geometry, ST_SRID(a)))
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	90 FROM users.current_user_area_utm() AS a (a)
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	91 $$
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	92 LANGUAGE SQL
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	93 STABLE PARALLEL SAFE;
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	94
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	95
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	96 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	97 AS $$
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	98 BEGIN
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	99 IF NEW.map_extent IS NULL
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	100 THEN
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	101 NEW.map_extent = ST_Extent(CAST(area AS geometry))
fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	102 FROM users.responsibility_areas ra
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	103 WHERE ra.country = NEW.country;
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	104 END IF;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	105 INSERT INTO internal.user_profiles (
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	106 username, country, map_extent, email_address)
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	107 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	108 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	109 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	110 NEW.username,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	111 NEW.rolname,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	112 internal.check_password(NEW.pw));
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	113
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	114 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	115 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	116 RETURN NEW;
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	117 END;
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	118 $$
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	119 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	120 SECURITY DEFINER;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	121
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	122 CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	123 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	124
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	125 CREATE OR REPLACE FUNCTION internal.update_metamorph() RETURNS trigger
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	126 AS $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	127 BEGIN
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	128 EXECUTE format('GRANT %I TO metamorph', NEW.username);
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	129 RETURN NEW;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	130 END;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	131 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	132 LANGUAGE plpgsql
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	133 SECURITY DEFINER;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	134
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	135 -- Note that PostgreSQL fires triggers for the same event in alphabetical
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	136 -- order! Make sure that the new role is created before this trigger is fired.
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	137 CREATE TRIGGER update_metamorph INSTEAD OF INSERT ON users.list_users
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	138 FOR EACH ROW
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	139 EXECUTE PROCEDURE internal.update_metamorph();
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	140
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	141
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 468 diff changeset	142 -- Prevent roles other than sys_admin to update any user but
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	143 -- themselves (affects waterway_admin)
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	144 CREATE OR REPLACE FUNCTION internal.authorize_update_user() RETURNS trigger
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	145 AS $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	146 BEGIN
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	147 IF OLD.username <> current_user
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 468 diff changeset	148 AND NOT pg_has_role('sys_admin', 'MEMBER')
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	149 THEN
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	150 RETURN NULL;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	151 ELSE
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	152 RETURN NEW;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	153 END IF;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	154 END;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	155 $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	156 LANGUAGE plpgsql;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	157
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	158 -- Note that PostgreSQL fires triggers for the same event in alphabetical
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	159 -- order! Make sure that authorization takes place before any other trigger
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	160 -- is fired that might execute otherwise unauthorized statements!
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	161 CREATE TRIGGER authorize_update_user INSTEAD OF UPDATE ON users.list_users
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	162 FOR EACH ROW
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	163 EXECUTE PROCEDURE internal.authorize_update_user();
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	164
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	165
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	166 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	167 AS $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	168 DECLARE
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	169 cur_username varchar;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	170 BEGIN
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	171 cur_username = OLD.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	172
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	173 UPDATE internal.user_profiles p
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	174 SET (username, country, map_extent, email_address)
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	175 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	176 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	177
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	178 IF NEW.username <> cur_username
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	179 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	180 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	181 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	182 cur_username = NEW.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	183 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	184
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	185 IF NEW.rolname <> OLD.rolname
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	186 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	187 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	188 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	189 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	190 'GRANT %I TO %I', NEW.rolname, cur_username);
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	191 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	192
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	193 IF NEW.pw IS NOT NULL AND NEW.pw <> ''
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	194 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	195 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	196 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	197 cur_username,
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	198 internal.check_password(NEW.pw));
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	199 END IF;
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	200
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	201 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	202 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	203 RETURN NEW;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	204 END;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	205 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	206 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	207 SECURITY DEFINER;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	208
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	209 CREATE TRIGGER update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	210 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	211
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	212
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	213 CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	214 AS $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	215 DECLARE
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	216 bid int;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	217 BEGIN
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	218 -- Terminate the users backends started before the current transaction
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	219 FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	220 LOOP
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	221 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	222 END LOOP;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	223 -- Note that any backend that might be started during the transaction
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	224 -- in which this function is executed will not be terminated but lost
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	225 -- without any privileges after commiting this transaction
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	226
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	227 -- Delete user
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	228 EXECUTE format('DROP ROLE %I', OLD.username);
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	229 DELETE FROM internal.user_profiles p
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	230 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	231
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	232 RETURN OLD;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	233 END;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	234 $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	235 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	236 SECURITY DEFINER;
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	237
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	238 CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	239 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	240
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	241
467 73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	242 -- To set a role from a hex-encoded user name (which is save from SQL injections).
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	243 CREATE OR REPLACE FUNCTION public.setrole(role text) RETURNS void
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	244 AS $$
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	245 BEGIN
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	246 IF role IS NOT NULL AND role <> '' THEN
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	247 EXECUTE format('SET ROLE %I', convert_from(decode(role, 'hex'), 'UTF-8'));
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	248 END IF;
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	249 END;
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	250 $$
73c7b2d6246e Used hex-encoded usernames and a stored procedure to decode them to impersonate with the metamorph user. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 463 diff changeset	251 LANGUAGE plpgsql;
468 ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	252
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	253 -- To set a role in form of a plannable statement (which is save from SQL injections).
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	254 CREATE OR REPLACE FUNCTION public.setrole_plan(role text) RETURNS void
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	255 AS $$
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	256 BEGIN
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	257 IF role IS NOT NULL AND role <> '' THEN
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	258 EXECUTE format('SET ROLE %I', role);
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	259 END IF;
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	260 END;
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	261 $$
ff9dbe14f033 Don't use hex encoding for user impersonation when running it from a planned statement. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 467 diff changeset	262 LANGUAGE plpgsql;

Mercurial > gemma

annotate schema/manage_users.sql @ 3678:8f58851927c0