Unable to Connect Asterisk to AWS RDS PostgreSQL via ODBC - "Peer Authentication Failed"

[soumya26-pi]

I’m trying to integrate my Asterisk PBX system with an AWS RDS PostgreSQL database using res_odbc and cdr_adaptive_odbc. Despite following the configuration guidelines, I’m encountering an error when attempting to connect via isql or when reloading Asterisk modules. The error suggests that “Peer authentication” is being used, but I have configured the database to use password-based authentication.

Error Messages

From isql:

[08001][unixODBC]connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "asterisk"
[ISQL]ERROR: Could not SQLConnect

From Asterisk logs:

[Dec 23 11:26:46] WARNING[1579554]: res_odbc.c:1083 odbc_obj_connect: res_odbc: Error SQLConnect=-1 errno=101 [unixODBC]connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "asterisk"
[Dec 23 11:26:46] WARNING[1579554]: cdr_adaptive_odbc.c:136 load_config: No such connection 'asterisk' in the 'asterisk' section of cdr_adaptive_odbc.conf. Check res_odbc.conf.

Environment Details

• Asterisk Version: 18.25.0
• Operating System: Ubuntu 22.04
• Database: AWS RDS PostgreSQL
• ODBC Driver: PostgreSQL ODBC driver (psqlodbc from odbc-postgresql package)
• unixODBC Version: 2.3.6

Configuration Files

1. /etc/asterisk/res_odbc.conf:

   [asterisk]
   enabled => yes
   dsn => asterisk
   username => asterisk
   password =>  password
   pre-connect => yes
   

2. /etc/asterisk/cdr_adaptive_odbc.conf:

   [asterisk]
   connection = asterisk
   table = intercom.asterisk_call_log
   loguniqueid = yes
   alias start => calldate
   alias clid => clid
   alias src => src
   alias dst => dst
   alias dcontext => dcontext
   alias channel => channel
   alias dstchannel => dstchannel
   alias lastapp => lastapp
   alias lastdata => lastdata
   alias duration => duration
   alias billsec => billsec
   alias disposition => disposition
   alias amaflags => amaflags
   alias accountcode => accountcode
   alias uniqueid => uniqueid
   alias userfield => userfield
   

3. /etc/odbc.ini:

   [asterisk]
   Description = PostgreSQL connection to 'asteriskcdrdb' database
   Driver = PostgreSQL
   Server = Database.rds.amazonaws.com
   Port = 5432
   Database = Database_Name
   UID = asterisk
   PWD = Password
   SSLmode = require
   

4. /etc/odbcinst.ini:

   [PostgreSQL]
   Description = ODBC for PostgreSQL
   Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbc.so
   Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
   FileUsage = 1
   

Steps Taken

1. Verified PostgreSQL user and password by connecting using psql:

   psql -h Database.rds.amazonaws.com -U asterisk -d Database_Name
   

   Result: Successfully connected with no issues.

2. Tested the ODBC connection using isql:

   ODBCINI=/etc/odbc.ini ODBCSYSINI=/etc isql -v asterisk asterisk  password
   

   Result: The connection fails with a “Peer authentication” error.

3. Ensured SSLmode is set to require in odbc.ini to match RDS requirements.

4. Enabled ODBC tracing and reviewed the logs, which confirmed the connection attempt was falling back to local peer authentication.

Questions

1. Why is the ODBC connection attempting to use peer authentication instead of MD5/password-based authentication?
2. Is there a specific configuration in unixODBC or Asterisk that overrides the odbc.ini settings?
3. How can I debug or force Asterisk to connect using the correct credentials and not attempt local socket connections?

Logs

• Output of asterisk -rx "odbc show":

  ODBC DSN Settings
  -----------------
  Name:   asterisk
  DSN:    asterisk
      Last fail connection attempt: 2024-12-23 11:22:46
      Number of active connections: 0 (out of 1)
      Logging: Disabled
  

What I Need

Any guidance or suggestions on fixing the “Peer authentication failed” error and ensuring Asterisk correctly connects to the AWS RDS PostgreSQL database using ODBC.

Thank you in advance for your help!