This post will attempt to cover everything necessary start to finish.
This guide assumes your using mariadb-server but if requested I can also cover mysql-server.
The beauty of this is a single database of contacts where you can block or prioritize calls.
You can have as many remote freepbx system as you want all using this single database.
If the database is going on the same machine as your freepbx then you already have one or the other.
On debian based systems you can check which is installed like this:
sudo apt list --installed | grep 'mariadb-server\|mysql-server'
In my case I actually run mariadb in a container separate from freepbx, so first I install mariadb-server:
sudo apt install mariadb-client mariadb-server
Next I secure the installation, I set a password for root and I answer Yes (default) to the other questions:
sudo mysql_secure_installation
Next I put my Let’s Encrypt Certs on my sql server.
I generate certs using pfsense ACME and a shell script to copy them to sql server via sftp in a chroot.
You could also just generate them directly on the sql server with certbot. (search letsencrypt certbot)
If your db is on the same host as freepbx and no remote systems will use the db then you can skip certs.
Most guides covering mysql/mariadb SSL certs use self signed certs. I do not recommend this because then every client needs certs in order to trust the self signed certs, but Let’s Encrypt is a trusted CA so certs are only needed on the sql server.
Make the directory where we will put our certs, and create the placeholder files:
sudo mkdir -p /etc/mysql/ssl
sudo touch /etc/mysql/ssl/{ca,server-cert,server-key}.pem
sudo chown -R mysql: /etc/mysql/ssl
sudo chmod 755 /etc/mysql/ssl
sudo chmod 400 /etc/mysql/ssl/*
Certbot can automatically renew certs, if you dont want to have to customize it to put the certs with the correct permissions in the correct location, then you can instead just setup a cron job.
You may have to adjust this a bit, its been years since I have used certbot, but what this does is copies the contents of the file from the certbot location to the mysql location, keeping the permissions in place that we already setup.
When you use crontab, it will ask how you want to edit, I suggest nano if your unsure:
sudo crontab -e
0 4 * * * cat /etc/letsencrypt/live/example.com/ca.pem | tee /etc/mysql/ssl/ca.pem >/dev/null
0 4 * * * cat /etc/letsencrypt/live/example.com/server-cert.pem | tee /etc/mysql/ssl/server-cert.pem >/dev/null
0 4 * * * cat /etc/letsencrypt/live/example.com/server-key.pem | tee /etc/mysql/ssl/server-key.pem >/dev/null
1 4 * * * systemctl restart mysql
Configure the mariadb server to use SSL and open it to listen to remote connections:
sudo nano /etc/mysql/mariadb.conf.d/z-server.cnf
[mariadb]
bind-address = 0.0.0.0
ssl-ca = /etc/mysql/ssl/ca.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
ssl-cipher = TLSv1.2,TLSv1.3
ssl = true
Now restart mariadb:
sudo systemctl restart mariadb
Now lets create a database, user, and table, and create a couple records:
sudo mysql
CREATE DATABASE contacts;
CREATE USER 'freepbx'@'%' IDENTIFIED BY 'ExampleSuperLongPassword';
GRANT SELECT ON contacts.* TO 'freepbx'@'%';
FLUSH PRIVILEGES;
USE contacts;
CREATE TABLE `contacts` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`phone` BIGINT NOT NULL,
`note` varchar(255),
`block` boolean NOT NULL DEFAULT false,
`vip` boolean NOT NULL DEFAULT false,
PRIMARY KEY (`id`)
);
INSERT INTO contacts (name, phone, note) VALUES ('test', 5555555555, 'test');
INSERT INTO contacts (name, phone, note) VALUES ('Cust Survey', 5555555555, 'robo');
INSERT INTO contacts (name, phone, note, block) VALUES ('Vacuum Sales', 5555555555, 'robo', true);
EXIT;
Now on freepbx host configure it to use the contacts db via ODBC.
Next we will try connecting to the sql server we just setup.
Mariadb/odbc will not use SSL by default, meaning credentials/data are sent in plain text.
If your sql server is on the same network as FreePBX then you should go ahead and test connecting.
If sql server is on different network then you should probably jump down to where we enforce SSL.
Connect and check if SSL is supported, if setup properly it should show output like this:
mysql -u freepbx -p -h 192.168.0.x
mysql -u freepbx -p -h sql.example.com
SHOW VARIABLES LIKE '%ssl%';
+-----------------------------------------------------
| have_openssl | YES
| have_ssl | YES
| ssl_ca | /etc/mysql/ssl/ca.pem
| ssl_cert | /etc/mysql/ssl/client-cert.pem
| ssl_key | /etc/mysql/ssl/client-key.pem
+-----------------------------------------------------
Above you can see that your able to connect via IP, but we will now enforce SSL:
sudo nano /etc/mysql/mariadb.conf.d/z-client.cnf
[client]
ssl-verify-server-cert=on
Now you should no longer be able to connect via IP.
You will need to use the domain name that matches your cert, try connecting again:
mysql -u freepbx -p -h sql.example.com
If this works then you have successfully setup a sql server and connected to it VIA SSL.
The next step is to setup ODBC so that you can use this database within FreePBX.
We will define another data source at the bottom of the odbc.ini file
sudo nano /etc/odbc.ini
[contacts]
Description=MySQL connection to contacts database
Trace=Off
TraceFile=stderr
Driver=MySQL
SERVER=sql.example.com
PORT=3306
DATABASE=contacts
SSLVERIFY=1
CONN_TIMEOUT=1
readtimeout=1
Modify res_odbc_custom.conf giving freepbx/asterisk access to the db we defined in odbc.ini
You need to use the same password here that you did above when you created the database and table:
sudo nano /etc/asterisk/res_odbc_custom.conf
[contacts]
enabled=>yes
dsn=>contacts
username=>freepbx
password=>ExampleSuperLongPassword
pooling=>no
limit=>1
pre-connect=>yes
connect_timeout=>1
Modify func_odbc.conf which gives freepbx/asterisk access to the db as a function:
sudo nano /etc/asterisk/func_odbc.conf
[contacts]
dsn=contacts
readsql=${ARG1}
Check the odbc settings:
sudo ldconfig
sudo odbcinst -i -d -f /etc/odbcinst.ini
sudo odbcinst -i -s -l -f /etc/odbc.ini
sudo odbcinst -q -d
Now we will reboot, and asterisk/freepbx should have access to this database.
sudo reboot
If it worked then you should be able to do this and connect via ODBC:
isql -v contacts freepbx ExampleSuperLongPassword
SELECT * FROM contacts.contacts;
Now in FreePBX Web GUI configure dynroutes to use db via ODBC.
Create a Dynamic route and set the ODBC function name to:
contacts
Set the Dynamic route query to:
SELECT COUNT(*) FROM (SELECT phone,name FROM contacts WHERE (name LIKE '${CALLERID(name)}' OR phone LIKE '${CALLERID(num)}') LIMIT 1) AS myvar
The above query checks for a matching name or number in the database, you can route it accordingly.
Now say a couple contacts in the db are rude or downright keep calling even after being told not to.
Maybe you also have a very important person that you want to prioritize their calls.
Lets update our database to block the rude caller, and prioritize the important caller.
on your sql server connect to mysql using unix socket or you can also connect using the domain name.
sudo mysql
mysql -u root -p -h sql.example.com
USE contacts;
select * from contacts;
UPDATE contacts SET block = true WHERE name = 'Cust Survey';
UPDATE contacts SET vip = true WHERE name = 'test';
select * from contacts;
So now we are using the block and vip fields of the DB so instead of simply checking for a name in the db, lets check if block or vip is set to true and route accordingly:
VIP:
SELECT COUNT(*) FROM (SELECT phone,name,VIP FROM contacts WHERE ((name LIKE '${CALLERID(name)}' OR phone LIKE '${CALLERID(num)}') AND VIP="1") LIMIT 1) AS myvar
Block:
SELECT COUNT(*) FROM (SELECT phone,name,block FROM contacts WHERE ((name LIKE '${CALLERID(name)}' OR phone LIKE '${CALLERID(num)}') AND block="1") LIMIT 1) AS myvar
I will cover more usage and example of dynamic routes soon. (including pictures)
I will show how to block callers or even prioritize callers, etc.
With small database changes you can do other things as well, dynroutes allows for very flexible routing.
This one is not SQL specific, but if you set the Source type to Asterisk variable instead of odbc for one of your routes. you can set the asterisk variable field to this:
${REGEX("^(800|888|877|866|855|844|833|822|880|881|882|883|884|885|886|887|889)" ${CALLERID(number)})}
That will catch all Toll Free numbers, you can route them to Terminate Call or if your woried it might be something you need, then you can just route it straight to voicemail. 99% of the toll free calls I get are marketing so I send them straight to voicemail so that I don’t get disturbed by the ringing phone.
Credits to these sources of information I used while working this out on my systems:
https://www.voipsupport.it/wiki/index.php/How_to_set_up_an_Asterisk_ODBC_data_source_on_Linux