BEGIN;

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

-- We do not want any users to be able to create any objects
REVOKE ALL ON SCHEMA public FROM PUBLIC;

--
-- Privileges for waterway_user
--
GRANT USAGE ON SCHEMA public, 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.list_users, 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;


--
-- Special privileges for pw_reset
--
GRANT USAGE ON SCHEMA pw_reset TO pw_reset;
GRANT SELECT (username, email_address) ON pw_reset.list_users TO pw_reset;
GRANT UPDATE (pw) ON pw_reset.list_users TO pw_reset;


--
-- 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 users.list_users)' instead
-- of 'username = current_user', because waterway_admin is intentionally
-- allowed more with these policies (note that the subselect implies different
-- filtering on list_users 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 user_templates ON users.user_templates FOR ALL TO waterway_user
    USING (username IN(SELECT username FROM users.list_users));
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
--

-- 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 = users.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 = users.current_user_country())));

COMMIT;