-- 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, 2019 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, sys_admin, caching TO waterway_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user;
GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA caching TO waterway_user;
GRANT SELECT ON sys_admin.system_config 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;

GRANT INSERT, UPDATE, DELETE ON
    users.templates TO waterway_admin;

GRANT USAGE ON SCHEMA import TO waterway_admin;
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin;
GRANT UPDATE ON
    import.imports, import.import_configuration,
        import.import_configuration_attributes
    TO waterway_admin;
GRANT DELETE ON
    import.track_imports, import.import_configuration,
        import.import_configuration_attributes
    TO waterway_admin;

--
-- Extended privileges for sys_admin
--
GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA users 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 INSERT, UPDATE ON sys_admin.system_config TO sys_admin;
GRANT UPDATE ON sys_admin.published_services TO sys_admin;
GRANT INSERT, DELETE, UPDATE ON sys_admin.password_reset_requests TO sys_admin;
GRANT DELETE ON import.imports, import.import_logs TO sys_admin;
GRANT SELECT, INSERT, DELETE, UPDATE ON sys_admin.stats_updates 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 'EXISTS(SELECT 1 FROM list_users WHERE username = ...)'
-- instead of 'username = current_user', since 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 POLICY hide_staging ON users.stretches
    FOR SELECT TO waterway_user USING (staging_done);
ALTER TABLE users.stretches ENABLE ROW LEVEL SECURITY;

DO LANGUAGE plpgsql
$$
DECLARE the_table varchar;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'gauge_measurements',
        'waterway_profiles',
        'fairway_dimensions',
        'sections',
        '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('CREATE POLICY sys_admin ON waterway.%I '
            'FOR ALL TO sys_admin USING (true)', the_table);
        EXECUTE format('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY',
            the_table);
    END LOOP;
END;
$$;

-- Tables without staging area
CREATE POLICY hide_nothing ON waterway.waterway_axis
    FOR SELECT TO waterway_user USING (true);
CREATE POLICY sys_admin ON waterway.waterway_axis
    FOR ALL TO sys_admin USING (true);
ALTER TABLE waterway.waterway_axis ENABLE ROW LEVEL SECURITY;


--
-- RLS policies for templates
--
CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user
    USING (country IS NULL OR country = (SELECT country FROM users.list_users WHERE username = current_user));

CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_admin
    USING (country = (SELECT country FROM users.list_users WHERE username = current_user));

CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin
    USING (true);

ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY;

--
-- RLS policies for waterway_admin
--

-- Staging area

DO LANGUAGE plpgsql
$do$
DECLARE
    the_table varchar;
    condition CONSTANT text = $$
        (location).country_code =
            (SELECT country FROM users.list_users
                WHERE username = current_user)
        $$;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'gauge_measurements',
        'waterway_profiles']
    LOOP
        EXECUTE format($$
            CREATE POLICY same_country_insert ON waterway.%I
                FOR INSERT TO waterway_admin
                WITH CHECK (%s)
            $$, the_table, condition);
        -- In many cases it is more efficient to check for "staging_done" to
        -- prevent the more expensive checks for read only access (which is
        -- allowed for all users, when staging is done).
        EXECUTE format($$
            CREATE POLICY same_country_select ON waterway.%I
                FOR SELECT TO waterway_admin
                USING (staging_done OR %s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY same_country_update ON waterway.%I
                FOR UPDATE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY same_country_delete ON waterway.%I
                FOR DELETE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
    END LOOP;
END;
$do$;

DO LANGUAGE plpgsql
$do$
DECLARE
    the_table varchar;
    condition CONSTANT text = $$
        (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a)))
            FROM users.current_user_area_utm() AS a (a))
        $$;
BEGIN
    FOREACH the_table IN ARRAY ARRAY[
        'fairway_dimensions',
        'bottlenecks',
        'sounding_results']
    LOOP
        EXECUTE format($$
            CREATE POLICY responsibility_area_insert ON waterway.%I
                FOR INSERT TO waterway_admin
                WITH CHECK (%s)
            $$, the_table, condition);
        -- In many cases it is more efficient to check for "staging_done" to
        -- prevent the more expensive checks for read only access (which is
        -- allowed for all users, when staging is done).
        EXECUTE format($$
            CREATE POLICY responsibility_area_select ON waterway.%I
                FOR SELECT TO waterway_admin
                USING (staging_done OR %s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY responsibility_area_update ON waterway.%I
                FOR UPDATE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
        EXECUTE format($$
            CREATE POLICY responsibility_area_delete ON waterway.%I
                FOR DELETE TO waterway_admin
                USING (%s)
            $$, the_table, condition);
    END LOOP;
END;
$do$;

-- In the case of sections differentiating between read and write
-- access is not neccessary: the country code based access check is
-- quiet cheap in this case and there are only (relatively) few
-- sections in the system anyway.
CREATE POLICY same_country ON waterway.sections
    FOR ALL TO waterway_admin
    USING (country = (
        SELECT country FROM users.list_users WHERE username = current_user));

CREATE POLICY sys_admin ON users.stretches
    FOR ALL TO sys_admin
    USING (true);

--
-- Tables without staging area
--
-- Use three policies instead of one FOR ALL to avoid costly expressions
-- being added in SELECT queries.
CREATE POLICY responsibility_area_insert ON waterway.waterway_axis
    FOR INSERT TO waterway_admin
    WITH CHECK ((SELECT ST_Covers(a,
            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
        FROM users.current_user_area_utm() AS a (a)));
CREATE POLICY responsibility_area_update ON waterway.waterway_axis
    FOR UPDATE TO waterway_admin
    USING ((SELECT ST_Covers(a,
            ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a)))
        FROM users.current_user_area_utm() AS a (a)));

--
-- RLS policies for imports and import config
--

CREATE POLICY same_country ON import.imports
    FOR ALL TO waterway_admin
    -- Relies on a user seeing only users from his own country:
    USING (EXISTS(SELECT 1 FROM users.list_users lu
            WHERE lu.username = imports.username));
ALTER table import.imports ENABLE ROW LEVEL SECURITY;

-- The job running the import queue is running as sys_admin and login users
-- with that role should be able to run imports without restrictions anyhow
CREATE POLICY import_all ON import.imports
    FOR ALL TO sys_admin
    USING (true);

-- For the given table, check whether the given value is used as primary key,
-- bypassing row level security.
CREATE OR REPLACE FUNCTION import.is_new_key(
        tablename varchar,
        kv anyelement)
    RETURNS boolean
AS $$
DECLARE columnname varchar;
DECLARE ret boolean;
BEGIN
    columnname = (SELECT column_name
        FROM information_schema.constraint_column_usage k
        JOIN information_schema.table_constraints USING (constraint_name)
        WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY');
    EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)',
            tablename, columnname)
        INTO ret
        USING kv;
    RETURN ret;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER
    STABLE PARALLEL SAFE;

CREATE POLICY parent_allowed ON import.import_logs
    FOR ALL TO waterway_admin
    USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
    WITH CHECK (import.is_new_key('imports', import_id)
        OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
ALTER table import.import_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY parent_allowed ON import.track_imports
    FOR ALL TO waterway_admin
    USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id))
    WITH CHECK (import.is_new_key('imports', import_id)
        OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id));
ALTER table import.track_imports ENABLE ROW LEVEL SECURITY;

CREATE POLICY import_configuration_policy ON import.import_configuration
    FOR ALL TO waterway_admin
    -- Relies on a user seeing only users from his own country:
    USING (EXISTS(SELECT 1 FROM users.list_users lu
            WHERE lu.username = import_configuration.username));

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

ALTER table import.import_configuration ENABLE ROW LEVEL SECURITY;

CREATE POLICY parent_allowed ON import.import_configuration_attributes
    FOR ALL TO waterway_admin
    USING (EXISTS(SELECT 1 FROM import.import_configuration
            WHERE id = import_configuration_id))
    WITH CHECK (
        import.is_new_key('import_configuration', import_configuration_id)
        OR EXISTS(SELECT 1 FROM import.import_configuration
            WHERE id = import_configuration_id));
ALTER table import.import_configuration_attributes ENABLE ROW LEVEL SECURITY;

COMMIT;