Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client - Mailing list pgsql-odbc
From | Stefan Viljoen |
---|---|
Subject | Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client |
Date | |
Msg-id | 003001d0c099$e21a7b60$a64f7220$@verishare.co.za Whole thread Raw |
In response to | Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting
localhost postgres instance with isql ODBC commandline client
|
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
pgsql-odbc by date:
Previous
From: Adrian KlaverDate:
Subject: Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
Next
From: Nick GorhamDate:
Subject: Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client