get_applied_network_rule(p_host_addr inet, p_instance_id uuid DEFAULT NULL::uuid, p_instance_owner_id uuid DEFAULT NULL::uuid)

-- File:        get_applied_network_rule.eex.sql

-- Location:    musebms/database/components/system/mscmp_syst_authn/ms_syst_priv/functions/get_applied_network_rule.eex.sql

-- Project:     Muse Systems Business Management System

--

-- Copyright © Lima Buttgereit Holdings LLC d/b/a Muse Systems

-- This file may include content copyrighted and licensed from third parties.

--

-- See the LICENSE file in the project root for license terms and conditions.

-- See the NOTICE file in the project root for copyright ownership information.

--

-- muse.information@musesystems.com :: https://muse.systems

​

    SELECT

        rule.precedence

      , rule.network_rule_id

      , rule.functional_type

      , rule.ip_host_or_network

      , rule.ip_host_range_lower

      , rule.ip_host_range_upper

    FROM ( SELECT

               1            AS precedence_sort

             , 'disallowed' AS precedence

             , 1            AS ordering

             , id           AS network_rule_id

             , 'deny'       AS functional_type

             , host_address AS ip_host_or_network

             , NULL         AS ip_host_range_lower

             , NULL         AS ip_host_range_upper

           FROM ms_syst_data.syst_disallowed_hosts

           WHERE host_address = p_host_addr

           UNION

           SELECT

               2        AS precedence_sort

             , 'global' AS precedence

             , ordering

             , id       AS network_rule_id

             , functional_type

             , ip_host_or_network

             , ip_host_range_lower

             , ip_host_range_upper

           FROM ms_syst_data.syst_global_network_rules

           WHERE

               ( ip_host_or_network >>= p_host_addr OR

                 p_host_addr BETWEEN ip_host_range_lower AND ip_host_range_upper )

           UNION

           SELECT

               3          AS precedence_sort

             , 'instance' AS precedence

             , ordering

             , id         AS network_rule_id

             , functional_type

             , ip_host_or_network

             , ip_host_range_lower

             , ip_host_range_upper

           FROM ms_syst_data.syst_instance_network_rules

           WHERE

                 instance_id = p_instance_id

             AND ( ip_host_or_network >>= p_host_addr OR

                   p_host_addr BETWEEN ip_host_range_lower AND ip_host_range_upper )

           UNION

           SELECT

               4                AS precedence_sort

             , 'instance_owner' AS precedence

             , ordering

             , id               AS network_rule_id

             , functional_type

             , ip_host_or_network

             , ip_host_range_lower

             , ip_host_range_upper

           FROM ms_syst_data.syst_owner_network_rules

           WHERE

                   owner_id = coalesce( p_instance_owner_id, ( SELECT owner_id

                                                               FROM ms_syst_data.syst_instances

                                                               WHERE id = p_instance_id ) )

             AND   ( ip_host_or_network >>= p_host_addr OR

                     p_host_addr BETWEEN ip_host_range_lower AND ip_host_range_upper )

           UNION

           SELECT

               5                 AS precedence_sort

             , 'implied'         AS precedence

             , 1                 AS ordering

             , NULL              AS network_rule_id

             , 'allow'           AS functional_type

             , '0.0.0.0/0'::inet AS ip_host_or_network

             , NULL              AS ip_host_range_lower

             , NULL              AS ip_host_range_upper

           ORDER BY precedence_sort, ordering

           LIMIT 1 ) rule;