-- 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 Gottfried <tom@intevation.de>

CREATE PROCEDURE create_roles()
AS $$
DECLARE
    -- Role names
    wwuser CONSTANT varchar = 'waterway_user';
    wwadmin CONSTANT varchar = 'waterway_admin';
    sysadmin CONSTANT varchar = 'sys_admin';
    metarole CONSTANT varchar = 'metamorph';

    -- Messages
    warn_message CONSTANT varchar = 'Role %I already exists';
    warn_detail CONSTANT varchar =
        'Role attributes and memberships are kept as is';
    err_message CONSTANT varchar =
        'Role %I already exists but lacks necessary privileges';
BEGIN
    --
    -- Primary GEMMA roles
    --
    IF to_regrole(wwuser) IS NULL THEN
        EXECUTE format('CREATE ROLE %I', wwuser);
    ELSE
        RAISE WARNING USING
            MESSAGE = format(warn_message, wwuser),
            DETAIL = warn_detail;
    END IF;

    IF to_regrole(wwadmin) IS NULL THEN
        EXECUTE format('CREATE ROLE %I IN ROLE %I', wwadmin, wwuser);
    ELSE
        IF pg_has_role(wwadmin, wwuser, 'USAGE') THEN
            RAISE WARNING USING
                MESSAGE = format(warn_message, wwadmin),
                DETAIL = warn_detail;
        ELSE
            RAISE USING MESSAGE = format(err_message, wwadmin);
        END IF;
    END IF;

    IF to_regrole(sysadmin) IS NULL THEN
        EXECUTE format('CREATE ROLE %I IN ROLE %I', sysadmin, wwadmin);
    ELSE
        IF pg_has_role(sysadmin, wwadmin, 'USAGE') THEN
            RAISE WARNING USING
                MESSAGE = format(warn_message, sysadmin),
                DETAIL = warn_detail;
        ELSE
            RAISE USING MESSAGE = format(err_message, sysadmin);
        END IF;
    END IF;

    --
    -- Special roles
    --

    -- A role that is intended to be used for backend- or
    -- GeoServer-connections on which SET ROLE has to be used to
    -- gain privileges of a specific role
    IF to_regrole(metarole) IS NULL THEN
        EXECUTE format('CREATE ROLE %I NOINHERIT', metarole);
    ELSE
        IF (SELECT NOT rolinherit FROM pg_roles WHERE rolname = metarole) THEN
            RAISE WARNING USING
                MESSAGE = format(warn_message, metarole),
                DETAIL = warn_detail;
        ELSE
            RAISE USING MESSAGE = format(err_message, metarole);
        END IF;
    END IF;

END;
$$ LANGUAGE plpgsql;
CALL create_roles();
DROP PROCEDURE create_roles();