Hi all
I've got postges 9.4 installed on Centos 6.5. I have access to it from psql and I have set up and filled a database I now want to access via ODBC - all on the localhost, no remote access involved.
If I do on my localhost machine on which postgres is running, while logged in as root:
Code:
psql -U mydbuser -d mydbname
it works fine - I get asked for my password (which is "mydbpassword"), which I then enter and I can then interact with postgres in the psql commandline client - run queries, create databases and tables, etc.
However, if I run
Code:
isql -v pgdb-cdr mydbuser mydbname mydbpassword
I get
Code:
[28000][unixODBC]FATAL: password authentication failed for user "mydbuser"
[ISQL]ERROR: Could not SQLConnect
How can I solve this? Postgres is running and accessible but ONLY through psql - once I try to use ODBC it just fails. Entering my password for -psql- WORKS - e. g. at postgres level it appears my password is set correctly. Only, when I try to access it via ODBC, my password is invalid?
Packages I installed in order to get postgres working on Centos 6.5:
Code:
# yum list installed | grep postgres
postgresql94.x86_64 9.4.4-1PGDG.rhel6
postgresql94-contrib.x86_64
postgresql94-devel.x86_64
postgresql94-docs.x86_64
postgresql94-libs.x86_64
postgresql94-odbc.x86_64
postgresql94-odbc-debuginfo.x86_64
postgresql94-server.x86_64
I have unixODBC installed via package:
Code:
# yum list installed | grep ODBC
unixODBC.x86_64 2.2.14-14.el6 @base
unixODBC-devel.x86_64 2.2.14-14.el6 @base
My /etc/odbcinst.ini (I have MySQL working fine via ODBC on that same physical machine, that is why it also appears):
Code:
# cat /etc/odbcinst.ini
# Example driver definitions
# Driver from the postgresql-odbc package
# Setup from the unixODBC package
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/pgsql-9.4/lib/psqlodbc.so
Setup = /usr/lib/libodbcpsqlS.so
Driver64 = /usr/pgsql-9.4/lib/psqlodbc.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
# Driver from the mysql-connector-odbc package
# Setup from the unixODBC package
[MySQL]
Description = ODBC for MySQL
Driver = /usr/lib/libmyodbc5.so
Setup = /usr/lib/libodbcmyS.so
Driver64 = /usr/lib64/libmyodbc5.so
Setup64 = /usr/lib64/libodbcmyS.so
FileUsage = 1
My /etc/odbc.ini:
Code:
#cat /etc/odbc.ini
[asterisk-cdr]
Description = MySQL connector for Asterisk
Driver = MySQL
Database = mymysqldb
Socket = /var/lib/mysql/mysql.sock
[pgdb-cdr]
Driver = PostgreSQL
ServerName = localhost
Port = 5432
UserName = mydbuser
Password = mydbpassword
Database = mydbname
ReadOnly = No
Protocol = 9.4
Trace = Yes
TraceFile = sql.log
The files referred to by odbcinst.ini exist and are accessible:
Code:
# ls -l /usr/lib64/libodbcpsqlS.so -l
lrwxrwxrwx 1 root root 21 Feb 18 13:07 /usr/lib64/libodbcpsqlS.so -> libodbcpsqlS.so.2.0.0
# ls -l /usr/pgsql-9.4/lib64/psqlodbc.so
lrwxrwxrwx 1 root root 12 Jul 14 15:41 /usr/pgsql-9.4/lib64/psqlodbc.so -> psqlodbcw.so
I have also created symlinks for psqlodbc.so and psqlodbcw.so in /usr/lib and /usr/lib64 that symlink back to /usr/pgsql-9.4/lib64.
My pg_hba.conf, located in /var/lib/pgsql/9.4/data :
Code:
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
Interestingly, when I had the last line like this
Code:
host all all ::1/128 trust
or
Code:
host all all ::1/128 ident
I could not even get in with psql - when I changed the last line to read (as above)
Code:
host all all ::1/128 md5
I could get in with psql - e. g IPV6?!?! Which I don't use / don't intend to use.
Anybody got any ideas? How can I get unixODBC (which works fine to access MySQL via ODBC / isql on my setup) to access postgres?
Might it have something to do with the fact that in pg_hba.conf I had to change the IPV6 line to get psql to work?
ANY help appreciated - been at this for almost a week now, no luck.
MySQL was a cinch to get ODBCed, but postgres 9.4 is proving completely impossible.
Thanks!
EDIT: I have now traced this over the past few days to the following problem.
If I use isql on my box to try and connect to postgress, the raw packet data that isql generates corrupts the database name. I tcpdumped traffic on port 5432 on the lo interface, and whenever I used the above isql commandline, the name of the target database instead of being "asteriskcdrdb" was various transmitted in the raw packet data as
&%@%!db
*&#!@#drdb
!@&&%$idb
instead of just plain
asteriskcdrdb
So postgres is behaving correctly, as I'm literally trying to login to non-existent databases and that is why it keeps replying
Code:
[28000][unixODBC]FATAL: password authentication failed for user "mydbuser"
[ISQL]ERROR: Could not SQLConnect
as there is no role and no database called
&%%@%!db
or
*&#!@#drdb
or
!@&&%$idb
there is only one
asteriskcdrdb
...
Also, I noticed when examining the pcap files with wireshark that when the native Postgres psql app connects, it uses IPV4, but if I use isql to try to connect it uses IPV6 - and that's when the corrupt database name is passed in every time.
So how can I prevent isql / unixODBC from fatally corrupting the database name when transmitting it for login to the running postgres instance over ODBC?
