BEGIN;

--
-- 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;

--
-- 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;

--
-- Extended privileges for waterway_admin
--
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA gemma_waterway, gemma_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;
GRANT SELECT ON responsibility_areas TO waterway_admin;

--
-- Extended privileges for sys_admin
--
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gemma
      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 (template_name IN(SELECT template_name FROM user_templates
                    WHERE username = current_user));
ALTER TABLE templates ENABLE ROW LEVEL SECURITY;

--
-- RLS policies for waterway_admin
--

-- 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 $$
       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
           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
           WHERE country = current_user_country())));

CREATE POLICY country_profiles ON user_profiles FOR SELECT TO waterway_admin
       USING (country = current_user_country());

CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin
       USING (template_name IN(SELECT template_name FROM user_templates ut
                    JOIN user_profiles p ON ut.username = p.username
                    WHERE p.country = current_user_country()));

COMMIT;