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

On 07/21/2015 02:23 AM, Stefan Viljoen wrote:
> Hi Jeremy
>
>> You probably want to set ServerName back to localhost in your odbc.ini
> file, I had you change it to /tmp when I thought psql was using a unix
> domain socket.
>
> Ok, I set it back to localhost, and tried again, same result - IPV6 is used,
> and the database name is randomly corrupted in the raw packet data if one
> tries to connect via isql.
>
>> It is strange that tcpdump is picking up this connection, if the postgres
> driver is using the ServerName parameter then there should NOT have been a
> TCP connection.
>
> Hmm - ok my tcpdump command was literally just to dump anything sent to /
> from the 5432 postgres port on 127.0.0.1 / localhost - and for each failed
> isql connection attempt over IPV6 exactly 13 packets are collected, and the
> database name is always corrupt (if I'm reading it right!)
>
>> Have you tried doing the tcpdump on a psql connection attempt for
> comparison?
>
> Yes, psql  emits more packets (28 for a login and logout), runs on IPV4,
> works correctly, and the database name is always correctly specified and not
> corrupted. Postgress accepts the connection.
>
> I find it interesting that the isql connection attempt fails with almost
> half the number of packets as the psql connection attempt (depending on ODBC
> protocol overhead?) indicating that isql fails about 1/2 way through the
> required amount of packets to send to establish a Postgres connection?
>
> I've got a feeling if I can somehow force isql to use IPV4 to postgress that
> will already be a big step in the right direction... but how does one do
> that?
>
> Thanks
>
> Regards
>
> Stefan
>
> -----Original Message-----
> From: Faith, Jeremy [mailto:jfaith@tycoint.com]
> Sent: Tuesday,July 21, 2015 11:08 AM
> 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
>
> Hi Stefan,
>
> You probably want to set ServerName back to localhost in your odbc.ini file,
> I had you change it to /tmp when I thought psql was using a unix domain
> socket.
>
> It is strange that tcpdump is picking up this connection, if the postgres
> driver is using the ServerName parameter then there should NOT have been a
> TCP connection.
>
> Have you tried doing the tcpdump on a psql connection attempt for
> comparison?
>
> Regards,
> Jeremy Faith
> ________________________________________
> From: pgsql-odbc-owner@postgresql.org [pgsql-odbc-owner@postgresql.org] on
> behalf of Stefan Viljoen [viljoens@verishare.co.za]
> Sent: 21 July 2015 09:25
> To: 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
>
> Hi List
>
> The plot thickens - I have now resorted to doing a tcpdump of the postgres
> port and then trying to connect via isql.
>
> The tcpdump command used was
>
> ---
> tcpdump -vvvvv -x -X -s 65535 -i lo 'port 5432' -w post.pcap
> ---
>
> This has revealed that in the post.pcap file (even just viewing the raw
> packet data with vi in the terminal) that the REASON postgress keeps
> rejecting isql login attempts is that isql apparently passes the database
> name only partially, or corrupts it randomly...
>
> E. g. I tried this five or six times and each time, the packets sent from
> unixODBC / isql binary, specifies the string
>
> asteriskcdrdb
>
> which denotes the database name to postgress (and therefore is critical to
> control login) as
>
> &%%@%!db
> *&#!@#drdb
> !@&&%$idb
>
> etc. and sometimes mixed / along with some other ASCII crud I cannot
> duplicate here.
>
> SSL is most definitely off so this cannot be encryption?
>
> The STRANGE thing is I can spot the username (asteriskcdruser) in the packet
> data and that is ALWAYS correct and uncorrupted, and is always passed as
> "asteriskcdruser".
>
> But the database name is -always- corrupted that is sent to port 5432 from
> isql to login to postgress.
>
> So if I do with isql
>
> ---
> isql -v pgdb-cdr asteriskcdruser pword
> ---
>
> In effect I try to login to postgress with
>
> *&#!@#drdb
>
> (as indicated by the packet data)  which is of course a database that does
> not exist, nor is there a role that matches "asteriskcdruser" for such a
> database.
>
>From there the error
>
> [S1000][unixODBC]The database does not exist on the server or user
> authentication failed.
> [ISQL]ERROR: Could not SQLConnect
>
> which is completely logical as I'm trying to log into non-existent databases
> as passed over ODBC from isql, no?
>
> Any thoughts? How can this be fixed?
>
> ODBC connections on the same box over the lo interface to MySQL keep working
> 100% - wonder why unixODBC is corrupting the database name, but ONLY when
> passing it to Psql-odbc?
>
> Or am I completely barking up the wrong tree?

Not sure, but here is what I see.

First on my machine openSUSE 13.1

unixODBC 2.3.1-3.1.2

psqlodbc 09.03.0400

odbcinst.ini

[Postgres]
Description             =
Driver          = /usr/local/lib/psqlodbcw.so
Driver64                = /usr/lib
Setup           = /usr/lib/unixODBC/libodbcdrvcfg1S.so
Setup64         = /usr/lib
UsageCount              = 1
CPTimeout               =
CPReuse         =

[ODBC]
Trace           = No
TraceFile               = /tmp/sql.log
ForceTrace              = No
Pooling         = No


odbc.ini

[Production]
Description             = Postgres
Driver          = Postgres
Host            = localhost
Database                = production
Port            =

aklaver@panda:~> isql -v Production
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Where Production is pointing at a 9.4 database.

I know we are not talking apples to apples, but at least it proves it is
possible.

Now my observations.

1) In my odbcinst.ini the Driver being pointed to is psqlodbcw.so
instead of psqlodbc.so in your odbcinst.ini. This leads to

2) In your original post you have:

# 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

Yet the Postgres yum repo:

http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/

has

postgresql94-odbc-09.03.0300-1PGDG.rhel6.x86_64.rpm

So is it possible you have a version mismatch in the Postgres ODBC
driver and the Postgres server?

I may have missed it, but do you know what version of psqlodbc you are
using or more to the point where is postgresql94-odbc.x86_64 is coming from?



>
> Thanks
>
> Stefan
>



--
Adrian Klaver
adrian.klaver@aklaver.com