Mercurial > gemma
diff schema/auth.sql @ 4158:5466562cca60
Remove utility function with possibly bad performance impact
Since the PostgreSQL planner will call functions used in a filter
condition once per row, even if the function is marked STABLE,
under some circumstances (e.g. the removed usage in an RLS policy),
remove the function from the database completely.
The DEFAULT on users.templates that used the function is unused,
thus remove it as a whole, too.
Recreate the function as a helper for tests in order to minimize
necessary changes there.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 02 Aug 2019 16:10:42 +0200 |
parents | 4f9a1ff2c2ee |
children | 64cd18281c76 |
line wrap: on
line diff
--- a/schema/auth.sql Fri Aug 02 13:37:40 2019 +0200 +++ b/schema/auth.sql Fri Aug 02 16:10:42 2019 +0200 @@ -41,9 +41,6 @@ GRANT INSERT, UPDATE, DELETE ON users.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; @@ -157,8 +154,8 @@ CREATE POLICY same_country ON import.imports FOR ALL TO waterway_admin - USING (users.current_user_country() = ( - SELECT country FROM users.list_users lu + -- Relies on a user seeing only users from his own country: + USING (EXISTS(SELECT 1 FROM users.list_users lu WHERE lu.username = imports.username)); ALTER table import.imports ENABLE ROW LEVEL SECURITY;