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

Поиск
Список
Период
Сортировка
От Stefan Viljoen
Тема Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
Дата
Msg-id 000b01d0c066$c58f3b80$50adb280$@verishare.co.za
обсуждение исходный текст
Список 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



В списке pgsql-odbc по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Manifest creation for psqlodbc*.dll
Следующее
От: "Stefan Viljoen"
Дата:
Сообщение: Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client