Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client - Mailing list pgsql-odbc

Hi Adrian

Thanks for replying!

>So what does your odbcinst.ini file look like?

Here is my odbcinst.ini file, as automatically created when installing the
postgresql-odbc package in Centos 6.5 via YUM:

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

>What happens if you temporarily change the authentication to trust in
pg_hba.conf and after reloading the Postgres server try to connect?

[root@jhbasterisk pg_log]# vim ../pg_hba.conf

Change to read like this:

---
local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
---

[root@jhbasterisk pg_log]# service postgresql-9.4 restart
Stopping postgresql-9.4 service:                           [  OK  ]
Starting postgresql-9.4 service:                           [  OK  ]
[root@jhbasterisk pg_log]# isql -vvvvvv pgdb-cdr dbuser password
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect

[root@jhbasterisk pg_log]# psql -U asteriskcdruser -d asteriskcdrdb
psql (9.4.4)
Type "help" for help.

asteriskcdrdb=>

So psql still works, and with isql the error remains exactly the same as
before when my pg_hba.con had md5 on all the lines.

> What does the Postgres log show when you connect?

Nothing... if I do a

psql -U asteriskcdruser -d asteriskcdrcdrdb

no new lines are added to my
/var/lib/pgsql/9.4/data/pg_log/postgresql-Fri.log

If I do a

isql -vvvvvv pgdb-cdr dbuser password and it fails as indicated above, no
new lines are added to my  /var/lib/pgsql/9.4/data/pg_log/postgresql-Fri.log
file.

The tail -5 of my  /var/lib/pgsql/9.4/data/pg_log/postgresql-Fri.log file
shows, after doing the above:

[root@jhbasterisk pg_log]# tail -5 postgresql-Fri.log
< 2015-07-17 15:51:24.253 SAST >LOG:  database system is shut down
< 2015-07-17 15:51:25.451 SAST >LOG:  database system was shut down at
2015-07-17 15:51:24 SAST
< 2015-07-17 15:51:25.626 SAST >LOG:  MultiXact member wraparound
protections are now enabled
< 2015-07-17 15:51:25.628 SAST >LOG:  autovacuum launcher started
< 2015-07-17 15:51:25.628 SAST >LOG:  database system is ready to accept
connections
[root@jhbasterisk pg_log]#

< 2015-07-17 15:51:25.628 SAST >LOG:  database system is ready to accept
connections

is the last line, and remains the last line if I try either isql or psql
commands as detailed above.

This seems to lend credence to my theory that the unixODBC drivers called by
isql do not in any way in fact contact Postgres via ODBC at all...? Postgres
literally does not know of any connection attempts, successful or otherwise,
from isql.

> What does the trace file sql.log show?

The trace file is not created at all. I changed the /etc/odbc.ini file to
have

---
Trace                   = Yes
TraceFile               = /tmp/sql.log
---

then restarted postgres:

[root@jhbasterisk pg_log]# service postgresql-9.4 restart
Stopping postgresql-9.4 service:                           [  OK  ]
Starting postgresql-9.4 service:                           [  OK  ]
[root@jhbasterisk pg_log]#

Then ran the isql and psql commands - isql still fails as above, psql still
works and allows me to access the database using the username and password
passed to isql.

The file /tmp/sql.log does not get created:

[root@jhbasterisk pg_log]# ls /tmp/sql.log -la
ls: cannot access /tmp/sql.log: No such file or directory
[root@jhbasterisk pg_log]#

Which kind of makes sense, since it appears there is a driver
incompatibility between the postgres 9.4 ODBC drivers and the Centos
unixODBC drivers?

[root@jhbasterisk pg_log]# yum list installed | grep ODBC
unixODBC.x86_64         2.2.14-14.el6   @base
unixODBC-devel.x86_64   2.2.14-14.el6   @base
unixODBC-kde.x86_64     2.2.14-14.el6   @base
[root@jhbasterisk pg_log]#

Thanks for the assistance - I'm beginning to think I might have to abandon
postgres (or at least 9.4) and either downgrade to an earlier postgres
version (IF that will make any difference) or use only MySQL as it is the
only one of the two I so far can get to accept ODBC connections from
unixODBC on Centos 6.5 at all...

Kind regards,

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Adrian Klaver
Sent: Friday,July 17, 2015 03:41 PM
To: Stefan Viljoen; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting
localhost postgres instance with isql ODBC commandline client

On 07/17/2015 06:02 AM, Stefan Viljoen wrote:
> Hi Nick
>
> Here is my odbc.ini entry for the pgdb-cdr data source:
>
> ---
> [pgdb-cdr]
> Driver                  = /usr/pgsql-9.4/lib/psqlodbc.so
> ServerName              = localhost
> Port                    = 5432
> UserName                = dbuser
> Password                = dbpassword
> Database                = dbname
> ReadOnly                = No
> Protocol                = 9.4
> Trace                   = No
> TraceFile               = sql.log
> ConnSettings       =
> ---
>
> I have tried to
>
> - Replace ServerName with 127.0.0.1 and ::1
> - Changing the protocol to 9.3, 9.2., 9.1
> - Making ReadOnly yes
> - Deliberately mis-spelling the Database name but the error remains
> the same
>
> - Omitting the password, error remains the same
> - Removing the port, error remains the same.
>
> The only thing I can conclude at this point is that 9.4 Postgres ODBC
> driver is in fact incompatible with the Centos 6.5 ODBC package version?
>
> As far as I can tell isql does not get any response from Postgres ODBC
> driver - given that I can corrupt the pgdb-cdr entry however I like,
> even severe errors or gaps in it is just ignored and the same error is
> returned each time.
>
> I have run strace on isql and I can see that it DOES parse the above
> file though - so I'm reasonable sure that the 9.4 Postgres driver is
> completely incompatible with the ODBC version supplied with Centos 6.5?

So what does your odbcinst.ini file look like?

What happens if you temporarily change the authentication to trust in
pg_hba.conf and after reloading the Postgres server try to connect?

What does the Postgres log show when you connect?

What does the trace file sql.log show?

>
> Again, THANK YOU for your assistance :)
>
> Kind regards,
>
>> It can only display the error the driver returns, so not more details.
>
>> What is in your odbc.ini for the pgdb-cr entry?
>
>> I would expect a
>
>> database=dbname
>
>> to direct the driver to the correct database.
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc