Mercurial > gemma

comparison schema/auth.sql @ 2230:4374d942b23d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 b66cfcde8ff7
children f8f2c4d88939
comparison
equal deleted inserted replaced
2229:6cce66a6ceb5 2230:4374d942b23d
37 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway 37 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
38 TO waterway_admin; 38 TO waterway_admin;
39 -- TODO: will there ever be UPDATEs and DELETEs or can we drop that for 39 -- TODO: will there ever be UPDATEs and DELETEs or can we drop that for
40 -- imported data due to historicisation? Special tables like 40 -- imported data due to historicisation? Special tables like
41 -- import_configuration will further need UPDATE and DELETE privileges. 41 -- import_configuration will further need UPDATE and DELETE privileges.
42
42 GRANT INSERT, UPDATE, DELETE ON 43 GRANT INSERT, UPDATE, DELETE ON
43 users.templates TO waterway_admin; 44 users.templates TO waterway_admin;
44 -- users.templates, users.user_templates TO waterway_admin; 45 -- Ensure templates are associated to the users country, if none is given
46 ALTER TABLE users.templates ALTER COLUMN country
47 SET DEFAULT users.current_user_country();
45 48
46 GRANT USAGE ON SCHEMA import TO waterway_admin; 49 GRANT USAGE ON SCHEMA import TO waterway_admin;
47 GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin; 50 GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin;
48 GRANT UPDATE ON 51 GRANT UPDATE ON
49 import.imports, import.import_configuration, 52 import.imports, import.import_configuration,
102 the_table); 105 the_table);
103 END LOOP; 106 END LOOP;
104 END; 107 END;
105 $$; 108 $$;
106 109
107 -- templates 110 --
108 -- CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user 111 -- RLS policies for templates
109 -- USING (username IN(SELECT username FROM users.list_users)); 112 --
110 -- ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY; 113 CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user
111 114 USING (country = users.current_user_country());
112 -- CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user 115
113 -- USING (template_name IN(SELECT template_name FROM users.user_templates)) 116 CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin
114 -- WITH CHECK (true); 117 USING (true);
115 -- ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; 118
119 ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;
116 120
117 -- 121 --
118 -- RLS policies for waterway_admin 122 -- RLS policies for waterway_admin
119 -- 123 --
120 124