Mercurial > gemma
changeset 2230:4374d942b23d
Remove debris and associate templates to country
While the original idea was to associate templates to users, managed by
a waterway admin per country, the new intention was to share templates
between users per country. Just removing any constraints on readability
and management of the templates did not fulfill this idea.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Wed, 13 Feb 2019 10:52:14 +0100 |
| parents | 6cce66a6ceb5 |
| children | 97bba8b51b9c |
| files | schema/auth.sql schema/auth_tests.sql schema/gemma.sql schema/run_tests.sh schema/tap_tests_data.sql |
| diffstat | 5 files changed, 41 insertions(+), 51 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/auth.sql Wed Feb 13 08:00:26 2019 +0100 +++ b/schema/auth.sql Wed Feb 13 10:52:14 2019 +0100 @@ -39,9 +39,12 @@ -- TODO: will there ever be UPDATEs and DELETEs or can we drop that for -- imported data due to historicisation? Special tables like -- import_configuration will further need UPDATE and DELETE privileges. + GRANT INSERT, UPDATE, DELETE ON users.templates TO waterway_admin; - -- users.templates, users.user_templates TO waterway_admin; +-- Ensure templates are associated to the users country, if none is given +ALTER TABLE users.templates ALTER COLUMN country + SET DEFAULT users.current_user_country(); GRANT USAGE ON SCHEMA import TO waterway_admin; GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin; @@ -104,15 +107,16 @@ END; $$; --- templates --- CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user --- USING (username IN(SELECT username FROM users.list_users)); --- ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY; +-- +-- RLS policies for templates +-- +CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user + USING (country = users.current_user_country()); --- 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 users.templates ENABLE ROW LEVEL SECURITY; +CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin + USING (true); + +ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; -- -- RLS policies for waterway_admin
--- a/schema/auth_tests.sql Wed Feb 13 08:00:26 2019 +0100 +++ b/schema/auth_tests.sql Wed Feb 13 10:52:14 2019 +0100 @@ -36,16 +36,15 @@ $$, 'Only staged data should be visible'); ---SELECT isnt_empty($$ --- SELECT * FROM users.templates --- $$, --- 'User should see templates associated to him'); ---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'); +SELECT isnt_empty($$ + SELECT * FROM users.templates + $$, + 'User should see templates associated to his country'); + +SELECT ok( + users.current_user_country() = ALL( + SELECT country FROM users.templates), + 'User should only see templates associated to his country'); -- -- Run tests as waterway administrator @@ -79,26 +78,22 @@ 'Waterway admin cannot insert data outside his region'); -- template management ---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 results_eq($$ + SELECT users.current_user_country() + $$, + $$ + INSERT INTO users.templates (template_name, template_data) + VALUES ('New AT', '\x') + RETURNING country + $$, + 'Waterway admin can add templates for his country'); ---SELECT lives_ok($$ --- INSERT INTO users.templates (template_name, template_data) --- VALUES ('New AT', '\x'); --- INSERT INTO users.user_templates --- VALUES ('test_user_at', 'New AT') --- $$, --- 'Waterway admin can add templates for users in his country'); - ---SELECT throws_ok($$ --- INSERT INTO users.user_templates VALUES ('waterway_user2', 'AT') --- $$, --- 42501, NULL, --- 'Waterway admin cannot add template for other country'); +SELECT throws_ok($$ + INSERT INTO users.templates (template_name, template_data, country) + VALUES ('New RO', '\x', 'RO') + $$, + 42501, NULL, + 'Waterway admin cannot add template for other country'); SELECT isnt_empty($$ UPDATE users.templates SET template_data = '\xDABE'
--- a/schema/gemma.sql Wed Feb 13 08:00:26 2019 +0100 +++ b/schema/gemma.sql Wed Feb 13 10:52:14 2019 +0100 @@ -183,19 +183,12 @@ CREATE TABLE templates ( template_name varchar PRIMARY KEY, + country char(2) NOT NULL REFERENCES countries, 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 internal.user_profiles - -- ON DELETE CASCADE ON UPDATE CASCADE, - -- template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE, - -- PRIMARY KEY (username, template_name) - --) ; ALTER TABLE internal.user_profiles ADD country char(2) NOT NULL REFERENCES users.responsibility_areas;
--- a/schema/run_tests.sh Wed Feb 13 08:00:26 2019 +0100 +++ b/schema/run_tests.sh Wed Feb 13 10:52:14 2019 +0100 @@ -28,7 +28,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f tap_tests_data.sql \ - -c 'SELECT plan(57)' \ + -c 'SELECT plan(56)' \ -f isrs_tests.sql \ -f auth_tests.sql \ -f manage_users_tests.sql \
--- a/schema/tap_tests_data.sql Wed Feb 13 08:00:26 2019 +0100 +++ b/schema/tap_tests_data.sql Wed Feb 13 10:52:14 2019 +0100 @@ -88,10 +88,8 @@ 'testriver' ); -INSERT INTO users.templates (template_name, template_data) - VALUES ('AT', '\x'), ('RO', '\x'); ---INSERT INTO users.user_templates --- VALUES ('test_user_at', 'AT'), ('test_user_ro', 'RO'); +INSERT INTO users.templates (template_name, country, template_data) + VALUES ('AT', 'AT', '\x'), ('RO', 'RO', '\x'); WITH job AS (