Биллинговая система Nodeny

http://app.nodeny-plus.com.ua/docs/work/dhcp.html - Модуль dchp + Radius

rad_recv: Access-Request packet from host 192.168.12.1 port 63238, id=51, length=284
	User-Name = "985a.ebc8.c8a4"
	User-Password = "hardpass5"
	Service-Type = Framed-User
	Chargeable-User-Identity = ""
	Acct-Session-Id = "110"
	ERX-Dhcp-Options = 0x350101370a017903060f77fc5f2c2e390205dc3d0701985aebc8c8a433040076a7000c0d4d6163426f6f6b2d50726f2d32521201060004000c010302080006001da141aa00
	ERX-Dhcp-Gi-Address = 10.0.101.1
	ERX-Dhcp-Mac-Addr = "985a.ebc8.c8a4"
	NAS-Identifier = "border"
	NAS-Port = 281018380
	NAS-Port-Id = "ge-1/1/3.1073741878:12"
	NAS-Port-Type = Ethernet
	ERX-Pppoe-Description = "pppoe 98:5a:eb:c8:c8:a4"
	ADSL-Agent-Circuit-Id = 0x0004000c0103
	ADSL-Agent-Remote-Id = 0x0006001da141aa00
	NAS-IP-Address = 192.168.12.1
# Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny

	ERX-Dhcp-Mac-Addr = "985a.ebc8.c8a4"
ADSL-Agent-Circuit-Id = 0x0004000c0103
	ADSL-Agent-Remote-Id = 0x0006001da141aa00

User-Name = "985a.ebc8.c8a4", ERX-Service-Activate:1='что-то там'

Sending delayed reject for request 1
Sending Access-Reject of id 89 to 10.100.100.1 port 58364
	Service-Type = Framed-User
	Framed-IP-Address = 10.100.100.5
	Acct-Interim-Interval = 600
Waking up in 4.9 seconds.

Oct 24 09:53:02.157585 Verify source address b074c003 (10.100.100.1) in routing instance index=0
Oct 24 09:53:02.157891 REQUEST: AUTHEN - module_index 0 module(radius) return: ASYNC
Oct 24 09:53:02.157950 Auth-FSM: GRES-Mirror for session-id:911 state:AuthStart(1)
Oct 24 09:53:03.070888 serviceRadiusRequestQueues Serviced 1 RADIUS requests
Oct 24 09:53:03.070963 serviceRadiusRequestQueues Queue local has 0 requests, peak is 0
Oct 24 09:53:03.071001 serviceRadiusRequestQueues Queue sbr has 0 requests, peak is 0
Oct 24 09:53:03.159551 authd_radius_get_config:Using radius option config from access profile stanza
Oct 24 09:53:03.159649 loadDefaultService:: default service for the subscriber is empty
Oct 24 09:53:03.159688 Radius result is CLIENT_REQ_STATUS_SUCCESS
Oct 24 09:53:03.159736 Parsing RADIUS message for session-id:911
Oct 24 09:53:03.159797 radius-access-reject: Framed-IP-Address received: 10.100.100.5
Oct 24 09:53:03.159856 radius-access-reject: Session-Timeout received: 600
Oct 24 09:53:03.159909 Framework - module(radius) return: FAILURE
Oct 24 09:53:03.159945 authd_advance_module_for_aaa_response_msg: result:3
Oct 24 09:53:03.159999 ../../../../../src/junos/usr.sbin/authd/aaa-service/authd_aaa_astable.cc:1650 Client-session response-attr:: type:21 len:4
Oct 24 09:53:03.160118 Auth-FSM: reinterpretFsmEvent 4 to 5
Oct 24 09:53:03.160163 AuthFsm::current state=AuthStart(1) event=5 astEntry=0x20d906c aaa msg=0x1f7006c
Oct 24 09:53:03.160198 Auth-FSM: Post the Auth-Response and clean up. session-id:911
Oct 24 09:53:03.160233 Framework: auth result is 2. Performing post-auth operations
Oct 24 09:53:03.160265 Framework: result is 2.
Oct 24 09:53:03.160301 authd_auth_send_answer: conn=2bfc000, reply-code=2 (FAIL), result-subopcode=2 (SESSION_ACTIVATE), sub-id=911, cookie=65781, rply_len=3972, num_tlv_blocks=2
Oct 24 09:53:03.160395 Delete session: 911
Oct 24 09:53:03.160433 Begin to logout Subscriber
Oct 24 09:53:03.160513 Removing client snapshot
Oct 24 09:53:03.160649 authd_auth_aaa_msg_destroy
Oct 24 09:53:03.160702 authd_auth_aaa_msg_destructauth_aaa_msg: 0x1f7006c
Oct 24 09:53:03.160739 authd_write_conn: response is 0x2bfc05c, total len is 3972 and sent is 0
Oct 24 09:53:03.160798 authd_write_conn: response is 0x2bfc05c, wrote 3972 bytes

[sql] 	expand: call radcheck('%{User-Name}') -> call radcheck('2892.4a23.b7c8')
[sql] User found in radcheck table
[sql] 	expand: call radreply('%{User-Name}') -> call radreply('2892.4a23.b7c8')
rlm_sql (sql): Released sql socket id: 3
++[sql] = ok
+} # group authorize = ok
WARNING: Please update your configuration, and remove 'Auth-Type = Local'
WARNING: Use the PAP or CHAP modules instead.
User-Password in the request does NOT match "known good" password.
Failed to authenticate the user.
Using Post-Auth-Type Reject
WARNING: Unknown value specified for Post-Auth-Type.  Cannot perform requested action.
# Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
Delaying reject of request 1 for 1 seconds
Going to the next request
Waking up in 0.9 seconds.

2892.4a23.b7c8  Cleartext-Password := "IPoE-Opt82"
                ERX-Egress-Policy-Name = "POLICER-5M",
                ERX-Ingress-Policy-Name = "POLICER-5M"

User-Password in the request does NOT match "known good" password.
Failed to authenticate the user

2892.4a23.b7c8  Cleartext-Password := "IPoE-Opt82"
               Framed-IP-Address = 10.100.100.5,
               ERX-Service-Activate:1 = "svc-global-ipoe(50m,50m,,,,)"
               ERX-Virtual-Router-Name = default:default

echo 'Acct-Session-Id ="1024",ERX-Service-Activate:1="svc-global-ipoe(10m,10m)" ' | radclient -x 127.0.0.1 coa hardpass5

Выдает данные с IP адресом. То есть конект есть.

User-Password in the request does NOT match "known good" password.
Failed to authenticate the user.
Using Post-Auth-Type Reject

[root@localhost /usr/local/nodeny]# echo 'Acct-Session-Id ="1026",ERX-Service-Activate:1="svc-global-ipoe(10m,10m)" ' | radclient -x 127.0.0.1 coa hardpass5
Sending CoA-Request of id 43 to 127.0.0.1 port 3799
	Acct-Session-Id = "1026"
	ERX-Service-Activate:1 = "svc-global-ipoe(10m,10m)"
rad_recv: CoA-ACK packet from host 127.0.0.1 port 3799, id=43, length=20

Ready to process requests.
rad_recv: CoA-Request packet from host 127.0.0.1 port 26545, id=43, length=59
	Acct-Session-Id = "1026"
	ERX-Service-Activate:1 = "svc-global-ipoe(10m,10m)"
server coa {
# Executing section recv-coa from file /usr/local/etc/raddb/sites-enabled/coa
+group recv-coa {
++[ok] = ok
+} # group recv-coa = ok
# Executing section send-coa from file /usr/local/etc/raddb/sites-enabled/coa
+group send-coa {
++[ok] = ok
+} # group send-coa = ok
} # server coa
Sending CoA-ACK of id 43 to 127.0.0.1 port 26545
Finished request 5.
Going to the next request
Cleaning up request 5 ID 43 with timestamp +179
Ready to process requests.

> show subscribers extensive    
Type: VLAN
Logical System: default
Routing Instance: default
Interface: ge-1/0/0.1073742672
Interface type: Dynamic
Underlying Interface: ge-1/0/0
Dynamic Profile Name: DYNINTF-SVLAN-DHCP-INET
State: Active
Session ID: 1045
VLAN Id: 999
Login Time: 2016-10-25 11:32:19 EEST

Type: DHCP
User Name: 2892.4a23.b7c8
IP Address: 10.100.100.5
IP Netmask: 255.255.255.128
Logical System: default
Routing Instance: default
Interface: demux0.1073742673
Interface type: Dynamic
Underlying Interface: ge-1/0/0.1073742672
Dynamic Profile Name: DYNSUB-SVLAN-IPDEMUX
MAC Address: 28:92:4a:23:b7:c8
State: Active
Radius Accounting ID: 1046
Session ID: 1046
VLAN Id: 999
Agent Circuit ID: len 6
00 04 03 e7 01 30
Agent Remote ID: len 8
00 06 00 1d a1 41 aa 00
Login Time: 2016-10-25 11:32:23 EEST
Service Sessions: 1
DHCP Options: len 69
35 01 01 3d 07 01 28 92 4a 23 b7 c8 0c 0a 48 50 2d 52 65 76
6f 6c 76 65 3c 08 4d 53 46 54 20 35 2e 30 37 0d 01 0f 03 06
2c 2e 2f 1f 21 79 f9 fc 2b 52 12 01 06 00 04 03 e7 01 30 02
08 00 06 00 1d a1 41 aa 00
IP Address Pool: dhcp-subs

   Service Session ID: 1047
   Service Session Name: svc-global-ipoe
   State: Active
   Family: inet
   IPv4 Input Filter Name: INET_IN-demux0.1073742673-in
   IPv4 Output Filter Name: INET_OUT-demux0.1073742673-out

> show dynamic-profile session service-id 1047    
svc-global-ipoe {
    interfaces {
        demux0 {
            unit 1073742673 {
                family {
                    inet {
                        filter {
                            input INET_IN precedence 50;
                            output INET_OUT precedence 50;
                        }
                    }
                }
            }
        }
    }
    firewall {
        family {
            inet {
                filter INET_IN {
                    interface-specific;
                    term 1 {
                        then {
                            policer POLICER_IN;
                            service-accounting;
                            accept;
                        }
                    }
                }
                filter INET_OUT {
                    interface-specific;
                    term 1 {
                        then {
                            policer POLICER_OUT;
                            service-accounting;
                            accept;
                        }
                    }
                }
            }
        }
        policer POLICER_IN {
            filter-specific;
            if-exceeding {
                bandwidth-limit 50m;
                burst-size-limit 512k;
            }
            then discard;
        }
        policer POLICER_OUT {
            filter-specific;
            if-exceeding {
                bandwidth-limit 50m;
                burst-size-limit 512k;
            }
            then discard;
        }
    }
}

> show configuration dynamic-profiles svc-global-ipoe 
variables {
    SPEED_IN default-value 1M;
    SPEED_OUT default-value 1M;
}
interfaces {
    demux0 {
        unit "$junos-interface-unit" {
            family inet {
                filter {
                    input INET_IN precedence 50;
                    output INET_OUT precedence 50;
                }
            }
        }
    }
}
firewall {
    family inet {
        filter INET_IN {
            interface-specific;
            term 1 {
                then {
                    policer POLICER_IN;
                    service-accounting;
                    accept;
                }
            }
        }
        filter INET_OUT {
            interface-specific;
            term 1 {
                then {
                    policer POLICER_OUT;
                    service-accounting;
                    accept;
                }
            }
        }
    }
    policer POLICER_IN {
        filter-specific;
        if-exceeding {
            bandwidth-limit "$SPEED_IN";
            burst-size-limit 512k;
        }
        then discard;
    }
    policer POLICER_OUT {
        filter-specific;
        if-exceeding {
            bandwidth-limit "$SPEED_OUT";
            burst-size-limit 512k;
        }
        then discard;
    }
}

echo 'Acct-Session-Id ="1047",ERX-Service-Deactivate="svc-global-ipoe" ' | radclient -x 127.0.0.1 coa hardpass5

@border> request network-access aaa subscriber modify session-id 1053 ?
Possible completions:
  <[Enter]>            Execute this command
  junos-cos-traffic-control-profile  Modify $junos-cos-traffic-control-profile variable
  |                    Pipe through a command
@border>

@border>request network-access aaa subscriber delete session-id 1052 service-profile svc-global-ipoe
Successful completion 

@border> request network-access aaa subscriber add service-profile "svc-global-ipoe(10m,10m)" session-id 1052 
Successful completion 

2892.4a23.b7c8  Cleartext-Password := "IPoE-Opt82"
                Framed-IP-Address = 10.100.100.5,

pool-match-order {
    ip-address-first;
    external-authority;
    option-82;
}
authentication {
    password IPoE-Opt82;
    username-include {
        mac-address;
    }
}
group local {
    dynamic-profile DYNSUB-SVLAN-IPDEMUX;
    interface ge-1/0/0.0;

# show dynamic-profiles DYNINTF-SVLAN-DHCP-INET 
interfaces {
    "$junos-interface-ifd-name" {
        unit "$junos-interface-unit" {
            demux-source inet;
            proxy-arp restricted;
            vlan-id "$junos-vlan-id";
            family inet {
                unnumbered-address lo0.0 preferred-source-address 10.100.100.1;
            }
        }
    }
}
# show dynamic-profiles DYNSUB-SVLAN-IPDEMUX 
interfaces {
    demux0 {
        unit "$junos-interface-unit" {
            proxy-arp;
            demux-options {
                underlying-interface "$junos-underlying-interface";
            }
            family inet {
                demux-source {
                    $junos-subscriber-ip-address;
                }
                unnumbered-address lo0.0 preferred-source-address 10.100.100.1;
            }
        }
    }
}

# run show subscribers extensive                                                               
Type: VLAN
Logical System: default
Routing Instance: default
Interface: ge-1/0/0.1073742693
Interface type: Dynamic
Underlying Interface: ge-1/0/0
Dynamic Profile Name: DYNINTF-SVLAN-DHCP-INET
State: Active
Session ID: 1083
VLAN Id: 999
Login Time: 2016-11-03 08:45:34 EET

Type: DHCP
User Name: 2892.4a23.b7c8
IP Address: 10.100.100.5
IP Netmask: 255.255.255.128
Logical System: default
Routing Instance: default
Interface: demux0.1073742694
Interface type: Dynamic
Underlying Interface: ge-1/0/0.1073742693
Dynamic Profile Name: DYNSUB-SVLAN-IPDEMUX
MAC Address: 28:92:4a:23:b7:c8
State: Active
Radius Accounting ID: 1084
Session ID: 1084
VLAN Id: 999
Agent Circuit ID: len 6
00 04 03 e7 01 30
Agent Remote ID: len 8
00 06 00 1d a1 41 aa 00
Login Time: 2016-11-03 08:45:38 EET
DHCP Options: len 69
35 01 01 3d 07 01 28 92 4a 23 b7 c8 0c 0a 48 50 2d 52 65 76
6f 6c 76 65 3c 08 4d 53 46 54 20 35 2e 30 37 0d 01 0f 03 06
2c 2e 2f 1f 21 79 f9 fc 2b 52 12 01 06 00 04 03 e7 01 30 02
08 00 06 00 1d a1 41 aa 00
IP Address Pool: dhcp-subs
 

# run request network-access aaa subscriber add session-id 1084 service-profile svc-global-ipoe 
   Service Session ID: 1086
   Service Session Name: svc-global-ipoe
   State: Active
   Family: inet
   IPv4 Input Filter Name: INET_IN-demux0.1073742694-in
   IPv4 Output Filter Name: INET_OUT-demux0.1073742694-out

# run show network-access aaa subscribers session-id 1084 detail                                      
Type: dhcp
Stripped username: 2892.4a23.b7c8
AAA Logical system/Routing instance: default:default
Target Logical system/Routing instance: default:default
Access-profile: sbr
Session ID: 1084
Accounting Session ID: 1084
Multi Accounting Session ID: 0
IP Address: 10.100.100.5
Authentication State: AuthStateActive
Accounting State: Acc-Interim-Sent
Provisioning Type: None
Service name: svc-global-ipoe
  Service State: SvcActive
  Service Family: inet
  Service Activation Source: Radius
  Session ID: 0
  Session uptime: 00:00:56

        postauth_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                'user=%{ERX-Dhcp-Mac-Addr};ses=%{Acct-Session-Id}')"
        accounting_update_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                'user=%{ERX-Dhcp-Mac-Addr};ses=%{Acct-Session-Id}')"}

CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));

CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,'.','')));

CALL set_auth(ip, CONCAT('mod=dhcp;',REPLACE(properties,'.','')));

set dynamic-profiles svc-global-ipoe variables SPEED_IN default-value 2m
set dynamic-profiles svc-global-ipoe variables SPEED_OUT default-value 2m
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter input "$INET_IN"
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter input precedence 50
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter output "$INET_OUT"
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter output precedence 50
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_IN interface-specific
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_IN term 1 then policer POLICER_IN
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_IN term 1 then service-accounting
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_IN term 1 then accept
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_OUT interface-specific
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_OUT term 1 then policer POLICER_OUT
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_OUT term 1 then service-accounting
set dynamic-profiles svc-global-ipoe firewall family inet filter INET_OUT term 1 then accept
set dynamic-profiles svc-global-ipoe firewall policer POLICER_IN filter-specific
set dynamic-profiles svc-global-ipoe firewall policer POLICER_IN if-exceeding bandwidth-limit "$SPEED_IN"
set dynamic-profiles svc-global-ipoe firewall policer POLICER_IN if-exceeding burst-size-limit 512k
set dynamic-profiles svc-global-ipoe firewall policer POLICER_IN then discard
set dynamic-profiles svc-global-ipoe firewall policer POLICER_OUT filter-specific
set dynamic-profiles svc-global-ipoe firewall policer POLICER_OUT if-exceeding bandwidth-limit "$SPEED_OUT"
set dynamic-profiles svc-global-ipoe firewall policer POLICER_OUT if-exceeding burst-size-limit 512k
set dynamic-profiles svc-global-ipoe firewall policer POLICER_OUT then discard

set dynamic-profiles svc-global-ipoe variables SPEED_IN default-value 2m
set dynamic-profiles svc-global-ipoe variables SPEED_OUT default-value 2m
set dynamic-profiles svc-global-ipoe variables INET_IN uid
set dynamic-profiles svc-global-ipoe variables INET_OUT uid
set dynamic-profiles svc-global-ipoe variables POLICER_IN uid
set dynamic-profiles svc-global-ipoe variables POLICER_OUT uid
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter input "$INET_IN"
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter input precedence 50
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter output "$INET_OUT"
set dynamic-profiles svc-global-ipoe interfaces demux0 unit "$junos-interface-unit" family inet filter output precedence 50
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_IN" interface-specific
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_IN" term 1 then policer "$POLICER_IN"
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_IN" term 1 then service-accounting
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_IN" term 1 then accept
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_OUT" interface-specific
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_OUT" term 1 then policer "$POLICER_OUT"
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_OUT" term 1 then service-accounting
set dynamic-profiles svc-global-ipoe firewall family inet filter "$INET_OUT" term 1 then accept
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_IN" filter-specific
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_IN" if-exceeding bandwidth-limit "$SPEED_IN"
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_IN" if-exceeding burst-size-limit 512k
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_IN" then discard
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_OUT" filter-specific
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_OUT" if-exceeding bandwidth-limit "$SPEED_OUT"
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_OUT" if-exceeding burst-size-limit 512k
set dynamic-profiles svc-global-ipoe firewall policer "$POLICER_OUT" then discard

# show dynamic-profiles svc-guest-pppoe                                                                                                                             
interfaces {                                                                                                                                                                            
    pp0 {                                                                                                                                                                               
        unit "$junos-interface-unit" {                                                                                                                                                  
            family inet {                                                                                                                                                               
                filter {                                                                                                                                                                
                    input svc-filter-in-nomoney precedence 250;                                                                                                                         
                }                                                                                                                                                                       
            }
        }
    }
}
firewall {
    family inet {
        filter svc-filter-in-nomoney {
            interface-specific;
            term 1 {
                from {
                    destination-address {
                        8.8.8.8/32;
                        x.x.x.x/32;  -<< Добавляешь адреса которые хочешь разрешить.
                    }
                    protocol [ tcp udp ];
                    destination-port [ 80 443 53 8080 ];
                }
                then accept;
            }
            term 2 {
                from {
                    protocol tcp;
                    destination-port [ 80 443 ];
                }
                then {
                    routing-instance neg_dep;
                }
            }
            term default {
                then {
                    discard;
                }
            }
        }
    }
}

# show routing-options 
interface-routes {
    rib-group inet neg_dep;
rib-groups {
    neg_dep {
        import-rib [ inet.0 neg_dep.inet.0 ];
    }
}

# show routing-instances neg_dep 
instance-type forwarding;
routing-options {
    static {
        route 0.0.0.0/0 next-hop x.x.x.x;  -<< x.x.x.x - ip заглушки
    }
}

fwd 127.0.0.1,8080 tcp from any to not me dst-port 80 via em1

fwd 127.0.0.1,8080 tcp from any to not me dst-port 80 via em1

    svc-guest-pppoe {
        interfaces {
            pp0 {
                unit "$junos-interface-unit" {
                    family inet {
                        filter {
                            input svc-filter-in-nomoney precedence 250;
                        }
                    }
                }
            }
        }
        firewall {
            family inet {
                filter svc-filter-in-nomoney {
                    interface-specific;
                    term 1 {
                        from {
                            destination-address {
                                x.x.x.x/32;
                                x.x.x.x/32;
                                x.x.x.x/32;
                                x.x.x.x/32;
                            }
                            protocol [ tcp udp ];
                            destination-port [ 80 443 53 8080 ];
                        }
                        then accept;
                    }
                    term 2 {
                        from {
                            protocol tcp;
                            destination-port [ 80 443 ];
                        }
                        then {
                            routing-instance neg_dep;
                        }
                    }
                    term default {
                        then {
                            discard;
                        }
                    }
                }
            }
        }
    }

    acl_in_prefix  => 'ACL-IN-',
    acl_out_prefix => 'ACL-OUT-'

    in  => '{{settings.acl_in_prefix}}{{user.service_id}}',
    out => '{{settings.acl_out_prefix}}{{user.service_id}}'

    { method => 'enable', param => $hw_connection{enable_password} },
    'configure terminal'

    show_alcs => 'do sh ip access-lists'

    ip access-list extended {{acl}}
    {% if is_in_acl %}
        permit ip any host {{ip}}
    {% else %}
        permit ip host {{ip}} any
    {% endif %}

    ip access-list extended {{acl}}
    {% if is_in_acl %}
        no permit ip any host {{ip}}
    {% else %}
        no permit ip host {{ip}} any
    {% endif %}

    class-map match-all CM-IN-{{service.service_id}}
    match access-group name {{settings.acl_in_prefix}}{{service.service_id}}

    {% if service.speed_in.1 %}
        policy-map PM-IN-1
        class CM-IN-{{service.service_id}}
        police {{service.speed_in.1}} 10000 exceed-action drop
    {% endif %}
    {% if service.speed_in.2 %}
        policy-map PM-IN-2
        class CM-IN-{{service.service_id}}
        police {{service.speed_in.2}} 10000 exceed-action drop
    {% endif %}

    class-map match-all CM-OUT-{{service.service_id}}
    match access-group name {{settings.acl_in_prefix}}{{service.service_id}}

    {% if service.speed_out.1 %}
        policy-map PM-OUT-1
        class CM-OUT-{{service.service_id}}
        police {{service.speed_out.1}} 10000 exceed-action drop
    {% endif %}
    {% if service.speed_out.2 %}
        policy-map PM-OUT-2
        class CM-OUT-{{service.service_id}}
        police {{service.speed_out.2}} 10000 exceed-action drop
    {% endif %}

    return $s =~ /Extended IP access list +([^\n]+)$/ ? $1 : ''; 

CREATE PROCEDURE `radreply`(IN login VARCHAR(64), IN nas_portid VARCHAR(64), IN `tag` VARCHAR(64))
BEGIN
    DECLARE usr_mac VARCHAR(12);
    DECLARE usr_ip VARCHAR(15);
    DECLARE usr_id INT;
    DECLARE dev_mac VARCHAR(64);
    DECLARE port VARCHAR(64);
    DECLARE one_connect INT;
    SELECT REPLACE(login, '.', '') INTO usr_mac;
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(nas_portid, ':', -1), '-', 1) INTO dev_mac;
    SELECT SUBSTRING_INDEX(nas_portid, '-', -1) INTO port;

SELECT u.id, u.balance, u.name, u.state, a.auth_start, a.ip, a.properties, INET_ATON(a.ip) AS ipn FROM ( SELECT INET_NTOA(i.ip) AS ip, '' as properties, UNIX_TIMESTAMP() AS auth_start FROM users u JOIN ip_pool i ON i.uid=u.id WHERE u.lstate=1 UNION ALL SELECT ip, properties, start AS auth_start FROM auth_now ) a JOIN ip_pool i ON INET_ATON(a.ip)=i.ip JOIN users u ON i.uid=u.id WHERE TRUE  AND u.id=5288 AND u.state='on'

SELECT INET_NTOA(i.ip) AS ip, '' as properties, UNIX_TIMESTAMP() AS auth_start FROM users u JOIN ip_pool i ON i.uid=u.id WHERE u.lstate=1 UNION ALL SELECT ip, properties, start AS auth_start FROM auth_now

SELECT count(*) from auth_now;

pkg install mysqltuner
 mysqltuner --user root --pass parolbd --color