Freeswitch: Аккаунты пользователей в базе данных MySQL / Postgresql

Директории пользователей хранят учетные данные и параметры устройств регистрирующихся в FreeSWITCH.
В ванильной конфигурации они хранятся в XML файлах.
Используем Lua DataBase Handling (Dbh) для извлечения этих данных из MySQL или PosgreSQL.

mod_lua

Установим Lua

sudo apt install lua5.2

Проверим установлен ли mod_lua:

freeswitch@fs3> module_exists mod_lua
true

если нет, установим его:

sudo apt install freeswitch-mod-lua

odbc (debian 8)

Установим unix odbc

   sudo apt install unixodbc

Установим mysql odbc connector

cd /usr/src &&
wget https://dev.mysql.com/get/Downloads/Connector-ODBC/5.3/mysql-connector-odbc-5.3.10-linux-debian8-x86-64bit.tar.gz &&
tar zxvf mysql-connector-odbc-5.3.10-linux-debian8-x86-64bit.tar.gz &&
cd mysql-connector-odbc-5.3.10-linux-debian8-x86-64bit/lib &&
cp libmyodbc5* /usr/lib/x86_64-linux-gnu/odbc/

cat >> /etc/odbcinst.ini << EOF
[MySQL]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc5w.so
UsageCount=2

EOF

Создайте соединение (DSN) с базой данных:

cat >> /etc/odbc.ini << EOF
[freeswitch]
Description=MySQL connection to 'freeswitch' database
driver=MySQL
server=localhost
database=freeswitch
Port=3306
Socket=/var/run/mysqld/mysqld.sock
option=3

EOF

Создание базы данных

Подключитесь к mysql cli и создайте БД, а также добавьте пользователя с паролем:
не забудьте изменить FS_DB_USER & FS_DB_SPASSWORD на ваши СОБСТВЕННЫЕ данные

mysql> create database freeswitch;
mysql> grant all privileges on freeswitch.* to FS_DB_USER@localhost identified by 'FS_DB_PASSWORD';
mysql> flush privileges;
mysql> use freeswitch;

Create table 'directory':

CREATE TABLE `directory` (
  `domain` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `id` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `number-alias` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mailbox` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `cidr` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `toll_allow` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `user_context` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `default_gateway` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `effective_caller_id_name` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `effective_caller_id_number` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `outbound_caller_id_name` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `outbound_caller_id_number` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `callgroup` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uservar1` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uservar2` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `uservar3` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE directory (
  "domain" varchar(32),
  "id" varchar(32),
  "number-alias" varchar(32),
  "mailbox" varchar(64),
  "cidr" varchar(64),
  "password" varchar(64),
  "toll_allow" varchar(32),
  "user_context" varchar(32),
  "default_gateway" varchar(32),
  "effective_caller_id_name" varchar(512),
  "effective_caller_id_number" varchar(64),
  "outbound_caller_id_name" varchar(512),
  "outbound_caller_id_number" varchar(64),
  "callgroup" varchar(64),
  "uservar1" varchar(64),
  "uservar2" varchar(64),
  "uservar3" varchar(64)
);

Естественно, можете модифицировать таблицу и добавить любые колонки, чтобы затем использовать их в FS. В этом случае нужно будет модифицировать XML_STRING в Lua скрипте, соответственно добавленным данным.

Lua dbh script

Создайте следующий скрипт в директории /usr/share/freeswitch/scripts:

cd /usr/share/freeswitch/scripts &&
touch directory_xml.lua &&
chown freeswitch. directory_xml.lua

И вставьте следующий код: Не забудьте изменить FS_DB_USER & FS_DB_SPASSWORD на заданные при создании БД данные.

-- directory_xml.lua
-- freeswitch.consoleLog("notice", "Debug from directory_xml.lua, provided params:\n" .. params:serialize() .. "\n")
 
local req_domain = params:getHeader("domain")
local req_key    = params:getHeader("key")
local req_user   = params:getHeader("user")
 
assert (req_domain and req_key and req_user,
  "This example script only supports generating directory xml for a single user !\n")
 
local dbh = freeswitch.Dbh("odbc://freeswitch:FS_DB_USER:FS_DB_PASSWORD")
if dbh:connected() == false then
  freeswitch.consoleLog("notice", "directory_xml.lua cannot connect to database" .. dsn .. "\n")
  return
end
 
local dir_query = string.format("select * from directory where domain = '%s' and `%s`='%s' limit 1", req_domain, req_key, req_user)
 
assert (dbh:query(dir_query, function(u)
  XML_STRING =
[[<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<document type="freeswitch/xml">
  <section name="directory">
    <domain name="]] .. u.domain .. [[">
            <user id="]] .. u.id .. [[" mailbox="]] .. u.mailbox .. [[" cidr="]]
           .. u.cidr .. [[" number-alias="]] .. u["number-alias"] .. [[">
        <params>
          <param name="a1-hash" value="]] .. u.password .. [["/>
          <param name="dial-string" value="{sip_secure_media=${regex(${sofia_contact(${dialed_user}@${dialed_domain})}|transport=tls)},presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(${dialed_user}@${dialed_domain})}"/>
          <param name="jsonrpc-allowed-methods" value="verto"/>
          <param name="jsonrpc-allowed-event-channels" value="demo,conference"/>
        </params>
        <variables>
	  <variable name="toll_allow" value="]] .. u.toll_allow .. [["/>
          <variable name="user_context" value="]] .. u.user_context .. [["/>
	  <variable name="default_gateway" value="]] .. u.default_gateway .. [["/>
	  <variable name="effective_caller_id_name" value="]] .. u.effective_caller_id_name .. [["/>
	  <variable name="effective_caller_id_number" value="]] .. u.effective_caller_id_number .. [["/>
	  <variable name="outbound_caller_id_name" value="]] .. u.outbound_caller_id_name .. [["/>
	  <variable name="outbound_caller_id_number" value="]] .. u.outbound_caller_id_number .. [["/>
          <variable name="callgroup" value="]] .. u.callgroup .. [["/>
          <variable name="uservar1" value="]] .. u.uservar1 .. [["/>
          <variable name="uservar2" value="]] .. u.uservar2 .. [["/>
          <variable name="uservar3" value="]] .. u.uservar3 .. [["/>
        </variables>
      </user>
    </domain>
  </section>
</document>]]
 
  -- закомментируйте для продакшн:
  freeswitch.consoleLog("notice", "Debug from directory_xml.lua, generated XML:\n" .. XML_STRING .. "\n")
end))

and enable sending XML directory lookups through Lua by adding the following lines to your lua.conf.xml::

    <param name="xml-handler-script" value="/usr/share/freeswitch/scripts/directory_xml.lua"/>
    <param name="xml-handler-bindings" value="directory"/>

postgres:

local dbh = freeswitch.Dbh("pgsql://host=127.0.0.1 dbname=DB user=USER password='PASS' options='-c client_min_messages=NOTICE' application_name='freeswitch'")
...
local dir_query = string.format("select * from directory where domain = '%s' and \"%s\"='%s' limit 1", req_domain, req_key, req_user)

Пример добавления данных в таблицу:

insert into directory (domain, id, `number-alias`, password, effective_caller_id_number, effective_caller_id_name, outbound_caller_id_number, outbound_caller_id_name, toll_allow, callgroup, default_gateway) values ('192.168.244.231','2669', '2669', 'bdcccee89976d1057dc3d116e2539a7d', '2669', '%D0%9E.%D0%A1%D0%BC%D0%B8%D1%80%D0%BD%D0%BE%D0%B2', '2669', 'Т.Тестов', 'obit', 'test', 'fs0');

Параметр a1-hash использует MD5 digest строки: «username:domain:password» (без кавычек):

 echo -n "username:domain:password" | md5sum

Пример Request Params при REGISTER

019-11-06 10:34:11.799679 [NOTICE] switch_cpp.cpp:1443 Debug from directory_xml.lua, provided params:
Event-Name: REQUEST_PARAMS
Core-UUID: ca11cf2a-ef31-11e9-a344-518f81fef5a9
FreeSWITCH-Hostname: fs.ru.net
FreeSWITCH-Switchname: fs.ru.net
FreeSWITCH-IPv4: 123.123.123.123
FreeSWITCH-IPv6: 
Event-Date-Local: 2019-11-06%2010%3A34%3A11
Event-Date-GMT: Wed,%2006%20Nov%202019%2007%3A34%3A11%20GMT
Event-Date-Timestamp: 1573025651799679
Event-Calling-File: sofia_reg.c
Event-Calling-Function: sofia_reg_parse_auth
Event-Calling-Line-Number: 2849
Event-Sequence: 7067192
action: sip_auth
sip_profile: secs
sip_user_agent: Zoiper%20rv2.9.2
sip_auth_username: 74345
sip_auth_realm: fs.ru.net
sip_auth_nonce: 0fbb3f92-0059-11ea-a62c-518f81fef5a9
sip_auth_uri: sip%3Afs.ru.net%3A5089%3Btransport%3DTLS
sip_contact_user: 74345
sip_contact_host: 124.124.124.124
sip_to_user: 74345
sip_to_host: fs.ru.net
sip_to_port: 5888
sip_via_protocol: tls
sip_from_user: 74345
sip_from_host: fs.ru.net
sip_from_port: 5888
sip_call_id: UF10F1hYSWQ7Dei8hyGTCw..
sip_request_host: fs.ru.net
sip_request_port: 5888
sip_auth_qop: auth
sip_auth_cnonce: 6cbb480b38f3b32f92bb66057cf9679c
sip_auth_nc: 00000070
sip_auth_response: f2efaec3c9bc091eb20adf0d4a0981e6
sip_auth_method: REGISTER
client_port: 59640
key: id
user: 74345
domain: fs.ru.net
ip: 124.124.124.124

This page on english in my blog

https://freeswitch.org/confluence/display/FREESWITCH/Lua+FreeSWITCH+Dbh

local req_domain = params:getHeader("domain")
local req_key    = params:getHeader("key")
local req_user   = params:getHeader("user")
local req_password   = params:getHeader("pass")
 
local dbh = freeswitch.Dbh("freeswitch","postgres","tttt");
freeswitch.consoleLog("NOTICE","start connect DB...\r\n");
assert(dbh:connected());
dbh:query("select password from users where id="..req_user,function(row)
        freeswitch.consoleLog("NOTICE",string.format("%s\n",row.password))
        req_password=string.format("%s",row.password)
end);
dbh:release();
 
freeswitch.consoleLog("NOTICE","info:"..req_domain.."--"..req_key.."--"..req_user.."--"..req_password.."\n");
 
 
 
--assert (req_domain and req_key and req_user,
--"This example script only supports generating directory xml for a single user !\n")
if req_domain ~= nil and req_key~=nil and req_user~=nil then
    XML_STRING =
    [[<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <document type="freeswitch/xml">
      <section name="directory">
        <domain name="]]..req_domain..[[">
          <params>
        <param name="dial-string"
        value="{presence_id=${dialed_user}@${dialed_domain}}${sofia_contact(${dialed_user}@${dialed_domain})}"/>
          </params>
          <groups>
        <group name="default">
          <users>
            <user id="]] ..req_user..[[">
              <params>
            <param name="password" value="]]..req_password..[["/>
            <param name="vm-password" value="]]..req_password..[["/>
              </params>
              <variables>
            <variable name="toll_allow" value="domestic,international,local"/>
            <variable name="accountcode" value="]] ..req_user..[["/>
            <variable name="user_context" value="default"/>
            <variable name="directory-visible" value="true"/>
            <variable name="directory-exten-visible" value="true"/>
            <variable name="limit_max" value="15"/>
            <variable name="effective_caller_id_name" value="Extension ]] ..req_user..[["/>
            <variable name="effective_caller_id_number" value="]] ..req_user..[["/>
            <variable name="outbound_caller_id_name" value="${outbound_caller_name}"/>
            <variable name="outbound_caller_id_number" value="${outbound_caller_id}"/>
            <variable name="callgroup" value="techsupport"/>
              </variables>
            </user>
          </users>
        </group>
          </groups>
        </domain>
      </section>
    </document>]]
else
    XML_STRING =
    [[<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <document type="freeswitch/xml">
      <section name="directory">
      </section>
    </document>]]
end