freeradius DHCP SQL static leases

Instalacia serveru:

apt install freeradius freeradius-mysql freeradius-dhcp

Vytvorenie databázy pre mysql DHCP

Potrebne tabulky:

  • radcheck – overenie MAC
  • radreply – definovanie parametrov MAC
  • postauth – LOG ak zariadenie dostalo IP

štruktúra a príklad radcheck:


štruktúra a príklad radreply:

štruktúra a príklad radposauth:

Vytvorenie tabuliek LMS VALA:

POSTAUTH:

CREATE TABLE `wifi_dhcp_radpostauth` (
  `id` int(11) NOT NULL,
  `username` varchar(64) NOT NULL DEFAULT '',
  `pass` varchar(64) NOT NULL DEFAULT '',
  `reply` varchar(32) NOT NULL DEFAULT '',
  `authdate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Kľúče pre exportované tabuľky
--

--
-- Indexy pre tabuľku `radpostauth`
--
ALTER TABLE `radpostauth`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT pre exportované tabuľky
--

--
-- AUTO_INCREMENT pre tabuľku `radpostauth`
--
ALTER TABLE `radpostauth`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

Nastavenie TRIGER pre update hodnot v tabulke nodes s casom kedy posledny krat dostal IP. 


CREATE or replace TRIGGER J5_dhcp_radpostauth_triger
AFTER INSERT ON J5_dhcp_radpostauth FOR EACH ROW


update nodestest set lastonline = UNIX_TIMESTAMP(NEW.authdate) where mac = NEW.username  and NEW.reply != '';

RADCHECK (WIFI):

CREATE or replace VIEW `v_staticwifi_radcheck` as
select UUID() as 'id', `nodes`.`mac_wifi`  AS `username`, 'Cleartext-Password' AS attribute, ':=' as op, '' as value from `nodes` where `nodes`.`access` = 1 and `nodes`.`wifi` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr_wifi`),'.',1) = 192 and nodes.mac_wifi !='0' and nodes.mac_wifi != ''

Nova verzia 1.11.2020

CREATE or replace VIEW `v_staticwifi_radcheck` as
select UUID() as 'id', `nodes`.`mac_wifi` AS `username`, 'Cleartext-Password' AS attribute, ':=' as op, '' as value from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr_wifi` = `networks`.`address_wifi`) = 1 and nodes.mac_wifi != '0' and nodes.mac_wifi != ''

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1

RADREPLY (WIFI):

CREATE or replace VIEW `v_staticwifi_radreply` as

select UUID() as 'id', `nodes`.`mac_wifi`  AS `username`, 'DHCP-Subnet-Mask' AS attribute, ':=' as op, (select networks.mask from networks where networks.`net1`= substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3) ) as value from `nodes` where `nodes`.`access` = 1 and `nodes`.`wifi` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr_wifi`),'.',1) = 192 and nodes.mac_wifi != '' and nodes.mac_wifi !='0'
union


select UUID() as 'id', `nodes`.`mac_wifi`  AS `username`, 'DHCP-Router-Address' AS attribute, ':=' as op, (select networks.wifi_gateway from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3)) as value from `nodes` where `nodes`.`access` = 1 and `nodes`.`wifi` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr_wifi`),'.',1) = 192 and nodes.mac_wifi != '' and nodes.mac_wifi !='0'
union

select UUID() as 'id', `nodes`.`mac_wifi`  AS `username`, 'DHCP-Domain-Name-Server' AS attribute, ':=' as op, (select networks.dns from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3)) as value from `nodes` where `nodes`.`access` = 1 and `nodes`.`wifi` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr_wifi`),'.',1) = 192 and nodes.mac_wifi != '' and nodes.mac_wifi !='0'
union

select UUID() as 'id', `nodes`.`mac_wifi`  AS `username`, 'DHCP-Domain-Name-Server' AS attribute, ':=' as op, (select networks.dns2 from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr_wifi`),9,3)) as value from `nodes` where `nodes`.`access` = 1 and `nodes`.`wifi` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr_wifi`),'.',1) = 192 and nodes.mac_wifi != '' and nodes.mac_wifi !='0'


union
select UUID() as 'id', `nodes`.`mac_wifi`  AS `username`, 'DHCP-Your-IP-Address' AS attribute, ':=' as op, INET_NTOA(`nodes`.`ipaddr_wifi`) AS `value` from `nodes` where `nodes`.`access` = 1 and `nodes`.`wifi` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr_wifi`),'.',1) = 192 and nodes.mac_wifi != '' and nodes.mac_wifi !='0'

Nova verzia 1.11.2020

CREATE or replace VIEW `v_staticwifi_radreply` as
select uuid() AS `id`,
`nodes`.`mac_wifi` AS `username`,
'DHCP-Subnet-Mask' AS `attribute`,
':=' AS `op`,

`networks`.`mask` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address_wifi`) = 1 and nodes.mac_wifi != '0' and nodes.mac_wifi != ''

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 

union 

select uuid() AS `id`,
`nodes`.`mac_wifi` AS `username`,
'DHCP-Router-Address' AS `attribute`,
':=' AS `op`,

`networks`.`wifi_gateway` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr_wifi` = `networks`.`address_wifi`) = 1 and nodes.mac_wifi != '0' and nodes.mac_wifi != ''

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 



union 

select uuid() AS `id`,
`nodes`.`mac_wifi` AS `username`,
'DHCP-Domain-Name-Server' AS `attribute`,
':=' AS `op`,

`networks`.`dns` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr_wifi` = `networks`.`address_wifi`) = 1 and nodes.mac_wifi != '0' and nodes.mac_wifi != ''

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 

union 

select uuid() AS `id`,
`nodes`.`mac_wifi` AS `username`,
'DHCP-Domain-Name-Server' AS `attribute`,
':=' AS `op`,

`networks`.`dns2` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr_wifi` = `networks`.`address_wifi`) = 1 and nodes.mac_wifi != '0' and nodes.mac_wifi != ''

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 



union 


select uuid() AS `id`,
`nodes`.`mac_wifi` AS `username`,
'DHCP-Your-IP-Address' AS `attribute`,
':=' AS `op`,

inet_ntoa(`nodes`.`ipaddr_wifi`)  as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr_wifi` = `networks`.`address_wifi`) = 1 and nodes.mac_wifi != '0' and nodes.mac_wifi != ''

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1

RADCHECK (CABLE):

CREATE or replace VIEW `v_staticBN_radcheck` as
select UUID() as 'id', `nodes`.`mac` AS `username`, 'Cleartext-Password' AS attribute, ':=' as op, '' as value from `nodes` where `nodes`.`access` = 1 and `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr`),'.',1) = 147 and (select `customers`.`seq_servis_zona` AS `seq_servis_zona` from `customers` where `customers`.`id` = `nodes`.`ownerid` and `customers`.`deleted` = 0 and (select `passwd`.`expdate` from `passwd` where `customers`.`id` = `passwd`.`ownerid` limit 1) > unix_timestamp()) = 5

Nova verzia select 27.10.2020:


create or replace view v_staticPO_radcheck as
select UUID() as 'id', `nodes`.`mac` AS `username`, 'Cleartext-Password' AS attribute, ':=' as op, '' as value from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1

RADREPLAY (CABLE):

CREATE or replace VIEW `v_staticBN_radreply` as

select UUID() as 'id', `nodes`.`mac`  AS `username`, 'DHCP-Subnet-Mask' AS attribute, ':=' as op, (select networks.mask from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3) ) as value from `nodes` where `nodes`.`access` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr`),'.',1) = 147 and (select `customers`.`seq_servis_zona` AS `seq_servis_zona` from `customers` where `customers`.`id` = `nodes`.`ownerid` and `customers`.`deleted` = 0 and (select `passwd`.`expdate` from `passwd` where `customers`.`id` = `passwd`.`ownerid` limit 1) > unix_timestamp()) = 5
union


select UUID() as 'id', `nodes`.`mac`  AS `username`, 'DHCP-Router-Address' AS attribute, ':=' as op, (select networks.gateway from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3)) as value from `nodes` where `nodes`.`access` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr`),'.',1) = 147 and (select `customers`.`seq_servis_zona` AS `seq_servis_zona` from `customers` where `customers`.`id` = `nodes`.`ownerid` and `customers`.`deleted` = 0 and (select `passwd`.`expdate` from `passwd` where `customers`.`id` = `passwd`.`ownerid` limit 1) > unix_timestamp()) = 5
union

select UUID() as 'id', `nodes`.`mac`  AS `username`, 'DHCP-Domain-Name-Server' AS attribute, ':=' as op, (select networks.dns from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3)) as value from `nodes` where `nodes`.`access` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr`),'.',1) = 147 and (select `customers`.`seq_servis_zona` AS `seq_servis_zona` from `customers` where `customers`.`id` = `nodes`.`ownerid` and `customers`.`deleted` = 0 and (select `passwd`.`expdate` from `passwd` where `customers`.`id` = `passwd`.`ownerid` limit 1) > unix_timestamp()) = 5
union

select UUID() as 'id', `nodes`.`mac`  AS `username`, 'DHCP-Domain-Name-Server' AS attribute, ':=' as op, (select networks.dns2 from networks where networks.`net1` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3) or networks.`net2` = substr(inet_ntoa(`nodes`.`ipaddr`),9,3)) as value from `nodes` where `nodes`.`access` = 1 and `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr`),'.',1) = 147 and (select `customers`.`seq_servis_zona` AS `seq_servis_zona` from `customers` where `customers`.`id` = `nodes`.`ownerid` and `customers`.`deleted` = 0 and (select `passwd`.`expdate` from `passwd` where `customers`.`id` = `passwd`.`ownerid` limit 1) > unix_timestamp()) = 5


union
select UUID() as 'id', `nodes`.`mac`  AS `username`, 'DHCP-Your-IP-Address' AS attribute, ':=' as op, INET_NTOA(`nodes`.`ipaddr`) AS `value` from `nodes` where `nodes`.`access` = 1 and  `SPLIT_STRING`(inet_ntoa(`nodes`.`ipaddr`),'.',1) = 147 and (select `customers`.`seq_servis_zona` AS `seq_servis_zona` from `customers` where `customers`.`id` = `nodes`.`ownerid` and `customers`.`deleted` = 0 and (select `passwd`.`expdate` from `passwd` where `customers`.`id` = `passwd`.`ownerid` limit 1) > unix_timestamp()) = 5

Nova verzia 27.10.2020

create or replace view v_staticPO_radreply as
select uuid() AS `id`,
`nodes`.`mac` AS `username`,
'DHCP-Subnet-Mask' AS `attribute`,
':=' AS `op`,

`networks`.`mask` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 

union 

select uuid() AS `id`,
`nodes`.`mac` AS `username`,
'DHCP-Router-Address' AS `attribute`,
':=' AS `op`,

`networks`.`gateway` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 



union 

select uuid() AS `id`,
`nodes`.`mac` AS `username`,
'DHCP-Domain-Name-Server' AS `attribute`,
':=' AS `op`,

`networks`.`dns` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 

union 

select uuid() AS `id`,
`nodes`.`mac` AS `username`,
'DHCP-Domain-Name-Server' AS `attribute`,
':=' AS `op`,

`networks`.`dns2` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 



union 

select uuid() AS `id`,
`nodes`.`mac` AS `username`,
'DHCP-Domain-Name-Server' AS `attribute`,
':=' AS `op`,

`networks`.`dns` as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1 

union 

select uuid() AS `id`,
`nodes`.`mac` AS `username`,
'DHCP-Your-IP-Address' AS `attribute`,
':=' AS `op`,

inet_ntoa(`nodes`.`ipaddr`)  as `value` 

from `nodes`

inner join networks on 
((-1 << (33 - INSTR(BIN(INET_ATON(networks.mask)), "0"))) &
       `nodes`.`ipaddr` = `networks`.`address`) = 1 and (networks.seq_servis_zona = 8 or networks.seq_servis_zona = 9)

inner join passwd on passwd.ownerid = nodes.ownerid and passwd.`expdate` > unix_timestamp()

where `nodes`.`access` = 1

Nastavenie freeradius:

  1. vytvorenie query_dhcp.conf
cd /etc/freeradius/3.0/mods-config/sql/main/mysql/
cp queries.conf queries_dhcp.conf

V súbore queries_dhcp.conf upraviť premennú:

sql_user_name = "%{DHCP-Client-Hardware-Address}"
group_membership_query = "SELECT 1 from ${postauth_table}"

2. vytvorenie SQL connection modul

cd /etc/freeradius/3.0/mods-available/
cp sql sqldhcp

V súbore sqldhcp je potrebne nastaviť parametre k databáze a príslušne tabuľky

Na konci súboru je potrebne includnut správny query file:

$INCLUDE ${modconfdir}/${.:name}/main/${dialect}/queries_dhcp.conf

Zmena nastaveni:

read_groups = no
read_profiles = no

3. vytvorenie virtual server

cd /etc/freeradius/3.0/sites-available/
nano wifi-dhcp
    server dhcp {
        listen {
            type = dhcp
            ipaddr = 147.232.191.233
            port = 67
            interface = ens192
            broadcast = no
        }
        dhcp DHCP-Discover {
            update reply {
                DHCP-Message-Type = DHCP-Offer
            }

            update reply {
                #DHCP-Domain-Name-Server = 0.0.0.0
                DHCP-IP-Address-Lease-Time = 3600
                DHCP-DHCP-Server-Identifier = 192.168.10.1
            }

            sql_dhcp.authorize
            sql_dhcp.post-auth
            linelog-dhcp
            ok
        }

        dhcp DHCP-Request {
            update reply {
                DHCP-Message-Type = DHCP-Ack
            }

            update reply {
                #DHCP-Domain-Name-Server = 0.0.0.0
                DHCP-IP-Address-Lease-Time = 3600
                DHCP-DHCP-Server-Identifier = 192.168.10.1
            }

            sql_dhcp.authorize
            sql_dhcp.post-auth
            linelog-dhcp
            ok
        }

        dhcp {
            sql_dhcp.post-auth
            linelog-dhcp
            reject
        }
    }

Úprava LOG do MYSQL:

nano /etc/freeradius/3.0/mods-config/sql/main/mysql/queries_dhcp.conf

V „post-auth {“ query pozmeniť premennú:

'%{reply:Packet-Type}', \

zmenit na:

'%{reply:DHCP-Your-IP-Address}', \



group_membership_query = "SELECT id from ${postauth_table} where id =1"

Nastavenie modulu linelog

linelog linelog-dhcp {
    filename = ${logdir}/linelog-dhcp.log
    format = ""
    reference = "%{%{reply:DHCP-Message-Type}:-%{request:DHCP-Message-Type}}"
    DHCP-Discover = "%S --> Transaction-ID: %{DHCP-Transaction-Id} DISCOVER: [%{DHCP-Client-Hardware-Address}] via (%{DHCP-Gateway-IP-Address}), hop count = %{DHCP-Hop-Count}, Relay = %{DHCP-Relay-Remote-Id}, Hostname =  %{DHCP-Hostname}"
    DHCP-Offer = "%S <-- Transaction-ID: %{DHCP-Transaction-Id} OFFER: %{reply:DHCP-Your-IP-Address} to [%{DHCP-Client-Hardware-Address}] ..."
    DHCP-Request = "%S --> Transaction-ID: %{DHCP-Transaction-Id} REQUEST: [%{DHCP-Client-Hardware-Address}] via (%{DHCP-Gateway-IP-Address}), hop count = %{DHCP-Hop-Count}, Relay = %{DHCP-Relay-Remote-Id} ..."
    DHCP-Ack = "%S <-- Transaction-ID: %{DHCP-Transaction-Id} ACK: %{reply:DHCP-Your-IP-Address} to [%{DHCP-Client-Hardware-Address}] ..."
    DHCP-NAK = "%S <-- Transaction-ID: %{DHCP-Transaction-Id} NAK: [%{DHCP-Client-Hardware-Address}] for %{request:DHCP-Client-IP-Address}; ..."
    0 = "%S -/- Transaction-ID: %{DHCP-Transaction-Id} %{request:DHCP-Message-Type} DROPPED: ..."
}
   Send article as PDF   

Check_mk freeradius check

Check_mk zobrazenie používateľov ktorý sú kontaktovaný pri danej službe
Pridaj komentár

26  +    =  35