Mercurial > gemma

comparison schema/gemma.sql @ 4740:2440d2f86f4e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Authorize sections based on country of creator Disregarding the area of resposibility will allow to create sections outside of the country of the user in order to allow data analysis and aggregation based on sections spanning over multiple countries.
author Tom Gottfried <tom@intevation.de>
date Fri, 18 Oct 2019 12:04:04 +0200
parents baabc2b2f094
children 47922c1a088d
comparison
equal deleted inserted replaced
4739:257dd6039a28 4740:2440d2f86f4e
438 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 438 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
439 UNIQUE (template_name, template_type, country) 439 UNIQUE (template_name, template_type, country)
440 ) 440 )
441 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates 441 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
442 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 442 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
443
444 CREATE VIEW users.list_users WITH (security_barrier) AS
445 SELECT
446 r.rolname,
447 p.username,
448 CAST('' AS varchar) AS pw,
449 p.country,
450 p.map_extent,
451 p.email_address
452 FROM internal.user_profiles p
453 JOIN pg_roles u ON p.username = u.rolname
454 JOIN pg_auth_members a ON u.oid = a.member
455 JOIN pg_roles r ON a.roleid = r.oid
456 WHERE p.username = current_user
457 OR pg_has_role('waterway_admin', 'MEMBER')
458 AND p.country = (
459 SELECT country FROM internal.user_profiles
460 WHERE username = current_user)
461 OR pg_has_role('sys_admin', 'MEMBER')
443 ; 462 ;
463
464
465 --
466 -- Functions to be used in DEFAULT expresions
467 --
468
469 -- Return current_user's country code
470 CREATE FUNCTION users.user_country(user_name name DEFAULT current_user)
471 RETURNS internal.user_profiles.country%TYPE
472 AS $$
473 SELECT country FROM users.list_users
474 WHERE username = user_name
475 $$
476 LANGUAGE SQL
477 STABLE PARALLEL SAFE;
444 478
445 479
446 -- Namespace for waterway data that can change in a running system 480 -- Namespace for waterway data that can change in a running system
447 CREATE SCHEMA waterway 481 CREATE SCHEMA waterway
448 482
587 section isrsrange NOT NULL, 621 section isrsrange NOT NULL,
588 area geography(MULTIPOLYGON, 4326) NOT NULL 622 area geography(MULTIPOLYGON, 4326) NOT NULL
589 CHECK(ST_IsValid(CAST(area AS geometry))), 623 CHECK(ST_IsValid(CAST(area AS geometry))),
590 objnam varchar NOT NULL, 624 objnam varchar NOT NULL,
591 nobjnam varchar, 625 nobjnam varchar,
626 country char(2) NOT NULL REFERENCES countries
627 DEFAULT users.user_country(),
592 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 628 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
593 source_organization varchar NOT NULL, 629 source_organization varchar NOT NULL,
594 staging_done boolean NOT NULL DEFAULT false, 630 staging_done boolean NOT NULL DEFAULT false,
595 UNIQUE(name, staging_done) 631 UNIQUE(name, staging_done)
596 ) 632 )