--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/auth.sql	Wed Jun 06 17:08:09 2018 +0200
@@ -0,0 +1,94 @@
+BEGIN;
+
+--
+-- Roles, privileges and policies for the WAMOS database
+--
+SET search_path TO public, wamos, wamos_waterway, wamos_fairway;
+
+-- We do not want any users to be able to create any objects
+REVOKE CREATE ON SCHEMA public FROM PUBLIC;
+
+--
+-- Primary WAMOS roles (SRS table 3)
+--
+CREATE ROLE waterway_user;
+CREATE ROLE waterway_admin IN ROLE waterway_user;
+CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin;
+
+--
+-- Privileges for waterway_user
+--
+GRANT USAGE ON SCHEMA wamos_waterway, wamos_fairway TO waterway_user;
+GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
+      TO waterway_user;
+GRANT SELECT ON templates, user_profiles TO waterway_user;
+
+--
+-- Extended privileges for waterway_admin
+--
+GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
+      TO waterway_admin;
+-- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
+GRANT INSERT, UPDATE ON templates, user_templates TO waterway_admin;
+
+--
+-- Extended privileges for sys_admin
+--
+GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wamos
+      TO sys_admin;
+
+--
+-- RLS policies for waterway_user
+--
+
+-- Staging area
+CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$
+DECLARE the_table varchar;
+BEGIN
+    FOREACH the_table IN ARRAY ARRAY[
+        'bottlenecks', 'sounding_results']
+    -- TODO: add all relevant tables here
+    LOOP
+        EXECUTE format('CREATE POLICY hide_staging ON %I '
+            'FOR SELECT TO waterway_user USING (staging_done)', the_table);
+        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table);
+    END LOOP;
+END;
+$$ LANGUAGE plpgsql;
+SELECT create_hide_staging_policy();
+DROP FUNCTION create_hide_staging_policy;
+
+CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user
+       USING (username = current_user);
+ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
+
+CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user
+       USING (id IN(SELECT t.id FROM templates t
+                    JOIN user_templates ut ON t.id = ut.template_id
+                    JOIN user_profiles p ON ut.username = p.username
+                    WHERE p.username = current_user));
+ALTER TABLE templates ENABLE ROW LEVEL SECURITY;
+
+--
+-- RLS policies for waterway_admin
+--
+
+-- 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
+           WHERE country = (SELECT country FROM user_profiles
+               WHERE username = current_user))));
+CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin
+       USING (ST_Within(area, (SELECT area FROM responsibility_areas
+           WHERE country = (SELECT country FROM user_profiles
+               WHERE username = current_user))));
+
+CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
+       USING (id IN(SELECT t.id FROM templates t
+                    JOIN user_templates ut ON t.id = ut.template_id
+                    JOIN user_profiles p ON ut.username = p.username
+                    WHERE p.country = (SELECT country FROM user_profiles
+                        WHERE username = current_user)));
+
+COMMIT;