Difference between revisions of "3rd party software/Asterisk"
(→psql login test) |
|||
| (14 intermediate revisions by 4 users not shown) | |||
| Line 1: | Line 1: | ||
| − | [[Category:3rd_party_software]] | + | [[Category:3rd_party_software]] |
Asterisk can be used as <b><em>Soft Switch</em></b> (among many other things). Freeside can examine recorded CDR data for subscriber billing purposes. | Asterisk can be used as <b><em>Soft Switch</em></b> (among many other things). Freeside can examine recorded CDR data for subscriber billing purposes. | ||
| Line 24: | Line 24: | ||
=== pg_hba.conf === | === pg_hba.conf === | ||
| − | + | This <code>pg_hba.conf</code> sample is only a snippet of the ACL to permit asterisk to connect. | |
| − | < | + | <pre># TYPE DATABASE USER CIDR-ADDRESS METHOD |
| + | hostssl freeside asterisk 10.14.0.0/24 md5</pre> | ||
| + | |||
| + | '''Reload postgres''' | ||
| − | + | <pre>freeside:~# /etc/init.d/postgresql-8.1 reload | |
| + | Reloading PostgreSQL 8.1 database server: main. | ||
| + | freeside:~#</pre> | ||
| − | + | If you forget these steps, you might see this error message on the asterisk console (or logs) after the call is finished. (eg, at hangup) | |
| − | |||
| − | + | <pre>Feb 20 21:39:52 ERROR[4322]: cdr_pgsql.c:89 pgsql_log: cdr_pgsql: Reason: FATAL: no pg_hba.conf entry for host "10.141.0.5", user "asterisk", database "freeside", SSL off</pre> | |
See Also: [http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF pg_hba.conf at postgres.org] for more information on Postgres client authentication. | See Also: [http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF pg_hba.conf at postgres.org] for more information on Postgres client authentication. | ||
| Line 39: | Line 43: | ||
=== Create asterisk pg user === | === Create asterisk pg user === | ||
| − | As the postgres superuser, create a non-privileged asterisk user. | + | As the postgres superuser, create a non-privileged asterisk user, and set the password saved in <code>cdr_pgsql.conf</code>. |
| − | <pre>postgres@freeside:~$ createuser asterisk | + | <pre>postgres@freeside:~$ createuser -P asterisk |
| + | Enter password for user "asterisk": | ||
| + | Enter it again: | ||
Shall the new role be a superuser? (y/n) n | Shall the new role be a superuser? (y/n) n | ||
Shall the new role be allowed to create databases? (y/n) n | Shall the new role be allowed to create databases? (y/n) n | ||
| Line 72: | Line 78: | ||
Code snippet to '''GRANT''' selective permissions to asterisk. | Code snippet to '''GRANT''' selective permissions to asterisk. | ||
| − | <pre> | + | <pre>GRANT INSERT ON cdr TO asterisk; |
| − | GRANT INSERT ON cdr TO | ||
| − | |||
| − | |||
GRANT UPDATE ON cdr_acctid_seq to asterisk;</pre> | GRANT UPDATE ON cdr_acctid_seq to asterisk;</pre> | ||
The psql client will respond similar to this: | The psql client will respond similar to this: | ||
| − | <pre>freeside | + | <pre>freeside:~# su - postgres |
| − | + | postgres@freeside:~$ psql freeside | |
| + | Welcome to psql 8.1.11, the PostgreSQL interactive terminal. | ||
| + | |||
| + | Type: \copyright for distribution terms | ||
| + | \h for help with SQL commands | ||
| + | \? for help with psql commands | ||
| + | \g or terminate with semicolon to execute query | ||
| + | \q to quit | ||
| + | |||
freeside=# GRANT INSERT ON cdr TO asterisk; | freeside=# GRANT INSERT ON cdr TO asterisk; | ||
| − | |||
| − | |||
| − | |||
| − | |||
GRANT | GRANT | ||
freeside=# GRANT UPDATE ON cdr_acctid_seq to asterisk; | freeside=# GRANT UPDATE ON cdr_acctid_seq to asterisk; | ||
GRANT | GRANT | ||
| − | freeside=# </pre> | + | freeside=#</pre> |
=== Reload cdr_pgsql === | === Reload cdr_pgsql === | ||
| Line 109: | Line 116: | ||
acctid | calldate | src | dst | duration | billsec | disposition | acctid | calldate | src | dst | duration | billsec | disposition | ||
--------+------------------------+------------+-------------+----------+---------+------------- | --------+------------------------+------------+-------------+----------+---------+------------- | ||
| − | 1 | 2008-02-20 15:40:00+00 | | + | 1 | 2008-02-20 15:40:00+00 | 8005550149 | 18004664411 | 19 | 0 | NO ANSWER |
| − | 2 | 2008-02-20 15:48:52+00 | | + | 2 | 2008-02-20 15:48:52+00 | 8005550149 | 18004664411 | 14 | 10 | ANSWERED |
(2 rows) | (2 rows) | ||
| Line 127: | Line 134: | ||
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:170 pgsql_log: cdr_pgsql: HARD ERROR! Attempted reconnection failed. DROPPING CALL RECORD! | Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:170 pgsql_log: cdr_pgsql: HARD ERROR! Attempted reconnection failed. DROPPING CALL RECORD! | ||
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:171 pgsql_log: cdr_pgsql: Reason: ERROR: permission denied for sequence cdr_acctid_seq</pre> | Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:171 pgsql_log: cdr_pgsql: Reason: ERROR: permission denied for sequence cdr_acctid_seq</pre> | ||
| + | |||
| + | == Related Topics == | ||
| + | * [[Freeside:1.7:Documentation:Administration:VoIP]] | ||
| + | [[Category:svc_phone]] | ||
== See Also == | == See Also == | ||
Latest revision as of 16:42, 3 January 2012
Asterisk can be used as Soft Switch (among many other things). Freeside can examine recorded CDR data for subscriber billing purposes.
Contents
cdr_pgsql
Call detail records are stored by asterisk (by means of the cdr_pgsql extension) into a postgres database of our choice. We will need to edit /etc/asterisk/cdr_pgsql.conf, add the asterisk user to postgres, add rights for asterisk to access the cdr table, and verify settings actually work.
cdr_pgsql.conf
This is our example config file. You will need to change the password to suit your install.
; Sample Asterisk config file for CDR logging to PostgresSQL [global] hostname=postgres port=5432 dbname=freeside password=FIXTHIS user=asterisk table=cdr ;SQL table where CDRs will be inserted spool=pgsql.spool
pg_hba.conf
This pg_hba.conf sample is only a snippet of the ACL to permit asterisk to connect.
# TYPE DATABASE USER CIDR-ADDRESS METHOD hostssl freeside asterisk 10.14.0.0/24 md5
Reload postgres
freeside:~# /etc/init.d/postgresql-8.1 reload Reloading PostgreSQL 8.1 database server: main. freeside:~#
If you forget these steps, you might see this error message on the asterisk console (or logs) after the call is finished. (eg, at hangup)
Feb 20 21:39:52 ERROR[4322]: cdr_pgsql.c:89 pgsql_log: cdr_pgsql: Reason: FATAL: no pg_hba.conf entry for host "10.141.0.5", user "asterisk", database "freeside", SSL off
See Also: pg_hba.conf at postgres.org for more information on Postgres client authentication.
Create asterisk pg user
As the postgres superuser, create a non-privileged asterisk user, and set the password saved in cdr_pgsql.conf.
postgres@freeside:~$ createuser -P asterisk Enter password for user "asterisk": Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n CREATE ROLE
psql login test
We login to the database from the asterisk server to verify our setup is (so far) working okay. pghostname is the postgres host where your freeside database is.
pbx:~$ psql -h pghostname -U asterisk freeside
Password for user asterisk:
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
freeside=>
GRANT asterisk permissions
We are allowing asterisk limited access to the Freeside database. Freeside isn't expected to have GRANT permissions on the freeside database. Connect as the postgres superuser.
Code snippet to GRANT selective permissions to asterisk.
GRANT INSERT ON cdr TO asterisk; GRANT UPDATE ON cdr_acctid_seq to asterisk;
The psql client will respond similar to this:
freeside:~# su - postgres
postgres@freeside:~$ psql freeside
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
freeside=# GRANT INSERT ON cdr TO asterisk;
GRANT
freeside=# GRANT UPDATE ON cdr_acctid_seq to asterisk;
GRANT
freeside=#
Reload cdr_pgsql
Changes to the cdr_pgsql.conf only need a reload of the cdr_pgsql.so module for changes to be applied immediately.
asterisk -rx "reload cdr_pgsql.so"
Make a test call
If your asterisk configuration is able to make calls, place a test call that applies to your needs of freeside and asterisk (eg, a sample call that would be billed).
Verify new data records
We check for CDR entries to confirm our call progress is being recorded properly.
freeside=# SELECT acctid,calldate,src,dst,duration,billsec,disposition from cdr;
acctid | calldate | src | dst | duration | billsec | disposition
--------+------------------------+------------+-------------+----------+---------+-------------
1 | 2008-02-20 15:40:00+00 | 8005550149 | 18004664411 | 19 | 0 | NO ANSWER
2 | 2008-02-20 15:48:52+00 | 8005550149 | 18004664411 | 14 | 10 | ANSWERED
(2 rows)
freeside=#
Troubleshooting
Trouble may arise due to insufficent permissions, invalid username or password in the config files, failure to reload cdr_pgsql after settings are updated, and a number of other related factors. The system logs and asterisk console are a good starting point when attempting to isolate underlying causes of your issue.
This sample was due to insufficent GRANT permissions to asterisk on the cdr_acctid_seq object.
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:159 pgsql_log: cdr_pgsql: Failed to insert call detail record into database! Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:160 pgsql_log: cdr_pgsql: Reason: ERROR: permission denied for sequence cdr_acctid_seq Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:161 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect. Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:164 pgsql_log: cdr_pgsql: Connection reestablished. Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:170 pgsql_log: cdr_pgsql: HARD ERROR! Attempted reconnection failed. DROPPING CALL RECORD! Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:171 pgsql_log: cdr_pgsql: Reason: ERROR: permission denied for sequence cdr_acctid_seq
