freeradius 3.0 ubuntu 18.04 with daloradius mikrotik ikev2 eap-radius wireless

Hi

First of all setup your favorite php sql webserver


apt install php-db php-gd git freeradius freeradius-mysql

cd /var/www/web001/htdocs 
git clone https://github.com/lirantal/daloradius.git

We have to import the freeradius 3.0 mysql schema first. Daloradius does only have freeradius 2.0 compatible sql schemas.

cat /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql | mysql -u radius -p radius

Now we import the daloradius sql schema without freeradius 2.0 sql schemas

cat /var/www/web001/htdocs/daloradius/contrib/db/mysql-daloradius.sql | mysql -u radius -p radius

here my freeradius mysql setup

cd /etc/freeradius/3.0/mods-enabled
ln -s ../mods-available/sql

vim sql

driver = "rlm_sql_mysql"
dialect = "mysql"
server = "localhost"
port = 3306
login = "radius"
password = "abcdefg"
radius_db = "radius"
read_clients = yes

here my changes to eap (eap for authenticating mikrotik wireless via wpa2 enterprise and mikrotik ikev2 eap radius)

vim /etc/freeradius/3.0/mods-enabled/eap

eap {
...
#ikev2 eap radius
default_eap_type = peap
...
}
tls-config tls-common {
private_key_file = path_to_your_ssl_private_key
certificate_file = path_to_your_ssl_certificate
ca_file = path_to_your_ssl_cabundle
}

I use rapidssl server certificate.

https://support.microsoft.com/en-ph/help/814394/certificate-requirements-when-you-use-eap-tls-or-peap-with-eap-tls


here my changes to the “default” site

cd /etc/freeradius/3.0/sites-enabled
vim default

authorize {
...
auth_log
...
sql
}

accounting {
...
sql
...
}

session {
...
sql
...
}

post-auth {
...
reply_log
sql
...
}

session {
...
sql
...
}

here my bulk radius settings

cd /etc/freeradius/3.0

vim radiusd.conf

log {
...
auth = yes
...
auth_badpass = yes
...
}

https://wiki.freeradius.org/guide/SQL-HOWTO-for-freeradius-3.x-on-Debian-Ubuntu

you have to create a systemd override for the freeradius unit. otherwise freeradius won’t start correctly if mysql is not running.

systemctl edit freeradius

[Unit]
After=network.target mysql.service

setup daloradius config

vim /var/www/web001/htdocs/daloradius/library/daloradius.conf.php

CONFIG_DB_USER
CONFIG_DB_PASS
CONFIG_DB_NAME

Have fun!

Simple PHP Mysql Bind dynamic DNS Service

Hi

Need a simple dyndns service.
Here my crappy 10 minutes approach to get this done:

curl -> webserver -> mysql -> cronjob -> namedZoneFile

Here my curl client cronjob every minute:

/usr/bin/curl --silent --user username:password http://dyn.xxx.xx

Webserver PHP implementation:

.htaccess

<IfModule mod_rewrite.c>
RewriteEngine on
RewriteRule .* - [E=HTTP_AUTHORIZATION:%{HTTP:Authorization},L]
</IfModule>

index.php

<?php

$ipAddress = $_SERVER['REMOTE_ADDR'];

if (!isset($_SERVER['PHP_AUTH_USER'])) {
  header('WWW-Authenticate: Basic realm="paranoids.at DynDns"');
  header('HTTP/1.0 401 Unauthorized');
  echo 'No Auth, Try again';
} 
else {
  $isAuth = getUserPass($_SERVER['PHP_AUTH_USER'],$_SERVER['PHP_AUTH_PW']);
  if (!empty($isAuth)) {
    if (empty(ifHostExists($isAuth['id']))) {
      insertHost($isAuth['id'], $ipAddress);
    }
    else {
      $host = ifHostExists($isAuth['id']);
      if (strcmp($host['ip'], $ipAddress) !== 0) {
        updateHost($host['userid'], $ipAddress);
      } 
    }
  }
  else {
    echo 'Wrong Auth, Try again';
  }
}

connect_db_dyn()->close();

function ifHostExists($userid) {
  $result = connect_db_dyn()->query("SELECT * FROM host WHERE userid='$userid';");
  return $result->fetch_assoc();
}

function getUserPass($user,$pass) {
  $result = connect_db_dyn()->query("SELECT * FROM user WHERE username='$user' AND password='$pass' LIMIT 1;");
  return $result->fetch_assoc();
}

function insertHost($userid,$newip) {
  connect_db_dyn()->query("INSERT INTO host (userid, ip, changed) VALUES ('$userid', '$newip', '1');");
}

function updateHost($userid,$ip) {
  connect_db_dyn()->query("UPDATE host SET ip='$ip', changed=1, timestamp=CURRENT_TIMESTAMP WHERE userid='$userid';");
}

function connect_db_dyn() {
  $dbh = new mysqli('localhost', 'username', 'password', 'database');
  return $dbh;
}

?>

Database dump:

-- phpMyAdmin SQL Dump
-- version 4.6.4
-- https://www.phpmyadmin.net/
--
-- Host: localhost

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

--
-- Database: `database`
--

-- --------------------------------------------------------

--
-- Table structure for table `host`
--

CREATE TABLE `host` (
 `id` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `ip` varchar(255) NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `changed` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `host`
--

INSERT INTO `host` (`id`, `userid`, `ip`, `timestamp`, `changed`) VALUES
(14, 1, '1.1.1.1', '2016-11-30 04:38:03', 0);

-- --------------------------------------------------------

--
-- Table structure for table `user`
--

CREATE TABLE `user` (
 `id` int(11) NOT NULL,
 `username` varchar(255) NOT NULL,
 `password` varchar(255) NOT NULL,
 `comment` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `user`
--

INSERT INTO `user` (`id`, `username`, `password`, `comment`) VALUES
(1, 'username1', 'password1', 'comment1');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `host`
--
ALTER TABLE `host`
 ADD PRIMARY KEY (`id`);

--
-- Indexes for table `user`
--
ALTER TABLE `user`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `host`
--
ALTER TABLE `host`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;
--
-- AUTO_INCREMENT for table `user`
--
ALTER TABLE `user`
 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

dnsserverside cronjob every minute:

<?php

if (!empty(getChange())) {
 deleteHosts();
 updateHosts();
 writeBind();
}

connect_Db_Dyn()->close();

function getChange() {
 $result = connect_Db_Dyn()->query("SELECT * FROM host WHERE changed = 1");
 return $result->fetch_assoc();
}

function getHosts() {
 $result = connect_Db_Dyn()->query("SELECT * FROM host");
 return $result->fetch_all($resulttype = MYSQLI_ASSOC);
}

function deleteHosts() {
 connect_Db_Dyn()->query("DELETE FROM host WHERE timestamp < date_add(current_date, interval -14 day) LIMIT 1000;");
}

function updateHosts() {
 connect_Db_Dyn()->query("UPDATE host SET changed=0;");
}

function getUsernameById($userid) {
 $result = connect_Db_Dyn()->query("SELECT username FROM user WHERE id='$userid'");
 $row = $result->fetch_assoc();
 return $row['username'];
}

function connect_Db_Dyn() {
 $dbh = new mysqli('localhost', 'username', 'password', 'database');
 return $dbh;
}

function writeBind() {
$date = new DateTime();
$texthead = '$TTL 60
@ IN SOA ns1.xxxx.xx. hostmaster.xxxx.xxx. (
 sedSerial ; serial, unix timestamp  #
 7200 ; refresh, seconds
 540 ; retry, seconds
 604800 ; expire, seconds
 3600 ) ; minimum, seconds
;'."\n\n";

$texthead = preg_replace("/sedSerial/",$date->getTimestamp(),$texthead);

$textbody = "@"."\t"."A"."\t"."1.1.1.1"."\n";

foreach (getHosts() as $host) {
 $textbody .= getUsernameById($host['userid'])."\t"."A"."\t".$host['ip']."\n";
}

$textfooter = '
@ NS ns2.xxx.xx.
@ NS ns1.xxx.xx.
';
$file = $texthead.$textbody.$textfooter;

file_put_contents("/etc/bind/dns.xxx.xx", $file);

system('/usr/sbin/rndc -q reload');

}

?>

Bind named.conf:

zone "dns.xxx.xx" {
 type master;
 allow-transfer {1.1.1.1;2:1:1::2;};
 file "/etc/bind/dns.xxx.xx";
};

Have fun!

Geiger Counter Visualisation

Hi there!

I build a visualisation (php javascript html5 chart.js mysql perl) for my Arduino Geiger Counter. Here some screenshots.

I’m no professional programmer. But it does what it should. Might someone find this useful.
Here the downloadlink.

https://www.paranoids.at/downloads/geiger-f5b7681.tar.gz

Just import the .sql files, connect the arduino via usb, change data formatting if needed and start logGeiger.pl.

Happy plotting :-)

ispconfig tlsa patch for dane using postfix

Hi There

I’ve added TLSA DNS RR support to my ispconfig server. This howto relies on my previous post which adds dnssec support to ispconfig. http://www.paranoids.at/bind9-ispconfig-dnssec-inline-signing-ubuntu-1204/

Actually I’m using ubuntu 14.04 with most recent version of ispconfig 3. With ubuntu 14.04 you don’t need the bind ppa cause bind version in 14.04 supports auto keyrollover for dnssec singed zones.

Simply copy the files as following:

cd /usr/local/ispconfig/interface/web/dns
cp -av dns_srv_edit.php  dns_tlsa_edit.php
cp -av form/dns_srv.tform.php form/dns_tlsa.tform.php
cp -av templates/dns_srv_edit.htm templates/dns_tlsa_edit.htm
cp -av lib/lang/de_dns_srv.lng templates/dns_tlsa_edit.htm

Then run the patches agains every file mentioned in the patch.

Here the patch for the interface:
http://www.paranoids.at/downloads/ispconfig-tlsa-interface-patch.txt
Here the patch for the server:
http://www.paranoids.at/downloads/ispconfig-tlsa-server-patch.txt

You also have to alter the table structure of dns_rr in dbispconfig. You only have to edit type as following:

`type` enum('A','AAAA','ALIAS','CNAME','HINFO','MX','NAPTR','NS','PTR','RP','SRV','TLSA','TXT') DEFAULT NULL

Here some nice Firefox tool to verify your dnssec and tlsa records: https://www.dnssec-validator.cz/

Here the config snippets from postfix’s main.cf:

smtp_dns_support_level = dnssec
smtp_tls_security_level = dane

Have fun!