gemma: schema/manage_users.sql annotate

annotate schema/manage_users.sql @ 4606:dfe9cde6a20c geoserver_sql_views

Reflect database model changes for SQL views in backend In principle, we could use many datasources with different database schemas, but this would imply changing GeoServer initialization, service filtering, endpoints and eventually more. Since we do not need it, just hard-code the schema name as a constant.

author	Tom Gottfried <tom@intevation.de>
date	Thu, 05 Sep 2019 12:23:31 +0200
parents	5466562cca60
children	5e38667f740c

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
2912 93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	61 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	62 RETURNS geometry
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	63 AS $$
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	64 DECLARE utm_area geometry;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	65 BEGIN
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	66 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	67 INTO STRICT utm_area
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	68 FROM users.responsibility_areas
4158 5466562cca60 Remove utility function with possibly bad performance impact Tom Gottfried <tom@intevation.de> parents: 2915 diff changeset	69 WHERE country = (SELECT country
5466562cca60 Remove utility function with possibly bad performance impact Tom Gottfried <tom@intevation.de> parents: 2915 diff changeset	70 FROM users.list_users WHERE username = current_user);
5466562cca60 Remove utility function with possibly bad performance impact Tom Gottfried <tom@intevation.de> parents: 2915 diff changeset	71 RETURN utm_area;
2912 93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	72 END;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	73 $$
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	74 LANGUAGE plpgsql
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	75 STABLE PARALLEL SAFE;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	76
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1338 diff changeset	77
2915 69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	78 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	79 $$
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	80 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	81 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	82 $$
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	83 LANGUAGE SQL
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	84 STABLE PARALLEL SAFE;
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	85
69292eb68984 Simplify function and move to appropriate schema Tom Gottfried <tom@intevation.de> parents: 2912 diff changeset	86
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	87 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	88 AS $$
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	89 BEGIN
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	90 IF NEW.map_extent IS NULL
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	91 THEN
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	92 NEW.map_extent = ST_Extent(CAST(area AS geometry))
fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	93 FROM users.responsibility_areas ra
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	94 WHERE ra.country = NEW.country;
212 229f385448fa Make map extent mandatory Tom Gottfried <tom@intevation.de> parents: 207 diff changeset	95 END IF;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	96 INSERT INTO internal.user_profiles (
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	97 username, country, map_extent, email_address)
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	98 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	99 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	100 '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	101 NEW.username,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	102 NEW.rolname,
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	103 internal.check_password(NEW.pw));
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	104
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	105 -- Do not leak new password
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	106 NEW.pw = '';
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	107 RETURN NEW;
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	108 END;
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	109 $$
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	110 LANGUAGE plpgsql
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 195 diff changeset	111 SECURITY DEFINER;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	112
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	113 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	114 EXECUTE PROCEDURE internal.create_user();
5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	115
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	116 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	117 AS $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	118 BEGIN
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	119 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	120 RETURN NEW;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	121 END;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	122 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	123 LANGUAGE plpgsql
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	124 SECURITY DEFINER;
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	125
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	126 -- 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	127 -- 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	128 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	129 FOR EACH ROW
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	130 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	131
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	132
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 468 diff changeset	133 -- 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	134 -- themselves (affects waterway_admin)
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	135 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	136 AS $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	137 BEGIN
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	138 IF OLD.username <> current_user
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 468 diff changeset	139 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	140 THEN
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	141 RETURN NULL;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	142 ELSE
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	143 RETURN NEW;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	144 END IF;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	145 END;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	146 $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	147 LANGUAGE plpgsql;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	148
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	149 -- 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	150 -- 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	151 -- 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	152 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	153 FOR EACH ROW
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	154 EXECUTE PROCEDURE internal.authorize_update_user();
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
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	157 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	158 AS $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	159 DECLARE
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	160 cur_username varchar;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	161 BEGIN
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	162 cur_username = OLD.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	163
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 263 diff changeset	164 UPDATE internal.user_profiles p
233 531d1f8a2b4b Fix spelling Tom Gottfried <tom@intevation.de> parents: 232 diff changeset	165 SET (username, country, map_extent, email_address)
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	166 = (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	167 WHERE p.username = cur_username;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	168
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	169 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	170 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	171 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	172 '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	173 cur_username = NEW.username;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	174 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	175
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	176 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	177 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	178 EXECUTE format(
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	179 'REVOKE %I FROM %I', OLD.rolname, cur_username);
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	180 EXECUTE format(
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	181 '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	182 END IF;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	183
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	184 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	185 THEN
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	186 EXECUTE format(
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	187 'ALTER ROLE %I PASSWORD %L',
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	188 cur_username,
312 0745b4d336c4 Place functions in more matching schemas Tom Gottfried <tom@intevation.de> parents: 307 diff changeset	189 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	190 END IF;
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	191
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	192 -- Do not leak new password
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	193 NEW.pw = '';
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	194 RETURN NEW;
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	195 END;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	196 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	197 LANGUAGE plpgsql
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	198 SECURITY DEFINER;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	199
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	200 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	201 EXECUTE PROCEDURE internal.update_user();
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 269 diff changeset	202
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	203
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	204 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	205 AS $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	206 DECLARE
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	207 bid int;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	208 BEGIN
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	209 -- 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	210 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	211 LOOP
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	212 PERFORM pg_terminate_backend(bid);
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	213 END LOOP;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	214 -- 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	215 -- 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	216 -- 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	217
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	218 -- Delete user
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	219 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	220 DELETE FROM internal.user_profiles p
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	221 WHERE p.username = OLD.username;
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	222
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	223 RETURN OLD;
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	224 END;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	225 $$
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	226 LANGUAGE plpgsql
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	227 SECURITY DEFINER;
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	228
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	229 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	230 EXECUTE PROCEDURE internal.delete_user();
c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	231
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 312 diff changeset	232
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	233 -- 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	234 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	235 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	236 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	237 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	238 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	239 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	240 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	241 $$
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 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	243
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	244 -- 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	245 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	246 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	247 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	248 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	249 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	250 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	251 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	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 LANGUAGE plpgsql;

Mercurial > gemma

annotate schema/manage_users.sql @ 4606:dfe9cde6a20c geoserver_sql_views