Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client

From: "Stefan Viljoen" <viljoens(at)verishare(dot)co(dot)za>
To: <pgsql-odbc(at)postgresql(dot)org>
Subject: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
Date: 2015-07-17 08:01:22
Message-ID: 000b01d0c066$c58f3b80$50adb280$@verishare.co.za
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-odbc

Hi list

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:

---
# 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

---
# isql -v pgdb-cdr mydbuser mydbname mydbpassword
---

I get

---
[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:

---
# 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 Centos 6.5 package:

---
# 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):

---
# 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:

---
#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:

---
# 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 :

---
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 in pg_hba.conf:

---
host all all ::1/128 trust
---

or

---
host all all ::1/128 ident
---

I could not even get in with psql - when I changed the last line to read (as
above)

---
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!

Stefan

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message Stefan Viljoen 2015-07-17 11:00:05 Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
Previous Message dsteigne 2015-07-16 18:38:14 When will you be adding ISC_REQ_MUTUAL_AUTH to the ODBC dwSSPIFlags variable?