How to build a router with radius authentication and web filtering

Intro

We asked me in my work to build a router for a school. Students need to authenticate with a login and a password in order to access to Internet. This way we could trace username through logs. The web access will be filtered with squidGuard. A web interface will allow teachers to create or remove accounts and to show trace from students web access.

Network diagram

Configuration

Installing

root@host:~# apt-get install freeradius mariadb-server freeradius-mysql freeradius-utils lighttpd

Settings

mariadb

Mariadb is a community-developed fork of the MySQL relational database management system.

Initialisation

CREATE DATABASE radius; GRANT ALL ON radius.* TO radius@localhost IDENTIFIED BY "secureradiuspassword"; flush privileges;
GRANT ALL PRIVILEGES ON radius.* TO radius@localhost IDENTIFIED BY "VakAp7wzMH"; flush privileges;
ALTER TABLE radcheck ADD COLUMN date_end DATETIME;

mysql -uroot -p radius < /etc/freeradius/sql/mysql/schema.sql

freeradius

/etc/freeradius/sql.conf

sql {   
        database = "mysql"
        driver = "rlm_sql_${database}"
        server = "localhost"
        port = 3306
        login = "root"
        password = "dbrvUWudw9"
        radius_db = "radius"
        acct_table1 = "radacct"
        acct_table2 = "radacct"
        postauth_table = "radpostauth"
        authcheck_table = "radcheck"
        authreply_table = "radreply"
        groupcheck_table = "radgroupcheck"
        groupreply_table = "radgroupreply"
        usergroup_table = "radusergroup"
        deletestalesessions = yes
        sqltrace = no
        sqltracefile = ${logdir}/sqltrace.sql
        num_sql_socks = ${thread[pool].max_servers}
        connect_failure_retry_delay = 60
        lifetime = 0
        max_queries = 0
        nas_table = "nas"
        $INCLUDE sql/${database}/dialup.conf
}

/etc/freeradius/sites-available/default

authorize {
        sql
        preprocess
        chap
        mschap
        digest
        suffix
        eap {   
                ok = return
        }
        files
        expiration
        logintime
        pap
}
authenticate {
        Auth-Type PAP {
                pap
        }
        Auth-Type CHAP {
                chap
        }
        Auth-Type MS-CHAP {
                mschap
        }
        digest
        unix
        eap
}
preacct {
        preprocess
        acct_unique
        suffix
        files
}
accounting {
        sql
        detail
        exec
        attr_filter.accounting_response
}
session {
        sql
        radutmp
}
post-auth {
        exec
        Post-Auth-Type REJECT {
                attr_filter.access_reject
        }
}
pre-proxy {
}
post-proxy {
        eap
}

/etc/freeradius/sites-available/inner-tunnel

server inner-tunnel {
listen {
       ipaddr = 127.0.0.1
       port = 18120
       type = auth
}
authorize {
        sql
        chap
        mschap
        suffix
        update control {
               Proxy-To-Realm := LOCAL
        }
        eap {   
                ok = return
        }
        files
        expiration
        logintime
        pap
}
authenticate {
        Auth-Type PAP {
                pap
        }
        Auth-Type CHAP {
                chap
        }
        Auth-Type MS-CHAP {
                mschap
        }
        unix
        eap
}
session {
        sql
        radutmp
}
post-auth {
        Post-Auth-Type REJECT {
                attr_filter.access_reject
        }
}
pre-proxy {
}
post-proxy {
        eap
}
} # inner-tunnel server block

/etc/freeradius/clients.conf

client localhost {
        ipaddr = 127.0.0.1
        secret          = 127localtesting127
        require_message_authenticator = no
}
client 10.0.0.59 {
        ipaddr = 10.0.0.59
        secret          = V4zTxc37vukxAr
        require_message_authenticator = no
}

Add a user : /etc/freeradius/users

DEFAULT Framed-Protocol == PPP
        Framed-Protocol = PPP,
        Framed-Compression = Van-Jacobson-TCP-IP
DEFAULT Hint == "CSLIP"
        Framed-Protocol = SLIP,
        Framed-Compression = Van-Jacobson-TCP-IP
DEFAULT Hint == "SLIP"
        Framed-Protocol = SLIP
testing Cleartext-Password := "password"

/etc/network/interfaces

auto wan
iface wan inet static
	address 192.168.10.10
	netmask 255.255.255.0
	gateway 192.168.10.1

auto wifi
iface wifi inet static
	address 10.64.0.254
	netmask 255.255.255.0
	up /usr/local/sbin/iptables.sh

/etc/freeradius/eap.conf

eap {
	default_eap_type = md5
	timer_expire     = 60
	ignore_unknown_eap_types = no
	cisco_accounting_username_bug = no
	max_sessions = ${max_requests}
	md5 {
	}
	leap {
	}
	gtc {
		auth_type = PAP
	}
	tls {
		certdir = /etc/letsencrypt/live/shebangthedolphins.net
		cadir = /etc/letsencrypt/live/shebangthedolphins.net
		private_key_file = ${certdir}/privkey.pem
		certificate_file = ${cadir}/cert.pem
		CA_file = ${cadir}/fullchain.pem
		dh_file = ${confdir}/certs/dh
		random_file = /dev/urandom
		CA_path = ${cadir}
		cipher_list = "DEFAULT"
		make_cert_command = "${certdir}/bootstrap"
		ecdh_curve = "prime256v1"
		cache {
		      enable = no
		      max_entries = 255
		}
		verify {
		}
		ocsp {
		      enable = no
		      override_cert_url = yes
		      url = "http://127.0.0.1/ocsp/"
		}
	}
	ttls {
		default_eap_type = md5
		copy_request_to_tunnel = no
		use_tunneled_reply = no
		virtual_server = "inner-tunnel"
	}
	peap {
		default_eap_type = mschapv2
		copy_request_to_tunnel = no
		use_tunneled_reply = no
		virtual_server = "inner-tunnel"
	}
	mschapv2 {
	}
}

let's encrypt certificate

In order to corectly work with windows we will use let's encrypt certificate

Source : https://www.nico-maas.de/ ; https://framebyframewifi.net/

letsencrypt renew --standalone --email support@shebangthedolphins.net -d shebangthedolphins.net

Useful commands

radtest testing password localhost 0 127localtesting127
freeradius -X
ldconfig

Squid

I'll use Squid as web proxie.

/etc/squid3/squid.conf

auth_param basic program /usr/lib/squid3/basic_radius_auth -f /etc/squid3/radius_config
auth_param basic children 5
auth_param basic realm Web-Proxy
auth_param basic credentialsttl 4 hours
auth_param basic casesensitive off

acl SSL_ports port 443
acl Safe_ports port 80          # http
acl Safe_ports port 21          # ftp
acl Safe_ports port 443         # https
acl Safe_ports port 70          # gopher
acl Safe_ports port 210         # wais
acl Safe_ports port 1025-65535  # unregistered ports
acl Safe_ports port 280         # http-mgmt
acl Safe_ports port 488         # gss-http
acl Safe_ports port 591         # filemaker
acl Safe_ports port 777         # multiling http
acl CONNECT method CONNECT
acl eleves src 10.64.0.0/24
acl radius-auth proxy_auth REQUIRED
acl localhost src 127.0.0.0/8
http_access deny !Safe_ports
http_access deny CONNECT !SSL_ports
http_access allow localhost manager
http_access allow eleves radius-auth
http_access deny manager
http_access allow localhost
http_access deny all
http_port 3128
coredump_dir /var/spool/squid3
refresh_pattern ^ftp:           1440    20%     10080
refresh_pattern ^gopher:        1440    0%      1440
refresh_pattern -i (/cgi-bin/|\?) 0     0%      0
refresh_pattern .               0       20%     4320
visible_hostname shebangthedolphins.local
cache_dir ufs /squid 15000 16 256
url_rewrite_program /usr/bin/squidGuard -c /etc/squidguard/squidGuard.conf
url_rewrite_children 5

/etc/squid3/radius_config

server 127.0.0.1
secret 127localtesting127

/var/www/pac/proxy.pac

function FindProxyForURL(url,host)
{
        return "PROXY 10.64.0.254:3128" ;
}

/var/www/pac/wpad.dat

function FindProxyForURL(url,host)
{
        return "PROXY 10.64.0.254:3128" ;
}

lighttpd

Enable cgi

lighttpd-enable-mod fastcgi

/etc/lighttpd/conf-enabled/10-cgi.conf

server.modules += ( "mod_cgi" )

$HTTP["url"] =~ "^/cgi-bin/" {
	cgi.assign = ( 
		".sh" => "/bin/bash", 
	)
}

/etc/lighttpd/lighttpd.conf

server.modules = (
        "mod_access",
        "mod_alias",
        "mod_compress",
        "mod_redirect",
        "mod_auth",
#       "mod_rewrite",
)

# ajout de l'authentification
auth.debug = 2
auth.backend = "plain"
auth.backend.plain.userfile = "/var/www/.lighttpdpassword"

$SERVER["socket"] == "192.168.10.10:80" {
        auth.require = ( "/" =>
        (
        "method" => "basic",
        "realm" => "Administration",
        "require" => "user=admin"
        )
        )
}

server.document-root        = "/var/www/html"
server.upload-dirs          = ( "/var/cache/lighttpd/uploads" )
server.errorlog             = "/var/log/lighttpd/error.log"
server.pid-file             = "/var/run/lighttpd.pid"
server.username             = "www-data"
server.groupname            = "www-data"
server.port                 = 80
server.bind                 = "192.168.10.10"

index-file.names            = ( "index.php", "index.html", "index.lighttpd.html" )
url.access-deny             = ( "~", ".inc" )
static-file.exclude-extensions = ( ".php", ".pl", ".fcgi", ".sh" )

compress.cache-dir          = "/var/cache/lighttpd/compress/"
compress.filetype           = ( "application/javascript", "text/css", "text/html", "text/plain" )

$SERVER["socket"] == "10.64.0.254:80" {
        server.document-root        = "/var/www/pac"
        server.errorlog             = "/var/log/lighttpd/pac/error.log"
        server.username             = "www-data"
        server.groupname            = "www-data"
}

# default listening port for IPv6 falls back to the IPv4 port
include_shell "/usr/share/lighttpd/use-ipv6.pl " + server.port
include_shell "/usr/share/lighttpd/create-mime.assign.pl"
include_shell "/usr/share/lighttpd/include-conf-enabled.pl"

dhcpd

/etc/dhcp/dhcpd.conf

ddns-update-style none;
option domain-name "example.org";
option domain-name-servers ns1.example.org, ns2.example.org;
default-lease-time 600;
max-lease-time 7200;
log-facility local7;
ddns-update-style none;
authoritative;
option autoproxy-script code 252 = text;
subnet 10.64.0.0 netmask 255.255.255.0 {
        range 10.64.0.20 10.64.0.250;
        option subnet-mask 255.255.255.0;
        option broadcast-address 10.64.0.255;
        option routers 10.64.0.254;
        option domain-name-servers 10.64.0.254;
        option domain-name "shebangthedolphins.local";
        option autoproxy-script "http://10.64.0.254/pac/proxy.pac";
}
host WiFiAP {
	hardware ethernet a0:2f:a8:19:8a:7c;
	fixed-address 10.64.0.10;
}

Code

/var/www/html/add_user.html

<html>
<head>
<title>Admin</title>
<meta content="text/html;charset=utf-8" http-equiv="Content-Type">
<meta content="utf-8" http-equiv="encoding">
</head>
<body>
<h1>Users management</h1>
<h2>Add users</h2>
<!--<form method='post' action='cgi-bin/add_user.sh' enctype='multipart/form-data'>
<input type='submit' value='Valider'>
</form> -->
<FORM ACTION="cgi-bin/add_user.sh" METHOD="GET">
        Nom : <INPUT TYPE="TEXT" NAME="nom"><br />
        Prénom : <INPUT TYPE="TEXT" NAME="prenom"><br />
        Date Fin (Format : jj/mm/aaaa ; un an par défaut)  : <INPUT TYPE="TEXT" NAME="date_fin"><br />
        <INPUT TYPE="SUBMIT" VALUE="AJOUTER">
</FORM>

<h2>List/Delete users</h2>
<ul>
<li><a href="cgi-bin/show_users.sh"><strong>Liste des utilisateurs</strong></a></li>
</ul>

<h2>Import users list from csv file</h2>
<h3>Instructions</h3>
<p>The file need to include three columns :
<ul>
	<li><b>first name</b></li>
	<li><b>last name</b></li>
	<li><b>date</b> format <b>dd/mm/yyyy</b> (optional, 1 year by default)</li>
</ul>
</p>
<form method='post' action='cgi-bin/deposer.sh' enctype='multipart/form-data'>
Fichier csv à importer <input type='file' name='upfile'><br>
<input type='submit' value='IMPORTER'>
</form>
<h2>Export users list from csv file</h2>
<FORM ACTION="cgi-bin/export.sh">
        <INPUT TYPE="SUBMIT" VALUE="EXPORTER">
</FORM>

<h1>Reporting</h1>
<ul>
<li><a href="awffull/index.html"><strong>Awffull</strong></a></li>
<li><a href="logs/"><strong>Logs</strong></a></li>
<li><a href="sarg/"><strong>Sarg</strong></a></li>
<!-- <li><a href="catamaris/report.html"><strong>Catamaris</strong></a></li>
<li><a href="webalizer/"><strong>Webalizer</strong></a></li> -->
</ul>

<h1>Restart server</h1>
<FORM ACTION="cgi-bin/reboot.sh">
        <INPUT TYPE="SUBMIT" VALUE="REBOOT">
</FORM>
</body>

/var/www/html/cgi-bin/add_user.sh

#! /bin/sh

echo "Content-type: text/html"
echo "" 
echo "<html>
<head><title>Add users</title></head>
<body>"
PRENOM=$(echo "$QUERY_STRING" | sed -n 's/^.*nom=.*prenom=\([^&]*\).*/\1/p' | sed -e "s/%20/ /g" -e "s/%E9/e/g" -e "s/%C9/e/g" -e "s/%E0/a/g" -e "s/%E7/c/g" | sed 's/[ +]/\./g')
NOM=$(echo "$QUERY_STRING" | sed -n 's/^.*nom=\([^&]*\).*prenom.*$/\1/p' | sed -e "s/%20/ /g" -e "s/%E9/e/g" -e "s/%C9/e/g" -e "s/%E0/a/g" -e "s/%E7/c/g" | sed 's/[ +]/\./g')
DATE=$(echo "$QUERY_STRING" | sed -n 's/^.*nom=.*prenom=.*date_fin=\([^&]*\).*/\1/p' | sed -e "s/%20/ /g" -e "s/%2F/\//g")
JOUR=$(echo "$DATE" | sed 's/\([0-9]\{1,2\}\)[/]\{,1\}[0-9]\{1,2\}[/]\{,1\}[0-9]\{2,4\}/\1/')
MOIS=$(echo "$DATE" | sed 's/[0-9]\{1,2\}[/]\{,1\}\([0-9]\{1,2\}\)[/]\{,1\}[0-9]\{2,4\}/\1/')
ANNEE=$(echo "$DATE" | sed 's/[0-9]\{1,2\}[/]\{,1\}[0-9]\{1,2\}[/]\{,1\}\([0-9]\{2,4\}\)/\1/')

if [ a"$PRENOM" != "a" ] && [ a"$NOM" != "a" ]; then
        if [ a"$DATE" = "a" ]; then
                DATE=$(/bin/date --date='1 year' '+%Y/%m/%d')
        else
                DATE="$ANNEE/$MOIS/$JOUR"
                /bin/date --date="$DATE" '+%Y%m%d' 2>/dev/null 1>/dev/null
                RES=$?
                if [ "$RES" -ne 0 ]; then
                        echo "error with date format"
                        echo "<meta HTTP-EQUIV=\"REFRESH\" content=\"4; url=/add_user.html\">"
                fi
        fi
        #MYSQL command to create a user
else 
        echo "User hasn't been created, one element is missing"
fi

LOGIN=$(echo "$PRENOM.$NOM" | tr '[:upper:]' '[:lower:]')

####PASSWORD CREATION
PASSGEN=$(grep "^[a-z]\{8\}$" /usr/share/dict/american-english | sort -R | head -n 1 | perl -p -e 'y/éèêâàïç/eeeaaic/' | sed 's/\(....\)..../\1/')
PASSGEN=$(grep "^[a-z]\{8\}$" /usr/share/dict/american-english | sort -R | head -n 1 | perl -p -e 'y/éèêâàïç/eeeaaic/' | sed 's/....\(....\)/\1/')"$PASSGEN"
num=5
PASSGEN="$PASSGEN"$(while [ "$num" -lt 10 ]; do num=$RANDOM; let 'num %= 100'; done; echo "$num")
####END PASSWORD CREATION

USER="radius"
PASSWORD="VakAp7wzMH"
USEBDD="radius"

mysql -u"$USER" -p"$PASSWORD" -e "USE "$USEBDD"; INSERT INTO radcheck (username , attribute , op , value, date_end ) VALUES ('"$LOGIN"','User-Password',':=','"$PASSGEN"','"$DATE"')"
RES=$?
if [ "$RES" -eq 0 ]; then
        echo "L'utilisateur <b>$PRENOM $NOM</b> a &eacute;t&eacute; cr&eacute;&eacute; :"
        echo "<ul><li>Login : <b>$LOGIN</b></li>" 
        echo "<li>Password : <b>$PASSGEN</b></li>" 
        echo "<li>End date : <b>"
        /bin/date --date="$DATE" '+%d/%m/%Y'
        echo "</b></li></ul>" 
else
        echo "error during user creation"
fi

echo "<FORM ACTION="/add_user.html">
        <INPUT TYPE="SUBMIT" VALUE="RETOUR">
</FORM>
"

echo "</body>"


/var/www/html/del_user.html

#! /bin/sh

echo "Content-type: text/html"
echo "" 
echo "<html>
<head>
<meta content=\"text/html;charset=utf-8\" http-equiv=\"Content-Type\">
<meta content=\"utf-8\" http-equiv=\"encoding\">
<title>Delete user</title></head>
<body>"


ID=$(echo "$QUERY_STRING" | sed -n 's/^.*id=\([^&]*\).*/\1/p' | sed "s/%20/ /g")


USER="radius"
PASSWORD="VakAp7wzMH"
USEBDD="radius"

function name_sql {
	LOGIN=$(mysql -u"$USER" -p"$PASSWORD" -e "USE "$USEBDD"; SELECT username FROM radcheck WHERE id="$1";")
	NAME=$(echo "$LOGIN" | sed -n 2p | awk -F: '{ print $1 }')
	echo "$NAME"
}
function delete_sql {
	for i in $(echo "$1" | sed 's/_/ /g'); do 
		echo "<br />"
		NAME=$(name_sql $i)
		mysql -u"$USER" -p"$PASSWORD" -e "USE \"$USEBDD\"; DELETE FROM radcheck WHERE id=\"$i\";"
		RES=$?
		if [ "$RES" -eq 0 ]; then
			echo "Le compte <b>$NAME</b> a &eacute;t&eacute; supprim&eacute;"
			echo ""
		else
			echo "erreur lors de la suppression de l'utilisateur"
		fi
	done
}

delete_sql "$ID"	
echo "<meta HTTP-EQUIV=\"REFRESH\" content=\"1; url=show_users.sh\">"


echo "<FORM ACTION="show_users.sh">
        <INPUT TYPE="SUBMIT" VALUE="RETOUR">
</FORM>
"

echo ""

/var/www/html/del_user.sh

#! /bin/sh

echo "Content-type: text/html"
echo "" 
echo "<html>
<head>
<meta content=\"text/html;charset=utf-8\" http-equiv=\"Content-Type\">
<meta content=\"utf-8\" http-equiv=\"encoding\">
<title>Supprimer un compte</title></head>
<body>"


ID=$(echo "$QUERY_STRING" | sed -n 's/^.*id=\([^&]*\).*/\1/p' | sed "s/%20/ /g")


USER="radius"
PASSWORD="VakAp7wzMH"
USEBDD="radius"

function name_sql {
	LOGIN=$(mysql -u"$USER" -p"$PASSWORD" -e "USE "$USEBDD"; SELECT username FROM radcheck WHERE id="$1";")
	NAME=$(echo "$LOGIN" | sed -n 2p | awk -F: '{ print $1 }')
	echo "$NAME"
}
function delete_sql {
	for i in $(echo "$1" | sed 's/_/ /g'); do 
		echo "<br />"
		NAME=$(name_sql $i)
		mysql -u"$USER" -p"$PASSWORD" -e "USE \"$USEBDD\"; DELETE FROM radcheck WHERE id=\"$i\";"
		RES=$?
		if [ "$RES" -eq 0 ]; then
			echo "Le compte <b>$NAME</b> a &eacute;t&eacute; supprim&eacute;"
			echo ""
		else
			echo "erreur lors de la suppression de l'utilisateur"
		fi
	done
}

delete_sql "$ID"	
echo "<meta HTTP-EQUIV=\"REFRESH\" content=\"1; url=show_users.sh\">"


echo "<FORM ACTION="show_users.sh">
        <INPUT TYPE="SUBMIT" VALUE="RETOUR">
</FORM>
"

echo "</body>"

/var/www/html/deposer.sh

#!/bin/bash

read boundary
read disposition
read ctype
read junk

a=${#boundary}
b=${#disposition}
c=${#ctype}
t=$((a*2+b+c+d+10))
SIZE=$((CONTENT_LENGTH-t))

echo "Content-Type: text/html"
echo ""

FILENAME=`echo "$disposition"|sed 's/.*filename="//'|sed 's/".*$//'`

DEST=`/bin/mktemp`
/bin/dd ibs=1 obs=512 count=$SIZE of=$DEST

cat "$DEST" | od -c > /tmp/od
for i in $(cat "$DEST" | tr '[:upper:]' '[:lower:]' | sed 'y/\xE7\xE9\xE8\xEA\x20\xE0\xE1\xE2/ceee_aaa/' | sed -e 's/\x0D//'); do 
        PASSGEN=$(grep "^[a-z]\{8\}$" /usr/share/dict/french | sort -R | head -n 1 | perl -p -e 'y/éèêâàïç/eeeaaic/' | sed 's/^\([a-z]\{4\}\).*/\1/')
        PASSGEN=$(grep "^[a-z]\{8\}$" /usr/share/dict/french | sort -R | head -n 1 | perl -p -e 'y/éèêâàïç/eeeaaic/' | sed 's/^....\([a-z]\{4\}\)/\1/')"$PASSGEN"
        num=5
        PASSGEN="$PASSGEN"$(while [ "$num" -lt 10 ]; do num=$RANDOM; let 'num %= 100'; done; echo "$num")
        
        NOM=$(echo "$i" | awk -F ';' '{print $1"."$2}')
        DATE=$(date -d "$(echo "$i" |awk -F ';' '{print $3}'|awk -F / '{print $3$2$1}')" '+%Y/%m/%d')
        if [ a"$DATE" = "a" ]; then
                DATE=$(/bin/date --date='1 year' '+%Y/%m/%d')
        else
                /bin/date --date="$DATE" '+%Y%m%d' 2>/dev/null 1>/dev/null
                RES=$?
                if [ "$RES" -ne 0 ]; then
                        echo "ERREUR DATE MAL RENSEIGNEE"
                        echo "<meta HTTP-EQUIV=\"REFRESH\" content=\"4; url=/add_user.html\">"
                fi
        fi

        echo -e "l'utilisateur <b>"$NOM"</b> avec le mot de passe <b>"$PASSGEN"</b> a &eacute;t&eacute; ajout&eacute;e<br />"
        ####FIN GENERATION DU MOT DE PASSE

        USER="radius"
        PASSWORD="VakAp7wzMH"
        USEBDD="radius"

        mysql -u"$USER" -p"$PASSWORD" -e "USE "$USEBDD"; INSERT INTO radcheck (username , attribute , op , value, date_end ) VALUES ('"$NOM"','User-Password',':=','"$PASSGEN"','"$DATE"')"
done

echo "<p>Importation termin&eacute;e vous allez &ecirc;tre redirig&eacute;</p>"
echo "<meta HTTP-EQUIV=\"REFRESH\" content=\"4; url=/add_user.html\">"

/var/www/html/export.sh

#! /bin/bash

DEST="/var/www/html/export/export.csv"

USER="radius"
PASSWORD="VakAp7wzMH"
USEBDD="radius"

if [ -f "$DEST" ]; then
        rm "$DEST"
fi
mysql -u"$USER" -p"$PASSWORD" -e "USE "$USEBDD"; SELECT username AS 'Utilisateur', value AS 'Password', DATE_FORMAT(date_end,'%d/%m/%Y') AS Date FROM radcheck INTO OUTFILE '"$DEST"' FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\"' LINES TERMINATED BY '\r\n';"
RES=$?
if [ "$RES" -eq 0 ]; then
        contentLength=$(wc -c < "$DEST")
        echo "Content-Type: text/csv"
        echo "Content-Length: $contentLength"
        echo 'Content-Disposition: attachment; filename="export.csv"'
        echo  # End of headers.
        cat "$DEST"
else
        echo "Content-Type: text/html"
        echo""
        echo "erreur lors de l'export"
        echo "<meta HTTP-EQUIV=\"REFRESH\" content=\"4; url=/add_user.html\">"
fi

/var/www/html/reboot.sh

#!/bin/bash

echo "Content-Type: text/html"
echo ""

sudo /sbin/reboot

echo "<p>Redémarrage du serveur</p>"

/var/www/html/show_users.sh

#! /bin/bash

echo "Content-type: text/html"
echo "" 
echo "<html>
<head>
<meta content=\"text/html;charset=utf-8\" http-equiv=\"Content-Type\">
<meta content=\"utf-8\" http-equiv=\"encoding\">
<script src=\"jquery-3.1.1.min.js\"></script>
<title>Afficher la liste des utilisateurs</title>
<link href=\"../001.css\" type=\"text/css\" rel=\"StyleSheet\" /></head>
<body>"

#<script src=\"https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js\"></script>
#<script src=\"my_jquery_functions.js\"></script>

echo "<script>
//appeler fonction + ouvrir page avec suppression des users : http://127.0.0.1:8080/cgi-bin/del_user.sh?id=325
//window.location.href = \"del_user.sh\"
function getvalue_func() {
        return \$('#div_table input:checkbox:checked').map(function() {
                \$(\"#\" + this.value).css(\"background-color\", \"red\");
                return this.value;
        }).get().join('_');
}
function getvalue_funch() {
        return \$('#div_table input:checkbox:not(:checked)').map(function() {
                \$(\"#\" + this.value).css(\"background-color\", \"\"); //on supprime le rouge sur les box décochées
        });
}

\$(document).ready(function(){
//DEBUT surligner en rouge la ligne
        \$('input[type=\"checkbox\"]').change(function() {
            // this function will get executed every time the #home element is clicked (or tab-spacebar changed)
            if(\$(this).is(\":checked\")) // \"this\" refers to the element that fired the event
            {
                getvalue_func();                        ////on appelle la fonction getvalue_fun() si on check
                //var TEST = getvalue_func();           //on appelle la fonction qui récupère les ids cochés
                //var TOTO = \$(\"input[type=checkbox][checked]\");
            } else {
                getvalue_funch();       //on appelle la fonction getvalue_funch() si on uncheck
                //var TEST = getvalue_funch();          //on appelle la fonction qui récupère les ids cochés
                //var TOTO = \$(\"input[type=checkbox][unchecked]\");
                }
        });
//FIN surligner en rouge la ligne
        \$(\".sup_mul\").click(function(){              //quand on clique sur un des deux boutons "Suppression multiples"
                var NUM = getvalue_func();              //on appelle la fonction qui récupère les ids cochés
                window.location.href = \"del_user.sh\?id=\" + NUM  //on ouvre la page del_user.sh avec les ids cochés
        });     
});     
</script>"

echo "<FORM ACTION="/add_user.html">
        <INPUT TYPE="SUBMIT" VALUE="RETOUR">
</FORM>
"
echo "<button id=\"sup_mul1\" class=\"sup_mul\">Suppression Multiple</button>"

LOGIN=$(echo "$PRENOM.$NOM" | tr '[:upper:]' '[:lower:]')
USER="radius"
PASSWORD="VakAp7wzMH"
USEBDD="radius"

QUERY=$(mysql -u"$USER" -p"$PASSWORD" -e "USE "$USEBDD"; SELECT id, username AS 'Utilisateur', value AS 'Password', DATE_FORMAT(date_end,'%d/%m/%Y') AS Date FROM radcheck ;")

ARRAY=( $( for i in "$QUERY" ; do echo "$i" ; done ) )
ROW_CLASS="row_off"

echo "<table id=\"div_table\" border=\"0\">"
echo "<tr>"
echo "<td><b>Utilisateur</b></td>"
echo "<td><b>Password</b></td>"
echo "<td><b>Date</b></td>"
echo "<td><b>Suppression</b></td>"
echo "</tr>"
for (( i=5; i<"${#ARRAY[@]}"; i="$i" ))
do
        SUP=${ARRAY[$i-1]}
        if [ "$ROW_CLASS" == "row_off" ]; then
                ROW_CLASS="row_on"
        else
                ROW_CLASS="row_off"
        fi
        echo "<tr id=\"$SUP\" class="$ROW_CLASS">"
        for (( a=0; a<3; a++ ))
        do
                echo "<td><b>${ARRAY[$i]}</b></td>"
                i="$i"+1
        done 
        echo "<td><FORM ACTION=\"del_user.sh\" METHOD=\"GET\">
                <INPUT TYPE=\"HIDDEN\" NAME=\"id\" VALUE=\"$SUP\">
                <INPUT TYPE=\"SUBMIT\" ALIGN=\"top\" VALUE=\"Suppression\">
                </FORM></td>"
        echo "<td>
                <INPUT TYPE=\"checkbox\" NAME=\"id2\" ALIGN=\"top\" VALUE=\"$SUP\">
                </td>"
        #echo "<td>"$SUP"</td>" # afficher l'id de chaque entrée
        i="$i"+1
        echo "</tr>"
done
echo "</table>"

echo "<button id=\"sup_mul2\" class=\"sup_mul\">Suppression Multiple</button>"
echo "<br /><br />"
echo "<FORM ACTION="/add_user.html">
        <INPUT TYPE="SUBMIT" VALUE="RETOUR">
</FORM>"
echo "</body>"
echo "</html>"

netfilter/iptables

#!/bin/sh -u

IPT=/sbin/iptables
TC=/sbin/tc
IP=/sbin/ip
RTE=/sbin/route
MODPROBE=/sbin/modprobe

WAN1=wan
WIFI_NET=10.64.0.0/24
WIFI_DL=3700
WIFI_DL=3700
WIFI_IFB=ifb0

QUANTUM=1514

$IPT -F
$IPT -X
$IPT -t nat -F
$IPT -t nat -X
$IPT -t mangle -F
$IPT -t mangle -X
$IPT -P INPUT DROP
$IPT -P FORWARD DROP
#$IPT -A OUTPUT -j DROP

$MODPROBE nf_conntrack_ftp
$MODPROBE nf_nat_ftp

# WiFi AP
$IPT -A FORWARD -s 10.64.0.10 -m mac --mac-source a0:2f:a8:19:8a:7c -j ACCEPT
$IPT -A FORWARD -d 10.64.0.10 -j ACCEPT #mac-destination n'existe pas dans iptables

# RDP access to 192.168.10.200
$IPT -A FORWARD -s 10.64.0.0/24 -d 192.168.10.200 -p tcp --dport 3389 -m state --state NEW,RELATED,ESTABLISHED -j ACCEPT
$IPT -A FORWARD -d 10.64.0.0/24 -s 192.168.10.200 -p tcp --sport 3389 -m state --state RELATED,ESTABLISHED -j ACCEPT

# DROP access to ADMIN from STUDENTS
$IPT -A OUTPUT -o wifi -s 10.64.0.0/24 -d 192.168.10.0/24 -j DROP

# nat if no proxy set
$IPT -t nat -A PREROUTING -s 10.64.0.0/24 -p tcp --dport 80 -j DNAT --to-destination 10.64.0.254:80
$IPT -t nat -A PREROUTING -s 10.64.0.0/24 -p tcp --dport 443 -j DNAT --to-destination 10.64.0.254:80

# nat
$IPT -t nat -A POSTROUTING -o wan -j MASQUERADE

# icmp
$IPT -A INPUT -p icmp -j ACCEPT
$IPT -A FORWARD -p icmp -j ACCEPT

# ADMIN services 
$IPT -A INPUT -i wan -p tcp -s 192.168.10.0/24,78.245.84.127 --dport 22 -j ACCEPT
$IPT -A OUTPUT -o wan -p tcp --sport 22 -j ACCEPT
$IPT -A INPUT -i wan -p tcp --dport 443 -j ACCEPT
$IPT -A OUTPUT -o wan -p tcp --sport 443 -j ACCEPT
# accès web
$IPT -A INPUT -i wan -p tcp --dport   80 -j ACCEPT

# STUDENTS services
## INPUT
$IPT -A INPUT -i wifi -p tcp --dport 3128 -j ACCEPT
$IPT -A INPUT -i wifi -p tcp --dport   80 -j ACCEPT
$IPT -A INPUT -i wifi -p tcp --dport   443 -j ACCEPT
$IPT -A INPUT -i wifi -p tcp --dport   53 -j ACCEPT
$IPT -A INPUT -i wifi -p udp --dport   53 -j ACCEPT
$IPT -A INPUT -i wifi -p udp --dport   67 -j ACCEPT
$IPT -A INPUT -i wifi -p tcp --dport   68 -j ACCEPT
$IPT -A INPUT -i wifi -p udp --dport   67 -j ACCEPT
$IPT -A INPUT -i wifi -p tcp --dport   68 -j ACCEPT
# radius Authentication Server
$IPT -A INPUT -i wifi -p udp --dport   1812 -j ACCEPT
# radius Accounting Server
$IPT -A INPUT -i wifi -p udp --dport   1813 -j ACCEPT
# services internes ELEVES
## OUTPUT
$IPT -A OUTPUT -o wifi -p tcp --sport 3128 -j ACCEPT

# established connexions + lo
$IPT -A INPUT -i lo -j ACCEPT
$IPT -A INPUT -j NFLOG --nflog-group 0
$IPT -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT
$IPT -t nat -A PREROUTING  -s 10.64.0.0/24 -j NFLOG --nflog-group 0
$IPT -A FORWARD -m state --state RELATED,ESTABLISHED -j NFLOG --nflog-group 0
$IPT -A FORWARD -m state --state RELATED,ESTABLISHED -j ACCEPT
$IPT -A OUTPUT -j NFLOG --nflog-group 0

# QoS
# traffic shaping et routes
$MODPROBE ifb numifbs=2
$MODPROBE sch_fq_codel
$MODPROBE act_mirred

for IFB in ifb0 ; do
        $IP link set dev $IFB down
done

for IF in wan ifb0 ; do
        $TC qdisc del dev $IF root    2> /dev/null > /dev/null
        $TC qdisc del dev $IF ingress 2> /dev/null > /dev/null
done

$IPT -t mangle -F

for IFB in ifb0 ; do
        $IP link set dev $IFB up
done

# WIFI
### UPLOAD - ~160 students max
$TC qdisc add dev wan root handle 10: htb default 15
$TC class add dev wan parent 10: classid 10:1 htb rate 2mbit ceil 6mbit
### DEFAULT 30
$TC class add dev wan parent 10:1 classid 10:15 htb rate 2mbit ceil 6mbit # default
$TC filter add dev wan protocol ip parent 10:0 prio 1 u32 match ip src 10.64.0.0/24 flowid 10:

# Martin Devera, author of HTB, then recommends SFQ for beneath these classes:
$TC qdisc add dev wan parent 1:30 handle 30: sfq perturb 10

## DOWNLOAD
$TC qdisc add dev ifb0 root handle 20: htb default 15
$TC class add dev ifb0 parent 20: classid 20:1 htb rate 20mbit
### DEFAULT 50
$TC class add dev ifb0 parent 20:1 classid 20:15 htb rate 10mbit ceil 20mbit # default
$TC filter add dev ifb0 protocol ip parent 20:0 prio 1 u32 match ip dst 10.64.0.0/24 flowid 20:50
## Miror
$TC qdisc add dev wan ingress handle ffff:
$TC filter add dev wan parent ffff: protocol all u32 match u32 0 0 action mirred egress redirect dev ifb0

# Martin Devera, author of HTB, then recommends SFQ for beneath these classes:
$TC qdisc add dev wan parent 20:15 handle 15: sfq perturb 10
Licence Creative Commons
This website http://shebangthedolphins.net is licensed to the public under a licence Creative Commons Attribution licence.
Contact :