Mercurial > gemma
changeset 195:5dc8e734487a
Introduce database schemas as privilege-based namespaces
Some privileges changed (e.g. for responsibility_areas), but additional
privileges were not left off intentionally before.
Search path settings have been replaced by schema-qualifying names in
statements to prevent object definitions from being dependend on
search path settings.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 20 Jul 2018 17:28:16 +0200 |
| parents | cf5dcc1761df |
| children | b67208d82543 |
| files | schema/auth.sql schema/auth_tests.sql schema/gemma.sql schema/manage_users.sql schema/manage_users_tests.sql schema/tap_tests_data.sql |
| diffstat | 6 files changed, 387 insertions(+), 371 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Fri Jul 20 18:32:40 2018 +0200 +++ b/schema/auth.sql Fri Jul 20 17:28:16 2018 +0200 @@ -3,7 +3,6 @@ -- -- Roles, privileges and policies for the GEMMA database -- -SET search_path TO public, gemma, gemma_waterway, gemma_fairway; -- We do not want any users to be able to create any objects REVOKE CREATE ON SCHEMA public FROM PUBLIC; @@ -11,25 +10,24 @@ -- -- Privileges for waterway_user -- -GRANT USAGE ON SCHEMA gemma, gemma_waterway, gemma_fairway TO waterway_user; -GRANT SELECT ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway - TO waterway_user; -GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user; +GRANT USAGE ON SCHEMA users, waterway TO waterway_user; +GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user; -- -- Extended privileges for waterway_admin -- -GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway - TO waterway_admin; +GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA waterway TO waterway_admin; -- TODO: will there ever be UPDATEs or can we drop that due to historicisation? -GRANT INSERT, UPDATE, DELETE ON templates, user_templates TO waterway_admin; -GRANT SELECT ON responsibility_areas TO waterway_admin; +GRANT INSERT, UPDATE, DELETE ON + users.templates, users.user_templates TO waterway_admin; -- -- Extended privileges for sys_admin -- -GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gemma - TO sys_admin; +GRANT INSERT, UPDATE, DELETE + ON users.user_profiles, users.responsibility_areas TO sys_admin; +GRANT USAGE ON SCHEMA sys_admin TO sys_admin; +GRANT UPDATE ON sys_admin.system_config TO sys_admin; -- -- RLS policies for waterway_user @@ -52,9 +50,10 @@ 'bottlenecks', 'sounding_results'] -- TODO: add all relevant tables here LOOP - EXECUTE format('CREATE POLICY hide_staging ON %I ' + EXECUTE format('CREATE POLICY hide_staging ON waterway.%I ' 'FOR SELECT TO waterway_user USING (staging_done)', the_table); - EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table); + EXECUTE format('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY', + the_table); END LOOP; END; $$ @@ -62,18 +61,18 @@ SELECT create_hide_staging_policy(); DROP FUNCTION create_hide_staging_policy; -CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user +CREATE POLICY see_yourself ON users.user_profiles FOR SELECT TO waterway_user USING (username = current_user); -ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; +ALTER TABLE users.user_profiles ENABLE ROW LEVEL SECURITY; -CREATE POLICY user_templates ON user_templates FOR ALL TO waterway_user - USING (username IN(SELECT username FROM user_profiles)); -ALTER TABLE user_templates ENABLE ROW LEVEL SECURITY; +CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user + USING (username IN(SELECT username FROM users.user_profiles)); +ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY; -CREATE POLICY user_templates ON templates FOR ALL TO waterway_user - USING (template_name IN(SELECT template_name FROM user_templates)) +CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user + USING (template_name IN(SELECT template_name FROM users.user_templates)) WITH CHECK (true); -ALTER TABLE templates ENABLE ROW LEVEL SECURITY; +ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; -- -- RLS policies for waterway_admin @@ -82,22 +81,27 @@ -- Security-definer function to get current users country, which allows to -- restrict the view on user_profiles by country without infinite recursion CREATE FUNCTION current_user_country() - RETURNS gemma.user_profiles.country%TYPE - AS $$ SELECT country FROM user_profiles WHERE username = session_user $$ + RETURNS users.user_profiles.country%TYPE + AS $$ + SELECT country FROM users.user_profiles WHERE username = session_user + $$ LANGUAGE SQL SECURITY DEFINER STABLE PARALLEL SAFE; -- Staging area -- TODO: add all relevant tables here -CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin - USING (ST_Within(area, (SELECT area FROM responsibility_areas +CREATE POLICY responsibility_area ON waterway.bottlenecks + FOR ALL TO waterway_admin + USING (ST_Within(area, (SELECT area FROM users.responsibility_areas WHERE country = current_user_country()))); -CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin - USING (ST_Within(area, (SELECT area FROM responsibility_areas +CREATE POLICY responsibility_area ON waterway.sounding_results + FOR ALL TO waterway_admin + USING (ST_Within(area, (SELECT area FROM users.responsibility_areas WHERE country = current_user_country()))); -CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin +CREATE POLICY country_profiles ON users.user_profiles + FOR SELECT TO waterway_admin USING (country = current_user_country()); COMMIT;
--- a/schema/auth_tests.sql Fri Jul 20 18:32:40 2018 +0200 +++ b/schema/auth_tests.sql Fri Jul 20 17:28:16 2018 +0200 @@ -3,8 +3,6 @@ -- SELECT plan(16); -- Give number of tests that have to be run -SET search_path TO public, gemma, gemma_waterway, gemma_fairway; - -- -- Run tests as unprivileged user -- @@ -13,21 +11,21 @@ SELECT throws_ok('CREATE TABLE test()', 42501, NULL, 'No objects can be created'); -SELECT isnt_empty('SELECT * FROM bottlenecks', +SELECT isnt_empty('SELECT * FROM waterway.bottlenecks', 'Staged data should be visible'); -SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done', +SELECT is_empty('SELECT * FROM waterway.bottlenecks WHERE NOT staging_done', 'Only staged data should be visible'); -SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1], +SELECT set_eq('SELECT count(*) FROM users.user_profiles', ARRAY[1], 'User should only see his own profile'); -SELECT results_eq('SELECT username FROM user_profiles', +SELECT results_eq('SELECT username FROM users.user_profiles', 'SELECT CAST(current_user AS varchar)', 'User should only see his own profile'); -SELECT isnt_empty('SELECT * FROM templates', +SELECT isnt_empty('SELECT * FROM users.templates', 'User should see templates associated to him'); -SELECT is_empty('SELECT * FROM templates - JOIN user_templates USING (template_name) +SELECT is_empty('SELECT * FROM users.templates + JOIN users.user_templates USING (template_name) WHERE username <> current_user', 'User should only see templates associated to him'); @@ -37,7 +35,7 @@ SET SESSION AUTHORIZATION waterway_admin; PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS - INSERT INTO bottlenecks ( + INSERT INTO waterway.bottlenecks ( bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, source_organization) VALUES ( @@ -61,35 +59,35 @@ 'Waterway admin cannot insert data outside his region'); -- template management -SELECT isnt_empty('SELECT * FROM templates - JOIN user_templates USING (template_name) +SELECT isnt_empty('SELECT * FROM users.templates + JOIN users.user_templates USING (template_name) WHERE username <> current_user', 'Waterway admin should see templates of other users'); -SELECT lives_ok('INSERT INTO templates (template_name, template_data) +SELECT lives_ok('INSERT INTO users.templates (template_name, template_data) VALUES (''New AT'', ''\x''); - INSERT INTO user_templates + INSERT INTO users.user_templates VALUES (''waterway_user'', ''New AT'')', 'Waterway admin can add templates for users in his country'); -SELECT throws_ok('INSERT INTO user_templates +SELECT throws_ok('INSERT INTO users.user_templates VALUES (''waterway_user2'', ''AT'')', 42501, NULL, 'Waterway admin cannot add template for other country'); -SELECT isnt_empty('UPDATE templates SET template_data = ''\xDABE'' +SELECT isnt_empty('UPDATE users.templates SET template_data = ''\xDABE'' WHERE template_name = ''AT'' RETURNING *', 'Waterway admin can alter templates for own country'); -SELECT is_empty('UPDATE templates SET template_data = ''\xDABE'' +SELECT is_empty('UPDATE users.templates SET template_data = ''\xDABE'' WHERE template_name = ''RO'' RETURNING *', 'Waterway admin cannot alter templates for other country'); -SELECT isnt_empty('DELETE FROM templates WHERE template_name = ''AT'' +SELECT isnt_empty('DELETE FROM users.templates WHERE template_name = ''AT'' RETURNING *', 'Waterway admin can delete templates for own country'); -SELECT is_empty('DELETE FROM templates WHERE template_name = ''RO'' +SELECT is_empty('DELETE FROM users.templates WHERE template_name = ''RO'' RETURNING *', 'Waterway admin cannot delete templates for other country');
--- a/schema/gemma.sql Fri Jul 20 18:32:40 2018 +0200 +++ b/schema/gemma.sql Fri Jul 20 17:28:16 2018 +0200 @@ -35,96 +35,40 @@ -- -- GEMMA data -- -CREATE SCHEMA gemma; -CREATE SCHEMA gemma_waterway; -CREATE SCHEMA gemma_fairway; -SET search_path TO public, gemma, gemma_waterway, gemma_fairway; --- TODO: consolidate schemas. The current distribution of tables is mainly --- for diagram generation. A privilege based distribution might ease --- privilege management. + +-- Namespace to be accessed by sys_admin only +CREATE SCHEMA sys_admin + CREATE TABLE sys_admin.system_config ( + config_key varchar PRIMARY KEY, + config_val varchar + ) +; -- --- Auxiliary tables +-- Look-up tables with data that are static in a running system -- -CREATE TABLE gemma.system_config ( - config_key varchar PRIMARY KEY, - config_val varchar -); - -CREATE TABLE gemma.countries ( +CREATE TABLE countries ( country_code char(2) PRIMARY KEY -- ISO 3166 country code -- A smallint PK would require even less disk space i.e. on the FK side. -- This might be an issue in case cache space becomes a limiting -- factor when there are many FKs pointing here. ); -CREATE TABLE gemma.responsibility_areas ( - country char(2) PRIMARY KEY REFERENCES countries, - area geometry(MULTIPOLYGON, 4326) - --XXX: Should be geography (elsewhere too) -); - -CREATE TABLE gemma.language_codes ( +CREATE TABLE language_codes ( language_code varchar PRIMARY KEY ); -CREATE TABLE gemma.user_profiles ( - username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger - country char(2) NOT NULL REFERENCES countries, - map_extent box2d, - email_adress varchar NOT NULL UNIQUE -); - -CREATE TABLE gemma.templates ( - template_name varchar PRIMARY KEY, - template_data bytea NOT NULL, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP -); -CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - -CREATE TABLE gemma.user_templates ( - username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE, - template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE, - PRIMARY KEY (username, template_name) -); - --- --- General river information --- - --- Eventually obsolete. --- See https://roundup-intern.intevation.de/gemma/issue5 --- CREATE TABLE rwdrs ( --- stretch isrsrange PRIMARY KEY, --- -- https://www.postgresql.org/docs/10/static/sql-createindex.html: --- -- Only B-tree supports UNIQUE indexes, but we need the GIST index --- -- below anyhow. --- -- Is it a good idea to build B-tree indexes on relatively large --- -- composites of string values or should we use inter PKs? --- -- => In case the index is used and cache space becomes a limiting --- -- factor, this might be an issue. --- rwdr double precision NOT NULL, --- EXCLUDE USING GIST (stretch WITH &&) ---); - -CREATE TABLE gemma_waterway.catccls ( +CREATE TABLE catccls ( catccl smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? ); -CREATE TABLE gemma_waterway.dirimps ( +CREATE TABLE dirimps ( dirimp smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? ); -CREATE TABLE gemma_waterway.waterway_area ( - area geometry(POLYGON, 4326) PRIMARY KEY, - catccl smallint REFERENCES catccls, - dirimp smallint REFERENCES dirimps -); - -CREATE TABLE gemma_fairway.depth_references ( +CREATE TABLE depth_references ( depth_reference varchar(4) PRIMARY KEY -- See col. AB and AI RIS-Index Encoding Guide -- XXX: We need a way to distinguish between geodetic (eg. col. AP @@ -135,76 +79,16 @@ -- (which would mean a model differing a bit from RIS-Index ideas) ); -CREATE TABLE gemma_fairway.reference_water_levels ( +CREATE TABLE reference_water_levels ( reference_water_level varchar(20) PRIMARY KEY ); -CREATE TABLE gemma_fairway.gauges ( - location isrs PRIMARY KEY, - function_code varchar(10) NOT NULL, -- XXX: What is this really for? - objname varchar NOT NULL, - is_left boolean, -- XXX: Or reference position_codes? - geom geometry(POINT, 4326) NOT NULL, - applicability isrsrange, - validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy - -- pasted text from a more general specification is given - -- (a gauge is not a berth!) - -- TODO: Ranges need a joint exclusion constaint to prevent overlaps? - zero_point double precision NOT NULL, - geodref varchar(4) REFERENCES depth_references, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL -); -CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - -CREATE TABLE gemma_fairway.gauges_reference_water_levels ( - gauge_id isrs NOT NULL REFERENCES gauges, - reference_water_level varchar(20) - NOT NULL REFERENCES reference_water_levels, - PRIMARY KEY (gauge_id, reference_water_level), - value int NOT NULL -); - -CREATE TABLE gemma_fairway.gauge_measurements ( - fk_gauge_id isrs NOT NULL REFERENCES gauges, - measure_date timestamp with time zone NOT NULL, - PRIMARY KEY (fk_gauge_id, measure_date), - -- XXX: Is country_code really relevant for GEMMA or just NtS? - -- country_code char(2) NOT NULL REFERENCES countries, - -- TODO: add relations to stuff provided as enumerations - sender varchar NOT NULL, -- "from" attribute from DRC - language_code varchar NOT NULL REFERENCES language_codes, - date_issue timestamp with time zone NOT NULL, - -- reference_code varchar(4) NOT NULL REFERENCES depth_references, - -- XXX: Always ZPG? - water_level double precision NOT NULL, - predicted boolean NOT NULL, - is_waterlevel boolean NOT NULL, - -- XXX: "measure_code" if really only W or Q - -- XXX: Do we need "unit" attribute or can we normalise on import? - value_min double precision, -- XXX: NOT NULL if predicted? - value_max double precision, -- XXX: NOT NULL if predicted? - --- TODO: Add a double range type for checking? - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL -- "originator" -); -CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - -CREATE TABLE gemma_waterway.waterway_axis ( - wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY, - -- TODO: Do we need to check data set quality as described in DRC 2.1.6? - objnam varchar NOT NULL, - nobjnam varchar -); - -CREATE TABLE gemma_waterway.distance_mark_functions ( +CREATE TABLE distance_mark_functions ( -- XXX: Redundant information to object code in isrs code of dist. mark distance_mark_function varchar(8) PRIMARY KEY ); -CREATE TABLE gemma_waterway.position_codes ( +CREATE TABLE position_codes ( position_code char(2) PRIMARY KEY -- Use smallint because of fairway availability provided on daily basis? -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, @@ -214,197 +98,319 @@ -- TODO: Do we need an attribute "meaning" or so? ); --- This table allows linkage between the 1D ISRS location codes and 2D space --- e.g. for cutting bottleneck area out of waterway area based on virtual --- distance marks along waterway axis (see SUC7). -CREATE TABLE gemma_waterway.distance_marks ( - location_code isrs PRIMARY KEY, - geom geometry(POINT, 4326) NOT NULL, - distance_mark_function varchar(8) - NOT NULL REFERENCES distance_mark_functions, - -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem - -- to encode the same thing as the object code in ISRS location code. - position_code char(2) NOT NULL REFERENCES position_codes -); - -CREATE TABLE gemma_waterway.sections_stretches ( - id varchar PRIMARY KEY, - is_section boolean NOT NULL, -- maps 'function' from interface - stretch isrsrange, - objnam varchar NOT NULL, - nobjnam varchar, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL -); -CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - -CREATE TABLE gemma_waterway.waterway_profiles ( - location isrs NOT NULL, - validity tstzrange, - EXCLUDE USING GIST (validity WITH &&), - PRIMARY KEY (location, validity), - lnwl smallint, - mwl smallint, - hnwl smallint, - fe30 smallint, - fe100 smallint, - -- XXX: further normalise using reference_water_levels? - CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL - OR validity IS NOT NULL), - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL -); -CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - -CREATE TABLE gemma_waterway.levels_of_service ( +CREATE TABLE levels_of_service ( level_of_service smallint PRIMARY KEY ); -CREATE TABLE gemma_waterway.fairway_dimensions ( - area geometry(POLYGON, 4326) PRIMARY KEY, - level_of_service smallint NOT NULL REFERENCES levels_of_service, - min_width smallint NOT NULL, - max_width smallint NOT NULL, - min_depth smallint NOT NULL, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL -); -CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - --- --- Bottlenecks --- -CREATE TABLE gemma_fairway.riverbed_materials ( +CREATE TABLE riverbed_materials ( material varchar PRIMARY KEY -- XXX: Should this table contain choices from DRC 2.2.3 or -- from IENC Encoding Guide M.4.3, attribute NATSUR? ); -CREATE TABLE gemma_fairway.survey_types ( +CREATE TABLE survey_types ( survey_type varchar PRIMARY KEY ); -CREATE TABLE gemma_fairway.coverage_types ( +CREATE TABLE coverage_types ( coverage_type varchar PRIMARY KEY ); -CREATE TABLE gemma_fairway.limiting_factors ( +CREATE TABLE limiting_factors ( limiting_factor varchar PRIMARY KEY ); --- XXX: Nullability differs between DRC (attributes marked "O") and WSDL --- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and --- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) -CREATE TABLE gemma_fairway.bottlenecks ( - bottleneck_id varchar PRIMARY KEY, - fk_g_fid isrs NOT NULL REFERENCES gauges, - -- XXX: DRC references "ch. 3.1.1", which does not exist in document. - objnam varchar, - nobjnm varchar, - stretch isrsrange NOT NULL, - area geometry(POLYGON, 4326) NOT NULL, - rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface - lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface - responsible_country char(2) NOT NULL REFERENCES countries, - revisiting_time smallint NOT NULL, - limiting varchar NOT NULL REFERENCES limiting_factors, - -- surtyp varchar NOT NULL REFERENCES survey_types, - -- XXX: Also an attribut of sounding result? - -- coverage varchar REFERENCES coverage_types, - -- XXX: Also an attribut of sounding result? - -- CHECK allowed combinations of surtyp and coverage or - -- different model approach? - -- depth_reference char(3) NOT NULL REFERENCES depth_references, - -- XXX: Also an attribut of sounding result? - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL, - -- additional_data xml -- Currently not relevant for GEMMA - staging_done boolean NOT NULL DEFAULT false -); -CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); - -CREATE TABLE gemma_fairway.bottlenecks_riverbed_materials ( - bottleneck_id varchar REFERENCES bottlenecks, - riverbed varchar REFERENCES riverbed_materials, - -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 - PRIMARY KEY (bottleneck_id, riverbed) -); - -CREATE TABLE gemma_fairway.sounding_results ( - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, - date_info date NOT NULL, - PRIMARY KEY (bottleneck_id, date_info), - area geometry(POLYGON, 4326) NOT NULL, - surtyp varchar NOT NULL REFERENCES survey_types, - coverage varchar REFERENCES coverage_types, - depth_reference char(3) NOT NULL REFERENCES depth_references, - sounding_data raster NOT NULL, - staging_done boolean NOT NULL DEFAULT false -); - --- --- Fairway availability --- -CREATE TABLE gemma_fairway.measure_types ( +CREATE TABLE measure_types ( measure_type varchar PRIMARY KEY ); -CREATE TABLE gemma_fairway.fairway_availability ( - id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - position_code char(2) REFERENCES position_codes, - bottleneck_id varchar NOT NULL REFERENCES bottlenecks, - surdat date NOT NULL, - UNIQUE (bottleneck_id, surdat), - -- additional_data xml -- Currently not relevant for GEMMA - critical boolean, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar NOT NULL -); -CREATE TRIGGER fairway_availability_date_info - BEFORE UPDATE ON fairway_availability - FOR EACH ROW EXECUTE PROCEDURE update_date_info(); + +-- Namespace for user management related data +CREATE SCHEMA users + CREATE TABLE responsibility_areas ( + country char(2) PRIMARY KEY REFERENCES countries, + area geometry(MULTIPOLYGON, 4326) + --XXX: Should be geography (elsewhere too) + ) + + CREATE TABLE user_profiles ( + username varchar PRIMARY KEY,-- TODO: check it's in pg_roles by trigger + country char(2) NOT NULL REFERENCES countries, + map_extent box2d, + email_adress varchar NOT NULL UNIQUE + ) + + CREATE TABLE templates ( + template_name varchar PRIMARY KEY, + template_data bytea NOT NULL, + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP + ) + CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates + FOR EACH ROW EXECUTE PROCEDURE update_date_info() + + CREATE TABLE user_templates ( + username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE, + template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE, + PRIMARY KEY (username, template_name) + ) +; + +-- Namespace for waterway data that can change in a running system +CREATE SCHEMA waterway + + -- Eventually obsolete. + -- See https://roundup-intern.intevation.de/gemma/issue5 + -- CREATE TABLE rwdrs ( + -- tretch isrsrange PRIMARY KEY, + -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html: + -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index + -- -- below anyhow. + -- -- Is it a good idea to build B-tree indexes on relatively large + -- -- composites of string values or should we use inter PKs? + -- -- => In case the index is used and cache space becomes a limiting + -- -- factor, this might be an issue. + -- rwdr double precision NOT NULL, + -- EXCLUDE USING GIST (stretch WITH &&) + --) + + CREATE TABLE waterway_area ( + area geometry(POLYGON, 4326) PRIMARY KEY, + catccl smallint REFERENCES catccls, + dirimp smallint REFERENCES dirimps + ) -CREATE TABLE gemma_fairway.fa_reference_values ( - fairway_availability_id int NOT NULL REFERENCES fairway_availability, - level_of_service smallint NOT NULL REFERENCES levels_of_service, - PRIMARY KEY (fairway_availability_id, level_of_service), - fairway_depth smallint, - fairway_width smallint, - fairway_radius int, - CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), - shallowest_spot geometry(POINT, 4326) -); + CREATE TABLE gauges ( + location isrs PRIMARY KEY, + function_code varchar(10) NOT NULL, -- XXX: What is this really for? + objname varchar NOT NULL, + is_left boolean, -- XXX: Or reference position_codes? + geom geometry(POINT, 4326) NOT NULL, + applicability isrsrange, + validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy + -- pasted text from a more general specification is given + -- (a gauge is not a berth!) + -- TODO: Ranges need a joint exclusion constaint to prevent overlaps? + zero_point double precision NOT NULL, + geodref varchar(4) REFERENCES depth_references, + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL + ) + CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges + FOR EACH ROW EXECUTE PROCEDURE update_date_info() + + CREATE TABLE gauges_reference_water_levels ( + gauge_id isrs NOT NULL REFERENCES gauges, + reference_water_level varchar(20) + NOT NULL REFERENCES reference_water_levels, + PRIMARY KEY (gauge_id, reference_water_level), + value int NOT NULL + ) + + CREATE TABLE gauge_measurements ( + fk_gauge_id isrs NOT NULL REFERENCES gauges, + measure_date timestamp with time zone NOT NULL, + PRIMARY KEY (fk_gauge_id, measure_date), + -- XXX: Is country_code really relevant for GEMMA or just NtS? + -- country_code char(2) NOT NULL REFERENCES countries, + -- TODO: add relations to stuff provided as enumerations + sender varchar NOT NULL, -- "from" attribute from DRC + language_code varchar NOT NULL REFERENCES language_codes, + date_issue timestamp with time zone NOT NULL, + -- reference_code varchar(4) NOT NULL REFERENCES depth_references, + -- XXX: Always ZPG? + water_level double precision NOT NULL, + predicted boolean NOT NULL, + is_waterlevel boolean NOT NULL, + -- XXX: "measure_code" if really only W or Q + -- XXX: Do we need "unit" attribute or can we normalise on import? + value_min double precision, -- XXX: NOT NULL if predicted? + value_max double precision, -- XXX: NOT NULL if predicted? + --- TODO: Add a double range type for checking? + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL -- "originator" + ) + CREATE TRIGGER gauge_measurements_date_info + BEFORE UPDATE ON gauge_measurements + FOR EACH ROW EXECUTE PROCEDURE update_date_info() + + CREATE TABLE waterway_axis ( + wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY, + -- TODO: Do we need to check data set quality (DRC 2.1.6)? + objnam varchar NOT NULL, + nobjnam varchar + ) + + -- This table allows linkage between 1D ISRS location codes and 2D space + -- e.g. for cutting bottleneck area out of waterway area based on virtual + -- distance marks along waterway axis (see SUC7). + CREATE TABLE distance_marks ( + location_code isrs PRIMARY KEY, + geom geometry(POINT, 4326) NOT NULL, + distance_mark_function varchar(8) + NOT NULL REFERENCES distance_mark_functions, + -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem + -- to encode the same thing as the object code in ISRS location code. + position_code char(2) NOT NULL REFERENCES position_codes + ) + + CREATE TABLE sections_stretches ( + id varchar PRIMARY KEY, + is_section boolean NOT NULL, -- maps 'function' from interface + stretch isrsrange, + objnam varchar NOT NULL, + nobjnam varchar, + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL + ) + CREATE TRIGGER sections_stretches_date_info + BEFORE UPDATE ON sections_stretches + FOR EACH ROW EXECUTE PROCEDURE update_date_info() -CREATE TABLE gemma_fairway.bottleneck_pdfs ( - fairway_availability_id int NOT NULL REFERENCES fairway_availability, - profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL - profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? - PRIMARY KEY (fairway_availability_id, profile_pdf_url), - pdf_generation_date timestamp with time zone NOT NULL, - source_organization varchar NOT NULL -); + CREATE TABLE waterway_profiles ( + location isrs NOT NULL, + validity tstzrange, + EXCLUDE USING GIST (validity WITH &&), + PRIMARY KEY (location, validity), + lnwl smallint, + mwl smallint, + hnwl smallint, + fe30 smallint, + fe100 smallint, + -- XXX: further normalise using reference_water_levels? + CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL + OR validity IS NOT NULL), + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL + ) + CREATE TRIGGER waterway_profiles_date_info + BEFORE UPDATE ON waterway_profiles + FOR EACH ROW EXECUTE PROCEDURE update_date_info() + + CREATE TABLE fairway_dimensions ( + area geometry(POLYGON, 4326) PRIMARY KEY, + level_of_service smallint NOT NULL REFERENCES levels_of_service, + min_width smallint NOT NULL, + max_width smallint NOT NULL, + min_depth smallint NOT NULL, + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL + ) + CREATE TRIGGER fairway_dimensions_date_info + BEFORE UPDATE ON fairway_dimensions + FOR EACH ROW EXECUTE PROCEDURE update_date_info() + + -- + -- Bottlenecks + -- + -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL + -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and + -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) + CREATE TABLE bottlenecks ( + bottleneck_id varchar PRIMARY KEY, + fk_g_fid isrs NOT NULL REFERENCES gauges, + -- XXX: DRC references "ch. 3.1.1", which does not exist in document. + objnam varchar, + nobjnm varchar, + stretch isrsrange NOT NULL, + area geometry(POLYGON, 4326) NOT NULL, + rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface + lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface + responsible_country char(2) NOT NULL REFERENCES countries, + revisiting_time smallint NOT NULL, + limiting varchar NOT NULL REFERENCES limiting_factors, + -- surtyp varchar NOT NULL REFERENCES survey_types, + -- XXX: Also an attribut of sounding result? + -- coverage varchar REFERENCES coverage_types, + -- XXX: Also an attribut of sounding result? + -- CHECK allowed combinations of surtyp and coverage or + -- different model approach? + -- depth_reference char(3) NOT NULL REFERENCES depth_references, + -- XXX: Also an attribut of sounding result? + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL, + -- additional_data xml -- Currently not relevant for GEMMA + staging_done boolean NOT NULL DEFAULT false + ) + CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks + FOR EACH ROW EXECUTE PROCEDURE update_date_info() -CREATE TABLE gemma_fairway.effective_fairway_availability ( - fairway_availability_id int NOT NULL REFERENCES fairway_availability, - measure_date timestamp with time zone NOT NULL, - level_of_service smallint NOT NULL REFERENCES levels_of_service, - PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), - available_depth_value smallint, - available_width_value smallint, - water_level_value smallint, - CHECK(COALESCE(available_depth_value, available_width_value, - water_level_value) IS NOT NULL), - measure_type varchar NOT NULL REFERENCES measure_types, - source_organization varchar NOT NULL, - forecast_generation_time timestamp with time zone, - CHECK(measure_type <> 'forecasted' - OR forecast_generation_time IS NOT NULL), - value_lifetime timestamp with time zone, - CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) -); + CREATE TABLE bottlenecks_riverbed_materials ( + bottleneck_id varchar REFERENCES bottlenecks, + riverbed varchar REFERENCES riverbed_materials, + -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 + PRIMARY KEY (bottleneck_id, riverbed) + ) + + CREATE TABLE sounding_results ( + bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + date_info date NOT NULL, + PRIMARY KEY (bottleneck_id, date_info), + area geometry(POLYGON, 4326) NOT NULL, + surtyp varchar NOT NULL REFERENCES survey_types, + coverage varchar REFERENCES coverage_types, + depth_reference char(3) NOT NULL REFERENCES depth_references, + sounding_data raster NOT NULL, + staging_done boolean NOT NULL DEFAULT false + ) + + -- + -- Fairway availability + -- + CREATE TABLE fairway_availability ( + id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, + position_code char(2) REFERENCES position_codes, + bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + surdat date NOT NULL, + UNIQUE (bottleneck_id, surdat), + -- additional_data xml -- Currently not relevant for GEMMA + critical boolean, + date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL + ) + CREATE TRIGGER fairway_availability_date_info + BEFORE UPDATE ON fairway_availability + FOR EACH ROW EXECUTE PROCEDURE update_date_info() + + CREATE TABLE fa_reference_values ( + fairway_availability_id int NOT NULL REFERENCES fairway_availability, + level_of_service smallint NOT NULL REFERENCES levels_of_service, + PRIMARY KEY (fairway_availability_id, level_of_service), + fairway_depth smallint, + fairway_width smallint, + fairway_radius int, + CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) + IS NOT NULL), + shallowest_spot geometry(POINT, 4326) + ) + + CREATE TABLE bottleneck_pdfs ( + fairway_availability_id int NOT NULL REFERENCES fairway_availability, + profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL + profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? + PRIMARY KEY (fairway_availability_id, profile_pdf_url), + pdf_generation_date timestamp with time zone NOT NULL, + source_organization varchar NOT NULL + ) + + CREATE TABLE effective_fairway_availability ( + fairway_availability_id int NOT NULL REFERENCES fairway_availability, + measure_date timestamp with time zone NOT NULL, + level_of_service smallint NOT NULL REFERENCES levels_of_service, + PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), + available_depth_value smallint, + available_width_value smallint, + water_level_value smallint, + CHECK(COALESCE(available_depth_value, available_width_value, + water_level_value) IS NOT NULL), + measure_type varchar NOT NULL REFERENCES measure_types, + source_organization varchar NOT NULL, + forecast_generation_time timestamp with time zone, + CHECK(measure_type <> 'forecasted' + OR forecast_generation_time IS NOT NULL), + value_lifetime timestamp with time zone, + CHECK(measure_type = 'minimum guaranteed' + OR value_lifetime IS NOT NULL) + ) +; COMMIT;
--- a/schema/manage_users.sql Fri Jul 20 18:32:40 2018 +0200 +++ b/schema/manage_users.sql Fri Jul 20 17:28:16 2018 +0200 @@ -3,18 +3,18 @@ -- exposing it to privileged users -- -CREATE OR REPLACE FUNCTION create_user( +CREATE OR REPLACE FUNCTION sys_admin.create_user( userrole varchar, - username user_profiles.username%TYPE, + username users.user_profiles.username%TYPE, pw varchar, - country user_profiles.country%TYPE, - map_extent user_profiles.map_extent%TYPE, - email_adress user_profiles.email_adress%TYPE + country users.user_profiles.country%TYPE, + map_extent users.user_profiles.map_extent%TYPE, + email_adress users.user_profiles.email_adress%TYPE ) RETURNS void AS $$ BEGIN - INSERT INTO user_profiles VALUES ( + INSERT INTO users.user_profiles VALUES ( username, country, map_extent, email_adress); EXECUTE format( 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw);
--- a/schema/manage_users_tests.sql Fri Jul 20 18:32:40 2018 +0200 +++ b/schema/manage_users_tests.sql Fri Jul 20 17:28:16 2018 +0200 @@ -2,30 +2,36 @@ -- -- pgTAP test script for user management functions -- -SELECT plan(5); -- Give number of tests that have to be run +SELECT plan(6); -- Give number of tests that have to be run SET search_path TO public, gemma, gemma_waterway, gemma_fairway; --- --- Run tests as system_admin --- +SET SESSION AUTHORIZATION waterway_admin; + +SELECT throws_ok($$ + SELECT sys_admin.create_user( + 'waterway_user', 'test0', 'secret', 'AT', NULL, 'test0') + $$, + 42501, NULL, + 'Less privileged user cannot call function in schema sys_admin'); + SET SESSION AUTHORIZATION sys_admin; SELECT lives_ok($$ - SELECT create_user( + SELECT sys_admin.create_user( 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1') $$, 'New waterway user can be added'); SELECT throws_ok($$ - SELECT create_user( + SELECT sys_admin.create_user( 'invalid', 'test2', 'secret', 'AT', NULL, 'test2') $$, 42704, NULL, 'Valid role name has to be provided'); SELECT throws_ok($$ - SELECT create_user( + SELECT sys_admin.create_user( 'waterway_user', NULL, 'secret', 'AT', NULL, 'test3') $$, 23502, NULL, @@ -33,14 +39,14 @@ -- Though other arguments are mandatory, too, there are no explicit tests SELECT throws_ok($$ - SELECT create_user( + SELECT sys_admin.create_user( 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4') $$, 23505, NULL, 'No duplicate user name is allowed'); SELECT throws_ok($$ - SELECT create_user( + SELECT sys_admin.create_user( 'waterway_user', 'test2', 'secret', 'AT', NULL, 'xxx') $$, 23505, NULL,
--- a/schema/tap_tests_data.sql Fri Jul 20 18:32:40 2018 +0200 +++ b/schema/tap_tests_data.sql Fri Jul 20 17:28:16 2018 +0200 @@ -1,11 +1,13 @@ -SET search_path TO public, gemma, gemma_waterway, gemma_fairway; +-- +-- Test data used in *_tests.sql scripts +-- INSERT INTO countries VALUES ('AT'), ('RO'); -INSERT INTO responsibility_areas VALUES +INSERT INTO users.responsibility_areas VALUES ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)); -INSERT INTO user_profiles (username, country, email_adress) +INSERT INTO users.user_profiles (username, country, email_adress) VALUES ('waterway_user', 'AT', 'xxx'), ('waterway_user2', 'RO', 'xxy'), @@ -14,7 +16,7 @@ INSERT INTO limiting_factors VALUES ('depth'), ('width'); -INSERT INTO gauges ( +INSERT INTO waterway.gauges ( location, function_code, objname, geom, zero_point, source_organization) VALUES ( ('AT', 'XXX', '00001', '00000', 1)::isrs, @@ -25,7 +27,7 @@ 'testorganization' ); -INSERT INTO bottlenecks ( +INSERT INTO waterway.bottlenecks ( bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, source_organization, staging_done) VALUES ( @@ -46,7 +48,7 @@ 1, 'depth', 'testorganization', true ); -INSERT INTO templates (template_name, template_data) +INSERT INTO users.templates (template_name, template_data) VALUES ('AT', '\x'), ('RO', '\x'); -INSERT INTO user_templates +INSERT INTO users.user_templates VALUES ('waterway_user', 'AT'), ('waterway_user2', 'RO');