1 package FS::part_export::sqlradius;
4 use vars qw(@ISA @EXPORT_OK $DEBUG %info %options $notes1 $notes2);
7 use FS::Record qw( dbh qsearch qsearchs str2time_sql );
15 @ISA = qw(FS::part_export);
16 @EXPORT_OK = qw( sqlradius_connect );
21 tie %options, 'Tie::IxHash',
22 'datasrc' => { label=>'DBI data source ' },
23 'username' => { label=>'Database username' },
24 'password' => { label=>'Database password' },
25 'usergroup' => { label => 'Group table',
27 options => [qw( usergroup radusergroup ) ],
29 'ignore_accounting' => {
31 label => 'Ignore accounting records from this database'
33 'process_single_realm' => {
35 label => 'Only process one realm of accounting records',
37 'realm' => { label => 'The realm of of accounting records to be processed' },
38 'ignore_long_sessions' => {
40 label => 'Ignore sessions which span billing periods',
44 label => 'Hide IP address information on session reports',
48 label => 'Hide download/upload information on session reports',
50 'show_called_station' => {
52 label => 'Show the Called-Station-ID on session reports', #as a phone number
54 'overlimit_groups' => {
55 label => 'Radius groups to assign to svc_acct which has exceeded its bandwidth or time limit (if not overridden by overlimit_groups global or per-agent config)',
61 option_values => sub {
63 map { $_->groupnum, $_->long_description }
64 qsearch('radius_group', {}),
69 'groups_susp_reason' => { label =>
70 'Radius group mapping to reason (via template user) (svcnum|username|username@domain reasonnum|reason)',
75 label => 'Export RADIUS group attributes to this database',
78 label => 'To send a disconnection request to each RADIUS client when modifying, suspending or deleting an account, enter a ssh connection string (username@host) with access to the radclient program',
80 'disconnect_port' => {
81 label => 'Port to send disconnection requests to, default 1700',
83 'disconnect_ignore_error' => {
84 label => 'Ignore disconnection request errors',
90 Real-time export of <b>radcheck</b>, <b>radreply</b> and <b>usergroup</b>/<b>radusergroup</b>
91 tables to any SQL database for
92 <a href="http://www.freeradius.org/">FreeRADIUS</a>
93 or <a href="http://radius.innercite.com/">ICRADIUS</a>.
97 An existing RADIUS database will be updated in realtime, but you can use
98 <a href="http://www.freeside.biz/mediawiki/index.php/Freeside:1.9:Documentation:Developer/bin/freeside-sqlradius-reset">freeside-sqlradius-reset</a>
99 to delete the entire RADIUS database and repopulate the tables from the
100 Freeside database. See the
101 <a href="http://search.cpan.org/dist/DBI/DBI.pm#connect">DBI documentation</a>
103 <a href="http://search.cpan.org/search?mode=module&query=DBD%3A%3A">documentation for your DBD</a>
104 for the exact syntax of a DBI data source.
106 <li>Using FreeRADIUS 0.9.0 with the PostgreSQL backend, the db_postgresql.sql schema and postgresql.conf queries contain incompatible changes. This is fixed in 0.9.1. Only new installs with 0.9.0 and PostgreSQL are affected - upgrades and other database backends and versions are unaffected.
107 <li>Using ICRADIUS, add a dummy "op" column to your database:
109 ALTER TABLE radcheck ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='<br>
110 ALTER TABLE radreply ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='<br>
111 ALTER TABLE radgroupcheck ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='<br>
112 ALTER TABLE radgroupreply ADD COLUMN op VARCHAR(2) NOT NULL DEFAULT '=='
114 <li>Using Radiator, see the
115 <a href="http://www.open.com.au/radiator/faq.html#38">Radiator FAQ</a>
116 for configuration information.
122 'desc' => 'Real-time export to SQL-backed RADIUS (FreeRADIUS, ICRADIUS)',
123 'options' => \%options,
126 'nas' => 'Y', # show export_nas selection in UI
127 'default_svc_class' => 'Internet',
129 'This export does not export RADIUS realms (see also '.
130 'sqlradius_withdomain). '.
134 sub _groups_susp_reason_map { map { reverse( /^\s*(\S+)\s*(.*)$/ ) }
135 split( "\n", shift->option('groups_susp_reason'));
138 sub rebless { shift; }
140 sub export_username { # override for other svcdb
141 my($self, $svc_acct) = (shift, shift);
142 warn "export_username called on $self with arg $svc_acct" if $DEBUG > 1;
146 sub radius_reply { #override for other svcdb
147 my($self, $svc_acct) = (shift, shift);
148 my %every = $svc_acct->EVERY::radius_reply;
149 map { @$_ } values %every;
152 sub radius_check { #override for other svcdb
153 my($self, $svc_acct) = (shift, shift);
154 my %every = $svc_acct->EVERY::radius_check;
155 map { @$_ } values %every;
159 my($self, $svc_x) = (shift, shift);
161 foreach my $table (qw(reply check)) {
162 my $method = "radius_$table";
163 my %attrib = $self->$method($svc_x);
164 next unless keys %attrib;
165 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'insert',
166 $table, $self->export_username($svc_x), %attrib );
167 return $err_or_queue unless ref($err_or_queue);
169 my @groups = $svc_x->radius_groups('hashref');
171 cluck localtime(). ": queuing usergroup_insert for ". $svc_x->svcnum.
172 " (". $self->export_username($svc_x). " with ". join(", ", @groups)
174 my $usergroup = $self->option('usergroup') || 'usergroup';
175 my $err_or_queue = $self->sqlradius_queue(
176 $svc_x->svcnum, 'usergroup_insert',
177 $self->export_username($svc_x), $usergroup, @groups );
178 return $err_or_queue unless ref($err_or_queue);
183 sub _export_replace {
184 my( $self, $new, $old ) = (shift, shift, shift);
186 local $SIG{HUP} = 'IGNORE';
187 local $SIG{INT} = 'IGNORE';
188 local $SIG{QUIT} = 'IGNORE';
189 local $SIG{TERM} = 'IGNORE';
190 local $SIG{TSTP} = 'IGNORE';
191 local $SIG{PIPE} = 'IGNORE';
193 my $oldAutoCommit = $FS::UID::AutoCommit;
194 local $FS::UID::AutoCommit = 0;
198 if ( $self->export_username($old) ne $self->export_username($new) ) {
199 my $usergroup = $self->option('usergroup') || 'usergroup';
200 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'rename',
201 $self->export_username($new), $self->export_username($old), $usergroup );
202 unless ( ref($err_or_queue) ) {
203 $dbh->rollback if $oldAutoCommit;
204 return $err_or_queue;
206 $jobnum = $err_or_queue->jobnum;
209 foreach my $table (qw(reply check)) {
210 my $method = "radius_$table";
211 my %new = $self->$method($new);
212 my %old = $self->$method($old);
213 if ( grep { !exists $old{$_} #new attributes
214 || $new{$_} ne $old{$_} #changed
217 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert',
218 $table, $self->export_username($new), %new );
219 unless ( ref($err_or_queue) ) {
220 $dbh->rollback if $oldAutoCommit;
221 return $err_or_queue;
224 my $error = $err_or_queue->depend_insert( $jobnum );
226 $dbh->rollback if $oldAutoCommit;
230 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
233 my @del = grep { !exists $new{$_} } keys %old;
235 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'attrib_delete',
236 $table, $self->export_username($new), @del );
237 unless ( ref($err_or_queue) ) {
238 $dbh->rollback if $oldAutoCommit;
239 return $err_or_queue;
242 my $error = $err_or_queue->depend_insert( $jobnum );
244 $dbh->rollback if $oldAutoCommit;
248 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
253 my (@oldgroups) = $old->radius_groups('hashref');
254 my (@newgroups) = $new->radius_groups('hashref');
255 ($error,$jobnum) = $self->sqlreplace_usergroups( $new->svcnum,
256 $self->export_username($new),
257 $jobnum ? $jobnum : '',
262 $dbh->rollback if $oldAutoCommit;
266 # radius database is used for authorization, so to avoid users reauthorizing
267 # before the database changes, disconnect users after changing database
268 if ($self->option('disconnect_ssh')) {
269 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'user_disconnect',
270 'disconnect_ssh' => $self->option('disconnect_ssh'),
271 'svc_acct_username' => $old->username,
272 'disconnect_port' => $self->option('disconnect_port'),
273 'ignore_error' => $self->option('disconnect_ignore_error'),
275 unless ( ref($err_or_queue) ) {
276 $dbh->rollback if $oldAutoCommit;
277 return $err_or_queue;
280 my $error = $err_or_queue->depend_insert( $jobnum );
282 $dbh->rollback if $oldAutoCommit;
288 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
293 #false laziness w/broadband_sqlradius.pm
294 sub _export_suspend {
295 my( $self, $svc_acct ) = (shift, shift);
297 my $new = $svc_acct->clone_suspended;
299 local $SIG{HUP} = 'IGNORE';
300 local $SIG{INT} = 'IGNORE';
301 local $SIG{QUIT} = 'IGNORE';
302 local $SIG{TERM} = 'IGNORE';
303 local $SIG{TSTP} = 'IGNORE';
304 local $SIG{PIPE} = 'IGNORE';
306 my $oldAutoCommit = $FS::UID::AutoCommit;
307 local $FS::UID::AutoCommit = 0;
312 my @newgroups = $self->suspended_usergroups($svc_acct);
314 unless (@newgroups) { #don't change password if assigning to a suspended group
316 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'insert',
317 'check', $self->export_username($new), $new->radius_check );
318 unless ( ref($err_or_queue) ) {
319 $dbh->rollback if $oldAutoCommit;
320 return $err_or_queue;
322 $jobnum = $err_or_queue->jobnum;
327 $self->sqlreplace_usergroups(
329 $self->export_username($new),
331 [ $svc_acct->radius_groups('hashref') ],
335 $dbh->rollback if $oldAutoCommit;
339 # radius database is used for authorization, so to avoid users reauthorizing
340 # before the database changes, disconnect users after changing database
341 if ($self->option('disconnect_ssh')) {
342 my $err_or_queue = $self->sqlradius_queue( $new->svcnum, 'user_disconnect',
343 'disconnect_ssh' => $self->option('disconnect_ssh'),
344 'svc_acct_username' => $svc_acct->username,
345 'disconnect_port' => $self->option('disconnect_port'),
347 unless ( ref($err_or_queue) ) {
348 $dbh->rollback if $oldAutoCommit;
349 return $err_or_queue;
352 my $error = $err_or_queue->depend_insert( $jobnum );
354 $dbh->rollback if $oldAutoCommit;
360 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
365 sub _export_unsuspend {
366 my( $self, $svc_x ) = (shift, shift);
368 local $SIG{HUP} = 'IGNORE';
369 local $SIG{INT} = 'IGNORE';
370 local $SIG{QUIT} = 'IGNORE';
371 local $SIG{TERM} = 'IGNORE';
372 local $SIG{TSTP} = 'IGNORE';
373 local $SIG{PIPE} = 'IGNORE';
375 my $oldAutoCommit = $FS::UID::AutoCommit;
376 local $FS::UID::AutoCommit = 0;
379 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'insert',
380 'check', $self->export_username($svc_x), $self->radius_check($svc_x) );
381 unless ( ref($err_or_queue) ) {
382 $dbh->rollback if $oldAutoCommit;
383 return $err_or_queue;
387 my (@oldgroups) = $self->suspended_usergroups($svc_x);
388 $error = $self->sqlreplace_usergroups(
390 $self->export_username($svc_x),
393 [ $svc_x->radius_groups('hashref') ],
396 $dbh->rollback if $oldAutoCommit;
399 $dbh->commit or die $dbh->errstr if $oldAutoCommit;
405 my( $self, $svc_x ) = (shift, shift);
409 my $usergroup = $self->option('usergroup') || 'usergroup';
410 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'delete',
411 $self->export_username($svc_x), $usergroup );
412 $jobnum = $err_or_queue->jobnum;
414 # radius database is used for authorization, so to avoid users reauthorizing
415 # before the database changes, disconnect users after changing database
416 if ($self->option('disconnect_ssh')) {
417 my $err_or_queue = $self->sqlradius_queue( $svc_x->svcnum, 'user_disconnect',
418 'disconnect_ssh' => $self->option('disconnect_ssh'),
419 'svc_acct_username' => $svc_x->username,
420 'disconnect_port' => $self->option('disconnect_port'),
421 'ignore_error' => $self->option('disconnect_ignore_error'),
423 return $err_or_queue unless ref($err_or_queue);
425 my $error = $err_or_queue->depend_insert( $jobnum );
426 return $error if $error;
430 ref($err_or_queue) ? '' : $err_or_queue;
433 sub sqlradius_queue {
434 my( $self, $svcnum, $method ) = (shift, shift, shift);
436 my $queue = new FS::queue {
438 'job' => "FS::part_export::sqlradius::sqlradius_$method",
441 $self->option('datasrc'),
442 $self->option('username'),
443 $self->option('password'),
448 sub suspended_usergroups {
449 my ($self, $svc_x) = (shift, shift);
451 return () unless $svc_x;
453 my $svc_table = $svc_x->table;
455 #false laziness with FS::part_export::shellcommands
456 #subclass part_export?
458 my $r = $svc_x->cust_svc->cust_pkg->last_reason('susp');
459 my %reasonmap = $self->_groups_susp_reason_map;
462 $userspec = $reasonmap{$r->reasonnum}
463 if exists($reasonmap{$r->reasonnum});
464 $userspec = $reasonmap{$r->reason}
465 if (!$userspec && exists($reasonmap{$r->reason}));
468 if ( $userspec =~ /^\d+$/ ){
469 $suspend_svc = qsearchs( $svc_table, { 'svcnum' => $userspec } );
470 } elsif ( $userspec =~ /^\S+\@\S+$/ && $svc_table eq 'svc_acct' ){
471 my ($username,$domain) = split(/\@/, $userspec);
472 for my $user (qsearch( 'svc_acct', { 'username' => $username } )){
473 $suspend_svc = $user if $userspec eq $user->email;
475 }elsif ( $userspec && $svc_table eq 'svc_acct' ){
476 $suspend_svc = qsearchs( 'svc_acct', { 'username' => $userspec } );
479 return $suspend_svc->radius_groups('hashref') if $suspend_svc;
483 sub sqlradius_insert { #subroutine, not method
484 my $dbh = sqlradius_connect(shift, shift, shift);
485 my( $table, $username, %attributes ) = @_;
487 foreach my $attribute ( keys %attributes ) {
489 my $s_sth = $dbh->prepare(
490 "SELECT COUNT(*) FROM rad$table WHERE UserName = ? AND Attribute = ?"
491 ) or die $dbh->errstr;
492 $s_sth->execute( $username, $attribute ) or die $s_sth->errstr;
494 if ( $s_sth->fetchrow_arrayref->[0] ) {
496 my $u_sth = $dbh->prepare(
497 "UPDATE rad$table SET Value = ? WHERE UserName = ? AND Attribute = ?"
498 ) or die $dbh->errstr;
499 $u_sth->execute($attributes{$attribute}, $username, $attribute)
500 or die $u_sth->errstr;
504 my $i_sth = $dbh->prepare(
505 "INSERT INTO rad$table ( UserName, Attribute, op, Value ) ".
506 "VALUES ( ?, ?, ?, ? )"
507 ) or die $dbh->errstr;
511 ( $attribute eq 'Password' ? '==' : ':=' ),
512 $attributes{$attribute},
513 ) or die $i_sth->errstr;
521 sub sqlradius_usergroup_insert { #subroutine, not method
522 my $dbh = sqlradius_connect(shift, shift, shift);
523 my $username = shift;
524 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
527 my $s_sth = $dbh->prepare(
528 "SELECT COUNT(*) FROM $usergroup WHERE UserName = ? AND GroupName = ?"
529 ) or die $dbh->errstr;
531 my $sth = $dbh->prepare(
532 "INSERT INTO $usergroup ( UserName, GroupName, Priority ) VALUES ( ?, ?, ? )"
533 ) or die $dbh->errstr;
535 foreach ( @groups ) {
536 my $group = $_->{'groupname'};
537 my $priority = $_->{'priority'};
538 $s_sth->execute( $username, $group ) or die $s_sth->errstr;
539 if ($s_sth->fetchrow_arrayref->[0]) {
540 warn localtime() . ": sqlradius_usergroup_insert attempted to reinsert " .
541 "$group for $username\n"
545 $sth->execute( $username, $group, $priority )
546 or die "can't insert into groupname table: ". $sth->errstr;
548 if ( $s_sth->{Active} ) {
549 warn "sqlradius s_sth still active; calling ->finish()";
552 if ( $sth->{Active} ) {
553 warn "sqlradius sth still active; calling ->finish()";
559 sub sqlradius_usergroup_delete { #subroutine, not method
560 my $dbh = sqlradius_connect(shift, shift, shift);
561 my $username = shift;
562 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
565 my $sth = $dbh->prepare(
566 "DELETE FROM $usergroup WHERE UserName = ? AND GroupName = ?"
567 ) or die $dbh->errstr;
568 foreach ( @groups ) {
569 my $group = $_->{'groupname'};
570 $sth->execute( $username, $group )
571 or die "can't delete from groupname table: ". $sth->errstr;
576 sub sqlradius_rename { #subroutine, not method
577 my $dbh = sqlradius_connect(shift, shift, shift);
578 my($new_username, $old_username) = (shift, shift);
579 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
580 foreach my $table (qw(radreply radcheck), $usergroup ) {
581 my $sth = $dbh->prepare("UPDATE $table SET Username = ? WHERE UserName = ?")
583 $sth->execute($new_username, $old_username)
584 or die "can't update $table: ". $sth->errstr;
589 sub sqlradius_attrib_delete { #subroutine, not method
590 my $dbh = sqlradius_connect(shift, shift, shift);
591 my( $table, $username, @attrib ) = @_;
593 foreach my $attribute ( @attrib ) {
594 my $sth = $dbh->prepare(
595 "DELETE FROM rad$table WHERE UserName = ? AND Attribute = ?" )
597 $sth->execute($username,$attribute)
598 or die "can't delete from rad$table table: ". $sth->errstr;
603 sub sqlradius_delete { #subroutine, not method
604 my $dbh = sqlradius_connect(shift, shift, shift);
605 my $username = shift;
606 my $usergroup = ( $_[0] =~ /^(rad)?usergroup/i ) ? shift : 'usergroup';
608 foreach my $table (qw( radcheck radreply), $usergroup ) {
609 my $sth = $dbh->prepare( "DELETE FROM $table WHERE UserName = ?" );
610 $sth->execute($username)
611 or die "can't delete from $table table: ". $sth->errstr;
616 sub sqlradius_connect {
617 #my($datasrc, $username, $password) = @_;
618 #DBI->connect($datasrc, $username, $password) or die $DBI::errstr;
619 DBI->connect(@_) or die $DBI::errstr;
622 # on success, returns '' in scalar context, ('',$jobnum) in list context
623 # on error, always just returns error
624 sub sqlreplace_usergroups {
625 my ($self, $svcnum, $username, $jobnum, $old, $new) = @_;
627 # (sorta) false laziness with FS::svc_acct::replace
628 my @oldgroups = @$old;
629 my @newgroups = @$new;
631 foreach my $oldgroup ( @oldgroups ) {
632 if ( grep { $oldgroup eq $_ } @newgroups ) {
633 @newgroups = grep { $oldgroup ne $_ } @newgroups;
636 push @delgroups, $oldgroup;
639 my $usergroup = $self->option('usergroup') || 'usergroup';
642 my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_delete',
643 $username, $usergroup, @delgroups );
645 unless ref($err_or_queue);
647 my $error = $err_or_queue->depend_insert( $jobnum );
648 return $error if $error;
650 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
654 cluck localtime(). ": queuing usergroup_insert for $svcnum ($username) ".
655 "with ". join(", ", @newgroups)
657 my $err_or_queue = $self->sqlradius_queue( $svcnum, 'usergroup_insert',
658 $username, $usergroup, @newgroups );
660 unless ref($err_or_queue);
662 my $error = $err_or_queue->depend_insert( $jobnum );
663 return $error if $error;
665 $jobnum = $err_or_queue->jobnum; # chain all of these dependencies
667 wantarray ? ('',$jobnum) : '';
673 =item usage_sessions HASHREF
675 =item usage_sessions TIMESTAMP_START TIMESTAMP_END [ SVC_ACCT [ IP [ PREFIX [ SQL_SELECT ] ] ] ]
677 New-style: pass a hashref with the following keys:
681 =item stoptime_start - Lower bound for AcctStopTime, as a UNIX timestamp
683 =item stoptime_end - Upper bound for AcctStopTime, as a UNIX timestamp
685 =item session_status - 'closed' to only show records with AcctStopTime,
686 'open' to only show records I<without> AcctStopTime, empty to show both.
688 =item starttime_start - Lower bound for AcctStartTime, as a UNIX timestamp
690 =item starttime_end - Upper bound for AcctStartTime, as a UNIX timestamp
702 TIMESTAMP_START and TIMESTAMP_END are specified as UNIX timestamps; see
703 L<perlfunc/"time">. Also see L<Time::Local> and L<Date::Parse> for conversion
706 SVC_ACCT, if specified, limits the results to the specified account.
708 IP, if specified, limits the results to the specified IP address.
710 PREFIX, if specified, limits the results to records with a matching
713 #SQL_SELECT defaults to * if unspecified. It can be useful to set it to
714 #SUM(acctsessiontime) or SUM(AcctInputOctets), etc.
716 Returns an arrayref of hashrefs with the following fields:
722 =item framedipaddress
728 =item acctsessiontime
730 =item acctinputoctets
732 =item acctoutputoctets
734 =item callingstationid
736 =item calledstationid
742 #some false laziness w/cust_svc::seconds_since_sqlradacct
748 my($start, $end, $svc_acct, $ip, $prefix) = ( '', '', '', '', '');
752 $start = $opt->{stoptime_start};
753 $end = $opt->{stoptime_end};
754 $svc_acct = $opt->{svc} || $opt->{svc_acct};
756 $prefix = $opt->{prefix};
757 $summarize = $opt->{summarize};
759 ( $start, $end ) = splice(@_, 0, 2);
760 $svc_acct = @_ ? shift : '';
761 $ip = @_ ? shift : '';
762 $prefix = @_ ? shift : '';
763 #my $select = @_ ? shift : '*';
768 return [] if $self->option('ignore_accounting');
770 my $dbh = sqlradius_connect( map $self->option($_),
771 qw( datasrc username password ) );
773 #select a unix time conversion function based on database type
774 my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
777 qw( username realm framedipaddress
778 acctsessiontime acctinputoctets acctoutputoctets
779 callingstationid calledstationid
781 "$str2time acctstarttime ) as acctstarttime",
782 "$str2time acctstoptime ) as acctstoptime",
785 @fields = ( 'username', 'sum(acctsessiontime) as acctsessiontime', 'sum(acctinputoctets) as acctinputoctets',
786 'sum(acctoutputoctets) as acctoutputoctets',
793 my $username = $self->export_username($svc_acct);
794 if ( $username =~ /^([^@]+)\@([^@]+)$/ ) {
795 push @where, '( UserName = ? OR ( UserName = ? AND Realm = ? ) )';
796 push @param, $username, $1, $2;
798 push @where, 'UserName = ?';
799 push @param, $username;
803 if ($self->option('process_single_realm')) {
804 push @where, 'Realm = ?';
805 push @param, $self->option('realm');
809 push @where, ' FramedIPAddress = ?';
813 if ( length($prefix) ) {
814 #assume sip: for now, else things get ugly trying to match /^\w+:$prefix/
815 push @where, " CalledStationID LIKE 'sip:$prefix\%'";
818 my $acctstoptime = '';
819 if ( $opt->{session_status} ne 'open' ) {
821 $acctstoptime .= "$str2time AcctStopTime ) >= ?";
823 $acctstoptime .= ' AND ' if $end;
826 $acctstoptime .= "$str2time AcctStopTime ) <= ?";
830 if ( $opt->{session_status} ne 'closed' ) {
831 if ( $acctstoptime ) {
832 $acctstoptime = "( ( $acctstoptime ) OR AcctStopTime IS NULL )";
834 $acctstoptime = 'AcctStopTime IS NULL';
837 push @where, $acctstoptime;
839 if ( $opt->{starttime_start} ) {
840 push @where, "$str2time AcctStartTime ) >= ?";
841 push @param, $opt->{starttime_start};
843 if ( $opt->{starttime_end} ) {
844 push @where, "$str2time AcctStartTime ) <= ?";
845 push @param, $opt->{starttime_end};
848 my $where = join(' AND ', @where);
849 $where = "WHERE $where" if $where;
852 $groupby = 'GROUP BY username' if $summarize;
854 my $orderby = 'ORDER BY AcctStartTime DESC';
855 $orderby = '' if $summarize;
857 my $sql = 'SELECT '. join(', ', @fields).
858 " FROM radacct $where $groupby $orderby";
861 warn join(',', @param);
863 my $sth = $dbh->prepare($sql) or die $dbh->errstr;
864 $sth->execute(@param) or die $sth->errstr;
866 [ map { { %$_ } } @{ $sth->fetchall_arrayref({}) } ];
877 my $conf = new FS::Conf;
880 my $dbh = sqlradius_connect( map $self->option($_),
881 qw( datasrc username password ) );
883 my $str2time = str2time_sql( $dbh->{Driver}->{Name} );
884 my @fields = qw( radacctid username realm acctsessiontime );
889 my $sth = $dbh->prepare("
890 SELECT RadAcctId, UserName, Realm, AcctSessionTime,
891 $str2time AcctStartTime), $str2time AcctStopTime),
892 AcctInputOctets, AcctOutputOctets
894 WHERE FreesideStatus IS NULL
895 AND AcctStopTime IS NOT NULL
896 ") or die $dbh->errstr;
897 $sth->execute() or die $sth->errstr;
899 while ( my $row = $sth->fetchrow_arrayref ) {
900 my($RadAcctId, $UserName, $Realm, $AcctSessionTime, $AcctStartTime,
901 $AcctStopTime, $AcctInputOctets, $AcctOutputOctets) = @$row;
902 warn "processing record: ".
903 "$RadAcctId ($UserName\@$Realm for ${AcctSessionTime}s"
906 my $fs_username = $UserName;
908 $fs_username = lc($fs_username) unless $conf->exists('username-uppercase');
910 #my %search = ( 'username' => $fs_username );
913 my $errinfo = "for RADIUS detail RadAcctID $RadAcctId ".
914 "(UserName $UserName, Realm $Realm)";
917 if ( ref($self) =~ /withdomain/ ) { #well, should be a callback to that
922 } elsif ( $fs_username =~ /\@/ ) {
923 ($fs_username, $domain) = split('@', $fs_username);
925 warn 'WARNING: nothing Realm column and no @realm in UserName column '.
926 "$errinfo -- skipping\n" if $DEBUG;
927 $status = 'skipped (no realm)';
930 $extra_sql = " AND '$domain' = ( SELECT domain FROM svc_domain
931 WHERE svc_domain.svcnum = svc_acct.domsvc ) ";
934 my $oldAutoCommit = $FS::UID::AutoCommit; # can't undo side effects, but at
935 local $FS::UID::AutoCommit = 0; # least we can avoid over counting
941 if ( $self->option('process_single_realm')
942 && $self->option('realm') ne $Realm )
944 warn "WARNING: wrong realm $errinfo - skipping\n" if $DEBUG;
947 grep { qsearch( 'export_svc', { 'exportnum' => $self->exportnum,
948 'svcpart' => $_->cust_svc->svcpart,
953 { 'username' => $fs_username },
959 warn "WARNING: no svc_acct record found $errinfo - skipping\n";
960 } elsif ( scalar(@svc_acct) > 1 ) {
961 warn "WARNING: multiple svc_acct records found $errinfo - skipping\n";
964 my $svc_acct = $svc_acct[0];
965 warn "found svc_acct ". $svc_acct->svcnum. " $errinfo\n" if $DEBUG;
967 $svc_acct->last_login($AcctStartTime);
968 $svc_acct->last_logout($AcctStopTime);
970 my $session_time = $AcctStopTime;
971 $session_time = $AcctStartTime
972 if $self->option('ignore_long_sessions');
974 my $cust_pkg = $svc_acct->cust_svc->cust_pkg;
975 if ( $cust_pkg && $session_time < ( $cust_pkg->last_bill
976 || $cust_pkg->setup ) ) {
977 $status = 'skipped (too old)';
980 push @st, _try_decrement($svc_acct,'seconds', $AcctSessionTime);
981 push @st, _try_decrement($svc_acct,'upbytes', $AcctInputOctets);
982 push @st, _try_decrement($svc_acct,'downbytes', $AcctOutputOctets);
983 push @st, _try_decrement($svc_acct,'totalbytes', $AcctInputOctets
984 + $AcctOutputOctets);
985 $status=join(' ', @st);
992 warn "setting FreesideStatus to $status $errinfo\n" if $DEBUG;
993 my $psth = $dbh->prepare("UPDATE radacct
994 SET FreesideStatus = ?
996 ) or die $dbh->errstr;
997 $psth->execute($status, $RadAcctId) or die $psth->errstr;
999 $fdbh->commit or die $fdbh->errstr if $oldAutoCommit;
1005 sub _try_decrement {
1006 my ($svc_acct, $column, $amount) = @_;
1007 if ( $svc_acct->$column !~ /^$/ ) {
1008 warn " svc_acct.$column found (". $svc_acct->$column.
1009 ") - decrementing\n"
1011 my $method = 'decrement_' . $column;
1012 my $error = $svc_acct->$method($amount);
1013 die $error if $error;
1016 warn " no existing $column value for svc_acct - skipping\n" if $DEBUG;
1021 =item export_nas_insert NAS
1023 =item export_nas_delete NAS
1025 =item export_nas_replace NEW_NAS OLD_NAS
1027 Update the NAS table (allowed RADIUS clients) on the attached RADIUS
1028 server. Currently requires the table to be named 'nas' and to follow
1029 the stock schema (/etc/freeradius/nas.sql).
1033 sub export_nas_insert { shift->export_nas_action('insert', @_); }
1034 sub export_nas_delete { shift->export_nas_action('delete', @_); }
1035 sub export_nas_replace { shift->export_nas_action('replace', @_); }
1037 sub export_nas_action {
1039 my ($action, $new, $old) = @_;
1040 # find the NAS in the target table by its name
1041 my $nasname = ($action eq 'replace') ? $old->nasname : $new->nasname;
1042 my $nasnum = $new->nasnum;
1044 my $err_or_queue = $self->sqlradius_queue('', "nas_$action",
1045 nasname => $nasname,
1048 return $err_or_queue unless ref $err_or_queue;
1052 sub sqlradius_nas_insert {
1053 my $dbh = sqlradius_connect(shift, shift, shift);
1055 my $nas = qsearchs('nas', { nasnum => $opt{'nasnum'} })
1056 or die "nasnum ".$opt{'nasnum'}.' not found';
1057 # insert actual NULLs where FS::Record has translated to empty strings
1058 my @values = map { length($nas->$_) ? $nas->$_ : undef }
1059 qw( nasname shortname type secret server community description );
1060 my $sth = $dbh->prepare('INSERT INTO nas
1061 (nasname, shortname, type, secret, server, community, description)
1062 VALUES (?, ?, ?, ?, ?, ?, ?)');
1063 $sth->execute(@values) or die $dbh->errstr;
1066 sub sqlradius_nas_delete {
1067 my $dbh = sqlradius_connect(shift, shift, shift);
1069 my $sth = $dbh->prepare('DELETE FROM nas WHERE nasname = ?');
1070 $sth->execute($opt{'nasname'}) or die $dbh->errstr;
1073 sub sqlradius_nas_replace {
1074 my $dbh = sqlradius_connect(shift, shift, shift);
1076 my $nas = qsearchs('nas', { nasnum => $opt{'nasnum'} })
1077 or die "nasnum ".$opt{'nasnum'}.' not found';
1078 my @values = map {$nas->$_}
1079 qw( nasname shortname type secret server community description );
1080 my $sth = $dbh->prepare('UPDATE nas SET
1081 nasname = ?, shortname = ?, type = ?, secret = ?,
1082 server = ?, community = ?, description = ?
1083 WHERE nasname = ?');
1084 $sth->execute(@values, $opt{'nasname'}) or die $dbh->errstr;
1087 =item export_attr_insert RADIUS_ATTR
1089 =item export_attr_delete RADIUS_ATTR
1091 =item export_attr_replace NEW_RADIUS_ATTR OLD_RADIUS_ATTR
1093 Update the group attribute tables (radgroupcheck and radgroupreply) on
1094 the RADIUS server. In delete and replace actions, the existing records
1095 are identified by the combination of group name and attribute name.
1097 In the special case where attributes are being replaced because a group
1098 name (L<FS::radius_group>->groupname) is changing, the pseudo-field
1099 'groupname' must be set in OLD_RADIUS_ATTR.
1103 # some false laziness with NAS export stuff...
1105 sub export_attr_insert { shift->export_attr_action('insert', @_); }
1107 sub export_attr_delete { shift->export_attr_action('delete', @_); }
1109 sub export_attr_replace { shift->export_attr_action('replace', @_); }
1111 sub export_attr_action {
1113 my ($action, $new, $old) = @_;
1116 if ( $action eq 'delete' ) {
1119 if ( $action eq 'delete' or $action eq 'replace' ) {
1120 # delete based on an exact match
1122 attrname => $old->attrname,
1123 attrtype => $old->attrtype,
1124 groupname => $old->groupname || $old->radius_group->groupname,
1126 value => $old->value,
1128 $err_or_queue = $self->sqlradius_queue('', 'attr_delete', %opt);
1129 return $err_or_queue unless ref $err_or_queue;
1131 # this probably doesn't matter, but just to be safe...
1132 my $jobnum = $err_or_queue->jobnum if $action eq 'replace';
1133 if ( $action eq 'replace' or $action eq 'insert' ) {
1135 attrname => $new->attrname,
1136 attrtype => $new->attrtype,
1137 groupname => $new->radius_group->groupname,
1139 value => $new->value,
1141 $err_or_queue = $self->sqlradius_queue('', 'attr_insert', %opt);
1142 $err_or_queue->depend_insert($jobnum) if $jobnum;
1143 return $err_or_queue unless ref $err_or_queue;
1148 sub sqlradius_attr_insert {
1149 my $dbh = sqlradius_connect(shift, shift, shift);
1153 # make sure $table is completely safe
1154 if ( $opt{'attrtype'} eq 'C' ) {
1155 $table = 'radgroupcheck';
1157 elsif ( $opt{'attrtype'} eq 'R' ) {
1158 $table = 'radgroupreply';
1161 die "unknown attribute type '$opt{attrtype}'";
1164 my @values = @opt{ qw(groupname attrname op value) };
1165 my $sth = $dbh->prepare(
1166 'INSERT INTO '.$table.' (groupname, attribute, op, value) VALUES (?,?,?,?)'
1168 $sth->execute(@values) or die $dbh->errstr;
1171 sub sqlradius_attr_delete {
1172 my $dbh = sqlradius_connect(shift, shift, shift);
1176 if ( $opt{'attrtype'} eq 'C' ) {
1177 $table = 'radgroupcheck';
1179 elsif ( $opt{'attrtype'} eq 'R' ) {
1180 $table = 'radgroupreply';
1183 die "unknown attribute type '".$opt{'attrtype'}."'";
1186 my @values = @opt{ qw(groupname attrname op value) };
1187 my $sth = $dbh->prepare(
1188 'DELETE FROM '.$table.
1189 ' WHERE groupname = ? AND attribute = ? AND op = ? AND value = ?'.
1192 $sth->execute(@values) or die $dbh->errstr;
1195 #sub sqlradius_attr_replace { no longer needed
1197 =item export_group_replace NEW OLD
1199 Replace the L<FS::radius_group> object OLD with NEW. This will change
1200 the group name and priority in all radusergroup records, and the group
1201 name in radgroupcheck and radgroupreply.
1205 sub export_group_replace {
1207 my ($new, $old) = @_;
1208 return '' if $new->groupname eq $old->groupname
1209 and $new->priority == $old->priority;
1211 my $err_or_queue = $self->sqlradius_queue(
1214 ($self->option('usergroup') || 'usergroup'),
1218 return $err_or_queue unless ref $err_or_queue;
1222 sub sqlradius_group_replace {
1223 my $dbh = sqlradius_connect(shift, shift, shift);
1224 my $usergroup = shift;
1225 $usergroup =~ /^(rad)?usergroup$/
1226 or die "bad usergroup table name: $usergroup";
1227 my ($new, $old) = (shift, shift);
1228 # apply renames to check/reply attribute tables
1229 if ( $new->{'groupname'} ne $old->{'groupname'} ) {
1230 foreach my $table (qw(radgroupcheck radgroupreply)) {
1231 my $sth = $dbh->prepare(
1232 'UPDATE '.$table.' SET groupname = ? WHERE groupname = ?'
1234 $sth->execute($new->{'groupname'}, $old->{'groupname'})
1235 or die $dbh->errstr;
1238 # apply renames and priority changes to usergroup table
1239 my $sth = $dbh->prepare(
1240 'UPDATE '.$usergroup.' SET groupname = ?, priority = ? WHERE groupname = ?'
1242 $sth->execute($new->{'groupname'}, $new->{'priority'}, $old->{'groupname'})
1243 or die $dbh->errstr;
1246 =item sqlradius_user_disconnect
1248 For a specified user, sends a disconnect request to all nas in the server database.
1250 Accepts L</sqlradius_connect> connection input and the following named parameters:
1252 I<disconnect_ssh> - user@host with access to radclient program (required)
1254 I<svc_acct_username> - the user to be disconnected (required)
1256 I<disconnect_port> - the port (on the nas) to send disconnect requests to (defaults to 1700)
1258 I<ignore_error> - do not die on error with the disconnect request
1260 Note this is NOT the opposite of sqlradius_connect.
1264 sub sqlradius_user_disconnect {
1265 my $dbh = sqlradius_connect(shift, shift, shift);
1268 my $sth = $dbh->prepare('select nasname, secret from nas') or die $dbh->errstr;
1269 $sth->execute() or die $dbh->errstr;
1270 my $nas = $sth->fetchall_arrayref({});
1273 die "No nas found in radius db" unless @$nas;
1274 # set up ssh connection
1275 my $ssh = Net::OpenSSH->new($opt{'disconnect_ssh'});
1276 die "Couldn't establish SSH connection: " . $ssh->error
1278 # send individual disconnect requests
1279 my $user = $opt{'svc_acct_username'}; #svc_acct username
1280 my $port = $opt{'disconnect_port'} || 1700; #or should we pull this from the db?
1282 foreach my $nas (@$nas) {
1283 my $nasname = $nas->{'nasname'};
1284 my $secret = $nas->{'secret'};
1285 my $command = qq(echo "User-Name=$user" | radclient -r 1 $nasname:$port disconnect '$secret');
1286 my ($output, $errput) = $ssh->capture2($command);
1287 $error .= "Error running $command: $errput " . $ssh->error . " "
1288 if $errput || $ssh->error;
1290 $error .= "Some clients may have successfully disconnected"
1291 if $error && (@$nas > 1);
1292 $error = "No clients found"
1294 die $error if $error && !$opt{'ignore_error'};
1299 # class method to fetch groups/attributes from the sqlradius install on upgrade
1302 sub _upgrade_exporttype {
1303 # do this only if the radius_attr table is empty
1304 local $FS::radius_attr::noexport_hack = 1;
1306 return if qsearch('radius_attr', {});
1308 foreach my $self ($class->all_sqlradius) {
1309 my $error = $self->import_attrs;
1310 die "exportnum ".$self->exportnum.":\n$error\n" if $error;
1317 my $dbh = DBI->connect( map $self->option($_),
1318 qw( datasrc username password ) );
1320 warn "Error connecting to RADIUS server: $DBI::errstr\n";
1324 my $usergroup = $self->option('usergroup') || 'usergroup';
1326 warn "Importing RADIUS groups and attributes from ".$self->option('datasrc').
1329 # map out existing groups and attrs
1332 foreach my $radius_group ( qsearch('radius_group', {}) ) {
1333 $attrs_of{$radius_group->groupname} = +{
1334 map { $_->attrname => $_ } $radius_group->radius_attr
1336 $groupnum_of{$radius_group->groupname} = $radius_group->groupnum;
1339 # get groupnames from radgroupcheck and radgroupreply
1341 SELECT groupname, attribute, op, value, \'C\' FROM radgroupcheck
1343 SELECT groupname, attribute, op, value, \'R\' FROM radgroupreply';
1344 my @fixes; # things that need to be changed on the radius db
1345 foreach my $row ( @{ $dbh->selectall_arrayref($sql) } ) {
1346 my ($groupname, $attrname, $op, $value, $attrtype) = @$row;
1347 warn "$groupname.$attrname\n";
1348 if ( !exists($groupnum_of{$groupname}) ) {
1349 my $radius_group = new FS::radius_group {
1350 'groupname' => $groupname,
1353 $error = $radius_group->insert;
1355 warn "error inserting group $groupname: $error";
1356 next;#don't continue trying to insert the attribute
1358 $attrs_of{$groupname} = {};
1359 $groupnum_of{$groupname} = $radius_group->groupnum;
1362 my $a = $attrs_of{$groupname};
1363 my $old = $a->{$attrname};
1366 if ( $attrtype eq 'R' ) {
1367 # Freeradius tolerates illegal operators in reply attributes. We don't.
1368 if ( !grep ($_ eq $op, FS::radius_attr->ops('R')) ) {
1369 warn "$groupname.$attrname: changing $op to +=\n";
1370 # Make a note to change it in the db
1372 'UPDATE radgroupreply SET op = \'+=\' WHERE groupname = ? AND attribute = ? AND op = ? AND VALUE = ?',
1373 $groupname, $attrname, $op, $value
1375 # and import it correctly.
1380 if ( defined $old ) {
1382 $new = new FS::radius_attr {
1387 $error = $new->replace($old);
1389 warn "error modifying attr $attrname: $error";
1394 $new = new FS::radius_attr {
1395 'groupnum' => $groupnum_of{$groupname},
1396 'attrname' => $attrname,
1397 'attrtype' => $attrtype,
1401 $error = $new->insert;
1403 warn "error inserting attr $attrname: $error" if $error;
1407 $attrs_of{$groupname}->{$attrname} = $new;
1411 my ($sql, @args) = @$_;
1412 my $sth = $dbh->prepare($sql);
1413 $sth->execute(@args) or warn $sth->errstr;
1426 #don't just look for ->can('usage_sessions'), we're sqlradius-specific
1427 # (radiator is supposed to be setup with a radacct table)
1428 #i suppose it would be more slick to look for things that inherit from us..
1430 my @part_export = ();
1431 push @part_export, qsearch('part_export', { 'exporttype' => $_ } )
1432 foreach qw( sqlradius sqlradius_withdomain radiator phone_sqlradius
1433 broadband_sqlradius );
1437 sub all_sqlradius_withaccounting {
1439 grep { ! $_->option('ignore_accounting') } $class->all_sqlradius;