diff options
Diffstat (limited to 'scripts/sql')
| -rwxr-xr-x | scripts/sql/align_sql_pools.pl | 311 | ||||
| -rwxr-xr-x | scripts/sql/generate_pool_addresses.pl | 456 | ||||
| -rwxr-xr-x | scripts/sql/radsqlrelay | 342 | ||||
| -rwxr-xr-x | scripts/sql/rlm_sqlippool_tool | 961 | ||||
| -rw-r--r-- | scripts/sql/users2mysql.pl | 157 |
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 |