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, DELETE 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
--
-- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing
-- data modifications generally.
-- Sometimes using 'username IN(SELECT username FROM user_profiles)' instead
-- of 'username = current_user', because waterway_admin is intentionally
-- allowed more with these policies (note that the subselect implies different
-- policies on user_profiles depending on current_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 user_templates ON user_templates FOR ALL TO waterway_user
    USING (username IN(SELECT username FROM user_profiles));
ALTER TABLE user_templates ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_templates ON templates FOR ALL TO waterway_user
    USING (template_name IN(SELECT template_name FROM user_templates))
    WITH CHECK (true);
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());

COMMIT;