freeradius utm5

Технические вопросы по UTM 5.0
Ответить
alexaaa
Сообщения: 7
Зарегистрирован: Пт июл 08, 2011 14:15

freeradius utm5

Сообщение alexaaa »

Добрый день!
Используем 5.3-004 без urfa + cisco в качестве pppoe
ip выдаются динамически из пула
помогите прикрутить freeradius вместо родного радиуса
может у кого есть рабочий вариант freeradius и биллинга?

taf
Сообщения: 309
Зарегистрирован: Вс янв 30, 2005 11:41

Сообщение taf »

Я сделал у нас связку freeradius+utm. Данные для авторизации берутся напрямую из ip_groups, аккаунтинг пишется в отдельную базу и в UTM никак не отображается (то есть ни в админке ни в л/к не видно ни сессий ни трафика). Работает на порядок надежнее родного радиуса.

alexaaa
Сообщения: 7
Зарегистрирован: Пт июл 08, 2011 14:15

Сообщение alexaaa »

taf писал(а):Я сделал у нас связку freeradius+utm. Данные для авторизации берутся напрямую из ip_groups, аккаунтинг пишется в отдельную базу и в UTM никак не отображается (то есть ни в админке ни в л/к не видно ни сессий ни трафика). Работает на порядок надежнее родного радиуса.
тип подключения какой у вас?
могли бы поделиться конфигами freeradius на почту alexaaa@inbox.ru

DanJackson
Сообщения: 4
Зарегистрирован: Пн ноя 21, 2016 10:20

Сообщение DanJackson »

Добрый день. Не стал создавать новую тему, так как мой вопрос связан как раз со связкой utm5 и freeradius. С недавнего времени, когда на сервер доступа ломятся одновременно более 200-300 абонентов, freeradius просто перестает отвечать. Mikrotik пишет в логи, что timeout ответа от radius-сервера и не авторизует. Почему то при рестарте freeradius в первые несколько секунд он пропускает энное количество абонентов и снова перестает отвечать. В штатном режиме все работает хорошо, но если необходимо разорвать сессии (перезапустить pptp/l2tp к примеру) то при авторизации большого количества абонентов возникает данная ситуация. Базу Postgesql пылесосил, размер у нее не большой, отвечает она вроде шустро. Увеличение параметра max_request_time не помогает. Помогите, пожалуйста, в решении данной проблемы.

В лог пишет следующее:
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 51800 - ID: 3 due to unfinished request 10
4324. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 43556 - ID: 5 due to unfinished request 10
4326. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 42499 - ID: 0 due to unfinished request 10
4321. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 57994 - ID: 6 due to unfinished request 10
4327. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 54593 - ID: 4 due to unfinished request 10
4325. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 42551 - ID: 2 due to unfinished request 10
4323. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 35950 - ID: 254 due to unfinished request
104319. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 40710 - ID: 1 due to unfinished request 10
4322. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 55948 - ID: 253 due to unfinished request
104318. Giving up on old request.
Jan 24 18:34:08 bill radiusd[20954]: Received conflicting packet from client "имя сервера доступа" port 43745 - ID: 252 due to unfinished request
104317. Giving up on old request.

taf
Сообщения: 309
Зарегистрирован: Вс янв 30, 2005 11:41

Сообщение taf »

alexaaa писал(а):тип подключения какой у вас?
могли бы поделиться конфигами freeradius на почту alexaaa@inbox.ru
У нас используется pppoe со статически назначенными адресами.

В приложении архив с тремя файлами - хелпер на перл для прохождения процедуры авторизации и выдачи адресов, и схема и конфиг sql для аккаунтинга. Дольше всего пришлось поприседать вокруг аккаунтнговых запросов в конфиге. А, да, у нас помимо всего клиентам еще и v6 раздаются, что отразилось на криптах.

хелпер:

Код: Выделить всё

#!/usr/bin/perl

use strict;
use DBI;
use NetAddr::IP::Lite;
# use ...
# This is very important ! Without this script will not get the filled hashesh from main.
use vars qw(%RAD_REQUEST %RAD_REPLY %RAD_CHECK);
#use Data::Dumper;

# This is hash wich hold original request from radius
#my %RAD_REQUEST;
# In this hash you add values that will be returned to NAS.
#my %RAD_REPLY;
#This is for check items
#my %RAD_CHECK;

#
# This the remapping of return values
#
    use constant    RLM_MODULE_REJECT=>    0;#  /* immediately reject the request */
    use constant    RLM_MODULE_FAIL=>      1;#  /* module failed, don't reply */
    use constant    RLM_MODULE_OK=>        2;#  /* the module is OK, continue */
    use constant    RLM_MODULE_HANDLED=>   3;#  /* the module handled the request, so stop. */
    use constant    RLM_MODULE_INVALID=>   4;#  /* the module considers the request invalid. */
    use constant    RLM_MODULE_USERLOCK=>  5;#  /* reject the request (user is locked out) */
    use constant    RLM_MODULE_NOTFOUND=>  6;#  /* user not found */
    use constant    RLM_MODULE_NOOP=>      7;#  /* module succeeded without doing anything */
    use constant    RLM_MODULE_UPDATED=>   8;#  /* OK (pairs modified) */
    use constant    RLM_MODULE_NUMCODES=>  9;#  /* How many return codes there are */

#  Global variables can persist across different calls to the module.
#
#
#       {
#        my %static_global_hash = ();
#
#               sub post_auth {
#               ...
#               }
#               ...
#       }

my $dbh;

sub CLONE(){
    $dbh = DBI->connect ("dbi:Pg:dbname=UTM_DATABASE;host=UTM_DATABASE_HOST",'UTM_DATABASE_USER', 'UTM_DATABASE_PASSWORD') or return RLM_MODULE_FAIL;
}

# Function to handle authorize
sub authorize {
    # For debugging purposes only
    &log_request_attributes;

    # Here's where your authorization code comes
    # You can call another function from here:
    # &test_call;

    my $query = $dbh->prepare("SELECT upass FROM ip_groups WHERE uname = LOWER('$RAD_REQUEST{'User-Name'}') AND is_deleted='0' LIMIT 1");
#&radiusd::radlog(1, "SELECT upass FROM ip_groups WHERE uname = LOWER('$RAD_REQUEST{'User-Name'}') AND is_deleted='0' LIMIT 1");

    $query->execute();

    my $row = $query->fetchrow_hashref;
#&radiusd::radlog(1, Dumper($row->{upass}));
    $RAD_CHECK{'Cleartext-Password'} = $row->{upass};
#    $RAD_CHECK{'Password'} = $row->{upass};

    return RLM_MODULE_OK;
}

# Function to handle authenticate
sub authenticate {
    # For debugging purposes only
    # &log_request_attributes;

    if ($RAD_REQUEST{'User-Name'} =~ /[[:^ascii:]]/i) {
        # Reject user and tell him why
        $RAD_REPLY{'Reply-Message'} = "Denied access by rlm_perl function";
        return RLM_MODULE_REJECT;
    } else {
        # Accept user and set some attribute
        $RAD_REPLY{'some-attribute'} = "null";
        return RLM_MODULE_OK;
    }
}

# Function to handle preacct
sub preacct {
    # For debugging purposes only
    # &log_request_attributes;

    return RLM_MODULE_OK;
}

# Function to handle accounting
sub accounting {
    # For debugging purposes only
    # &log_request_attributes;

    # You can call another subroutine from here
    # &test_call;

    return RLM_MODULE_OK;
}

# Function to handle checksimul
sub checksimul {
    # For debugging purposes only
    # &log_request_attributes;

    return RLM_MODULE_OK;
}

# Function to handle pre_proxy
sub pre_proxy {
    # For debugging purposes only
    # &log_request_attributes;

    return RLM_MODULE_OK;
}

# Function to handle post_proxy
sub post_proxy {
    # For debugging purposes only
    # &log_request_attributes;

    return RLM_MODULE_OK;
}

# Function to handle post_auth
sub post_auth {
    # For debugging purposes only
    # &log_request_attributes;

    my $query = $dbh->prepare(
        "SELECT inet_ntoa_ipv6(ip,ip_ext)     AS ip,
                inet_ntoa_ipv6(mask,mask_ext) AS mask,
                ip_type
        FROM ip_groups
        WHERE uname = LOWER('$RAD_REQUEST{'User-Name'}')
        AND is_deleted = 0 ORDER BY ip_type");
    $query->execute();

    my $draft = $query->fetchall_arrayref;

    for my $i ( 0 .. $#{$draft} )
    {
        if ($draft->[$i][2] == 256)
        {
            my $a = NetAddr::IP::Lite->new6($draft->[$i][0]."/".$draft->[$i][1]);
            $RAD_REPLY{'Delegated-IPv6-Prefix'} = $a->network();
            $RAD_REPLY{'Framed-IPv6-Prefix'} = $a->network();
        }
        else
        {
            $RAD_REPLY{'Framed-IP-Address'} = $draft->[$i][0];
            $RAD_REPLY{'Framed-IP-Netmask'} = $draft->[$i][1];
        }
    }
    $RAD_REPLY{'Session-Timeout'} = '604800';

    return RLM_MODULE_OK;
}

# Function to handle xlat
sub xlat {
    # For debugging purposes only
    # &log_request_attributes;

    # Loads some external perl and evaluate it
    my ($filename,$a,$b,$c,$d) = @_;
    &radiusd::radlog(1, "From xlat $filename ");
    &radiusd::radlog(1,"From xlat $a $b $c $d ");
    local *FH;
    open FH, $filename or die "open '$filename' $!";
    local($/) = undef;
    my $sub = <FH>;
    close FH;
    my $eval = qq&#123; sub handler&#123; $sub;&#125; &#125;;
    eval $eval;
    eval &#123;main->handler;&#125;;
&#125;

# Function to handle detach
sub detach &#123;
    # For debugging purposes only
    # &log_request_attributes;

    # Do some logging.
    &radiusd&#58;&#58;radlog&#40;0,"rlm_perl&#58;&#58;Detaching. Reloading. Done."&#41;;
&#125;

#
# Some functions that can be called from other functions
#

sub test_call &#123;
    # Some code goes here
&#125;

sub log_request_attributes &#123;
    # This shouldn't be done in production environments!
    # This is only meant for debugging!
    for &#40;keys %RAD_REQUEST&#41; &#123;
        &radiusd&#58;&#58;radlog&#40;1, "RAD_REQUEST&#58; $_ = $RAD_REQUEST&#123;$_&#125;"&#41;;
    &#125;
&#125; 
конфиг sql:

Код: Выделить всё

sql &#123;
        #
        #  Set the database to one of&#58;
        #
        #       mysql, mssql, oracle, postgresql
        #
        database = "postgresql"

        #
        #  Which FreeRADIUS driver to use.
        #
        driver = "rlm_sql_$&#123;database&#125;"

        # Connection info&#58;
        server = "UTM_DATABASE_HOST"
        port = 5432
        login = "UTM_DATABASE_USER"
        password = "UTM_DATABASE_PASSWORD"

        # Database table configuration for everything except Oracle
        radius_db = "radius"
        # If you are using Oracle then use this instead
        # radius_db = "&#40;DESCRIPTION=&#40;ADDRESS=&#40;PROTOCOL=TCP&#41;&#40;HOST=localhost&#41;&#40;PORT=1521&#41;&#41;&#40;CONNECT_DATA=&#40;SID=your_sid&#41;&#41;&#41;"

        # If you want both stop and start records logged to the
        # same SQL table, leave this as is.  If you want them in
        # different tables, put the start table in acct_table1
        # and stop table in acct_table2
        acct_table1 = "radacct"
        acct_table2 = "radacct"

        # Allow for storing data after authentication
#       postauth_table = "radpostauth"

#       authcheck_table = "radcheck"
#       authreply_table = "radreply"

#       groupcheck_table = "radgroupcheck"
#       groupreply_table = "radgroupreply"

        # Table to keep group info
#       usergroup_table = "radusergroup"

        # If set to 'yes' &#40;default&#41; we read the group tables
        # If set to 'no' the user MUST have Fall-Through = Yes in the radreply table
        # read_groups = yes

        # Remove stale session if checkrad does not see a double login
#       deletestalesessions = yes

        # Print all SQL statements when in debug mode &#40;-x&#41;
        sqltrace = no
        sqltracefile = $&#123;logdir&#125;/sqltrace.sql

        # number of sql connections to make to server
        #
        # Setting this to LESS than the number of threads means
        # that some threads may starve, and you will see errors
        # like "No connections available and at max connection limit"
        #
        # Setting this to MORE than the number of threads means
        # that there are more connections than necessary.
        # 
#       num_sql_socks = $&#123;thread&#91;pool&#93;.max_servers&#125;
        num_sql_socks = 5

        # number of seconds to dely retrying on a failed database
        # connection &#40;per_socket&#41;
        connect_failure_retry_delay = 60

        # lifetime of an SQL socket.  If you are having network issues
        # such as TCP sessions expiring, you may need to set the socket
        # lifetime.  If set to non-zero, any open connections will be
        # closed "lifetime" seconds after they were first opened.
        lifetime = 0

        # Maximum number of queries used by an SQL socket.  If you are
        # having issues with SQL sockets lasting "too long", you can
        # limit the number of queries performed over one socket.  After
        # "max_qeuries", the socket will be closed.  Use 0 for "no limit".
        max_queries = 0

        # Set to 'yes' to read radius clients from the database &#40;'nas' table&#41;
        # Clients will ONLY be read on server startup.  For performance
        # and security reasons, finding clients via SQL queries CANNOT
        # be done "live" while the server is running.
        # 
        #readclients = yes

        # Table to keep radius client info
#       nas_table = "nas"

        # Read driver-specific configuration
#       $INCLUDE sql/$&#123;database&#125;/dialup.conf
        sql_user_name = "%&#123;User-Name&#125;"

accounting_onoff_query = "UPDATE $&#123;acct_table1&#125; \
  SET Acct_Stop_Time    = &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
      Acct_Session_Time = &#40;EXTRACT&#40;EPOCH FROM &#40;'%S'&#58;&#58;timestamp with time zone - \
                           Acct_Start_Time&#58;&#58;timestamp with time zone - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;&#41;&#41;&#58;&#58;BIGINT, \
      Acct_Terminate_Cause = '%&#123;Acct-Terminate-Cause&#125;', \
      Acct_Stop_Delay = 0 \
  WHERE AcctStopTime IS NULL \
  AND Nas_Ip_Address= '%&#123;NAS-IP-Address&#125;' \
  AND Acct_Start_Time <= '%S'&#58;&#58;timestamp"

accounting_update_query = "UPDATE $&#123;acct_table1&#125; \
  SET Framed_IP_Address   = NULLIF&#40;'%&#123;Framed-IP-Address&#125;', ''&#41;&#58;&#58;inet, \
      Framed_IPv6_Prefix  = NULLIF&#40;'%&#123;Framed-IPv6-Prefix&#125;', ''&#41;&#58;&#58;inet, \
      Acct_Session_Time   = '%&#123;Acct-Session-Time&#125;', \
      Acct_Input_Octets   = &#40;&#40;'%&#123;%&#123;Acct-Input-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Input-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
      Acct_Output_Octets  = &#40;&#40;'%&#123;%&#123;Acct-Output-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Output-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
      Acct_Input_Packets  = &#40;'%&#123;%&#123;Acct-Input-Packets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
      Acct_Output_Packets = &#40;'%&#123;%&#123;Acct-Output-Packets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41; \
  WHERE Acct_Session_Id  = '%&#123;Acct-Session-Id&#125;' \
      AND User_Name      = '%&#123;SQL-User-Name&#125;' \
      AND Nas_Ip_Address = '%&#123;NAS-IP-Address&#125;' \
      AND Acct_Stop_Time IS NULL"

accounting_update_query_alt = "INSERT INTO $&#123;acct_table1&#125; \
    &#40;Acct_Session_Id, \
     acct_unique_session_id, \
     User_Name, \
     Nas_Ip_Address, \
     Nas_Port_Id, \
     Nas_Port, \
     Nas_Port_Type, \
     Acct_Start_Time, \
     Acct_Session_Time, \
     Acct_Authentic, \
     Acct_Input_Octets, \
     Acct_Output_Octets, \
     Called_Station_Id, \
     Calling_Station_Id, \
     Service_Type, \
     Framed_Protocol, \
     Framed_IP_Address, \
     Framed_IPv6_Prefix&#41; \
  VALUES &#40; \
    '%&#123;Acct-Session-Id&#125;', \
    '%&#123;Acct-Unique-Session-Id&#125;', \
    '%&#123;SQL-User-Name&#125;', \
    '%&#123;NAS-IP-Address&#125;', \
    '%&#123;NAS-Port-Id&#125;', \
    %&#123;%&#123;NAS-Port&#125;&#58;-NULL&#125;, \
    '%&#123;NAS-Port-Type&#125;', \
    &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval - '%&#123;%&#123;Acct-Session-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
    '%&#123;Acct-Session-Time&#125;', \
    '%&#123;Acct-Authentic&#125;', \
    &#40;&#40;'%&#123;%&#123;Acct-Input-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Input-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
    &#40;&#40;'%&#123;%&#123;Acct-Output-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Output-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
    '%&#123;Called-Station-Id&#125;', \
    '%&#123;Calling-Station-Id&#125;', \
    '%&#123;Service-Type&#125;', \
    '%&#123;Framed-Protocol&#125;', \
    NULLIF&#40;'%&#123;Framed-IP-Address&#125;', ''&#41;&#58;&#58;inet, \
    NULLIF&#40;'%&#123;Framed-IPv6-Prefix&#125;', ''&#41;&#58;&#58;inet&#41;"

accounting_start_query = "INSERT INTO $&#123;acct_table1&#125; \
    &#40;Acct_Session_Id, \
     acct_unique_session_id, \
     User_Name, \
     Nas_Ip_Address, \
     Nas_Port, \
     Nas_Port_Id, \
     Nas_Port_Type, \
     Acct_Start_Time, \
     Acct_Authentic, \
     Called_Station_Id, \
     Calling_Station_Id, \
     Service_Type, \
     Framed_Protocol, \
     Framed_IP_Address, \
     Framed_IPv6_Prefix, \
     Acct_Start_Delay&#41; \
  VALUES &#40; \
    '%&#123;Acct-Session-Id&#125;', \
    '%&#123;Acct-Unique-Session-Id&#125;', \
    '%&#123;SQL-User-Name&#125;', \
    '%&#123;NAS-IP-Address&#125;', \
    %&#123;%&#123;NAS-Port&#125;&#58;-NULL&#125;, \
    '%&#123;NAS-Port-Id&#125;', \
    '%&#123;NAS-Port-Type&#125;', \
    &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
    '%&#123;Acct-Authentic&#125;', \
    '%&#123;Called-Station-Id&#125;', \
    '%&#123;Calling-Station-Id&#125;', \
    '%&#123;Service-Type&#125;', \
    '%&#123;Framed-Protocol&#125;', \
    NULLIF&#40;'%&#123;Framed-IP-Address&#125;', ''&#41;&#58;&#58;inet, \
    NULLIF&#40;'%&#123;Framed-IPv6-Prefix&#125;', ''&#41;&#58;&#58;inet, \
    0&#41;"

accounting_start_query_alt  = "UPDATE $&#123;acct_table1&#125; \
  SET Acct_Start_Time = &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
      Acct_Start_Delay = 0, \
  WHERE Acct_Session_Id = '%&#123;Acct-Session-Id&#125;' \
  AND User_Name = '%&#123;SQL-User-Name&#125;' \
  AND NAS_IP_Address = '%&#123;NAS-IP-Address&#125;' \
  AND Acct_Stop_Time IS NULL"

accounting_stop_query = "UPDATE $&#123;acct_table1&#125; \
  SET Acct_Stop_Time     = &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
      Acct_Session_Time  = CASE WHEN '%&#123;Acct-Session-Time&#125;' = '' THEN \
                               &#40;EXTRACT&#40;EPOCH FROM &#40;'%S'&#58;&#58;TIMESTAMP WITH TIME ZONE - Acct_Start_Time&#58;&#58;TIMESTAMP WITH TIME ZONE \
                                - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;INTERVAL&#41;&#41;&#41;&#58;&#58;BIGINT \
                           ELSE NULLIF&#40;'%&#123;Acct-Session-Time&#125;',''&#41;&#58;&#58;BIGINT END, \
      Acct_Input_Octets  = &#40;&#40;'%&#123;%&#123;Acct-Input-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Input-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
      Acct_Output_Octets = &#40;&#40;'%&#123;%&#123;Acct-Output-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Output-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
      Acct_Terminate_Cause = '%&#123;Acct-Terminate-Cause&#125;', \
      Acct_Stop_Delay = 0, \
      Framed_IP_Address = NULLIF&#40;'%&#123;Framed-IP-Address&#125;', ''&#41;&#58;&#58;inet, \
      Framed_IPv6_Prefix  = NULLIF&#40;'%&#123;Framed-IPv6-Prefix&#125;', ''&#41;&#58;&#58;inet \
  WHERE Acct_Session_Id = '%&#123;Acct-Session-Id&#125;' \
  AND User_Name = '%&#123;SQL-User-Name&#125;' \
  AND NAS_IP_Address = '%&#123;NAS-IP-Address&#125;' \
  AND Acct_Stop_Time IS NULL"

accounting_stop_query_alt = "INSERT INTO $&#123;acct_table1&#125; \
    &#40;Acct_Session_Id, \
     acct_unique_session_id, \
     User_Name, \
     NAS_IP_Address, \
     NAS_Port, \
     NAS_Port_Id, \
     NAS_Port_Type, \
     Acct_Start_Time, \
     Acct_Stop_Time, \
     Acct_Session_Time, \
     Acct_Authentic, \
     Acct_Input_Octets, \
     Acct_Output_Octets, \
     Called_Station_Id, \
     Calling_Station_Id, \
     Acct_Terminate_Cause, \
     Service_Type, \
     Framed_Protocol, \
     Framed_IP_Address, \
     Framed_IPv6_Prefix, \
     Acct_Stop_Delay&#41; \
  VALUES &#40; \
    '%&#123;Acct-Session-Id&#125;', \
    '%&#123;Acct-Unique-Session-Id&#125;', \
    '%&#123;SQL-User-Name&#125;', \
    '%&#123;NAS-IP-Address&#125;', \
    %&#123;%&#123;NAS-Port&#125;&#58;-NULL&#125;, \
    '%&#123;NAS-Port-Id&#125;', \
    '%&#123;NAS-Port-Type&#125;', \
    &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval - '%&#123;%&#123;Acct-Session-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
    &#40;'%S'&#58;&#58;timestamp - '%&#123;%&#123;Acct-Delay-Time&#125;&#58;-0&#125;'&#58;&#58;interval&#41;, \
    NULLIF&#40;'%&#123;Acct-Session-Time&#125;', ''&#41;&#58;&#58;bigint, \
    '%&#123;Acct-Authentic&#125;', \
    &#40;&#40;'%&#123;%&#123;Acct-Input-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Input-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
    &#40;&#40;'%&#123;%&#123;Acct-Output-Gigawords&#125;&#58;-0&#125;'&#58;&#58;bigint << 32&#41; + '%&#123;%&#123;Acct-Output-Octets&#125;&#58;-0&#125;'&#58;&#58;bigint&#41;, \
    '%&#123;Called-Station-Id&#125;', \
    '%&#123;Calling-Station-Id&#125;', \
    '%&#123;Acct-Terminate-Cause&#125;', \
    '%&#123;Service-Type&#125;', \
    '%&#123;Framed-Protocol&#125;', \
    NULLIF&#40;'%&#123;Framed-IP-Address&#125;', ''&#41;&#58;&#58;inet, \
    NULLIF&#40;'%&#123;Framed-IPv6-Prefix&#125;', ''&#41;&#58;&#58;inet, \
    0&#41;"

&#125;
схема:

Код: Выделить всё

CREATE TABLE radacct &#40;
    rad_acct_id BIGSERIAL PRIMARY KEY,
    user_name character varying&#40;255&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    nas_ip_address inet DEFAULT '0.0.0.0'&#58;&#58;inet NOT NULL,
    nas_port integer DEFAULT 0 NOT NULL,
    nas_port_id character varying&#40;255&#41;,
    nas_port_type character varying&#40;32&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    service_type character varying&#40;32&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    framed_protocol character varying&#40;32&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    called_station_id character varying&#40;255&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    calling_station_id character varying&#40;255&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    acct_status_type character varying&#40;32&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    acct_session_id character varying&#40;64&#41; NOT NULL,
    acct_unique_session_id character varying&#40;64&#41; NOT NULL,
    acct_authentic character varying&#40;32&#41;,
    acct_session_time bigint DEFAULT '0'&#58;&#58;bigint NOT NULL,
    acct_start_time timestamp with time zone,
    acct_stop_time timestamp with time zone,
    acct_input_octets bigint DEFAULT '0'&#58;&#58;bigint NOT NULL,
    acct_output_octets bigint DEFAULT '0'&#58;&#58;bigint NOT NULL,
    acct_input_packets bigint DEFAULT '0'&#58;&#58;bigint NOT NULL,
    acct_output_packets bigint DEFAULT '0'&#58;&#58;bigint NOT NULL,
    acct_terminate_cause character varying&#40;32&#41; DEFAULT ''&#58;&#58;character varying NOT NULL,
    acct_stop_delay integer,
    framed_ip_address inet,
    framed_ipv6_prefix inet,
    acct_start_delay integer
&#41;;

ALTER TABLE ONLY radacct ADD CONSTRAINT radacct_acct_unique_session_id_key UNIQUE &#40;acct_unique_session_id&#41;;
CREATE INDEX radacct_active_user_idx ON radacct USING btree &#40;user_name, nas_ip_address, acct_session_id&#41; WHERE &#40;acct_stop_time IS NULL&#41;;
CREATE INDEX radacct_start_user_idx ON radacct USING btree &#40;acct_start_time, user_name&#41;;


CREATE FUNCTION inet_ntoa_ipv6&#40;ip bigint, ip_ext bigint&#41; RETURNS text
    LANGUAGE sql
    AS $$

SELECT
    LOWER&#40;
        CONCAT&#40;
            CASE
                WHEN ip_ext != 0 THEN
                CONCAT_WS&#40;
                    '&#58;',
                    SUBSTR&#40;LPAD&#40;TO_HEX&#40;ip_ext&#41;, 16, '0'&#41;, 1, 4&#41;,
                    SUBSTR&#40;LPAD&#40;TO_HEX&#40;ip_ext&#41;, 16, '0'&#41;, 5, 4&#41;,
                    SUBSTR&#40;LPAD&#40;TO_HEX&#40;ip_ext&#41;, 16, '0'&#41;, 9, 4&#41;,
                    RIGHT&#40;LPAD&#40;TO_HEX&#40;ip_ext&#41;, 16, '0'&#41;, 4&#41;
                &#41;
            END,
            CASE
                WHEN ip_ext != 0 THEN
                    '&#58;'
            END,
            CASE
                WHEN ip_ext != 0 THEN
                CONCAT_WS&#40;
                    '&#58;',
                    SUBSTR&#40;LPAD&#40;TO_HEX&#40;ip&#41;, 16, '0'&#41;, 1, 4&#41;,
                    SUBSTR&#40;LPAD&#40;TO_HEX&#40;ip&#41;, 16, '0'&#41;, 5, 4&#41;,
                    SUBSTR&#40;LPAD&#40;TO_HEX&#40;ip&#41;, 16, '0'&#41;, 9, 4&#41;,
                    RIGHT&#40;LPAD&#40;TO_HEX&#40;ip&#41;, 16, '0'&#41;, 4&#41;
                &#41;
                ELSE INET_NTOA&#40;ip & x'ffffffff'&#58;&#58;bigint&#41;
            END
        &#41;
    &#41;
    as result
$$;

Ответить