Jump to content
Калькуляторы

покритикуйте UTM5+freeradius запросы

Решил перейти со встроеного радиуса на freeradius.

Погуглил, пояндексил, покопипастил, поправил, и вот, что получилось:

 

sql_user_name = "%{User-Name}"

authorize_check_query="\
SELECT ip_groups.ip_group_id, ip_groups.uname,'Cleartext-Password', ip_groups.upass,':=' FROM UTM5.ip_groups, \
UTM5.iptraffic_service_links, UTM5.service_links, UTM5.accounts WHERE ip_groups.uname = '%{SQL-User-Name}' AND \
ip_groups.is_deleted='0' AND iptraffic_service_links.is_deleted='0' AND service_links.is_deleted='0' AND accounts.is_deleted='0' \
AND accounts.is_blocked='0' AND ip_groups.ip_group_id=iptraffic_service_links.ip_group_id \
AND iptraffic_service_links.id=service_links.id AND service_links.account_id=accounts.id \
UNION SELECT ip_groups.ip_group_id, ip_groups.uname,'Simultaneous-Use','1',':=' FROM UTM5.ip_groups, \
UTM5.iptraffic_service_links, UTM5.service_links, UTM5.accounts WHERE ip_groups.uname = '%{SQL-User-Name}' \
AND ip_groups.is_deleted='0' AND iptraffic_service_links.is_deleted='0' AND service_links.is_deleted='0' \
AND accounts.is_deleted='0' AND accounts.is_blocked='0' AND ip_groups.ip_group_id=iptraffic_service_links.ip_group_id \
AND iptraffic_service_links.id=service_links.id AND service_links.account_id=accounts.id \
"

authorize_reply_query="\
SELECT ip_group_id, uname, 'Framed-IP-Address', inet_ntoa(ip_groups.ip & 0xFFFFFFFF) AS a, ':=' FROM UTM5.ip_groups \
WHERE uname='%{SQL-User-Name}' AND is_deleted='0' \
UNION SELECT ip_group_id, uname, 'Auth-Type', 'Reject' as a, ':=' \
FROM ip_groups WHERE uname='%{SQL-User-Name}' AND is_deleted='0' \
"

## probably not needed
#postauth_query="\
#INSERT INTO dhs_access_log (recv_date,User_Name,Service_Type,Framed_Protocol,NAS_IP_Address,NAS_Id,is_success) \
#VALUES(UNIX_TIMESTAMP('%S'),'%{SQL-User-name}','0','2','0','%{NAS-Identifier}','1') \
#"

#LOCK TABLES ip_groups WRITE, iptraffic_service_links WRITE, service_links WRITE;                               \
#UNLOCK TABLES;                                                                                                 \
#

accounting_start_query = "\
SET @ipgid = (select ip_group_id from ip_groups where uname = '%{SQL-User-Name}' and is_deleted <> '1' limit 1);\
SET @slid = (select id from iptraffic_service_links where is_deleted <> '1' and ip_group_id = @ipgid limit 1);  \
SET @accid = (select account_id from service_links where id = @slid limit 1);                                   \
SET @framed_ip = (INET_ATON(CONCAT_WS('.',SUBSTRING_INDEX('%{Framed-IP-Address}','.',-1),                       \
                                    SUBSTRING_INDEX(SUBSTRING_INDEX('%{Framed-IP-Address}','.',-2),'.',1),     \
                                    SUBSTRING_INDEX(SUBSTRING_INDEX('%{Framed-IP-Address}','.',2),'.',-1),     \
                                    SUBSTRING_INDEX('%{Framed-IP-Address}','.',1))));                          \
SET @framed_ip = (IF(@framed_ip > 268435455,CAST(@framed_ip-0x100000000 AS SIGNED),@framed_ip));                \
SET @nas_ip = (INET_ATON(CONCAT_WS('.',SUBSTRING_INDEX('%{%{NAS-IP-Address}:-%{Client-IP-Address}}','.',-1),    \
            SUBSTRING_INDEX(SUBSTRING_INDEX('%{%{NAS-IP-Address}:-%{Client-IP-Address}}','.',-2),'.',1),       \
            SUBSTRING_INDEX(SUBSTRING_INDEX('%{%{NAS-IP-Address}:-%{Client-IP-Address}}','.',2),'.',-1),       \
            SUBSTRING_INDEX('%{%{NAS-IP-Address}:-%{Client-IP-Address}}','.',1))));                            \
SET @nas_ip = (IF(@nas_ip > 268435455,CAST(@nas_ip-0x100000000 AS SIGNED),@nas_ip));                            \
SET @inbytes = (IF(%{%{Acct-Input-Octets}:-0} < 0,                                                              \
                   (4294967296 * %{%{Acct-Input-Gigawords}:-0}) + 2147483648 - %{%{Acct-Input-Octets}:-0},     \
                   (4294967296 * %{%{Acct-Input-Gigawords}:-0}) + %{%{Acct-Input-Octets}:-0}));                \
SET @outbytes = (IF(%{%{Acct-Output-Octets}:-0} < 0,                                                            \
                   (4294967296 * %{%{Acct-Output-Gigawords}:-0}) + 2147483648 - %{%{Acct-Output-Octets}:-0},   \
                   (4294967296 * %{%{Acct-Output-Gigawords}:-0}) + %{%{Acct-Output-Octets}:-0}));              \
\
INSERT INTO dhs_sessions_log                    \
(account_id, slink_id, recv_date,last_update_date,Framed_IP_Address, NAS_Port, Acct_Delay_Time, Acct_Session_Id,\
NAS_Port_Type, User_Name, Service_Type, Framed_Protocol, NAS_IP_Address, NAS_Id, Acct_Status_Type,              \
Acct_Input_Packets, Acct_Input_Octets, Acct_Output_Packets, Acct_Output_Octets, Acct_Session_Time,              \
Called_Station_Id, Calling_Station_Id)          \
VALUES                                          \
(@accid, @slid, '%l', UNIX_TIMESTAMP('%S'), @framed_ip, '%{NAS-Port}', '%{Acct-Delay-Time}', '%{Acct-Unique-Session-ID}', \
'0', '%{SQL-User-name}', '2', '1', @nas_ip, '%{NAS-Identifier}', '1',                                           \
'%{%{Acct-Input-Packets}:-0}', @inbytes, '%{%{Acct-Output-Packets}:-0}', @outbytes, '%{%{Acct-Session-Time}:-0}', \
'%{Called-Station-Id}','%i' );                  \
"

accounting_update_query = "\
SET @inbytes = (IF(%{%{Acct-Input-Octets}:-0} < 0,                                                              \
                   (4294967296 * %{%{Acct-Input-Gigawords}:-0}) + 2147483648 - %{%{Acct-Input-Octets}:-0},     \
                   (4294967296 * %{%{Acct-Input-Gigawords}:-0}) + %{%{Acct-Input-Octets}:-0}));                \
SET @outbytes = (IF(%{%{Acct-Output-Octets}:-0} < 0,                                                            \
                   (4294967296 * %{%{Acct-Output-Gigawords}:-0}) + 2147483648 - %{%{Acct-Output-Octets}:-0},   \
                   (4294967296 * %{%{Acct-Output-Gigawords}:-0}) + %{%{Acct-Output-Octets}:-0}));              \
UPDATE dhs_sessions_log SET last_update_date=UNIX_TIMESTAMP('%S'), Acct_Status_Type='3',\
Acct_Input_Packets ='%{%{Acct-Input-Packets}:-0}', Acct_Input_Octets = @inbytes,        \
Acct_Output_Packets ='%{%{Acct-Output-Packets}:-0}', Acct_Output_Octets = @outbytes,    \
Acct_Session_Time ='%{Acct-Session-Time}'                                               \
WHERE Acct_Session_Id = '%{Acct-Unique-Session-ID}' AND NAS_Port = '%{NAS-Port}' AND User_Name = '%{SQL-User-Name}' \
"

accounting_stop_query = "\
SET @inbytes = (IF(%{%{Acct-Input-Octets}:-0} < 0,                                                              \
                   (4294967296 * %{%{Acct-Input-Gigawords}:-0}) + 2147483648 - %{%{Acct-Input-Octets}:-0},     \
                   (4294967296 * %{%{Acct-Input-Gigawords}:-0}) + %{%{Acct-Input-Octets}:-0}));                \
SET @outbytes = (IF(%{%{Acct-Output-Octets}:-0} < 0,                                                            \
                   (4294967296 * %{%{Acct-Output-Gigawords}:-0}) + 2147483648 - %{%{Acct-Output-Octets}:-0},   \
                   (4294967296 * %{%{Acct-Output-Gigawords}:-0}) + %{%{Acct-Output-Octets}:-0}));              \
UPDATE dhs_sessions_log SET last_update_date=UNIX_TIMESTAMP('%S'), Acct_Status_Type='2',\
Acct_Input_Packets ='%{%{Acct-Input-Packets}:-0}', Acct_Input_Octets = @inbytes,        \
Acct_Output_Packets ='%{%{Acct-Output-Packets}:-0}', Acct_Output_Octets = @outbytes,    \
Acct_Session_Time ='%{Acct-Session-Time}'                                               \
WHERE Acct_Session_Id = '%{Acct-Unique-Session-ID}' AND NAS_Port = '%{NAS-Port}' AND User_Name = '%{SQL-User-Name}' \
"

simul_count_query  = "SELECT COUNT(*) FROM dhs_sessions_log WHERE User_Name='%{SQL-User-Name}' \
AND (Acct_Status_Type = '3' OR Acct_Status_Type = '1') \
"

simul_verify_query = "SELECT COUNT(*) FROM dhs_sessions_log WHERE User_Name='%{SQL-User-Name}' \
AND (Acct_Status_Type = '3' OR Acct_Status_Type = '1') \
"

 

Ядро биллинга у меня старое, 5.1.10-017, и переходить на новое пока не возжелал - оно и так нормально работает.

Соответственно, если кто захочет заюзать мою версию запросов - придется добавить пару полей, которые добавились в 5.2.1-xxx, а может и так работать будет.

 

Я не силен в SQL, но старался избегать лишних (и медленных) запросов.

Корректно ли я оформил запросы?

 

Что правильнее: использовать Acct-Output-Gigawords и Acct-Output-Octets, или Acct-Output-Octets-64?

Правильно ли выводить всего одну запись (limit 1), или есть ситуации, когда к одному юзеру в UTM привязано больше одной сервисной связки?

 

Нужно ли делать WRITE LOCK на таблицы при авторизации? UTM5 не использует механизм LOCKS, и вообще единолично общается с БД, не поплохеет ли ему от заблокированой таблицы? кто пробовал?

Edited by [anp/hsw]

Share this post


Link to post
Share on other sites

Не надо блочить таблицу при авторизации - смысла в этом нет, скорее всего с биллингом не сможете работать, т.к. таблица будет вечно заблочена.

' timestamp='1331735316' post=698322]

Корректно ли я оформил запросы?

Если работает - значит корректно, хотя authorize_check_query мне не понравился.

Share this post


Link to post
Share on other sites

Не надо блочить таблицу при авторизации - смысла в этом нет, скорее всего с биллингом не сможете работать, т.к. таблица будет вечно заблочена.

 

нет, я не имею ввиду полную блокировку, а только блокировку на время вычисления путя от логина до сервисной связки:

LOCK TABLES ip_groups WRITE, iptraffic_service_links WRITE, service_links WRITE;                                \
SET @ipgid = (select ip_group_id from ip_groups where uname = '%{SQL-User-Name}' and is_deleted <> '1' limit 1);\
SET @slid = (select id from iptraffic_service_links where is_deleted <> '1' and ip_group_id = @ipgid limit 1);  \
SET @accid = (select account_id from service_links where id = @slid limit 1);                                   \
UNLOCK TABLES;                                                                                                  \

 

Если работает - значит корректно, хотя authorize_check_query мне не понравился.

А что именно не понравилось в authorize_check_query? Он сделан дампингом mysql-запроса ядра при авторизации.

Предложите ваш вариант, если не сложно.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this