--
-- Functions encapsulating user management functionality and
-- exposing it to appropriately privileged users
--

CREATE OR REPLACE FUNCTION internal.check_password(
    pw varchar
    )
    RETURNS varchar
AS $$
DECLARE
    min_len CONSTANT int = 8;
BEGIN
    IF char_length(pw) < min_len
        OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
        OR pw NOT SIMILAR TO '%[[:digit:]]%'
    THEN
        RAISE invalid_password USING MESSAGE = 'Invalid password';
    ELSE
        RETURN pw;
    END IF;
END;
$$
    LANGUAGE plpgsql;


-- Security-definer function to get current users country, which allows to
-- restrict the view on user_profiles by country without infinite recursion
CREATE FUNCTION users.current_user_country()
    RETURNS internal.user_profiles.country%TYPE
    AS $$
        SELECT country FROM internal.user_profiles
            WHERE username = session_user
    $$
    LANGUAGE SQL
    SECURITY DEFINER
    STABLE PARALLEL SAFE;


CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
    SELECT
            r.rolname,
            p.username,
            CAST('' AS varchar) AS pw,
            p.country,
            p.map_extent,
            p.email_address
        FROM internal.user_profiles p
            JOIN pg_roles u ON p.username = u.rolname
            JOIN pg_auth_members a ON u.oid = a.member
            JOIN pg_roles r ON a.roleid = r.oid
        WHERE p.username = current_user
            OR pg_has_role('waterway_admin', 'MEMBER')
                AND p.country = users.current_user_country()
            OR pg_has_role('pw_reset', 'MEMBER')
            OR pg_has_role('sys_admin', 'MEMBER');


CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
AS $$
BEGIN
    IF NEW.map_extent IS NULL
    THEN
        NEW.map_extent = ST_Extent(CAST(area AS geometry))
            FROM users.responsibility_areas ra
            WHERE ra.country = NEW.country;
    END IF;
    INSERT INTO internal.user_profiles (
        username, country, map_extent, email_address)
        VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
    EXECUTE format(
        'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
        NEW.username,
        NEW.rolname,
        internal.check_password(NEW.pw));

    -- Do not leak new password
    NEW.pw = '';
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW
    EXECUTE PROCEDURE internal.create_user();


CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
AS $$
DECLARE
    cur_username varchar;
BEGIN
    cur_username = OLD.username;

    IF cur_username <> session_user
        AND NOT (pg_has_role(session_user, 'sys_admin', 'MEMBER')
            OR pg_has_role(session_user, 'pw_reset', 'MEMBER'))
    THEN
        -- Discard row. This is what WITH CHECK in an RLS policy would do.
        RETURN NULL;
    END IF;

    UPDATE internal.user_profiles p
        SET (username, country, map_extent, email_address)
        = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
        WHERE p.username = cur_username;

    IF NEW.username <> cur_username
    THEN
        EXECUTE format(
            'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
        cur_username = NEW.username;
    END IF;

    IF NEW.rolname <> OLD.rolname
    THEN
        EXECUTE format(
            'REVOKE %I FROM %I', OLD.rolname, cur_username);
        EXECUTE format(
            'GRANT %I TO %I', NEW.rolname, cur_username);
    END IF;

    IF NEW.pw IS NOT NULL AND NEW.pw <> ''
    THEN
        EXECUTE format(
            'ALTER ROLE %I PASSWORD %L',
            cur_username,
            internal.check_password(NEW.pw));
    END IF;

    -- Do not leak new password
    NEW.pw = '';
    RETURN NEW;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

CREATE TRIGGER update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW
    EXECUTE PROCEDURE internal.update_user();


CREATE OR REPLACE FUNCTION internal.delete_user() RETURNS trigger
AS $$
DECLARE
    bid int;
BEGIN
    -- Terminate the users backends started before the current transaction
    FOR bid IN SELECT pid FROM pg_stat_activity WHERE usename = OLD.username
    LOOP
        PERFORM pg_terminate_backend(bid);
    END LOOP;
    -- Note that any backend that might be started during the transaction
    -- in which this function is executed will not be terminated but lost
    -- without any privileges after commiting this transaction

    -- Delete user
    EXECUTE format('DROP ROLE %I', OLD.username);
    DELETE FROM internal.user_profiles p
        WHERE p.username = OLD.username;

    RETURN OLD;
END;
$$
    LANGUAGE plpgsql
    SECURITY DEFINER;

CREATE TRIGGER delete_user INSTEAD OF DELETE ON users.list_users FOR EACH ROW
    EXECUTE PROCEDURE internal.delete_user();


CREATE OR REPLACE VIEW pw_reset.list_users AS
    SELECT username, pw, email_address FROM users.list_users;