Freeswitch 使用postgres存储用户

FreeSwitch的用户默认是以xml文件方式存放在directory目录下，对于后期维护来说非常不方便，本文用postgres方式存储用户，并用lua脚本来借还用户验证，步骤如下：

配置ODBC:

这里我的ODBC驱动安装在/etc/目录下
修改odbc.ini文件如下

[postgreSQL]
Description = PostgreSQL
Driver = /usr/lib64/psqlodbcw.so  #这个地方一定要注意，要填写psqlodbcw.so所在路径，否则在连接数据库的时候会报如下错误
#[IM002][unixODBC][Driver Manager]Data source name not found, and no default #driver specified
#[ISQL]ERROR: Could not SQLConnect
Database = postgres
Servername = 172.20.11.71
UserName = postgres
Password = 123456
Port = 5432
#Protocol        = 6.4  协议可以不用写，如果写错也会报错[28000][unixODBC]FATAL:  SASL authentication is not supported in protocol version 2
#[ISQL]ERROR: Could not SQLConnect
ReadOnly        = No
RowVersioning   = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
#ConnSettings    =
#ConnSettings = set client_encoding to UTF8

修改odbcinst.ini配置如下:

FileUsage       = 1
[postgreSQL]
Description     = PostgreSQL
Driver          = /usr/lib64/psqlodbcw.so
Setup           = /usr/lib64/libodbcpsqlS.so
Driver64        = /usr/lib64/psqlodbcw.so
Setup64         = /usr/lib64/libodbcpsqlS.so
FileUsage       = 1
#这里我的Driver和Driver64写的路径一致，不太清楚这两个地方有什么区别。

执行odbcinst -j命令可以查看当前配置信息情况，以确认相关配置信息的路径是否正确。

unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

修改环境变量

在这里插入代码片
export ODBCINI=etc/odbc.ini
export ODBCSYSINI=/etc

执行isql -v PostgreSQL
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL>
提示连接成功。

创建数据库表

#创建用户表
CREATE TABLE users
(
  "id" serial   PRIMARY KEY,
  "extension" SMALLINT DEFAULT NULL,
  "password" INT DEFAULT NULL
) 
	
 
#插入两个用户
insert into users  values('1011','1234');
insert into users  values('1012','1234');

将用户验证转到lua脚本

1、将freeswitch/conf/autoload_configs/lua.conf.xml中的两行代码修改为如下所示

<param name="xml-handler-script" value="gen_dir_user_xml.lua" />
<param name="xml-handler-bindings" value="directory" />

2、让lua脚本接管用户注册验证，这里调用的脚本是freeswitch/script/gen_dir_user_xml.lua内容如下，默认无该脚本，需自行创建

freeswitch.consoleLog("NOTICE","lua take the users...\n");
 
local req_domain = params:getHeader("domain")#这个地方domain要写成话机的注册地址，即本机地址下面三行不用改
local req_key    = params:getHeader("key")
local req_user   = params:getHeader("user")
local req_password   = params:getHeader("pass")
 
freeswitch.consoleLog("NOTICE","start connect DB...\r\n");
 
local dbh = freeswitch.Dbh("freeswitch","root","123456");#这个地方需要修改数据库的地址和数据库用户名密码
assert(dbh:connected());
dbh:query("select password from user where user="..req_user,function(row)
        freeswitch.consoleLog("NOTICE","DB select password="..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");
 
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
 
freeswitch.consoleLog("NOTICE", "debug from gen_dir_user_xml.lua, generated XML:\n" .. XML_STRING .. "\n");

3、修改freeswitch/conf/directory/default.xml中的一部分内容，使得通过xml验证用户的功能失效，这样lua才能真正接管用户注册，删除的内容如下（或者注释也可以）：

<group name="default">
    <users>
      <X-PRE-PROCESS cmd="include" data="default/*.xml"/>
    </users>
 </group>

四、测试

使用eyeBeam测试1011、或1012是否能正常登陆，可登陆即为配置成功