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