summaryrefslogtreecommitdiffstats
path: root/scripts/sql
diff options
context:
space:
mode:
Diffstat (limited to '')
-rwxr-xr-xscripts/sql/align_sql_pools.pl311
-rwxr-xr-xscripts/sql/generate_pool_addresses.pl456
-rwxr-xr-xscripts/sql/radsqlrelay342
-rwxr-xr-xscripts/sql/rlm_sqlippool_tool961
-rw-r--r--scripts/sql/users2mysql.pl157
5 files changed, 2227 insertions, 0 deletions
diff --git a/scripts/sql/align_sql_pools.pl b/scripts/sql/align_sql_pools.pl
new file mode 100755
index 0000000..04d2d4b
--- /dev/null
+++ b/scripts/sql/align_sql_pools.pl
@@ -0,0 +1,311 @@
+#!/usr/bin/perl -Tw
+
+######################################################################
+#
+# Copyright (C) 2020 Network RADIUS
+#
+# $Id$
+#
+######################################################################
+#
+# Helper script for generating the SQL commands to align the SQL IP pools in a
+# database with a given specification.
+#
+# The radippool table is updated is a way that preserves existing leases,
+# provided that the corresponding IP addresses still exist in their pool.
+#
+# This script typically receives the output of the generate_pool_addresses.pl
+# script, as follows:
+#
+# generate_pool_addresses.pl <options> | align_sqlippools.pl <sql_dialect>
+#
+# For example:
+#
+# generate_pool_addresses.pl main_pool 10.0.1.0 10.0.1.255 | \
+# align_sqlippools.pl mariadb
+#
+# generate_pool_addresses.pl yaml pool_defs.yml existing_ips.txt | \
+# align_sqlippools.pl postgresql
+#
+# For the latter example the existing_ips.txt file might be created as
+# follows:
+#
+# psql radius -qtAc 'SELECT framedipaddress FROM radippool' > existing_ips.txt
+#
+# Note: The generate_pool_addresses.pl script describes the input format
+# expected by this script (as well as the format of the pool_defs.yml and
+# existing_ips.txt files.)
+#
+# Output:
+#
+# The output of this script is the SQL command sequence for aligning the pools
+# with the definition provided, which you should review before running them
+# against your database.
+#
+
+use strict;
+use Template;
+
+my %template=load_templates();
+
+if ($#ARGV != 0) {
+ print STDERR <<'EOF';
+Usage: generate_pool_addresses.pl ... | align_sqlippools.pl <dialect>
+
+EOF
+ exit 1;
+}
+
+my $dialect=$ARGV[0];
+
+unless (defined $template{$dialect}) {
+ print STDERR "Unknown dialect. Pick one of: ";
+ print STDERR "$_ " foreach sort keys %template;
+ print STDERR "\n";
+ exit 1;
+}
+
+my @ips=();
+
+my $line = 0;
+while (<STDIN>) {
+ $line++;
+
+ chomp;
+
+ next if $_ =~ /^#/ || $_ =~ /^\s*$/;
+
+ # The SQL works out what addresses to remove by itself
+ next if $_ =~ /^-/;
+
+ (my $action, my $pool_name, my $ip) = $_ =~ /^(.)\s+(.+)\s+([^\s]+)$/;
+
+ unless (defined $ip) {
+ warn "Unrecognised line $line: $_";
+ next;
+ }
+
+ push @ips, { poolname => $pool_name, ip => $ip };
+
+}
+
+my $tt=Template->new();
+$tt->process(\$template{$dialect}, {ips => \@ips, batchsize => 100}) || die $tt->error();
+
+exit 0;
+
+
+#
+# SQL dialect templates
+#
+
+sub load_templates {
+
+ my %template;
+
+#
+# MariaDB
+#
+ $template{'mariadb'} = <<'END_mariadb';
+-- Temporary table holds the provided IP pools
+DROP TEMPORARY TABLE IF EXISTS radippool_temp;
+CREATE TEMPORARY TABLE radippool_temp (
+ id int(11) unsigned NOT NULL auto_increment,
+ pool_name varchar(30) NOT NULL,
+ framedipaddress varchar(15) NOT NULL,
+ PRIMARY KEY (id),
+ KEY pool_name_framedipaddress (pool_name,framedipaddress)
+);
+
+-- Populate the temporary table
+[%- FOREACH m IN ips %]
+[%- "\n\nINSERT INTO radippool_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% m.poolname %]','[% m.ip %]');
+[%- ELSE %]
+('[% m.poolname %]','[% m.ip %]'),
+[%- END %]
+[%- END %]
+
+START TRANSACTION;
+
+-- Delete old pools that have been removed
+DELETE r FROM radippool r
+ LEFT JOIN radippool_temp t USING (pool_name,framedipaddress)
+ WHERE t.id IS NULL;
+
+-- Add new pools that have been created
+INSERT INTO radippool (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM radippool_temp t WHERE NOT EXISTS (
+ SELECT * FROM radippool r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );
+
+COMMIT;
+END_mariadb
+
+
+#
+# PostgreSQL
+#
+ $template{'postgresql'} = <<'END_postgresql';
+-- Temporary table holds the provided IP pools
+DROP TABLE IF EXISTS radippool_temp;
+CREATE TEMPORARY TABLE radippool_temp (
+ pool_name varchar(64) NOT NULL,
+ FramedIPAddress INET NOT NULL
+);
+CREATE INDEX radippool_temp_idx ON radippool_temp USING btree (pool_name,FramedIPAddress);
+
+-- Populate the temporary table
+[%- FOREACH m IN ips %]
+[%- "\n\nINSERT INTO radippool_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% m.poolname %]','[% m.ip %]');
+[%- ELSE %]
+('[% m.poolname %]','[% m.ip %]'),
+[%- END %]
+[%- END %]
+
+START TRANSACTION;
+
+-- Delete old pools that have been removed
+DELETE FROM radippool r WHERE NOT EXISTS (
+ SELECT FROM radippool_temp t
+ WHERE t.pool_name = r.pool_name AND t.framedipaddress = r.framedipaddress
+);
+
+-- Add new pools that have been created
+INSERT INTO radippool (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM radippool_temp t WHERE NOT EXISTS (
+ SELECT * FROM radippool r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );
+
+COMMIT;
+END_postgresql
+
+#
+# Oracle
+#
+ $template{'oracle'} = <<'END_oracle';
+-- Temporary table holds the provided IP pools
+CREATE GLOBAL TEMPORARY TABLE radippool_temp (
+ pool_name VARCHAR(30) NOT NULL,
+ FramedIPAddress VARCHAR(15) NOT NULL
+) ON COMMIT DELETE ROWS;
+CREATE INDEX radippool_temp_idx ON radippool_temp (pool_name,FramedIPAddress);
+
+-- Populate the temporary table
+[%- FOREACH m IN ips %]
+[%- "\nINSERT INTO radippool_temp (pool_name,framedipaddress) VALUES " %]('[% m.poolname %]','[% m.ip %]');
+[%- END %]
+
+-- Delete old pools that have been removed
+DELETE FROM radippool WHERE (pool_name, framedipaddress)
+ NOT IN (SELECT pool_name, framedipaddress FROM radippool_temp);
+
+-- Add new pools that have been created
+INSERT INTO radippool (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM radippool_temp t WHERE NOT EXISTS (
+ SELECT * FROM radippool r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );
+
+COMMIT;
+END_oracle
+
+#
+# SQLite
+#
+
+ $template{'sqlite'} = <<'END_sqlite';
+-- Temporary table holds the provided IP pools
+DROP TABLE IF EXISTS radippool_temp;
+CREATE TABLE radippool_temp (
+ pool_name varchar(30) NOT NULL,
+ framedipaddress varchar(15) NOT NULL
+);
+
+CREATE INDEX radippool_temp_idx ON radippool_temp (pool_name,FramedIPAddress);
+
+-- Populate the temporary table
+[%- FOREACH m IN ips %]
+[%- "\n\nINSERT INTO radippool_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% m.poolname %]','[% m.ip %]');
+[%- ELSE %]
+('[% m.poolname %]','[% m.ip %]'),
+[%- END %]
+[%- END %]
+
+BEGIN TRANSACTION;
+
+-- Delete old pools that have been removed
+DELETE FROM radippool WHERE rowid IN (
+ SELECT r.rowid FROM radippool r
+ LEFT JOIN radippool_temp t USING (pool_name,framedipaddress)
+ WHERE t.rowid IS NULL);
+
+-- Add new pools that have been created
+INSERT INTO radippool (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM radippool_temp t WHERE NOT EXISTS (
+ SELECT * FROM radippool r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );
+
+COMMIT;
+
+DROP TABLE radippool_temp;
+END_sqlite
+
+#
+# MS SQL
+#
+ $template{'mssql'} = <<'END_mssql';
+-- Temporary table holds the provided IP pools
+DROP TABLE IF EXISTS #radippool_temp;
+GO
+
+CREATE TABLE #radippool_temp (
+ id int identity(1, 1) NOT NULL,
+ pool_name varchar(30) NOT NULL,
+ framedipaddress varchar(15) NOT NULL,
+ PRIMARY KEY (id),
+);
+GO
+
+CREATE INDEX pool_name_framedipaddress ON #radippool_temp(pool_name, framedipaddress);
+GO
+
+-- Populate the temporary table
+[%- FOREACH m IN ips %]
+[%- "\n\nINSERT INTO #radippool_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% m.poolname %]','[% m.ip %]');
+GO
+[%- ELSE %]
+('[% m.poolname %]','[% m.ip %]'),
+[%- END %]
+[%- END %]
+
+BEGIN TRAN;
+
+-- Delete old pools that have been removed
+DELETE r FROM radippool r
+ LEFT JOIN #radippool_temp t ON r.pool_name = t.pool_name AND r.framedipaddress = t.framedipaddress
+ WHERE t.id IS NULL;
+
+-- Add new pools that have been created
+INSERT INTO radippool (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM #radippool_temp t WHERE NOT EXISTS (
+ SELECT * FROM radippool r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );
+
+COMMIT TRAN;
+END_mssql
+
+ return %template;
+
+}
diff --git a/scripts/sql/generate_pool_addresses.pl b/scripts/sql/generate_pool_addresses.pl
new file mode 100755
index 0000000..85fe030
--- /dev/null
+++ b/scripts/sql/generate_pool_addresses.pl
@@ -0,0 +1,456 @@
+#!/usr/bin/perl -Tw
+
+######################################################################
+#
+# Copyright (C) 2020 Network RADIUS
+#
+# $Id$
+#
+######################################################################
+#
+# Helper script for populating IP pools with address entries.
+#
+# This script generates output that is useful for populating an IP pool for
+# use with FreeRADIUS (and possibly other purposes). The pool may be
+# implemented as an SQL IP Pool (rlm_sqlippool) or any other backing store
+# that has one entry per IP address.
+#
+# This script output a list of address to add, retain and remove in order to
+# align a pool to a specification. It is likely that you will want to
+# process the output to generate the actual commands (e.g. SQL statements)
+# that make changes to the datastore. For example:
+#
+# generate_pool_addresses.pl ... | align_sql_pools.pl postgresql
+#
+#
+# Use with a single address range
+# -------------------------------
+#
+# For basic use, arguments can be provided to this script that denote the ends
+# of a single IP (v4 or v6) address range together with the pool_name.
+#
+# Optionally the number of IPs to sparsely populate the range with can be
+# provided. If the range is wider than a /16 then the population of the range
+# is capped at 65536 IPs, unless otherwise specified.
+#
+# In the case that a sparse range is defined, a file containing pre-existing
+# IP entries can be provided. The range will be populated with entries from
+# this file that fall within the range, prior to the remainder of the range
+# being populated with random address in the range.
+#
+# generate_pool_addresses.pl <pool_name> <range_start> <range_end> \
+# [ <capacity> [ <existing_ips_file> ] ]
+#
+# Note: Sparse ranges are populated using a deterministic, pseudo-random
+# function. This allows pools to be trivially extended without having to
+# supply the existing contents using a file. If you require
+# less-predictable randomness or a different random sequence then remove
+# or modify the line calling srand(), below.
+#
+#
+# Use with multiple pools and address ranges
+# ------------------------------------------
+#
+# For more complex us, the script allows a set of pool definitions to be
+# provided in a YAML file which describes a set of one or more pools, each
+# containing a set of one or more ranges. The first argument in this case is
+# always "yaml":
+#
+# generate_pool_addresses.pl yaml <pool_defs_yaml_file> [ <existing_ips_file> ]
+#
+# The format for the YAML file is demonstrated by the following example:
+#
+# pool_with_a_single_contiguous_range:
+# - start: 192.0.2.3
+# end: 192.0.2.250
+#
+# pool_with_a_single_sparse_range:
+# - start: 10.10.10.0
+# end: 10.10.20.255
+# capacity: 200
+#
+# pool_with_multiple_ranges:
+# - start: 10.10.10.1
+# end: 10.10.10.253
+# - start: 10.10.100.0
+# end: 10.10.199.255
+# capacity: 1000
+#
+# v6_pool_with_contiguous_range:
+# - start: '2001:db8:1:2:3:4:5:10'
+# end: '2001:db8:1:2:3:4:5:7f'
+#
+# v6_pool_with_sparse_range:
+# - start: '2001:db8:1:2::'
+# end: '2001:db8:1:2:ffff:ffff:ffff:ffff'
+# capacity: 200
+#
+# As with the basic use case, a file containing pre-existing IP entries can be
+# provided with which any sparse ranges will be populated ahead of any random
+# addresses.
+#
+#
+# Output
+# ------
+#
+# The script returns line-based output beginning with "+", "=" or "-", and
+# includes the pool_name and an IP address.
+#
+# + pool_name 192.0.2.10
+#
+# A new address to be added to the corresponding range in the pool.
+#
+# = pool_name 192.0.2.20
+#
+# A pre-existing address that is to be retained in the pool. (Only if a
+# pre-existing pool entries file is given.)
+#
+# - pool_name 192.0.2.30
+#
+# A pre-existing address that is to be removed from the corresponding
+# range in the pool. (Only if a pre-existing pool entries file is given.)
+#
+# # main_pool: 192.0.10.3 - 192.0.12.250 (500)
+#
+# Lines beginning with "#" are comments
+#
+#
+# Examples
+# --------
+#
+# generate_pool_addresses.pl main_pool 192.0.2.3 192.0.2.249
+#
+# Will create a pool from a full populated IPv4 range, i.e. all IPs in the
+# range available for allocation).
+#
+# generate_pool_addresses.pl main_pool 10.66.0.0 10.66.255.255 10000
+#
+# Will create a pool from a sparsely populated IPv4 range for a /16
+# network (maximum of 65.536 addresses), populating the range with 10,000
+# addreses. The effective size of the pool can be increased in future by
+# increasing the capacity of the range with:
+#
+# generate_pool_addresses.pl main_pool 10.66.0.0 10.66.255.255 20000
+#
+# This generates the same initial set of 10,000 addresses as the previous
+# example but will create 20,000 addresses overall, unless the random seed
+# has been amended since the initial run.
+#
+# generate_pool_addresses.pl main_pool 2001:db8:1:2:: \
+# 2001:db8:1:2:ffff:ffff:ffff:ffff
+#
+# Will create a pool from the IPv6 range 2001:db8:1:2::/64, initially
+# populating the range with 65536 (by default) addresses.
+#
+# generate_pool_addresses.pl main_pool 2001:db8:1:2:: \
+# 2001:db8:1:2:ffff:ffff:ffff:ffff \
+# 10000 existing_ips.txt
+#
+# Will create a pool using the same range as the previous example, but
+# this time the range will be populated with 10,000 addresses. The range
+# will be populated using lines extracted from the `existing_ips.txt` file
+# that represent IPs which fall within range.
+#
+# generate_pool_addresses.pl yaml pool_defs.yml existing_ips.txt
+#
+# Will create one of more pools using the definitions found in the
+# pool_defs.yml YAML file. The pools will contain one or more ranges with
+# each of the ranges first being populated with entries from the
+# existing_ips.txt file that fall within the range, before being filled
+# with random addresses to the defined capacity.
+#
+
+use strict;
+use Net::IP qw/ip_bintoip ip_iptobin ip_bincomp ip_binadd ip_is_ipv4 ip_is_ipv6/;
+
+my $yaml_available = 0;
+
+eval {
+ require YAML::XS;
+ YAML::XS->import('LoadFile');
+ $yaml_available = 1;
+};
+
+if ($#ARGV < 2 || $#ARGV > 4) {
+ usage();
+ exit 1;
+}
+
+if ($ARGV[0] eq 'yaml') {
+
+ if ($#ARGV > 3) {
+ usage();
+ exit 1;
+ }
+
+ unless ($yaml_available) {
+ die "ERROR: YAML is not available. Install the YAML::XS Perl module.";
+ }
+ process_yaml_file();
+
+ goto done;
+
+}
+
+process_commandline();
+
+done:
+
+exit 0;
+
+
+sub usage {
+ print STDERR <<'EOF'
+Usage:
+ generate_pool_addresses.pl <pool_name> <range_start> <range_end> [ <capacity> [ <existing_ips_file> ] ]
+or:
+ generate_pool_addresses.pl yaml <pool_defs_yaml_file> [ <existing_ips_file> ]
+
+EOF
+}
+
+
+sub process_commandline {
+
+ $SIG{__DIE__} = sub { usage(); die(@_); };
+
+ my $pool_name = $ARGV[0];
+ my $range_start = $ARGV[1];
+ my $range_end = $ARGV[2];
+ my $capacity = $ARGV[3];
+
+ my @entries = ();
+ @entries = load_entries($ARGV[4]) if ($#ARGV >= 4);
+
+ handle_range($pool_name, $range_start, $range_end, $capacity, @entries);
+
+}
+
+
+sub process_yaml_file {
+
+ my $yaml_file = $ARGV[1];
+
+ unless (-r $yaml_file) {
+ die "ERROR: Cannot open <pool_defs_yaml_file> for reading: $yaml_file";
+ }
+
+ my %pool_defs = %{LoadFile($yaml_file)};
+
+ my @entries = ();
+ @entries = load_entries($ARGV[2]) if ($#ARGV >= 2);
+
+ foreach my $pool_name (sort keys %pool_defs) {
+ foreach my $range (@{$pool_defs{$pool_name}}) {
+ my $range_start = $range->{start};
+ my $range_end = $range->{end};
+ my $capacity = $range->{capacity};
+ handle_range($pool_name, $range_start, $range_end, $capacity, @entries);
+ }
+ }
+
+}
+
+
+sub load_entries {
+
+ my $entries_file = shift;
+
+ my @entries = ();
+ unless (-r $entries_file) {
+ die "ERROR: Cannot open <existing_ips_file> for reading: $entries_file"
+ }
+ open(my $fh, "<", $entries_file) || die "Failed to open $entries_file";
+ while(<$fh>) {
+ chomp;
+ push @entries, $_;
+ }
+
+ return @entries;
+
+}
+
+
+sub handle_range {
+
+ my $pool_name = shift;
+ my $range_start = shift;
+ my $range_end = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ unless (ip_is_ipv4($range_start) || ip_is_ipv6($range_start)) {
+ die "ERROR: Incorrectly formatted IPv4/IPv6 address for range_start: $range_start";
+ }
+
+ unless (ip_is_ipv4($range_end) || ip_is_ipv6($range_end)) {
+ die "ERROR: Incorrectly formatted IPv4/IPv6 address for range_end: $range_end";
+ }
+
+ my $ip_start = new Net::IP($range_start);
+ my $ip_end = new Net::IP($range_end);
+ my $ip_range = new Net::IP("$range_start - $range_end");
+
+ unless (defined $ip_range) {
+ die "ERROR: The range defined by <range_start> - <range_end> is invalid: $range_start - $range_end";
+ }
+
+ my $range_size = $ip_range->size;
+ $capacity = $range_size < 65536 ? "$range_size" : 65536 unless defined $capacity;
+
+ if ($range_size < $capacity) {
+ $capacity = "$range_size";
+ warn "WARNING: Insufficent IPs in the range. Will create $capacity entries.";
+ }
+
+ # Prune the entries to only those within the specified range
+ for (my $i = 0; $i <= $#entries; $i++) {
+ my $version = ip_is_ipv4($entries[$i]) ? 4 : 6;
+ my $binip = ip_iptobin($entries[$i],$version);
+ if ($ip_start->version != $version ||
+ ip_bincomp($binip, 'lt', $ip_start->binip) == 1 ||
+ ip_bincomp($binip, 'gt', $ip_end->binip) == 1) {
+ $entries[$i]='';
+ }
+ }
+
+ #
+ # We use the sparse method if the number of entries available occupies < 80% of
+ # the network range, otherwise we use a method that involves walking the
+ # entire range.
+ #
+
+ srand(42); # Set the seed for the PRNG
+
+ if (length($range_size) > 9 || $capacity / "$range_size" < 0.8) { # From "BigInt" to FP
+ @entries = sparse_fill($pool_name, $ip_start, $ip_end, $capacity, @entries);
+ } else {
+ @entries = dense_fill($pool_name, $ip_start, $ip_end, $ip_range, $capacity, @entries);
+ }
+
+ print "# $pool_name: $range_start - $range_end ($capacity)\n";
+ print "$_\n" foreach @entries;
+ print "\n";
+
+}
+
+
+#
+# With this sparse fill method we randomly allocate within the scope of the
+# smallest enclosing network prefix, checking that we are within the given
+# range, retrying if we are outside or we hit a duplicate.
+#
+# This method can efficiently choose a small number of addresses relative to
+# the size of the range. It becomes slower as the population of a range nears
+# the range's limit since it is harder to choose a free address at random.
+#
+# It is useful for selecting a handful of addresses from an enourmous IPv6 /64
+# network for example.
+#
+sub sparse_fill {
+
+ my $pool_name = shift;
+ my $ip_start = shift;
+ my $ip_end = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ # Find the smallest network that encloses the given range
+ my $version = $ip_start->version;
+ ( $ip_start->binip ^ $ip_end->binip ) =~ /^\0*/;
+ my $net_prefix = $+[0];
+ my $net_bits = substr($ip_start->binip, 0, $net_prefix);
+ my $host_length = length($ip_start->binip) - $net_prefix;
+
+ my %ips = ();
+ my $i = 0;
+ while ($i < $capacity) {
+
+ # Use the given entries first
+ my $rand_ip;
+ my $given_lease = 0;
+ shift @entries while $#entries >= 0 && $entries[0] eq '';
+ if ($#entries >= 0) {
+ $rand_ip = ip_iptobin(shift @entries, $version);
+ $given_lease = 1;
+ } else {
+ $rand_ip = $net_bits;
+ $rand_ip .= [0..1]->[rand 2] for 1..$host_length;
+ # Check that we are inside the given range
+ next if ip_bincomp($rand_ip, 'lt', $ip_start->binip) == 1 ||
+ ip_bincomp($rand_ip, 'gt', $ip_end->binip) == 1;
+ }
+
+ next if defined $ips{$rand_ip};
+
+ $ips{$rand_ip} = $given_lease ? '=' : '+';
+ $i++;
+
+ }
+
+ # Allow the pool to be shrunk
+ $ips{ip_iptobin($_, $version)} = '-' foreach @entries;
+
+ return map { $ips{$_}." ".$pool_name." ".ip_bintoip($_, $version) } sort keys %ips;
+
+}
+
+
+#
+# With this dense fill method, after first selecting the given entries we walk
+# the network range picking IPs with evenly distributed probability.
+#
+# This method can efficiently choose a large number of addresses relative to
+# the size of a range, provided that the range isn't massive. It becomes
+# slower as the range size increases.
+#
+sub dense_fill {
+
+ my $pool_name = shift;
+ my $ip_start = shift;
+ my $ip_end = shift;
+ my $ip_range = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ my $version = $ip_start->version;
+
+ my $one = ("0"x($version == 4 ? 31 : 127)) . '1';
+
+ my %ips = ();
+ my $remaining_entries = $capacity;
+ my $remaining_ips = $ip_range->size;
+ my $ipbin = $ip_start->binip;
+
+ while ($remaining_entries > 0 && (ip_bincomp($ipbin, 'le', $ip_end->binip) == 1)) {
+
+ # Use the given entries first
+ shift @entries while $#entries >= 0 && $entries[0] eq '';
+ if ($#entries >= 0) {
+ $ips{ip_iptobin(shift @entries, $version)} = '=';
+ $remaining_entries--;
+ $remaining_ips--;
+ next;
+ }
+
+ goto next_ip if defined $ips{$ipbin};
+
+ # Skip the IP that we have already selected by given entries, otherwise
+ # randomly pick it
+ if (!defined $ips{$ipbin} &&
+ (rand) <= $remaining_entries / "$remaining_ips") { # From "BigInt" to FP
+ $ips{$ipbin} = '+';
+ $remaining_entries--;
+ }
+
+ $remaining_ips--;
+ $ipbin = ip_binadd($ipbin,$one);
+
+ }
+
+ # Allow the pool to be shrunk
+ $ips{ip_iptobin($_, $version)} = '-' foreach @entries;
+
+ return map { $ips{$_}." ".$pool_name." ".ip_bintoip($_, $version) } sort keys %ips;
+
+}
diff --git a/scripts/sql/radsqlrelay b/scripts/sql/radsqlrelay
new file mode 100755
index 0000000..74531ba
--- /dev/null
+++ b/scripts/sql/radsqlrelay
@@ -0,0 +1,342 @@
+#!/usr/bin/perl -w
+##
+## radsqlrelay.pl This program tails a SQL logfile and forwards
+## the queries to a database server. Used to
+## replicate accounting records to one (central)
+## database, even if the database has extended
+## downtime.
+##
+## Version: $Id$
+##
+## Author: Nicolas Baradakis <nicolas.baradakis@cegetel.net>
+##
+## Copyright (C) 2005 Cegetel
+## Copyright (C) 2019 Network RADIUS
+##
+## This program is free software; you can redistribute it and/or
+## modify it under the terms of the GNU General Public License
+## as published by the Free Software Foundation; either version 2
+## of the License, or (at your option) any later version.
+##
+## This program is distributed in the hope that it will be useful,
+## but WITHOUT ANY WARRANTY; without even the implied warranty of
+## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+## GNU General Public License for more details.
+##
+## You should have received a copy of the GNU General Public License
+## along with this program; if not, write to the Free Software
+## Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
+##
+
+use strict;
+
+use DBI;
+use Fcntl;
+use Getopt::Std;
+use POSIX qw(:unistd_h :errno_h);
+use Time::HiRes qw(clock_gettime usleep CLOCK_MONOTONIC);
+
+# We send watchdogs at half the indicated interval if the
+# Linux::Systemd::Daemon module is available and the WATCHDOG_USEC environment
+# variable is set by the systemd service manager.
+my $watchdog_usec;
+my $next_watchdog;
+eval {
+ require Linux::Systemd::Daemon;
+ Linux::Systemd::Daemon->import();
+ $watchdog_usec = $ENV{'WATCHDOG_USEC'} || undef;
+};
+
+# To synthetically test the watchdog functionality then uncomment these next
+# lines:
+#
+# $watchdog_usec = 30 *1000*1000;
+# sub sd_notify {}
+
+my $maxcollect = 100; # tunable, works for MySQL!
+
+my $lastinsert;
+my @values;
+
+my $need_exit = 0;
+my $debug = 0;
+
+sub got_signal()
+{
+ $need_exit = 1;
+ sd_notify(stopping => 1, status => 'Signalled. Shutting down.') if $watchdog_usec;
+}
+
+sub debug
+{
+ print shift if $debug;
+}
+
+# /!\ OS-dependent structure
+# Linux struct flock
+# short l_type;
+# short l_whence;
+# off_t l_start;
+# off_t l_len;
+# pid_t l_pid;
+# c2ph says: typedef='s2 l2 i', sizeof=16
+my $FLOCK_STRUCT = 's2l2i';
+
+sub setlock($;$$)
+{
+ my ($fh, $start, $len) = @_;
+ $start = 0 unless defined $start;
+ $len = 0 unless defined $len;
+
+ #type whence start till pid
+ my $packed = pack($FLOCK_STRUCT, F_WRLCK, SEEK_SET, $start, $len, 0);
+ if (fcntl($fh, F_SETLKW, $packed)) { return 1 }
+ else { return 0 }
+}
+
+sub usage()
+{
+ print STDERR <<HERE;
+usage: radsqlrelay [options] file_path
+options:
+ -? Print this help message.
+ -1 One-shot mode: push the file to database and exit.
+ -b database Name of the database to use.
+ -d sql_driver Driver to use: mysql, pg, oracle.
+ -f file Read password from file, instead of command line.
+ -h host Connect to host.
+ -P port Port number to use for connection.
+ -p password Password to use when connecting to server.
+ -u user User for login.
+ -x Turn on debugging.
+HERE
+}
+
+
+# Sleep for given amount of time, but don't oversleep the watchdog interval.
+# Send a watchdog notification if it is due.
+# interval can be given as 0 to just check whether a watchdog is due and send
+# it as necessary, in which case we do not yield.
+sub sleep_for ($)
+{
+ my $interval=shift;
+ $interval*=1000*1000;
+ if ($watchdog_usec) {
+ my $now=clock_gettime(CLOCK_MONOTONIC)*1000*1000;
+ if ($now >= $next_watchdog) {
+ $next_watchdog=$now+($watchdog_usec / 2);
+ debug "Sending watchdog\n";
+ sd_notify(watchdog => 1);
+ debug "Next watchdog due in ".(($next_watchdog-$now)/1000/1000)." secs.\n";
+ }
+ # Don't oversleep!
+ $interval=$next_watchdog-$now if $next_watchdog-$now < $interval;
+ }
+ return unless $interval; # Don't yield if we are not asked to sleep
+ debug "Sleeping for ".($interval/1000/1000)." secs.\n";
+ usleep ($interval);
+}
+
+
+sub connect_wait($)
+{
+ my $dbinfo = shift;
+ my $dbh;
+ debug "Connecting to " . $dbinfo->{base};
+ while (!$dbh) {
+ debug ".";
+ $dbh = DBI->connect($dbinfo->{base}, $dbinfo->{user}, $dbinfo->{pass},
+ { RaiseError => 0, PrintError => 0,
+ AutoCommit => 1 });
+ sleep_for (1) if !$dbh;
+ exit if $need_exit;
+ }
+ debug "\n";
+ $dbinfo->{handle} = $dbh;
+}
+
+
+
+sub process_file($$)
+{
+ my ($dbinfo, $path) = @_;
+
+ sub do_inserts($) {
+ my $dbinfo = shift;
+ debug "I";
+ if (scalar(@values) > 0) {
+ my $query = $lastinsert . " ";
+ $query .= join(" ), ( ",@values);
+ $query .= " );";
+ do_query($dbinfo,$query);
+ }
+ @values = ();
+ }
+
+ sub do_query($$) {
+ my ($dbinfo,$query) = @_;
+ debug ">";
+ until ($dbinfo->{handle}->do($query)) {
+ # If an error occured and we're disconnected then try to recomnnect
+ # and redo the query, otherwise give up so we don't become stuck.
+ print $dbinfo->{handle}->errstr."\n";
+ if ($dbinfo->{handle}->ping) {
+ sleep_for (1);
+ last;
+ } else {
+ print "error: Lost connection to database\n";
+ $dbinfo->{handle}->disconnect;
+ connect_wait($dbinfo);
+ }
+ }
+ sleep_for(0) if $watchdog_usec; # Send a watchdog if it is due
+ }
+
+ unless (-e $path.'.work') {
+ debug "Waiting for $path\n";
+ until (rename($path, $path.'.work')) {
+ if ($! == ENOENT) {
+ sleep_for(1);
+ return if $need_exit;
+ } else {
+ print STDERR "error: Couldn't move $path to $path.work: $!\n";
+ exit 1;
+ }
+ }
+ debug "Renamed $path to $path.work\n";
+ }
+
+ debug "\nOpening $path.work\n";
+ open(FILE, "+< $path.work") or die "error: Couldn't open $path.work: $!\n";
+ debug "Getting file lock\n";
+ setlock(\*FILE) or die "error: Couldn't lock $path.work: $!\n";
+
+ $lastinsert = "";
+ @values = ();
+
+ debug "Reading: ";
+ my $lines = 0;
+ while (<FILE>) {
+ chomp (my $line = $_);
+ $lines++;
+
+ if (!($line =~ /^\s*insert\s+into\s+`?\w+`?\s+(?:\(.*?\)\s+)?
+ values\s*\(.*\)\s*;\s*$/ix)) {
+ # This is no INSERT, so start new collection
+ do_inserts($dbinfo);
+ debug ".";
+ $lastinsert = "";
+ # must output this line
+ do_query($dbinfo, "$line");
+
+ } else {
+ # This is an INSERT, so collect it
+ debug "+";
+ my $insert = $line;
+ my $values = $line;
+ $insert =~ s/^\s*(insert\s+into\s+`?\w+`?\s+(?:\(.*?\)\s+)?
+ values\s*\().*\)\s*;\s*$/$1/ix;
+ $values =~ s/^\s*insert\s+into\s+`?\w+`?\s+(?:\(.*?\)\s+)?
+ values\s*\((.*)\)\s*;\s*$/$1/ix;
+
+ if (($lastinsert ne "") && ($insert ne $lastinsert)) {
+ # This is different from the last one
+ do_inserts($dbinfo);
+ }
+ push(@values, $values);
+ $lastinsert = $insert; # start new collection
+ }
+
+ # limit to $maxcollect collected lines
+ if (scalar(@values) >= $maxcollect) {
+ debug "hit maxcollect limit, doing inserts";
+ do_inserts($dbinfo);
+ }
+ }
+
+ # Cleanup
+ debug "\nNo more lines to read, doing any final inserts: ";
+ do_inserts($dbinfo);
+ debug "\n";
+
+ debug "Processed $lines lines\n";
+ debug "Removing and closing $path.work\n\n";
+ unlink($path.'.work');
+ close(FILE); # and unlock
+}
+
+# sub main()
+
+my %args = (
+ b => 'radius',
+ d => 'mysql',
+ h => 'localhost',
+ p => 'radius',
+ u => 'radius',
+);
+my $ret = getopts("b:d:f:h:P:p:u:x1?", \%args);
+if (!$ret or @ARGV != 1) {
+ usage();
+ exit 1;
+}
+if ($args{'?'}) {
+ usage();
+ exit 0;
+}
+$debug = 1 if $args{'x'};
+
+my $data_source;
+if (lc($args{d}) eq 'mysql') {
+ $data_source = "DBI:mysql:database=$args{b};host=$args{h}";
+} elsif (lc($args{d}) eq 'pg') {
+ $data_source = "DBI:Pg:dbname=$args{b};host=$args{h}";
+} elsif (lc($args{d}) eq 'oracle') {
+ $data_source = "DBI:Oracle:$args{b}";
+ # Oracle does not conform to the SQL standard for multirow INSERTs
+ $maxcollect = 1;
+} else {
+ print STDERR "error: SQL driver not supported yet: $args{d}\n";
+ exit 1;
+}
+$data_source .= ";port=$args{P}" if $args{'P'};
+
+my $pw;
+if($args{f}) {
+ open(FILE, "< $args{f}") or die "error: Couldn't open $args{f}: $!\n";
+ $pw = <FILE>;
+ chomp($pw);
+ close(FILE);
+} else {
+ # args{p} is always defined.
+ $pw = $args{p};
+}
+
+$SIG{INT} = \&got_signal;
+$SIG{TERM} = \&got_signal;
+
+if ($watchdog_usec) {
+ debug "Watchdog set to $watchdog_usec\n";
+ my $now=clock_gettime(CLOCK_MONOTONIC)*1000*1000;
+ $next_watchdog=$now+($watchdog_usec / 2);
+ sd_notify(ready => 1, status => 'Started');
+}
+
+my %dbinfo = (
+ base => $data_source,
+ user => $args{u},
+ pass => $pw,
+);
+connect_wait(\%dbinfo);
+
+my $path = shift @ARGV;
+
+until ($need_exit) {
+ process_file(\%dbinfo, $path);
+ last if ($args{1} || $need_exit);
+ debug "Sleeping\n";
+ sleep_for(10);
+}
+
+debug "Disconnecting from database\n";
+$dbinfo{handle}->disconnect;
+
diff --git a/scripts/sql/rlm_sqlippool_tool b/scripts/sql/rlm_sqlippool_tool
new file mode 100755
index 0000000..47a48fc
--- /dev/null
+++ b/scripts/sql/rlm_sqlippool_tool
@@ -0,0 +1,961 @@
+#!/usr/bin/perl -Tw
+
+######################################################################
+#
+# Copyright (C) 2020 Network RADIUS
+#
+# $Id$
+#
+######################################################################
+#
+# Helper script for populating IP pools with address entries.
+#
+# This script generates SQL output that is useful for populating an IP pool
+# for use with FreeRADIUS (and possibly other purposes). Alternatively,
+# if called with the -f option will directly operate on the database configured
+# within the FreeRADIUS configuration to update the IP pool table specified
+#
+# Note: Direct connection to databases is done using Perl DBI. You may need
+# to install the appropriate Perl DBD driver to enable this functionality.
+# Formatted SQL output is produced using Perl Template::Toolkit which
+# will need to be installed if this output is required.
+#
+#
+# Use with a single address range
+# -------------------------------
+#
+# For basic use, arguments can be provided to this script that denote the ends
+# of a single IP (v4 or v6) address range together with the pool_name and
+# SQL dialect or a raddb directory from which the database config will be
+# read.
+#
+# If a raddb directory is specified, then the instance of the FreeRADIUS sql
+# module to be found in the config can be specified. It defaults to "sql".
+#
+# Optionally the number of IPs to sparsely populate the range with can be
+# provided. If the range is wider than a /16 then the population of the range
+# is capped at 65536 IPs, unless otherwise specified.
+#
+# In the case that a sparse range is defined, a file containing pre-existing
+# IP entries can be provided. The range will be populated with entries from
+# this file that fall within the range, prior to the remainder of the range
+# being populated with random address in the range.
+#
+# rlm_sqlippool_tool -p <pool_name> -s <range_start> -e <range_end> \
+# -t <table_name> (-d <sql_dialect> | -f <raddb_dir> [ -i <instance> ]) \
+# [ -c <capacity> ] [ -x <existing_ips_file> ]
+#
+# Note: Sparse ranges are populated using a deterministic, pseudo-random
+# function. This allows pools to be trivially extended without having to
+# supply the existing contents using a file. If you require
+# less-predictable randomness or a different random sequence then remove
+# or modify the line calling srand(), below.
+#
+#
+# Use with multiple pools and address ranges
+# ------------------------------------------
+#
+# For more complex us, the script allows a set of pool definitions to be
+# provided in a YAML file which describes a set of one or more pools, each
+# containing a set of one or more ranges.
+#
+# rlm_sqlippool_tool -y <pool_defs_yaml_file> -t <table_name> \
+# ( -d <dialect> | -f <raddb_dir> [ -i <instance> ] ) \
+# [ -x <existing_ips_file> ]
+#
+# The format for the YAML file is demonstrated by the following example:
+#
+# pool_with_a_single_contiguous_range:
+# - start: 192.0.2.3
+# end: 192.0.2.250
+#
+# pool_with_a_single_sparse_range:
+# - start: 10.10.10.0
+# end: 10.10.20.255
+# capacity: 200
+#
+# pool_with_multiple_ranges:
+# - start: 10.10.10.1
+# end: 10.10.10.253
+# - start: 10.10.100.0
+# end: 10.10.199.255
+# capacity: 1000
+#
+# v6_pool_with_contiguous_range:
+# - start: '2001:db8:1:2:3:4:5:10'
+# end: '2001:db8:1:2:3:4:5:7f'
+#
+# v6_pool_with_sparse_range:
+# - start: '2001:db8:1:2::'
+# end: '2001:db8:1:2:ffff:ffff:ffff:ffff'
+# capacity: 200
+#
+# As with the basic use case, a file containing pre-existing IP entries can be
+# provided with which any sparse ranges will be populated ahead of any random
+# addresses.
+#
+#
+# Output
+# ------
+#
+# The script returns SQL formatted appropriately for one of a number of
+# different SQL dialects.
+#
+# The SQL first creates a temporary table to insert the new pools into,
+# inserts the addresses, then removes any exisitng entries from the pool
+# table that do not exist in the new pool. Finally any new entries that
+# don't exist in the existing pool table are copied from the temporary
+# table.
+#
+# The SQL templates assume that the pool name will be in a field called
+# "pool_name" and the IP address in a field named "framedipaddress",
+# matching the default schema for ippools and DHCP ippools as shipped with
+# FreeRADIUS.
+#
+#
+# Examples
+# --------
+#
+# rlm_sqlippool_tool -p main_pool -s 192.0.2.3 -e 192.0.2.249 \
+# -d postgresql -t radippool
+#
+# Will create a pool from a full populated IPv4 range, i.e. all IPs in the
+# range available for allocation, with SQL output suitable for PostgreSQL
+#
+# rlm_sqlippool_tool -p main_pool -s 10.66.0.0 -e 10.66.255.255 -c 10000 \
+# -d mysql -t radippool
+#
+# Will create a pool from a sparsely populated IPv4 range for a /16
+# network (maximum of 65.536 addresses), populating the range with 10,000
+# addreses. With SQL output suitable for MySQL.
+# The effective size of the pool can be increased in future by increasing
+# the capacity of the range with:
+#
+# rlm_sqlippool_tool -p main_pool -s 10.66.0.0 -e 10.66.255.255 -c 20000 \
+# -d mysql -t radippool
+#
+# This generates the same initial set of 10,000 addresses as the previous
+# example but will create 20,000 addresses overall, unless the random seed
+# has been amended since the initial run.
+#
+# rlm_sqlippool_tool -p main_pool -s 2001:db8:1:2:: \
+# -e 2001:db8:1:2:ffff:ffff:ffff:ffff -d mssql -t radippool
+#
+# Will create a pool from the IPv6 range 2001:db8:1:2::/64, initially
+# populating the range with 65536 (by default) addresses.
+#
+# rlm_sqlippool_tool -p main_pool -s 2001:db8:1:2:: \
+# -e 2001:db8:1:2:ffff:ffff:ffff:ffff \
+# -c 10000 -x existing_ips.txt -d mysql -t radippool
+#
+# Will create a pool using the same range as the previous example, but
+# this time the range will be populated with 10,000 addresses. The range
+# will be populated using lines extracted from the `existing_ips.txt` file
+# that represent IPs which fall within range.
+#
+# rlm_sqlippool_tool -y pool_defs.yml -d postgresql -t radippool \
+# -x existing_ips.txt
+#
+# Will create one of more pools using the definitions found in the
+# pool_defs.yml YAML file. The pools will contain one or more ranges with
+# each of the ranges first being populated with entries from the
+# existing_ips.txt file that fall within the range, before being filled
+# with random addresses to the defined capacity.
+#
+
+use strict;
+use Net::IP qw/ip_bintoip ip_iptobin ip_bincomp ip_binadd ip_is_ipv4 ip_is_ipv6/;
+
+#
+# Option defaults
+#
+my $opts = {
+ instance => 'sql',
+ capacity => 65536
+};
+
+#
+# Parse the command line arguments
+#
+my $opt = '';
+for (my $i = 0; $i <= $#ARGV; $i++) {
+ if ($ARGV[$i] =~ m/^-(.)$/) {
+ if ($1 eq 'p') {
+ $opt = 'pool_name';
+ } elsif ($1 eq 's') {
+ $opt = 'range_start';
+ } elsif ($1 eq 'e') {
+ $opt = 'range_end';
+ } elsif ($1 eq 'c') {
+ $opt = 'capacity';
+ } elsif ($1 eq 't') {
+ $opt = 'table_name';
+ } elsif ($1 eq 'd') {
+ $opt = 'dialect';
+ } elsif ($1 eq 'y') {
+ $opt = 'yaml';
+ } elsif ($1 eq 'x') {
+ $opt = 'entries';
+ } elsif ($1 eq 'f') {
+ $opt = 'raddb_dir';
+ } elsif ($1 eq 'i') {
+ $opt = 'instance';
+ } else {
+ usage();
+ exit 1;
+ }
+ } else {
+ if ($opt eq '') {
+ usage();
+ exit 1;
+ } else {
+ $opts->{$opt} = $ARGV[$i]
+ }
+ }
+}
+
+#
+# If a raddb dir is set then we parse the mods-enabled config
+#
+
+if ($opts->{raddb_dir}) {
+ my $found = 0;
+ if (-d $opts->{raddb_dir}.'/mods-enabled') {
+ opendir(my $dh, $opts->{raddb_dir}.'/mods-enabled') || die 'ERROR: Could not open directory '.$opts->{raddb_dir}.'/mods-enabled';
+ my @dir = grep { -f "$opts->{raddb_dir}/mods-enabled/$_" } readdir($dh);
+ closedir($dh);
+ my $instance = $opts->{instance};
+ foreach my $file (@dir) {
+ open (my $fh, $opts->{raddb_dir}.'/mods-enabled/'.$file);
+ my $level = 0;
+ my $section = '';
+ my $subsection = '';
+ while (<$fh>) {
+ if ($found) {
+ $_ =~ s/#.*//; # Remove comments
+ if ($_ =~ m/\s*([a-z_]+)\s*=\s*(.*)/) {
+ my $param = $1;
+ my $value = $2;
+ $value =~ s/^"//;
+ $value =~ s/"\s*$//;
+ if ($level == 1) {
+ $opts->{$param} = $value;
+ } elsif ($level == 2) {
+ $opts->{$section}->{$param} = $value;
+ } elsif ($level == 3) {
+ $opts->{$section}->{$subsection}->{$param} = $value;
+ }
+ }
+ if ($_ =~ m/([a-z_]*)\s+\{/) { # Find nested sectinos
+ $level++ ;
+ if ($level == 2) {
+ $section = $1;
+ } elsif ($level == 3) {
+ $subsection = $1;
+ }
+ }
+ $level-- if ($_ =~ m/\s+\}/); # Close of nesting
+ last if ($level == 0); # We've got to the end of the instance
+ }
+ if ($_ =~ m/\b$instance\s+\{/) {
+ # We've found the specified SQL instance
+ $found = 1;
+ $level = 1;
+ }
+ }
+ close ($fh);
+ if ($found) {
+ last;
+ }
+ }
+ } else {
+ die 'ERROR: Specified FreeRADIUS config directory does not contain mods-enabled';
+ }
+ if ($found == 0) {
+ die 'ERROR: SQL instance not found in FreeRADIUS config';
+ }
+}
+
+#
+# The SQL dialect and table name must be set
+#
+if ((!($opts->{dialect})) || (!($opts->{table_name}))) {
+ usage();
+ exit 1;
+}
+
+if ($opts->{yaml}) {
+ my $yaml_available = 0;
+
+ eval {
+ require YAML::XS;
+ YAML::XS->import('LoadFile');
+ $yaml_available = 1;
+ };
+
+ unless ($yaml_available) {
+ die "ERROR: YAML is not available. Install the YAML::XS Perl module.";
+ }
+ process_yaml_file($opts);
+
+ goto done;
+
+}
+
+
+if ((!($opts->{pool_name})) || (!($opts->{range_start})) || (!($opts->{range_end}))) {
+ usage();
+ exit 1;
+}
+
+process_commandline($opts);
+
+done:
+
+exit 0;
+
+
+sub usage {
+ print STDERR <<'EOF'
+Usage:
+ rlm_sqlippool_tool -p <pool_name> -s <range_start> -e <range_end> -t <table_name> (-d <sql_dialect> | -f <raddb_dir> [ -i <instance> ]) [ -c <capacity> ] [ -x <existing_ips_file> ]
+or:
+ rlm_sqlippool_tool -y <pool_defs_yaml_file> -t <table_name> (-d <dialect> | -f <raddb_dir> [ -i <instance> ]) [ -x <existing_ips_file> ]
+
+EOF
+}
+
+
+sub process_commandline {
+
+ my $opts = shift;
+ $SIG{__DIE__} = sub { usage(); die(@_); };
+
+ (my $template, my $queries)=load_templates($opts->{table_name});
+
+ unless (defined $template->{$opts->{dialect}}) {
+ print STDERR "Unknown dialect. Pick one of: ";
+ print STDERR "$_ " foreach sort keys %{$template};
+ print STDERR "\n";
+ exit 1;
+ }
+
+ my @entries = ();
+ @entries = load_entries($opts->{entries}) if ($opts->{entries});
+
+ @entries = handle_range($opts->{range_start}, $opts->{range_end}, $opts->{capacity}, @entries);
+
+ if ($opts->{radius_db}) {
+ &call_database($opts, $queries, @entries);
+ } else {
+ &output_sql($template->{$opts->{dialect}}, {ranges => [{pool_name => $opts->{pool_name}, ips => \@entries}], batchsize => 100, tablename => $opts->{table_name}});
+ }
+}
+
+sub process_yaml_file {
+
+ my $opts = shift;
+
+ unless (-r $opts->{yaml}) {
+ die "ERROR: Cannot open <pool_defs_yaml_file> for reading: $opts->{yaml}";
+ }
+
+ my %pool_defs = %{LoadFile($opts->{yaml})};
+
+ (my $template, my $queries)=load_templates($opts->{table_name});
+
+ unless (defined $template->{$opts->{dialect}}) {
+ print STDERR "Unknown dialect. Pick one of: ";
+ print STDERR "$_ " foreach sort keys %{$template};
+ print STDERR "\n";
+ exit 1;
+ }
+
+ my @entries = ();
+ @entries = load_entries($opts->{entries}) if ($opts->{entries});
+
+ my @ranges;
+ foreach my $pool_name (sort keys %pool_defs) {
+ foreach my $range (@{$pool_defs{$pool_name}}) {
+ my $range_start = $range->{start};
+ my $range_end = $range->{end};
+ my $capacity = $range->{capacity};
+ my @ips = handle_range($range_start, $range_end, $capacity, @entries);
+ push (@ranges, {pool_name => $pool_name, ips => \@ips});
+ }
+ }
+
+ if ($opts->{radius_db}) {
+ &call_database($opts, $queries, @entries);
+ } else {
+ &output_sql($template->{$opts->{dialect}}, {ranges => \@ranges, batchsize => 100, tablename => $opts->{table_name}});
+ }
+}
+
+sub output_sql {
+ my $template = shift();
+ my $vars = shift();
+
+ my $tt_available = 0;
+ eval {
+ require Template;
+ $tt_available = 1;
+ };
+ if ($tt_available) {
+ my $tt=Template->new();
+ $tt->process(\$template, $vars) || die $tt->error();
+ } else {
+ die "ERROR: Template Toolkit is not available. Install the Template Perl module.";
+ }
+}
+
+sub call_database {
+
+ my $opts = shift;
+ my $queries = shift;
+ my @entries = @_;
+
+ my $dbi_avail = 0;
+ eval {
+ require DBI;
+ $dbi_avail = 1;
+ };
+ unless($dbi_avail) {
+ die "ERROR: DBI is not available. Install the DBI Perl module.";
+ }
+
+ my $dsn;
+ if ($opts->{dialect} eq 'mysql') {
+ $dsn = "DBI:mysql:database=$opts->{radius_db};host=$opts->{server}";
+ if (defined($opts->{mysql}->{tls})) {
+ $dsn .= ';mysql_ssl=1';
+ $dsn .= ';mysql_ssl_ca_file='.$opts->{mysql}->{tls}->{ca_file} if ($opts->{mysql}->{tls}->{ca_file});
+ $dsn .= ';mysql_ssl_ca_path='.$opts->{mysql}->{tls}->{ca_path} if ($opts->{mysql}->{tls}->{ca_path});
+ $dsn .= ';mysql_ssl_client_key='.$opts->{mysql}->{tls}->{private_key_file} if ($opts->{mysql}->{tls}->{private_key_file});
+ $dsn .= ';mysql_ssl_client_cert='.$opts->{mysql}->{tls}->{certificate_file} if ($opts->{mysql}->{tls}->{certificate_file});
+ $dsn .= ';mysql_ssl_cipher='.$opts->{mysql}->{tls}->{cipher} if ($opts->{mysql}->{tls}->{cipher});
+ }
+ } elsif ($opts->{dialect} eq 'postgresql') {
+ # Parse FreeRADIUS alternative connection string
+ if ($opts->{radius_db} =~ m/host=(.+?)\b/) {
+ $opts->{server} = $1;
+ }
+ if ($opts->{radius_db} =~ m/user=(.+?)\b/) {
+ $opts->{login} = $1;
+ }
+ if ($opts->{radius_db} =~ m/password=(.+?)\b/) {
+ $opts->{password} = $1;
+ }
+ if ($opts->{radius_db} =~ m/sslmode=(.+?)\b/) {
+ $opts->{sslmode} = $1;
+ }
+ if ($opts->{radius_db} =~ m/dbname=(.+?)\b/) {
+ $opts->{radius_db} = $1;
+ }
+ $dsn = "DBI:Pg:dbname=$opts->{radius_db};host=$opts->{server}";
+ #
+ # DBD doesn't have all the options used by FreeRADIUS - just enable ssl if
+ # FreeRADIUS has SSL options enabled
+ #
+ $dsn .= ';sslmode=prefer' if ($opts->{sslmode});
+ } elsif ($opts->{dialect} eq 'sqlite') {
+ $dsn = "DBI:SQLite:dbname=$opts->{sqlite}->{filename}";
+ } elsif ($opts->{dialect} eq 'mssql') {
+ if ($opts->{driver} eq 'rlm_sql_unixodbc') {
+ $dsn = "DBI:ODBC:DSN=$opts->{server}";
+ } else {
+ $dsn = "DBI:Sybase:server=$opts->{server};database=$opts->{radius_db}";
+ }
+ } elsif ($opts->{dialect} eq 'oracle') {
+ # Extract data from Oracle connection string as used by FreeRADIUS
+ if ($opts->{radius_db} =~ m/HOST=(.+?)\)/) {
+ $opts->{server} = $1;
+ }
+ if ($opts->{radius_db} =~ m/PORT=(.+?)\)/) {
+ $opts->{port} =$1;
+ }
+ if ($opts->{radius_db} =~ m/SID=(.+?)\)/) {
+ $opts->{sid} = $1;
+ }
+ $dsn = "DBI:Oracle:host=$opts->{server};sid=$opts->{sid}";
+ } else {
+ $dsn = "DBI:$opts->{dialect}:database=$opts->{radius_db};host=$opts->{server}";
+ }
+ $dsn .= ";port=$opts->{port}" if ($opts->{port}) && ($opts->{driver} ne 'rlm_sql_unixodbc');
+
+ # Read the results by running our query against the database
+ my $dbh = DBI->connect($dsn, $opts->{login}, $opts->{password}) || die "Unable to connect to database";
+
+ foreach my $query (@{$queries->{$opts->{dialect}}->{pre}}) {
+ $dbh->do($query);
+ }
+
+ my $sth = $dbh->prepare($queries->{$opts->{dialect}}->{insert});
+ foreach my $ip (@entries) {
+ $sth->execute($opts->{pool_name}, $ip);
+ }
+ $sth->finish();
+
+ foreach my $query (@{$queries->{$opts->{dialect}}->{post}}) {
+ $dbh->do($query);
+ }
+
+ $dbh->disconnect();
+}
+
+sub load_entries {
+
+ my $entries_file = shift;
+
+ my @entries = ();
+ unless (-r $entries_file) {
+ die "ERROR: Cannot open <existing_ips_file> for reading: $entries_file"
+ }
+ open(my $fh, "<", $entries_file) || die "Failed to open $entries_file";
+ while(<$fh>) {
+ chomp;
+ push @entries, $_;
+ }
+
+ return @entries;
+
+}
+
+
+sub handle_range {
+
+ my $range_start = shift;
+ my $range_end = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ unless (ip_is_ipv4($range_start) || ip_is_ipv6($range_start)) {
+ die "ERROR: Incorrectly formatted IPv4/IPv6 address for range_start: $range_start";
+ }
+
+ unless (ip_is_ipv4($range_end) || ip_is_ipv6($range_end)) {
+ die "ERROR: Incorrectly formatted IPv4/IPv6 address for range_end: $range_end";
+ }
+
+ my $ip_start = new Net::IP($range_start);
+ my $ip_end = new Net::IP($range_end);
+ my $ip_range = new Net::IP("$range_start - $range_end");
+
+ unless (defined $ip_range) {
+ die "ERROR: The range defined by <range_start> - <range_end> is invalid: $range_start - $range_end";
+ }
+
+ my $range_size = $ip_range->size;
+
+ if ($range_size < $capacity) {
+ $capacity = "$range_size";
+ warn 'WARNING: Insufficent IPs in the range. Will create '.$capacity.' entries.';
+ }
+
+ # Prune the entries to only those within the specified range
+ for (my $i = 0; $i <= $#entries; $i++) {
+ my $version = ip_is_ipv4($entries[$i]) ? 4 : 6;
+ my $binip = ip_iptobin($entries[$i],$version);
+ if ($ip_start->version != $version ||
+ ip_bincomp($binip, 'lt', $ip_start->binip) == 1 ||
+ ip_bincomp($binip, 'gt', $ip_end->binip) == 1) {
+ $entries[$i]='';
+ }
+ }
+
+ #
+ # We use the sparse method if the number of entries available occupies < 80% of
+ # the network range, otherwise we use a method that involves walking the
+ # entire range.
+ #
+
+ srand(42); # Set the seed for the PRNG
+
+ if ($capacity / "$range_size" > 0.9) {
+ @entries = walk_fill($ip_start, $ip_end, $capacity, @entries);
+ } elsif (length($range_size) > 9 || $capacity / "$range_size" < 0.8) { # From "BigInt" to FP
+ @entries = sparse_fill($ip_start, $ip_end, $capacity, @entries);
+ } else {
+ @entries = dense_fill($ip_start, $ip_end, $ip_range, $capacity, @entries);
+ }
+
+ return @entries;
+}
+
+
+#
+# With this sparse fill method we randomly allocate within the scope of the
+# smallest enclosing network prefix, checking that we are within the given
+# range, retrying if we are outside or we hit a duplicate.
+#
+# This method can efficiently choose a small number of addresses relative to
+# the size of the range. It becomes slower as the population of a range nears
+# the range's limit since it is harder to choose a free address at random.
+#
+# It is useful for selecting a handful of addresses from an enourmous IPv6 /64
+# network for example.
+#
+sub sparse_fill {
+
+ my $ip_start = shift;
+ my $ip_end = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ # Find the smallest network that encloses the given range
+ my $version = $ip_start->version;
+ ( $ip_start->binip ^ $ip_end->binip ) =~ /^\0*/;
+ my $net_prefix = $+[0];
+ my $net_bits = substr($ip_start->binip, 0, $net_prefix);
+ my $host_length = length($ip_start->binip) - $net_prefix;
+
+ my %ips = ();
+ my $i = 0;
+ while ($i < $capacity) {
+
+ # Use the given entries first
+ my $rand_ip;
+ my $given_lease = 0;
+ shift @entries while $#entries >= 0 && $entries[0] eq '';
+ if ($#entries >= 0) {
+ $rand_ip = ip_iptobin(shift @entries, $version);
+ $given_lease = 1;
+ } else {
+ $rand_ip = $net_bits;
+ $rand_ip .= [0..1]->[rand 2] for 1..$host_length;
+ # Check that we are inside the given range
+ next if ip_bincomp($rand_ip, 'lt', $ip_start->binip) == 1 ||
+ ip_bincomp($rand_ip, 'gt', $ip_end->binip) == 1;
+ }
+
+ next if defined $ips{$rand_ip};
+
+ $ips{$rand_ip} = $given_lease ? '=' : '+';
+ $i++;
+
+ }
+
+ return map { ip_bintoip($_, $version) } keys %ips;
+
+}
+
+
+#
+# With this dense fill method, after first selecting the given entries we walk
+# the network range picking IPs with evenly distributed probability.
+#
+# This method can efficiently choose a large number of addresses relative to
+# the size of a range, provided that the range isn't massive. It becomes
+# slower as the range size increases.
+#
+sub dense_fill {
+
+ my $ip_start = shift;
+ my $ip_end = shift;
+ my $ip_range = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ my $version = $ip_start->version;
+
+ my $one = ("0"x($version == 4 ? 31 : 127)) . '1';
+
+ my %ips = ();
+ my $remaining_entries = $capacity;
+ my $remaining_ips = $ip_range->size;
+ my $ipbin = $ip_start->binip;
+
+ while ($remaining_entries > 0 && (ip_bincomp($ipbin, 'le', $ip_end->binip) == 1)) {
+
+ # Use the given entries first
+ shift @entries while $#entries >= 0 && $entries[0] eq '';
+ if ($#entries >= 0) {
+ $ips{ip_iptobin(shift @entries, $version)} = '=';
+ $remaining_entries--;
+ $remaining_ips--;
+ next;
+ }
+
+ goto next_ip if defined $ips{$ipbin};
+
+ # Skip the IP that we have already selected by given entries, otherwise
+ # randomly pick it
+ if (!defined $ips{$ipbin} &&
+ (rand) <= $remaining_entries / "$remaining_ips") { # From "BigInt" to FP
+ $ips{$ipbin} = '+';
+ $remaining_entries--;
+ }
+
+ $remaining_ips--;
+ $ipbin = ip_binadd($ipbin,$one);
+
+ }
+
+ return map { ip_bintoip($_, $version) } keys %ips;
+
+}
+
+#
+# With this walk fill method we walk the IP range from the beginning
+# for as many IPs as are required
+#
+# It is useful for selecting a fully populated network.
+#
+
+sub walk_fill {
+ my $ip_start = shift;
+ my $ip_end = shift;
+ my $capacity = shift;
+ my @entries = @_;
+
+ my $version = $ip_start->version;
+
+ my $one = ("0"x($version == 4 ? 31 : 127)) . '1';
+
+ my %ips = ();
+ my $remaining_entries = $capacity;
+ my $ipbin = $ip_start->binip;
+
+ # Sort existing IPs and remove any blank entries. Allows existing entries to be
+ # matched quickly in the new pool
+ my @sorted_entries = sort @entries;
+ shift @sorted_entries while $#sorted_entries >= 0 && $sorted_entries[0] eq '';
+
+ # Walk through the IP range from the beginning
+ while ($remaining_entries > 0 && (ip_bincomp($ipbin, 'le', $ip_end->binip) == 1)) {
+
+ if ($#sorted_entries >= 0) {
+ # If there are existing entries check if they match
+ $ips{$ipbin} = (ip_bincomp($ipbin, 'eq', ip_iptobin($sorted_entries[0]) == 1) && shift(@sorted_entries) ? '=' : '+');
+ } else {
+ $ips{$ipbin} = '+';
+ }
+ $remaining_entries--;
+ $ipbin = ip_binadd($ipbin,$one);
+
+ }
+
+ return map { ip_bintoip($_, $version) } keys %ips;
+
+}
+
+
+
+#
+# SQL dialect templates
+#
+
+sub load_templates {
+
+ my $tablename = shift;
+
+ my $template;
+ my $queries;
+#
+# MySQL / MariaDB
+#
+ $queries->{'mysql'}->{pre} = [
+ 'DROP TEMPORARY TABLE IF EXISTS '.$tablename.'_temp;',
+ 'CREATE TEMPORARY TABLE '.$tablename.'_temp (
+ id int(11) unsigned NOT NULL auto_increment,
+ pool_name varchar(30) NOT NULL,
+ framedipaddress varchar(15) NOT NULL,
+ PRIMARY KEY (id),
+ KEY pool_name_framedipaddress (pool_name,framedipaddress)
+);'
+ ];
+ $queries->{'mysql'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,framedipaddress) VALUES (?, ?)';
+ $queries->{'mysql'}->{post} = [
+ 'START TRANSACTION;',
+ 'DELETE r FROM '.$tablename.' r
+ LEFT JOIN '.$tablename.'_temp t USING (pool_name,framedipaddress)
+ WHERE t.id IS NULL;',
+ 'INSERT INTO '.$tablename.' (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS (
+ SELECT * FROM '.$tablename.' r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );',
+ 'COMMIT;'
+ ];
+ $template->{'mysql'} = join("\n", @{$queries->{'mysql'}->{pre}})."\n";
+ $template->{'mysql'} .= <<'END_mysql';
+-- Populate the temporary table
+[%- FOREACH r IN ranges %]
+[%- FOREACH i IN r.ips %]
+[%- "\n\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% r.pool_name %]','[% i %]');
+[%- ELSE %]
+('[% r.pool_name %]','[% i %]'),
+[%- END %]
+[%- END %]
+[%- END %]
+END_mysql
+ $template->{'mysql'} .= join("\n", @{$queries->{'mysql'}->{post}})."\n";
+
+#
+# PostgreSQL
+#
+ $queries->{'postgresql'}->{pre} = [
+ 'DROP TABLE IF EXISTS '.$tablename.'_temp;',
+ 'CREATE TEMPORARY TABLE '.$tablename.'_temp (
+ pool_name varchar(64) NOT NULL,
+ FramedIPAddress INET NOT NULL
+);',
+ 'CREATE INDEX '.$tablename.'_temp_idx ON '.$tablename.'_temp USING btree (pool_name,FramedIPAddress);'
+ ];
+ $queries->{'postgresql'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,framedipaddress) VALUES (?, ?)';
+ $queries->{'postgresql'}->{post} = [
+ 'START TRANSACTION;',
+ 'DELETE FROM '.$tablename.' r WHERE NOT EXISTS (
+ SELECT FROM '.$tablename.'_temp t
+ WHERE t.pool_name = r.pool_name AND t.framedipaddress = r.framedipaddress
+);',
+ 'INSERT INTO '.$tablename.' (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS (
+ SELECT * FROM '.$tablename.' r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );',
+ 'COMMIT;'
+ ];
+ $template->{'postgresql'} = join("\n", @{$queries->{'postgresql'}->{pre}})."\n";
+ $template->{'postgresql'} .= <<'END_postgresql';
+-- Populate the temporary table
+[%- FOREACH r IN ranges %]
+[%- FOREACH i IN r.ips %]
+[%- "\n\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% r.pool_name %]','[% i %]');
+[%- ELSE %]
+('[% r.pool_name %]','[% i %]'),
+[%- END %]
+[%- END %]
+[%- END %]
+END_postgresql
+ $template->{'postgresql'} .= join("\n", @{$queries->{'postgresql'}->{post}})."\n";
+#
+# Oracle
+#
+ $queries->{'oracle'}->{pre} = [
+ 'CREATE TABLE '.$tablename.'_temp (
+ pool_name VARCHAR(30) NOT NULL,
+ FramedIPAddress VARCHAR(15) NOT NULL
+)',
+ 'CREATE INDEX '.$tablename.'_temp_idx ON '.$tablename.'_temp (pool_name,FramedIPAddress)'
+ ];
+ $queries->{'oracle'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,FramedIPAddress) VALUES (?, ?)';
+ $queries->{'oracle'}->{post} = [
+ 'DELETE FROM '.$tablename.' r WHERE NOT EXISTS
+ (SELECT * FROM '.$tablename.'_temp t WHERE
+ r.pool_name = t.pool_name AND r.framedipaddress = t.framedipaddress)',
+ 'INSERT INTO '.$tablename.' (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS (
+ SELECT * FROM '.$tablename.' r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ )',
+ 'DROP TABLE '.$tablename.'_temp',
+ 'COMMIT'
+ ];
+
+ $template->{'oracle'} = join(";\n", @{$queries->{'oracle'}->{pre}}).";\n";
+ $template->{'oracle'} .= <<'END_oracle';
+-- Populate the temporary table
+[%- FOREACH r IN ranges %]
+[%- FOREACH i IN r.ips %]
+[%- "\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES " %]('[% r.pool_name %]','[% i %]');
+[%- END %]
+[%- END %]
+END_oracle
+ $template->{'oracle'} .= join(";\n", @{$queries->{'oracle'}->{post}})."\n";
+
+#
+# SQLite
+#
+ $queries->{'sqlite'}->{pre} = [
+ 'DROP TABLE IF EXISTS '.$tablename.'_temp;',
+ 'CREATE TABLE '.$tablename.'_temp (
+ pool_name varchar(30) NOT NULL,
+ framedipaddress varchar(15) NOT NULL
+);',
+ 'CREATE INDEX '.$tablename.'_temp_idx ON '.$tablename.'_temp (pool_name,FramedIPAddress);'
+ ];
+ $queries->{'sqlite'}->{insert} = 'INSERT INTO '.$tablename.'_temp (pool_name,framedipaddress) VALUES (?, ?)';
+ $queries->{'sqlite'}->{post} = [
+ 'BEGIN TRANSACTION;',
+ 'DELETE FROM '.$tablename.' WHERE rowid IN (
+ SELECT r.rowid FROM '.$tablename.' r
+ LEFT JOIN '.$tablename.'_temp t USING (pool_name,framedipaddress)
+ WHERE t.rowid IS NULL);',
+ 'INSERT INTO '.$tablename.' (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM '.$tablename.'_temp t WHERE NOT EXISTS (
+ SELECT * FROM '.$tablename.' r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );',
+ 'COMMIT;',
+ 'DROP TABLE '.$tablename.'_temp;'
+ ];
+
+ $template->{'sqlite'} = join("\n", @{$queries->{'sqlite'}->{pre}})."\n";
+ $template->{'sqlite'} .= <<'END_sqlite';
+-- Populate the temporary table
+[%- FOREACH r IN ranges %]
+[%- FOREACH i IN r.ips %]
+[%- "\n\nINSERT INTO ${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% r.pool_name %]','[% i %]');
+[%- ELSE %]
+('[% r.pool_name %]','[% i %]'),
+[%- END %]
+[%- END %]
+[%- END %]
+END_sqlite
+ $template->{'sqlite'} .= join("\n", @{$queries->{'sqlite'}->{post}})."\n";
+
+#
+# MS SQL
+#
+ $queries->{'mssql'}->{pre} = [
+ 'DROP TABLE IF EXISTS #'.$tablename.'_temp;',
+ 'CREATE TABLE #'.$tablename.'_temp (
+ id int identity(1, 1) NOT NULL,
+ pool_name varchar(30) NOT NULL,
+ framedipaddress varchar(15) NOT NULL,
+ PRIMARY KEY (id),
+);',
+ 'CREATE INDEX pool_name_framedipaddress ON #'.$tablename.'_temp(pool_name, framedipaddress);'
+ ];
+ $queries->{'mssql'}->{insert} = 'INSERT INTO #'.$tablename.'_temp (pool_name, framedipaddress) VALUES (?, ?)';
+ $queries->{'mssql'}->{post} = [
+ 'BEGIN TRAN;',
+ 'DELETE r FROM '.$tablename.' r
+ LEFT JOIN #'.$tablename.'_temp t ON r.pool_name = t.pool_name AND r.framedipaddress = t.framedipaddress
+ WHERE t.id IS NULL;',
+ 'INSERT INTO '.$tablename.' (pool_name,framedipaddress)
+ SELECT pool_name,framedipaddress FROM #'.$tablename.'_temp t WHERE NOT EXISTS (
+ SELECT * FROM '.$tablename.' r
+ WHERE r.pool_name=t.pool_name AND r.framedipaddress=t.framedipaddress
+ );',
+ 'COMMIT TRAN;'
+ ];
+
+ $template->{'mssql'} = join("\nGO\n", @{$queries->{'mssql'}->{pre}})."\nGO\n";
+ $template->{'mssql'} .= <<'END_mssql';
+-- Populate the temporary table
+[%- FOREACH r IN ranges %]
+[%- FOREACH i IN r.ips %]
+[%- "\n\nINSERT INTO #${tablename}_temp (pool_name,framedipaddress) VALUES" IF loop.index % batchsize == 0 %]
+[%- IF (loop.index+1) % batchsize == 0 OR loop.last %]
+('[% r.pool_name %]','[% i %]');
+GO
+[%- ELSE %]
+('[% r.pool_name %]','[% i %]'),
+[%- END %]
+[%- END %]
+[% END %]
+END_mssql
+ $template->{'mssql'} .= join("\n", @{$queries->{'mssql'}->{post}})."\n";
+
+ return ($template, $queries);
+
+}
+
diff --git a/scripts/sql/users2mysql.pl b/scripts/sql/users2mysql.pl
new file mode 100644
index 0000000..abaa9c0
--- /dev/null
+++ b/scripts/sql/users2mysql.pl
@@ -0,0 +1,157 @@
+#!/usr/bin/perl -w
+#
+# users2mysql.pl -- a script to parse a RADIUS users file and fill
+# a freeradius mysql database...
+#
+#
+# Script developed by Rich Puhek, Znet Telecom
+#
+# last change: Aug 8th, 2002.
+#
+
+
+
+#Modify to suit your db.
+$database="radius";
+$hostname="localhost";
+$user="radius";
+$password="passwd";
+
+
+#location of source users file:
+$users_file="/etc/raddb_cistron_backup/users";
+
+
+#The following are defaults from freeradius 0.7
+# ...shouldn't have to change.
+$groups_table="usergroup";
+$check_table="radcheck";
+$reply_table="radreply";
+
+$debug=3;
+
+use DBD::mysql;
+
+#open the users file, and the db.
+open USERS, $users_file or die "ERROR: Unable to open $users_file $!\n";
+$database = DBI->connect("DBI:mysql:$database:$hostname",$user, $password) or die "ERROR: Unable to connect to $database on $hostname $!\n";
+
+sub check_attribs {
+
+ if (!defined($_[0]) or !defined($_[1])) {
+ print "undefined parameter!\n";
+ return undef;
+ };
+
+ $attr = $_[0];
+ $val = $_[1];
+
+ if ($attr !~ /Password|Framed-IP-Address|Framed-IP-Netmask|Framed-IP-Routing|Framed-Routing|Framed-IP-Route|Password|Simultaneous-Use|Idle-Timeout|Auth-Type|Service-Type|Netmask|Framed-Protocol/ ) {
+ print "unrecognized attribute: $attr\n" if $debug>1;
+ return undef;
+ };
+
+ return undef if ( (! defined($val) ) or
+ ( ($attr =~ /Simultaneous\-Use/i) && ( $val !~ /^[0-9]*$/ ) )
+ );
+ print "attribs ok!\n" if $debug>3;
+ return "TRUE";
+};
+
+sub cleanup {
+ #clean up variables: strip leading/trailing spaces and trailing commas...
+ my $myval;
+ $myval = $_[0];
+ $myval =~ s/^\s//g;
+ $myval =~ s/\s$//g;
+ $myval =~ s/,$//;
+ return $myval;
+};
+
+
+sub user_attribute {
+ #push values into db...
+ $dtable=$_[0];
+ $duser=$_[1];
+ $dattrib=$_[2];
+ $dval=$_[3];
+
+ print "inserting \"$dattrib\", \"$dval\" for \"$duser\" in rad$dtable\n" if ( $dtable !~ /group/ and $debug>2);
+ print "inserting \"$duser\" into usergroup table as member of \"$dattrib\"\n" if ( $dtable =~ /group/ and $debug>2);
+
+ if ( $dtable =~ /group/ ) {
+ $table = "usergroup";
+ } elsif ( $dtable =~ /check/ ) {
+ $table = "radcheck";
+ } elsif ( $dtable =~ /reply/ ) {
+ $table = "radreply";
+ } else {
+ die "argh! what table is $dtable?\n";
+ };
+
+
+ if ( $table =~ /usergroup/ ) {
+ if ( $dattrib =~ /static/ ) {
+ #Delete the "dynamic" entry...
+ $return = $database->do ("DELETE FROM `$table` WHERE `UserName`='$duser' LIMIT 1");
+ };
+ $return = $database->do ("INSERT INTO `$table` SET `UserName`='$duser',`GroupName`='$dattrib'");
+
+ } else {
+ $return = $database->do ("INSERT INTO `$table` SET `UserName`='$duser',`Attribute`='$dattrib',`Value`='$dval', `op`=':='");
+ };
+ return $return;
+};
+
+
+while (<USERS>) {
+
+ chop;
+ #Skip comment lines and blank lines...
+ next if ( /^\#/ );
+ next if ( /^$/ );
+ next if ( /^\s*$/ );
+
+ if ( /^[a-zA-Z0-9]+/ ) {
+ print "located a user entry: $_\n" if $debug>6;
+ ($user,$rest) = split /\s/, $_, 2;
+ #Put user into usergroup as dynamic, if the user's attributes
+ # include an IP address, the script will change that later...
+ user_attribute("group",$user,"dynamic","");
+ @attribs = split /,/, $rest;
+ } else {
+ # Already found the user, now finding attributes...
+ @attribs = $_;
+ };
+
+ foreach $attr (@attribs) {
+ ($attrib,$value) = split /=/, $attr, 2;
+ #TODO: insert sanity checks here!
+ $value = cleanup($value) if (defined($value));
+ $attrib = cleanup($attrib) if (defined($attrib));
+ unless (check_attribs($attrib,$value)) {
+ print "ERROR: something bad with line $.: \"$attrib\", \"$value\"\n";
+ next;
+ };
+ print "attrib: $attrib has value: $value\n" if $debug>8;
+
+ if ( $attrib =~ /Framed-IP-Address/ ) {
+ #user is a static IP user...
+ $static{$user} = 1;
+ user_attribute("group",$user,"static","");
+ };
+
+ if ( $attrib =~ /Password|Simultaneous-Use/ ) {
+ #This is an individual check attribute, so we'll pass it along...
+ user_attribute("check",$user,$attrib,$value);
+ };
+ if ( $attrib =~ /Framed-IP-Address|Framed-IP-Routing|Framed-Routing/ ) {
+ #This is an individual reply attribute, so we'll pass this along...
+ user_attribute("reply",$user,$attrib,$value);
+ };
+ };
+
+};
+
+close USERS;
+exit($database->disconnect); \ No newline at end of file