-- This is Free Software under GNU Affero General Public License v >= 3.0
-- without warranty, see README.md and license for details.

-- SPDX-License-Identifier: AGPL-3.0-or-later
-- License-Filename: LICENSES/AGPL-3.0.txt

-- Copyright (C) 2018 by via donau
--   – Österreichische Wasserstraßen-Gesellschaft mbH
-- Software engineering by Intevation GmbH

-- Author(s):
--  * Tom Gottried <tom@intevation.de>
--  * Sascha Wilde <sascha.wilde@intevation.de>
--  * Sascha L. Teichmann <sascha.teichmann@intevation.de>

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, systemconf TO waterway_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;
GRANT SELECT ON systemconf.feature_colours TO waterway_user;
GRANT UPDATE (pw, map_extent, email_address) ON users.list_users
    TO waterway_user;

--
-- Extended privileges for waterway_admin
--
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway
    TO waterway_admin;
-- TODO: will there ever be UPDATEs and DELETEs or can we drop that for
-- imported data due to historicisation? Special tables like
-- import_configuration will further need UPDATE and DELETE privileges.
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;
GRANT UPDATE ON systemconf.feature_colours TO sys_admin;
GRANT UPDATE ON sys_admin.published_services TO sys_admin;
GRANT INSERT, DELETE ON sys_admin.password_reset_requests TO sys_admin;

--
-- Privileges assigned directly to metamorph
--
-- Needed for GeoServer's system inspection run before session startup SQL
GRANT USAGE ON SCHEMA public TO metamorph;

--
-- 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
DO LANGUAGE plpgsql
$$
DECLARE the_table varchar;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'gauge_measurements',
        'sections_stretches',
        'waterway_profiles',
        'fairway_dimensions',
        'bottlenecks',
        'sounding_results']
    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;
$$;

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

CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS
$$
DECLARE
  center geometry;
BEGIN
  SELECT ST_Centroid(g) INTO center;
  RETURN
    CASE WHEN ST_Y(center) > 0 THEN
    32600
  ELSE
    32700
  END + floor((ST_X(center)+180)/6)::int + 1;
END;
$$
LANGUAGE plpgsql
IMMUTABLE;

CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS
$$
DECLARE
  user_area geometry;
  utm integer;
BEGIN
    SELECT area::geometry FROM users.responsibility_areas INTO user_area
    WHERE country = users.current_user_country();
    SELECT best_utm(user_area) INTO utm;
    RETURN ST_Covers(
      ST_Transform(user_area, utm),
      ST_Transform(g::geometry, utm));
END;
$$
LANGUAGE plpgsql
STABLE;

-- Staging area
-- TODO: add all relevant tables here

CREATE POLICY same_country ON waterway.gauge_measurements
    FOR ALL TO waterway_admin
    USING ((fk_gauge_id).country_code = users.current_user_country());

CREATE POLICY responsibility_area ON waterway.bottlenecks
    FOR ALL TO waterway_admin
    USING (utm_covers(area));

CREATE POLICY responsibility_area ON waterway.sounding_results
    FOR ALL TO waterway_admin
    USING (utm_covers(area));

-- Imports and import config

CREATE POLICY same_country ON waterway.imports
    FOR ALL TO waterway_admin
    USING (users.current_user_country() = (
        SELECT country FROM users.list_users lu
            WHERE lu.username = imports.username));
ALTER table waterway.imports ENABLE ROW LEVEL SECURITY;

-- The job running the import queue is running as sys_admin and login users
-- with that role should see all imports anyhow
CREATE POLICY read_all ON waterway.imports
    FOR SELECT TO sys_admin
    USING (true);
CREATE POLICY update_all ON waterway.imports
    FOR UPDATE TO sys_admin
    USING (true);

CREATE POLICY parent_allowed ON waterway.import_logs
    FOR ALL TO waterway_admin
    USING (import_id IN (SELECT id FROM waterway.imports));
ALTER table waterway.import_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY parent_allowed ON waterway.track_imports
    FOR ALL TO waterway_admin
    USING (import_id IN (SELECT id FROM waterway.imports));
ALTER table waterway.track_imports ENABLE ROW LEVEL SECURITY;

CREATE POLICY import_configuration_policy ON waterway.import_configuration
    FOR ALL TO waterway_admin
    USING (
        users.current_user_country() = (
            SELECT country FROM users.list_users lu
            WHERE lu.username = waterway.import_configuration.username));

CREATE POLICY import_configuration_policy_sys_admin ON waterway.import_configuration
    FOR ALL TO sys_admin
    USING (true);

ALTER table waterway.import_configuration ENABLE ROW LEVEL SECURITY;

COMMIT;