Биллинговая система Nodeny

nas4# cat nofire.pl
#!/usr/bin/perl
# ==============================================================================#
#                              #
#      NoDeny - подпрограммы разрешения/блокировки доступа      #
#                              #
# ==============================================================================#

$FW='/sbin/ipfw -q ';

sub Flush
{
 system("$FW table 1 flush");
 system("$FW table 10 flush");
 system("$FW table 11 flush");
 system("$FW table 12 flush");
 system("$FW table 13 flush");
 system("$FW table 14 flush");

 system("$FW pipe flush");
 
}

sub Deny
{
 my ($p, $id, $num, $main_num, $pipe_in, $pipe_out, $ip);
 $p=$_[0];
 $id=$p->{id};
 $num=$p->{num};
 $main_num=$p->{main_num};
 $ip=$p->{ip};
 $pipe_in=&Get_Pipe_Num($main_num);
 $pipe_out=$pipe_in+1;
 
 $pipe=$Pipe_used{$num};
 &Add_Rule("table 10 delete $ip");
 &Add_Rule("table 11 delete $ip");
 &Add_Rule("table 12 delete $ip");
 &Add_Rule("table 13 delete $ip");
 &Add_Rule("table 14 delete $ip");
 
 if( --$Pipe_count{$pipe}<=0 )
 {
 $pipe2=$pipe+1;
 system("$FW pipe $pipe delete");
 system("$FW pipe $pipe2 delete");
 }
 

}
sub Allow
{
 my($p, $num, $main_num, $id, $ip, $speed_in, $speed_out, $pipe_in, $pipe_out, $auth, $dop_param, $s_in, $s_out, $a_ports, $options, $traf);
 $p=$_[0];

 $num=$p->{num};
 $main_num=$p->{main_num};
 $id=$p->{id};
 $ip=$p->{ip};
 $speed_in=$p->{speed_in};
 $speed_out=$p->{speed_out};
 $auth=$p->{auth};
 $dop_param=$p->{dop_param};
 $s_in=$dop_param->{_speed_in};
 $s_out=$dop_param->{_speed_out};
 $a_ports=$dop_param->{_open_ports};
 $udp=$dop_param->{_udp};
 $alldata=$p->{alldata};
 $d_traf=$p->{traf};
# $options=$alldata->{options};
# $options=$p->{options};

 $pipe_in=&Get_Pipe_Num($main_num);
 $pipe_out=$pipe_in+1;

 $Pipe_used{$num}=$pipe_in;
 $Pipe_count{$pipe_in}++;

 system("$FW pipe $pipe_in config bw ${speed_in}Kbit/s");
 system("$FW pipe $pipe_out config bw ${speed_out}Kbit/s");
# system("$FW pipe $pipe_in config bw 2Mbit/s");
# system("$FW pipe $pipe_out config bw 2Mbit/s");


 if ($s_in>0)
 {
    system("$FW pipe $pipe_in config bw ${s_in}Kbit/s");
    system("$FW pipe $pipe_out config bw ${s_in}Kbit/s");
 }

 #system("$FW table 10 add $ip");
 &Add_Tbl_Rule(10,$ip,0);
 return if $auth eq 'ong';

############UA-IX#####################
foreach $i (2..4)
 {
     $speed=$alldata->{"speed$i"};
     $speed or next;
     $pipe=$pipe_in+$i;
     system("$FW pipe $pipe config bw ${speed}Kbit/s");
#     system("$FW table 13 add $ip $pipe");
#     system("$FW table 14 add $ip $pipe");
      &Add_Tbl_Rule(13,$ip,$pipe);
      &Add_Tbl_Rule(14,$ip,$pipe);
 }
######################################

 
# system("$FW table 11 add $ip $pipe_in");
# system("$FW table 12 add $ip $pipe_out");
&Add_Tbl_Rule(11,$ip,$pipe_in);
&Add_Tbl_Rule(12,$ip,$pipe_out);
}
sub Add_Tbl_Rule
{
# return if $Tbl{$_[1]}{$_[0]};
# $Tbl{$_[1]}{$_[0]}=1;
 $Rules.="table $_[0] add $_[1] $_[2]\n";
}

sub Get_Pipe_Num
{
 return($_[0]*2+1000);
}

#############################################
sub Add_Rule
{
 $Rules.=$_[0]."\n";
 $nRules++;
}
#############################################
sub Add_To_Allow_Ip
{
# &Add_Rule("table 1 add $_[0]");
}
############################################
$Del_Files=1;
sub Run_Ipfw_Rules
{
 return unless $Rules;
  my $fname=$Program_dir.'/addip/'.time();
   if (open(F,">>$fname"))
      {
      print F $Rules;
      close (F);
      $Rules='';
      system("$FW $fname");
      unlink $fname if $Del_Files;
      }
}
############################################
sub Delete_From_Allow_Ip
{
# &Add_Rule("table 1 delete $_[0]");
}

sub Add_To_All_Ip {}
sub Delete_From_All_Ip {}
sub Add_To_Table {}
sub Delete_From_Table {}

1;

nas4# ipfw show
00100            0               0 check-state
00200        15222          865560 allow ip from any to any via lo0
00300    777497647    670981331801 allow ip from me to any keep-state
01100   3596451893   1774843751869 allow ip from table(10) to table(10)
01200    115026104     11258846233 allow ip from table(5) to 172.16.21.124,91.214.163.7
01300     12646486     11223258034 allow ip from 172.16.21.124,91.214.163.7 to table(5)
01310       165688        32798812 skipto 1370 ip from table(5) to table(6)
01500       165688        32798812 allow ip from table(5) to table(6)
01510       159942       113297462 allow ip from table(6) to table(5)
01600 374630847462 298612205697016 allow ip from any to any via vlan5
01700  17116634527  14374602295967 allow ip from any to any via vlan11
01900         2301          214136 allow tcp from any to me dst-port 22
02000          635           50166 allow icmp from any to me
02100            0               0 allow ip from table(5) to me dst-port 161
02300         4563          360026 deny ip from any to me
02400     16439030       986825125 deny ip from table(5) to table(5)
02420       599797        36899024 deny ip from table(50) to any
02450  20099405962  25367215063865 allow ip from table(20) to table(13)
02455 160620032436 117274836040441 skipto 2600 ip from table(12) to not table(20)
02460   7823323619   1975828000034 allow ip from table(14) to any
02500 161009480895 142962674475760 allow ip from any to table(11)
02600 146061490331 103193757214633 allow ip from table(12) to any
65400    380659943     26753533506 deny ip from any to any

DBD::mysql::st execute failed: ����������� ������� 'revision' � 'field list' at nosat.pm line 245.

а, да. В логах noserver.pl вот такое:

id групп клиентов с которыми работаем, перечислите через запятую. Пустая строка - все группы. Если в таблице клиентов доступны только наши группы - ставьте пустую строку

our $Where_grp=$c{Usr_nosrvr_groups}? " AND grp IN($c{Usr_nosrvr_groups})" : '';

our $Where_grp=" AND grp IN(1,2,3,4,5,6,7,8)";

Db_server xxx.xxx.xxx.1
Db_mysql_connect_timeout 5
Db_server_a xxx.xxx.xxx.6
Db_mysql_connect_timeouta 5
Db_name nodeny
Db_usr_table users_kitsman
Usr_nosrvr_groups 1,2,3,4,5,6,7,8
Noserver_monitor 1
Noserver_dopdata_tmpl 1
Period_load_user_info 8
Period_load_all_info 30
Allow_auth_off 0
Allow_unauth 0
Allow_overlimits 0
Start_num_ipfw 5000
End_num_ipfw 32000
Start_num2_ipfw 33000
End_num2_ipfw 60000
DefaultSpeedIn 4096
DefaultSpeedOut 512
Denied_ports 25
Usr_auth_groups
L2_auth_monitor 0
V_auth_Ip
T_usr_block 120
T_get_old 120
T_get_new 10
My_server_ip 0
My_port 7723
Ver_client 14
Nomake_monitor 0

nas2# perl noserver.pl -v | grep SQL
DBD::mysql::st execute failed: ����������� ������� 'revision' � 'field list' at nosat.pm line 245.
SQL: SELECT SQL_BUFFER_RESULT id,mid,ip,auth,lstate,paket FROM users_kitsman WHERE state<>'off'
05.12.2012 11:30:06 Error: SELECT SQL_BUFFER_RESULT id,mid,ip,auth,lstate,paket FROM users_dunaivtsi WHERE state<>'off'
SQL: SELECT SQL_BUFFER_RESULT id,mid,ip,auth,lstate,paket FROM users_kitsman WHERE state<>'off' AND auth<>'off' AND (auth<>'no' OR lstate<>0)
SQL: SELECT SQL_BUFFER_RESULT id,mid,ip,auth,lstate,paket FROM users_kitsman WHERE state<>'off' AND auth<>'off' AND (auth<>'no' OR lstate<>0)

DBD::mysql::st execute failed:
05.12.2012 11:30:06 Error:
не совсем все хорошо

SELECT SQL_BUFFER_RESULT id,mid,ip,auth,lstate,paket FROM users_dunaivtsi WHERE state<>'off'

это что за таблица users_dunaivtsi ?