PostgreSQL notes

October 31st, 2013

1. Edit postgresql.conf line listen_addresses = '10.14.1.21' to your appropriate value.
2. Add to pg_hba.conf a line, which will allow connection to the database from certain IP address/subnet: host johnsdb john 10.14.1.201/32 md5
3. service postgresql reload
4. As root, ‘su - postgres‘ , then ‘psql‘ , then:

4.1. postgres=# CREATE USER john WITH ENCRYPTED PASSWORD 'johnstailislong' ;
4.2. postgres=# CREATE DATABASE johnsdb WITH OWNER john ;
( If the database owner is wrong, change it: postgres=# ALTER DATABASE johnsdb OWNER TO john ; )
5. Then connect from outside to the already created database and create a table inside it:
psql -d johnsdb -U john -h 10.14.1.21
johnsdb=> CREATE TABLE johnstable (type varchar(6),name varchar(128),secret varchar(128),context varchar(128),host varchar(128)) WITH (OIDS=FALSE);

Hints:

Change DB’s owner (as postgresql user):
ALTER DATABASE johnsdb OWNER TO john ;

Change table’s owner (as postgresql user):
ALTER TABLE johnstable OWNER TO john ;

Table permissions: (as postgresql user):
GRANT ALL ON TABLE johnstable TO john ;

Change default values in a column:
ALTER TABLE tablename ALTER COLUMN columnname SET DEFAULT 'green,red' ;

Rename a column:
ALTER TABLE tablename RENAME COLUMN oldcolname TO newcolname;

Change column type:
ALTER TABLE tablename ALTER COLUMN columnname TYPE varchar(30);

Get last N rows:
SELECT * FROM mytable ORDER BY record_date DESC LIMIT 5;

Get first N rows:
SELECT * FROM mytable ORDER BY record_date ASC LIMIT 5;

Select between X and Y values:
SELECT name,callerid FROM sip_conf WHERE name BETWEEN '1301' and '1310' ORDER BY name;

Show databases:

astertest=> \list
                                  List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+-----------+----------+-------------+-------------+-----------------------
 astertest | astertest | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | 
 postgres  | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | 
 template0 | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |           |          |             |             | postgres=CTc/postgres
 template1 | postgres  | UTF8     | ru_RU.UTF-8 | ru_RU.UTF-8 | =c/postgres          +
           |           |          |             |             | postgres=CTc/postgres
(4 rows)

Show tables in the current database:

astertest=> \d
                   List of relations
 Schema |          Name          |   Type   |   Owner   
--------+------------------------+----------+-----------
 public | sip_conf_office        | table    | astertest
 public | sip_conf_office_id_seq | sequence | astertest
(2 rows)

Show table structure:

astertest=> \d sip_conf_office
                                     Table "public.sip_conf_office"
     Column     |          Type          |                          Modifiers                           
----------------+------------------------+--------------------------------------------------------------
 id             | integer                | not null default nextval('sip_conf_office_id_seq'::regclass)
 name           | character varying(80)  | not null default ''::character varying
 accountcode    | character varying(20)  | 
 amaflags       | character varying(7)   | 
 callgroup      | character varying(10)  | 
 callerid       | character varying(80)  | 
 canreinvite    | character varying(3)   | default 'no'::character varying
 context        | character varying(80)  | 
 defaultip      | character varying(15)  | 
 dtmfmode       | character varying(7)   | 
 fromuser       | character varying(80)  | 
 fromdomain     | character varying(80)  | 
 host           | character varying(31)  | not null default ''::character varying
 insecure       | character varying(4)   | 
 language       | character varying(2)   | 
 mailbox        | character varying(50)  | 
 md5secret      | character varying(80)  | 
 nat            | character varying(5)   | not null default 'no'::character varying
...

To insert many lines in SQL-syntax from txt file:

psql -U asterisk -h localhost -d asterisk -f /root/peers.txt

… assuming that peers.txt looks like:

INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0001','Operator 0001 <0001>','0001','operators','5060');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0001d','Operator 0001 <0001>','0001d','operators','5061');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0002','Operator 0002 <0002>','0002','operators','5060');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('0002d','Operator 0002 <0002>','0002d','operators','5061');
...
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('9999','Operator 9999 <9999>','9999','operators','5060');
INSERT INTO sip_conf_new (name,callerid,defaultuser,context,port) VALUES ('9999d','Operator 9999 <9999>','9999d','operators','5061');

Answer please

October 31st, 2013

Those who visit my blog from north, get in touch please, I’m just interested who are you and from where have you known of this inconspicuous weblog. :)
I’m available at kurgan-rus a t inbox ddot ru and/or at http://zantsev.com/.
sever

Asterisk AMI

October 28th, 2013

Let your first-line support do ‘sip reload’ neither with knowing Asterisk, nor with a priveleged account in the system.
Set up manager.conf:

[general]
enabled = yes
webenabled = no

port = 5038
bindaddr = 10.190.52.14

[support]
secret = supportpass
deny=0.0.0.0/0.0.0.0
permit=10.190.52.0/255.255.255.0
permit=10.190.51.4/255.255.255.255

displayconnects = yes
read = system,command
write = system,command

Create a php page on some host with Apache and PHP configured:

';
}
fclose($socket);
}
?>

Asterisk run user

September 27th, 2013

When installing Asterisk from source, you need to create an unprivileged user manually.
Add to /etc/group something like:
asterisk:x:110:
and to /etc/passwd:
asterisk:x:107:110:Asterisk PBX daemon,,,:/var/lib/asterisk:/bin/false
Just set unused GID and UID.

Then change permissions:
chown -R asterisk:asterisk /var/lib/asterisk/
chown -R asterisk:asterisk /var/spool/asterisk/
chown -R asterisk:asterisk /var/log/asterisk/
chown -R asterisk:asterisk /var/run/asterisk/

And finally, set the running user in asterisk.conf.

It is not mentioned in the Book, but I think chown‘ing /etc/asterisk/ with the -R option is also worth doing; changing config files (not directories, if exist) permissions in /etc/asterisk to 640 either.

Putting a plenty of numbers to the blacklist

September 16th, 2013

for i in (list_numbers); do
asterisk -rx "database put blacklist $i 1"
done

Where ‘list_numbers’ is your file with numbers.
Tnanks to zzuz.

How to define an Asterisk module by function name

September 16th, 2013

Asterisk has a nice function PITCH_SHIFT, which can modify caller’s/callee’s voice. Let’s imagine, we have a system without autoloaded modules, and we need to find which module provides this function.

1. Go to the astmoddir (I have a Debian system):

cd /usr/lib/asterisk/modules

2. Find.

root@voiprouter1:/usr/lib/asterisk/modules# grep PITCH *
Binary file func_pitchshift.so matches
root@voiprouter1:/usr/lib/asterisk/modules#

3. We can also find the dependencies:

root@voiprouter1:/usr/lib/asterisk/modules# ldd func_pitchshift.so
linux-gate.so.1 => (0xb776e000)
libpthread.so.0 => /lib/i386-linux-gnu/i686/cmov/libpthread.so.0 (0xb7747000)
libc.so.6 => /lib/i386-linux-gnu/i686/cmov/libc.so.6 (0xb75e4000)
/lib/ld-linux.so.2 (0xb776f000)

4. That’s it! Thanks to meral.

OpenBSD pkg_add: flavours

August 20th, 2013

Use pkg_add -i to choose from package flavours.

Package description (pkg_info asterisk) tells us that it can be standard or have IMAP flavour:
Asterisk supports Voice over IP in many protocols, and can
interoperate with almost all standards-based telephony equipment
using relatively inexpensive hardware.

Flavours:

IMAP - use imap for voicemail storage instead of files.

Maintainer: Stuart Henderson

WWW: http://www.asterisk.org/

Installation:
# pkg_add -ivvv asterisk

Ambiguous: choose package for asterisk
a 0:
1: asterisk-10.12.1
2: asterisk-10.12.1-imap
Your choice: 2

Digium Certified Asterisk Administrator (dCAA)

July 10th, 2013

Now I am a Digium Certified Asterisk Administrator (dCAA).
dcaa

SalixOS, OpenVPN and iproute2

June 25th, 2013

I have an OpenVPN server through which its clients get some routes. One client with SalixOS had an error while connecting to the server:
Linux ip link set failed: could not execute external program
The reason is that OpenVPN client sets the routes with the ‘ip’ command. But Salix doesn’t install the iproute2 package by default. Install it and the connection will be finished.

Openvpn >= 2.1, Windows and *nix clients, topology subnet, client-to-client

June 6th, 2013

If you have both Windows and *nix clients in your OpenVPN implementation and need them to be able to communicate with each other (‘client-to’client’ option in the server configuration file), you may face the problem on Windows, while using the stable version of OpenVPN GUI. It’s based on 2.0 OpenVPN, which doesn’t have the ability to handle client connection with /24 subnet mask, and we strongly need it.
In such a case use the development version of OpenVPN GUI. For this moment (June 6, 2013) it is OpenVPN 2.1_beta7 & OpenVPN GUI 1.0.3. The trick is that OpenVPN supports ‘topology xxx’ directive starting from the 2.1 version, and the development package (as for today) for Windows is based on it.
Your Windows client’s configuration file should be similar to this:

client
dev tun
proto udp
remote IP.ADD.RE.SS 1194
topology subnet
nobind
persist-key
persist-tun
;
ca "c:\\program files\\openvpn\\ca.crt"
cert "c:\\program files\\openvpn\\client5.crt"
key "c:\\program files\\openvpn\\client5.key"
;
comp-lzo
verb 3

UPDATE: as for now (January 30, 2018) Windows version is available from other location: https://openvpn.net/index.php/open-source/downloads.html
Howto: https://community.openvpn.net/openvpn/wiki/Easy_Windows_Guide