Aerospace Software Ltd.

Pre-Paid Card How-To

Scope

This guide explains how to handle Pre-Paid Cards for a RADIUS authenticated system. The idea is to create a simple comma delimited text file that a commercial printer can use to print tickets and fill the RADIUS SQL database with the data.

In a typical Paid For online system, the user buys a ticket and when he tries to go online, the system diverts him to a login screen where he has to enter the username and password printed on the ticket. This is authenticated by the RADIUS server and the user then gets access to the system until his ticket expires.

RADIUS Tables

First we need to get an idea of which tables are used for what. Here is a summary of all the information we need to create users and groups:

# mysql
mysql> use radius;

mysql> show tables;
+------------------+
| Tables_in_radius |
+------------------+
| nas              |
| radacct          |
| radcheck         |
| radgroupcheck    |
| radgroupreply    |
| radpostauth      |
| radreply         |
| usergroup        |
+------------------+
8 rows in set (0.00 sec)

mysql> show columns from usergroup;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned |      | PRI | NULL    | auto_increment |
| UserName  | varchar(64)      |      | MUL |         |                |
| GroupName | varchar(64)      |      |     |         |                |
+-----------+------------------+------+-----+---------+----------------+
3 rows in set (0.07 sec)

mysql> select * from usergroup;
+----+----------+-----------+
| id | UserName | GroupName |
+----+----------+-----------+
|  1 | herman   | support   |
|  2 | dean     | support   |
|  3 | pat      | support   |
|  4 | guest    | users     |
+----+----------+-----------+
4 rows in set (0.40 sec)

mysql> select * from radreply;
+----+----------+-----------------+----+-----------------------------+
| id | UserName | Attribute       | op | Value                       |
+----+----------+-----------------+----+-----------------------------+
|  1 | guest    | Reply-Message   | =  | Hello User Guest            |
|  2 | herman   | Reply-Message   | =  | Hello Support Group, Herman |
|  3 | dean     | Reply-Message   | =  | Hello Support Group, Dean   |
|  4 | pat      | Reply-Message   | =  | Hello Support Group, Pat    |
|  5 | herman   | Session-Timeout | =  | 8h                          |
+----+----------+-----------------+----+-----------------------------+
5 rows in set (0.08 sec)

mysql> select * from radgroupreply;
+----+-----------+---------------+----+---------------------+------+
| id | GroupName | Attribute     | op | Value               | prio |
+----+-----------+---------------+----+---------------------+------+
|  1 | support   | Reply-Message | =  | Hello Support Group |    0 |
|  2 | users     | Reply-Message | =  | Hello User Group    |    0 |
+----+-----------+---------------+----+---------------------+------+
2 rows in set (0.41 sec)mysql>

mysql> select * from radcheck;
+----+----------+---------------+----+---------+
| id | UserName | Attribute     | op | Value   |
+----+----------+---------------+----+---------+
|  1 | herman   | User-Password | := | secret  |
|  2 | guest    | User-Password | := | guest   |
|  3 | dean     | User-Password | := | secret1 |
|  4 | pat      | User-Password | := | secret2 |
+----+----------+---------------+----+---------+
4 rows in set (0.05 sec)

Random Passwords

The 'passgen' utility is available from http://www.linuxbuilt.com/software/passgen/. This utility is simple, no nonsense, generates pronounceble passwords and it is very fast. The APG generator for example, is very good, but unusably slow if you need a batch of 1000 passwords.

Do the usual drill to install it:

# tar -jxvf passgen-0.4.tar.bz2
# cd pass[tab]
# ./configure
# make
# checkinstall
# rpm -i passgen-0.4-1.i386.rpm

Now you can test it manually:

# passgen -g 1000 -l 6

to rapidly generate 1000 passwords.

CSV Username and Password Generator

We need a simple script to generate unique usernames and random passwords. The easiest way to create unique usernames, is to use consecutive numbers. For a simple human readable way to identify who a batch of cards are for, we can prefix the usernames with an alpha code. Here is a simple script called 'usergen' that will output a CSV file:

#! /bin/bash
# Generate a batch of 1000 usernames and passwords
# for a Pre-Paid Card system

# This script is rather crude.
# The idea is that it will generate a new batch of
# username data each time you run it and that it will
# remember the last run, so usernames will be consecutive
# and won't repeat.

# Presets:
# /tmp/usergenname: The username prefix, eg. 'S'
# /tmp/usergennum: The first username suffix, eg. '1000'
# /tmp/usergen.csv: Comma separated value file
# File format: username,password,sessiontime

# The session time defaults to 1h, change it below
# The number of passcodes to generate is set to 1000
# which should be enough for a batch of cards.

# Initialize the presets with:
# echo -n "1000">/tmp/usergennum
# echo -n "S">/tmp/usergenname

# Cleanup
rm -f /tmp/usergen.csv

# Session Time
TIME="1h"
echo "Session Time=$TIME"

# Read the last username
USERNAME="$(cat /tmp/usergenname)"
#echo Username=$USERNAME
USERNUM="$(cat /tmp/usergennum)"
echo Usernum=$USERNUM
echo First Username=$USERNAME$USERNUM

# Number of Passcodes to Generate
MAXCOUNT=1000
count=1

echo "Generate $MAXCOUNT Usernames:"

while [ "$count" -le $MAXCOUNT ]
do
  # Numerals and upper case letters, remove similar looking ones
  PASS=$(/usr/local/bin/passgen -g 1 -l 6)
  #echo Password=$PASS

  # Output results to the screen and to a file
  echo $USERNAME$USERNUM,$PASS,$TIME
  echo $USERNAME$USERNUM,$PASS,$TIME >> /tmp/usergen.csv

  # Increment
  let "count += 1"
  let "USERNUM += 1"
done

# Save last username in /tmp directory
echo $USERNAME > /tmp/usergenname
echo $USERNUM > /tmp/usergennum

echo Done!

This script is not ideal, but it is a start. The next step is to create a script that will not only generate a CSV file for the commercial printers, but which will also update the RADIUS database.

SQL Username and Password Generator

This script is an improved version of the previous example. It tries to ensure that passwords won't obviously repeat and creates SQL insert statements in file /tmp/usergen.sql which can be used to feed the data into the radius database:

#! /bin/bash
# Generate a batch of 1000 usernames and passwords
# for a Pre-Paid Card system

# This script is rather crude.
# The idea is that it will generate a new batch of
# username data each time you run it and that it will
# remember the last run, so usernames will be consecutive
# and won't repeat.

# Files:
# /tmp/usergenname: The username prefix, eg. 'S'
# /tmp/usergennum: The first username suffix, eg. '1000'
# /tmp/usergen.csv: Comma separated value file
# File format: username,password,sessiontime
# /tmp/usergen.sql: SQL insert command file.

# The session time defaults to 1h, change it below
# The number of passcodes to generate is set to 1000
# which should be enough for a batch of cards.

# Initialize the presets with:
# echo -n "1000">/tmp/usergennum
# echo -n "S">/tmp/usergenname

# Cleanup
rm -f /tmp/usergen.csv
rm -f /tmp/usergen.sql

# Session Time
TIME="1h"
echo "Session Time=$TIME"

# Read the last username
USERNAME="$(cat /tmp/usergenname)"
#echo Username=$USERNAME
USERNUM="$(cat /tmp/usergennum)"
echo Usernum=$USERNUM
echo First Username=$USERNAME$USERNUM

# Number of Passcodes to Generate
MAXCOUNT=1000
COUNT=1

echo "Generate $MAXCOUNT Usernames:"

while [ "$COUNT" -le $MAXCOUNT ]
do
  # Random generators usually have trouble in a script
  # Passgen also needs some help to reduce obvious repeats
  # Run it a random number of times and use the last value returned
  MAXSEED=$RANDOM
  let "MAXSEED %= 10"
  let "MAXSEED += 10"
  SEED=1
  while [ "$SEED" -le $MAXSEED ]
  do
    PASS=$(/usr/local/bin/passgen -g 1 -l 6)
    #echo Password=$PASS

    let "SEED += 1"
  done

  # Output results to the screen and to a CSV file
  echo $USERNAME$USERNUM,$PASS,$TIME
  echo $USERNAME$USERNUM,$PASS,$TIME >> /tmp/usergen.csv

  # Output results to a SQL command file
  echo "insert into usergroup (UserName,GroupName)
values (\"$USERNAME$USERNUM\",\"users\");" >> /tmp/usergen.sql
  echo "insert into radreply (UserName,Attribute,op,Value)
values (\"$USERNAME$USERNUM\",\"Reply-Message\",\"=\",\"Hello user
$USERNAME$USERNUM\");" >> /tmp/usergen.sql
  echo "insert into radreply (UserName,Attribute,op,Value)
values (\"$USERNAME$USERNUM\",\"Session-Timeout\",\"=\",\"$TIME\");"
>> /tmp/usergen.sql
  echo "insert into radcheck (UserName,Attribute,op,Value)
values (\"$USERNAME$USERNUM\",\"User-Password\",\":=\",\"$PASS\");" >>
/tmp/usergen.sql

  # Increment
  let "COUNT += 1"
  let "USERNUM += 1"
done

# Save last username in /tmp directory
echo $USERNAME > /tmp/usergenname
echo $USERNUM > /tmp/usergennum

# Comments
echo "Comments:"
echo "Quality Assurance: Give the output files a look over
before using them"
echo "Send file /tmp/usergen.csv to a ticket printer"
echo "Feed file /tmp/usergen.sql into the radius database"
echo "Example: # mysql -uroot -ppass -Dradius < /tmp/usergen.sql"

echo Done!

Now take the CSV file to your local print shop and you are in business. Don't try to print tickets yourself - it is a PITA and really not worth the effort. Rather give it to Minuteprint or Kinkos to print on business card stock.

La voila!