BEGIN;

--
-- Roles, privileges and policies for the GEMMA database
--

-- 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 users, waterway TO waterway_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;

--
-- Extended privileges for waterway_admin
--
GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA waterway TO waterway_admin;
-- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
GRANT INSERT, UPDATE, DELETE ON
    users.templates, users.user_templates TO waterway_admin;

--
-- Extended privileges for sys_admin
--
GRANT INSERT, UPDATE, DELETE
    ON users.responsibility_areas TO sys_admin;
GRANT USAGE ON SCHEMA sys_admin TO sys_admin;
GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin;
GRANT UPDATE ON sys_admin.system_config 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 waterway.%I '
            'FOR SELECT TO waterway_user USING (staging_done)', the_table);
        EXECUTE format('ALTER TABLE waterway.%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 users.user_profiles FOR SELECT TO waterway_user
    USING (username = current_user);
ALTER TABLE users.user_profiles ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user
    USING (username IN(SELECT username FROM users.user_profiles));
ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user
    USING (template_name IN(SELECT template_name FROM users.user_templates))
    WITH CHECK (true);
ALTER TABLE users.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 users.user_profiles.country%TYPE
    AS $$
        SELECT country FROM users.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 waterway.bottlenecks
    FOR ALL TO waterway_admin
    USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
        WHERE country = current_user_country())));
CREATE POLICY responsibility_area ON waterway.sounding_results
    FOR ALL TO waterway_admin
    USING (ST_Within(area, (SELECT area FROM users.responsibility_areas
        WHERE country = current_user_country())));

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

COMMIT;