Thread: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
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
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Nick
Thanks for the response - ok, I tried
[root@jhbasterisk ~]# isql -v pgdb-cdr dbuser dbname password
[28000][unixODBC]FATAL: password authentication failed for user
"asteriskcdruser"
[ISQL]ERROR: Could not SQLConnect
[root@jhbasterisk ~]# isql pgdb-cdr dbuser password
[ISQL]ERROR: Could not SQLConnect
[root@jhbasterisk ~]#
E. g. the 28000 error message is gone, but now I just get Could Not
SQLConnect from isql...
So maybe a step in the right direction?
What can I try next?
Thank you very much for the assistance.
Kind regards,
-----Original Message-----
From: Nick Gorham [mailto:nick@lurcher.org]
Sent: Friday,July 17, 2015 10:38 AM
To: Stefan Viljoen
Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting
localhost postgres instance with isql ODBC commandline client
On 17/07/15 09:01, Stefan Viljoen wrote:
> 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
Hi,
From the unixODBC perspective, isql only takes three arguments (ignoring
options)
isql dsn user password
If you want to set the dbname it would normally be done in the DSN entry.
--
Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Nick,
Ok, I added -v back, this gives
[root@jhbasterisk ~]# isql -v 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 ~]#
If I do
[root@jhbasterisk ~]# psql -U dbuser -d dbname
all works fine:
[root@jhbasterisk ~]# psql -U dbuser -d dbname
Password for user asteriskcdruser:
psql (9.4.4)
Type "help" for help.
dbname=> \d+
;
List of relations
Schema | Name | Type | Owner | Size |
Description
--------+----------------+----------+-----------------+------------+--------
-----
public | cdr | table | dbuser | 8192 bytes |
public | cdr_id_seq | sequence | dbuser | 8192 bytes |
public | cdr_leg | table | dbuser | 8192 bytes |
public | cdr_leg_id_seq | sequence | dbuser | 8192 bytes |
public | cel | table | dbuser | 8192 bytes |
public | cel_id_seq | sequence | dbusr | 8192 bytes |
(6 rows)
dbname=> ;
dbname=>
So Postgres itself does seem to know of the user and the database... but
accessing them via ODBC goes south somehow.
Is there any way I can raise the verbosity level (for example)? I've tried
isql -vvvvvv but I still just get "Database soed not exist on the server or
user authentication failed".
Thank you very much for your kind assistance.
Regards,
-----Original Message-----
From: Nick Gorham [mailto:nick@lurcher.org]
Sent: Friday,July 17, 2015 01:02 PM
To: Stefan Viljoen
Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting
localhost postgres instance with isql ODBC commandline client
On 17/07/15 12:00, Stefan Viljoen wrote:
> Hi Nick
>
> Thanks for the response - ok, I tried
>
> [root@jhbasterisk ~]# isql -v pgdb-cdr dbuser dbname password
> [28000][unixODBC]FATAL: password authentication failed for user
> "asteriskcdruser"
> [ISQL]ERROR: Could not SQLConnect
> [root@jhbasterisk ~]# isql pgdb-cdr dbuser password
> [ISQL]ERROR: Could not SQLConnect
> [root@jhbasterisk ~]#
>
> E. g. the 28000 error message is gone, but now I just get Could Not
> SQLConnect from isql...
>
> So maybe a step in the right direction?
>
> What can I try next?
>
> Thank you very much for the assistance.
>
> Kind regards,
The error has gone because you didn't have the -v option.
My point was you cant specify the database on the command line with isql
isql -v pgdb-cdr dbuser password
Will show you the error.
--
Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
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? 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.
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Adrian Klaver
Date:
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
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
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
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Nick Gorham
Date:
On 17/07/15 14:02, 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? > > Again, THANK YOU for your assistance :) > > Kind regards, Another thing to check is that the driver is reading the same ini file as the driver manager. What error do you get from isql -v pgdb-cdr dbuser password It may be the driver (the folks on this list will know better that I do) is reading the ini file itself, so it may be lookingelsewhere (instead of using the libodbcinst.so helper lib). Try setting export ODBCINI=/path/to/odbc.ini That may help. also try removing the white space before and after the = [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= -- Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Nick Gorham
Date:
On 17/07/15 15:07, Stefan Viljoen wrote: > 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 Getting confused now. The above leads me to believe you should have Database = asteriskcdrcdrdb in the odbc.ini entry and connect as isql -v pgdb-cdr asteriskcdruser whateverthepasswordis I was assuming you were replacing dbuser, dbname and password with the real ones for your situation. -- Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi again Nick >Another thing to check is that the driver is reading the same ini file as the driver manager. >What error do you get from >isql -v pgdb-cdr dbuser password I get --- [root@jhbasterisk pg_log]# isql -v 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]# --- >It may be the driver (the folks on this list will know better that I do) is reading the ini file itself, so it may be looking elsewhere (instead of using the libodbcinst.so helper lib). >Try setting >export ODBCINI=/path/to/odbc.ini Ok, I tried that --- [root@jhbasterisk pg_log]# export ODBCINI=/etc/odbc.ini [root@jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc# [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# set | grep ODBCINI ODBCINI=/etc/odbc.ini [root@jhbasterisk pg_log]# --- so I get the same error , and ODBCINI is set in the environment after exporting the symbol ODBCINI - odbc.ini IS in /etc: --- [root@jhbasterisk pg_log]# ls /etc/odbc.ini -l -rw-r--r-- 1 root root 646 Jul 17 16:00 /etc/odbc.ini [root@jhbasterisk pg_log]# --- >That may help. also try removing the white space before and after the = Ok, I removed whitespace in the postgress - relevant lines in odbcinst.ini and odbc.ini - the now look like this: /etc/odbcinst.ini: --- # Example driver definitions # Driver from the postgresql-odbc package # Setup from the unixODBC package [PostgreSQL] Description=ODBC for PostgreSQL ;Driver = /usr/lib/psqlodbc.so Driver=/usr/pgsql-9.4/lib/psqlodbc.so Setup=/usr/lib/libodbcpsqlS.so ;Driver64 = /usr/lib64/psqlodbc.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 --- /etc/odbc.ini: --- [ODBC Data Sources] pgdb=PostgreSQL [asterisk-cdr] Description=MySQL connector for Asterisk Driver=MySQL Database=asteriskcdrdb Socket=/var/lib/mysql/mysql.sock [pgdb-cdr] ;Driver = /usr/pgsql-9.4/lib/psqlodbc.so Driver=PostgreSQL ServerName=localhost Port=5432 UserName=asteriskcdruser Password=1ndestruc# Database=asteriskcdrdb ReadOnly=No Protocol=9.4 Trace=Yes TraceFile=/tmp/sql.log ConnSettings= DSN=pgdb ServerType=Postgres Debug=1 DebugFile=/var/log/postgresql_debug.log --- The error remains the same, after restarting postgress (which should not have any effect nonetheless): [root@jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc# [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# psql still works: [root@jhbasterisk pg_log]# psql -U asteriskcdruser -d asteriskcdrdb psql (9.4.4) Type "help" for help. asteriskcdrdb=> but that is because it does not try to use ODBC. Thanks a lot for the help. Kind regards Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Nick >Getting confused now. The above leads me to believe you should have >Database = asteriskcdrcdrdb >in the odbc.ini entry and connect as >isql -v pgdb-cdr asteriskcdruser whateverthepasswordis >I was assuming you were replacing dbuser, dbname and password with the real ones for your situation. That is a typeo I made. You are correct, I'm replacing with the real ones - made a typo. My odbc.ini actually is -- [root@jhbasterisk pg_log]# vim /etc/odbc.ini [ODBC Data Sources] pgdb=PostgreSQL [asterisk-cdr] Description=MySQL connector for Asterisk Driver=MySQL Database=asteriskcdrdb Socket=/var/lib/mysql/mysql.sock [pgdb-cdr] ;Driver = /usr/pgsql-9.4/lib/psqlodbc.so Driver=PostgreSQL ServerName=localhost Port=5432 UserName=dbuser Password=dbpassword Database=dbname ReadOnly=No Protocol=9.4 Trace=Yes TraceFile=/tmp/sql.log ConnSettings= DSN=pgdb ServerType=Postgres Debug=1 DebugFile=/var/log/postgresql_debug.log -- e. g. I have now checked that the symantic dbname is NOT dbnamedname If I do [root@jhbasterisk pg_log]# isql -v pgdb-cdr dbuser dbpassword [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# The interesting thing is if I change Database=dbname to symantic Database=dnam (e. g. leave out the last char) isql does this: [root@jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc# [08S01][unixODBC]Could not send Query(connection dead) [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# So, my theory that unixODBC is not trying to connect or get far enough to even touch Postgres is incorrect... therefore is DOES appear that it IS postgress that is rejecting the ODBC connections? Kind regards Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Nick Gorham
Date:
On 17/07/15 15:48, Stefan Viljoen wrote:
> Hi Nick
>
>> Getting confused now. The above leads me to believe you should have
>> Database = asteriskcdrcdrdb
>> in the odbc.ini entry and connect as
>> isql -v pgdb-cdr asteriskcdruser whateverthepasswordis
>> I was assuming you were replacing dbuser, dbname and password with the real
> ones for your situation.
>
> That is a typeo I made. You are correct, I'm replacing with the real ones -
> made a typo.
>
> My odbc.ini actually is
>
> --
> [root@jhbasterisk pg_log]# vim /etc/odbc.ini
> [ODBC Data Sources]
> pgdb=PostgreSQL
>
> [asterisk-cdr]
> Description=MySQL connector for Asterisk
> Driver=MySQL
> Database=asteriskcdrdb
> Socket=/var/lib/mysql/mysql.sock
>
> [pgdb-cdr]
> ;Driver = /usr/pgsql-9.4/lib/psqlodbc.so
> Driver=PostgreSQL
> ServerName=localhost
> Port=5432
> UserName=dbuser
> Password=dbpassword
> Database=dbname
> ReadOnly=No
> Protocol=9.4
> Trace=Yes
> TraceFile=/tmp/sql.log
> ConnSettings=
> DSN=pgdb
> ServerType=Postgres
> Debug=1
> DebugFile=/var/log/postgresql_debug.log
> --
>
> e. g. I have now checked that the symantic
>
> dbname
>
> is NOT
>
> dbnamedname
>
> If I do
>
> [root@jhbasterisk pg_log]# isql -v pgdb-cdr dbuser dbpassword
> [S1000][unixODBC]The database does not exist on the server
> or user authentication failed.
> [ISQL]ERROR: Could not SQLConnect
> [root@jhbasterisk pg_log]#
>
> The interesting thing is if I change
>
> Database=dbname
>
> to symantic
>
> Database=dnam
>
> (e. g. leave out the last char)
>
> isql does this:
>
> [root@jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc#
> [08S01][unixODBC]Could not send Query(connection dead)
> [ISQL]ERROR: Could not SQLConnect
> [root@jhbasterisk pg_log]#
>
> So, my theory that unixODBC is not trying to connect or get far enough to
> even touch Postgres is incorrect... therefore is DOES appear that it IS
> postgress that is rejecting the ODBC connections?
>
> Kind regards
>
> Stefan
>
Its getting past the driver manager to the driver in all cases, those
errors are from postgres driver or server.
So when you do
psql -U asteriskcdruser -d asteriskcdrdb
That does connect you are connecting to a different database, dont you need to change the odbc.ini to have
Database=asteriskcdrdb
instead of the
Database=dbname
That you have shown to be in the ini file.
> [pgdb-cdr]
> ;Driver = /usr/pgsql-9.4/lib/psqlodbc.so
> Driver=PostgreSQL
> ServerName=localhost
> Port=5432
> UserName=dbuser
> Password=dbpassword
> Database=dbname
> ReadOnly=No
> Protocol=9.4
> Trace=Yes
> TraceFile=/tmp/sql.log
> ConnSettings=
> DSN=pgdb
> ServerType=Postgres
> Debug=1
> DebugFile=/var/log/postgresql_debug.log
>
--
Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Faith, Jeremy"
Date:
Hi Stefan, Is your PGHOST environment variable set? If not psql will be using /tmp by default(i.e. unix domain socket which is the 'local' line in the pg_hba.conf file), try psql -h localhost ... If this does NOT work it explains why your odbc.ini settings are not working, you could then try changing the odbc.ini lineto:- Servername = /tmp Regards, Jeremy Tyco Safety Products/CEM Systems Ltd. ________________________________ This e-mail contains privileged and confidential information intended for the use of the addressees named above. If you arenot the intended recipient of this e-mail, you are hereby notified that you must not disseminate, copy or take any actionin respect of any information contained in it. If you have received this e-mail in error, please notify the senderimmediately by e-mail and immediately destroy this e-mail and its attachments.
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Nick
>Its getting past the driver manager to the driver in all cases, those
errors are from postgres driver or server.
Ok
>So when you do
psql -U asteriskcdruser -d asteriskcdrdb
>That does connect you are connecting to a different database, dont you need
to change the odbc.ini to have
> Database=asteriskcdrdb
>instead of the
> Database=dbname
>That you have shown to be in the ini file.
> [pgdb-cdr]
> ;Driver = /usr/pgsql-9.4/lib/psqlodbc.so
> Driver=PostgreSQL
> ServerName=localhost
> Port=5432
> UserName=dbuser
> Password=dbpassword
> Database=dbname
> ReadOnly=No
> Protocol=9.4
> Trace=Yes
> TraceFile=/tmp/sql.log
> ConnSettings=
> DSN=pgdb
> ServerType=Postgres
> Debug=1
> DebugFile=/var/log/postgresql_debug.log
Ok I changed by odbc.ini to read
---
[pgdb-cdr]
;Driver = /usr/pgsql-9.4/lib/psqlodbc.so
Driver=PostgreSQL
ServerName=localhost
Port=5432
UserName=username
Password=pword
Database=asteriskcdrdb
ReadOnly=No
Protocol=9.4
Trace=Yes
TraceFile=/tmp/sql.log
ConnSettings=
DSN=pgdb
ServerType=Postgres
Debug=1
DebugFile=/var/log/postgresql_debug.log
---
PostgreSQL is defined in my odbcinst.conf
The response still remains
---
[root@jhbasterisk pg_log]# isql -v pgdb-cdr username pword
[S1000][unixODBC]The database does not exist on the server
or user authentication failed.
[ISQL]ERROR: Could not SQLConnect
[root@jhbasterisk pg_log]#
---
If i check using psql:
---
[root@jhbasterisk pg_log]# psql -U username -d asteriskcdrdb
psql (9.4.4)
Type "help" for help.
asteriskcdrdb=> \d+
List of relations
Schema | Name | Type | Owner | Size |
Description
--------+----------------+----------+-----------------+------------+--------
-----
public | cdr | table | asteriskcdruser | 8192 bytes |
public | cdr_id_seq | sequence | asteriskcdruser | 8192 bytes |
public | cdr_leg | table | asteriskcdruser | 8192 bytes |
public | cdr_leg_id_seq | sequence | asteriskcdruser | 8192 bytes |
public | cel | table | asteriskcdruser | 8192 bytes |
public | cel_id_seq | sequence | asteriskcdruser | 8192 bytes |
(6 rows)
asteriskcdrdb=> \list
List of databases
Name | Owner | Encoding | Collate | Ctype |
Access privileges
---------------+----------+----------+-------------+-------------+----------
--------------------
asteriskcdrdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=Tc/postgres +
| | | | |
postgres=CTc/postgres +
| | | | |
asteriskcdruser=CTc/postgres
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
(4 rows)
asteriskcdrdb=>
---
Tables cdr_leg, cdr and cel are in asteriskcdrdb.
Thanks for your continued assistance!
Kind regards,
--
Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Jeremy Thanks for replying. >Is your PGHOST environment variable set? My PGHOST environment variable is NOT set. Should I set it to 127.0.0.1 or to localhost? >If not psql will be using /tmp by default(i.e. unix domain socket which is the 'local' line in the pg_hba.conf file), try >psql -h localhost ... Ok, I used this commandline: --- psql -h localhost -U username -d asteriskcdrdb --- and it works 100% just like --- psql -U username -d asteriskcdrdb --- works fine. >If this does NOT work it explains why your odbc.ini settings are not working, you could then try changing the odbc.ini line to:- >Servername = /tmp Ok, I changed the servername line in my odbc.ini to the above, result from isql stays the same: --- [root@jhbasterisk pg_log]# isql -v pgdb-cdr username pword [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# --- Is there any way that traffic on localhost can be captured or somehow a way in which verbosity can be raised? It seems that the unixODBC version that ships with Centos 6.5 is just incompatible with the ODBC driver that ships with Postgres 9.4.4... should I consider a downgrade of Postgres? Thank you, Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi again Jeremy >>Is your PGHOST environment variable set? >My PGHOST environment variable is NOT set. >Should I set it to 127.0.0.1 or to localhost? I have tried with both PGHOST set to 127.0.0.1 and set to localhost still getting the same error with isql: --- [root@jhbasterisk pg_log]# export PGHOST='127.0.0.1' [root@jhbasterisk pg_log]# set | grep PGHOST PGHOST=127.0.0.1 _=PGHOST [root@jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser pword [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# export PGHOST='localhost' [root@jhbasterisk pg_log]# set | grep PGHOST PGHOST=localhost _=PGHOST [root@jhbasterisk pg_log]# isql -v pgdb-cdr asteriskcdruser 1ndestruc# [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk pg_log]# --- psql itself is still working fine... Is there any way I can get more resolution on "the database does not exist on the server OR user authentication failed" - e. g. can I narrow it down somehow to one OR the other? Or should I just give up and can 9.4 and start trying 9.3, then 9.2, then 9.1 etc. until I get a Postgres version that can be connected from the version of ODBC shipped with Centos 6.5? I really wanted to try postgres 9.4 as I have postgres 8.4 running happily on another box with Centos 6.5 ODBC working 100% fine, but 8.4 is a bit long in the tooth and I read that 9.4 has significant speed improvements. Anybody else reading this - ever yet got Postgres 9.4 ODBC working with the stock Centos 6.5 unixODBC drivers?
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Faith, Jeremy"
Date:
Hi Stefan, I had thought perhaps psql was using unix domain socket(/tmp) and not localhost but as psql -h localhost ... works for you that is NOT the problem. Just one last thing to check if you run grep localhost /etc/hosts what do you get. In particular is the 127.0.0.1 line(IPv4) before the ::1 line(ipv6). Regards, Jeremy Faith Tyco Safety Products/CEM Systems Ltd. ________________________________ This e-mail contains privileged and confidential information intended for the use of the addressees named above. If you arenot the intended recipient of this e-mail, you are hereby notified that you must not disseminate, copy or take any actionin respect of any information contained in it. If you have received this e-mail in error, please notify the senderimmediately by e-mail and immediately destroy this e-mail and its attachments.
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Jeremy >I had thought perhaps psql was using unix domain socket(/tmp) and not localhost but as psql -h localhost ... >works for you that is NOT the problem. >Just one last thing to check if you run >grep localhost /etc/hosts >what do you get. In particular is the 127.0.0.1 line(IPv4) before the ::1 line(ipv6). This is the output: --- [root@jhbasterisk ~]# grep localhost /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 [root@jhbasterisk ~]# --- One thing I did notice though. When I was initially setting up postgress, the last line of my pg_hba.conf was --- host all all ::1/128 trust --- And I could not get access with psql to the running postgres instance. Changing the last line to --- host all all ::1/128 ident --- also caused that I could not get access with psql to the running postgres instance (restarting postgres after changing pg_hba.conf each time, of course). Only when I put --- host all all ::1/128 md5 --- as the last line of pg_hba.conf and restarted postgres, could I get access with psql to the running postgres instance. This seems to indicate that I'm running postgres on IPV6 (unintentionally) instead of IPV4? I just want / need IPV4 functionality, IPV6 is unneccessary. HOWEVER Can this be why unixODBC is not working? E. g. it is string to connect to postgres but it is ignoring IPV4 requests? My current pg_hba.conf does allow IPV4: --- local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust --- I can drop the first two lines and nothing changes - psql still works and isql still does NOT work - but drop the very last line and psql AND isql don't work. Ergo... IPV6 is being used by postgres and IPV4 traffic is completely ignored? I have not tried to connect to the box in question from any host besides local. Thanks for the assistance. Kind regards Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Faith, Jeremy"
Date:
Hi Stefan, It does seem that psql is connecting on IPv6. I think your /etc/hosts file is fine but just to be certain you could try psql -h 127.0.0.1 ... if this works it is probably NOT the problem. Regards, Jeremy Faith ________________________________________ From: pgsql-odbc-owner@postgresql.org [pgsql-odbc-owner@postgresql.org] on behalf of Stefan Viljoen [viljoens@verishare.co.za] Sent: 20 July 2015 11:59 To: pgsql-odbc@postgresql.org Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandlineclient Hi Jeremy >I had thought perhaps psql was using unix domain socket(/tmp) and not localhost but as psql -h localhost ... >works for you that is NOT the problem. >Just one last thing to check if you run >grep localhost /etc/hosts >what do you get. In particular is the 127.0.0.1 line(IPv4) before the ::1 line(ipv6). This is the output: --- [root@jhbasterisk ~]# grep localhost /etc/hosts 127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 ::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 [root@jhbasterisk ~]# --- One thing I did notice though. When I was initially setting up postgress, the last line of my pg_hba.conf was --- host all all ::1/128 trust --- And I could not get access with psql to the running postgres instance. Changing the last line to --- host all all ::1/128 ident --- also caused that I could not get access with psql to the running postgres instance (restarting postgres after changing pg_hba.conf each time, of course). Only when I put --- host all all ::1/128 md5 --- as the last line of pg_hba.conf and restarted postgres, could I get access with psql to the running postgres instance. This seems to indicate that I'm running postgres on IPV6 (unintentionally) instead of IPV4? I just want / need IPV4 functionality, IPV6 is unneccessary. HOWEVER Can this be why unixODBC is not working? E. g. it is string to connect to postgres but it is ignoring IPV4 requests? My current pg_hba.conf does allow IPV4: --- local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust --- I can drop the first two lines and nothing changes - psql still works and isql still does NOT work - but drop the very last line and psql AND isql don't work. Ergo... IPV6 is being used by postgres and IPV4 traffic is completely ignored? I have not tried to connect to the box in question from any host besides local. Thanks for the assistance. Kind regards Stefan -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc Tyco Safety Products/CEM Systems Ltd. ________________________________ This e-mail contains privileged and confidential information intended for the use of the addressees named above. If you arenot the intended recipient of this e-mail, you are hereby notified that you must not disseminate, copy or take any actionin respect of any information contained in it. If you have received this e-mail in error, please notify the senderimmediately by e-mail and immediately destroy this e-mail and its attachments.
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Jeremy >I think your /etc/hosts file is fine but just to be certain you could try > psql -h 127.0.0.1 ... >if this works it is probably NOT the problem. Ok, tried this - psql -works-? --- [root@jhbasterisk ~]# psql -h 127.0.0.1 -U asteriskcdruser -d asteriskcdrdb psql (9.4.4) Type "help" for help. asteriskcdrdb=> --- So that blows my theory out of the water - postgres clearly is listening via IPV4 on 127.0.0.1 and accepts the connection (my hba file currently has "trust" on all lines, so no password is requested.) Putting all the lines in the hba.conf to "md5" asks for a password for the above step, but STILL works, and isql still fails with the exact same error it constantly fails with. All I can surmise is that the unixODBC drivers are incapable due to some protocol difference or mismatch in connecting to the pgsql driver supplied with Postgres 9.4 as there apparently is no error in my configuration or installation of either. BTW MariaDB 10.0.20 ver 15.1 / MySQL works perfectly via unixODBC on the same physical box I'm finding it impossible to get postgres 9.4 ODBC access working on. So I'm reasonably sure the unixODBC infrastructure IS installed correctly and is working... Last resort is probably to start tcpdump'ing and try and ferret out just what isql is "saying" that Postgres 9.4's ODBC drivers don't "understand"? But that is going to take such an enormous amount of effort it might be better to just downgrade pg and if that fails abandon postgres entirely and try to use MySQL (which is slower and IMO much more limited). In an ideal situation I'd install Centos 7 which has newer unixODBC drivers (I suppose) but I have to get this working on a deployed machine that can't just be sunk for several hours. Thanks, Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Nick Gorham
Date:
On 20/07/15 13:07, Stefan Viljoen wrote: > Hi Jeremy > >> I think your /etc/hosts file is fine but just to be certain you could try >> psql -h 127.0.0.1 ... >> if this works it is probably NOT the problem. > Ok, tried this - psql -works-? > > --- > [root@jhbasterisk ~]# psql -h 127.0.0.1 -U asteriskcdruser -d asteriskcdrdb > psql (9.4.4) > Type "help" for help. > > asteriskcdrdb=> > --- > > So that blows my theory out of the water - postgres clearly is listening via > IPV4 on 127.0.0.1 and accepts the connection (my hba file currently has > "trust" on all lines, so no password is requested.) > > Putting all the lines in the hba.conf to "md5" asks for a password for the > above step, but STILL works, and isql still fails with the exact same error > it constantly fails with. > > All I can surmise is that the unixODBC drivers are incapable due to some > protocol difference or mismatch in connecting to the pgsql driver supplied > with Postgres 9.4 as there apparently is no error in my configuration or > installation of either. Can I just check, when you say "unixODBC drivers" you don't mean the age old version of the postgres driver that used to be part of the default unixODBC build? If it is, don't touch that, its only there as a example. -- Nick Gorham
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Nick >Can I just check, when you say "unixODBC drivers" you don't mean the age old version of the postgres driver that used to be part of the default unixODBC build? If it is, don't touch that, its only there as a example. Unsure. Here's what I did to install unixODBC and postgres 9.4 - I installed unixODBC via yum on Centos 6.5 and updated it via yum - I edited /etc/yum.repos.d/CentOSBase.repo and in the [base] section added exlucde=postgresql* - I edited /etc/yum.repos.d/CentOSBase.repo and in the [updates] section added exlucde=postgresql* - I added a file pdgd-94-centos.repo which contains: --- [pgdg94] name=PostgreSQL 9.4 $releasever - $basearch baseurl=http://yum.postgresql.org/9.4/redhat/rhel-$releasever-$basearch enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94 [pgdg94-source] name=PostgreSQL 9.4 $releasever - $basearch - Source failovermethod=priority baseurl=http://yum.postgresql.org/srpms/9.4/redhat/rhel-$releasever-$basearc h enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94 --- - Afer doing that I installed the following postgres packages from the postgres repo added above: --- 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 then configured pg_hba.conf and started the postgress instance via service postgresql-9.4 start chkconfig postgresql-9.4 on - I set up odbc.ini as detailed before: --- [pgdb-cdr] Driver = /usr/pgsql-9.4/lib/psqlodbc.so ;Driver=PostgreSQL ServerName=/tmp Port=5432 UserName=asteriskcdruser Password=1ndestruc# Database=asteriskcdrdb ReadOnly=No Protocol=9.4 Trace=Yes TraceFile=/tmp/sql.log ConnSettings= DSN=pgdb ServerType=Postgres Debug=1 DebugFile=/var/log/postgresql_debug.log --- in which I explicitly select the driver in the above location, not another driver somewhere else? isql fails to connect to postgres with --- isql -v pgdb-cdr dbuser dbpass [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect [root@jhbasterisk yum.repos.d]# --- while psql itself works fine: --- #psql -h 127.0.0.1 -U asteriskcdruser -d asteriskcdrdb psql (9.4.4) Type "help" for help. asteriskcdrdb=> \list List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges ---------------+----------+----------+-------------+-------------+---------- -------------------- asteriskcdrdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres + | | | | | asteriskcdruser=CTc/postgres postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) asteriskcdrdb=> --- Does this nonetheless mean I'm using the incorrect driver and that is what is causing the unixODBC utlity isql to no be able to connect to postgres? Thanks Kind regards Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Nick Gorham
Date:
On 20/07/15 13:57, Stefan Viljoen wrote: > Hi Nick > >> Can I just check, when you say "unixODBC drivers" you don't mean the age > old version of the postgres driver that used to be part of the default > unixODBC build? If it is, don't touch that, its only there as a example. > > Unsure. > > Here's what I did to install unixODBC and postgres 9.4 Thats fine (I assume), its not the sample in unixODBC. -- Nick
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Joe Whoops, type in my message. I just rechecked the .repo file, they do read exclude Thanks for replying. Regards Stefan -----Original Message----- From: Joe Gilmour [mailto:joe@gilmours.com.au] Sent: Monday,July 20, 2015 03:03 PM To: Stefan Viljoen Subject: Re: [ODBC] Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client On 20/07/15 22:57, Stefan Viljoen wrote: > - I installed unixODBC via yum on Centos 6.5 and updated it via yum > > - I edited /etc/yum.repos.d/CentOSBase.repo and in the [base] section > added > exlucde=postgresql* > > - I edited /etc/yum.repos.d/CentOSBase.repo and in the [updates] > section added exlucde=postgresql* > > - I added a file pdgd-94-centos.repo which contains: > Haven't followed this one too much but if the lines above are as they are in your repos files they should read exclude not exlucde. Hope this helps. Joe
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi List The problems continue - I've now raised the debug logging values to maximum on the 9.4 instance here that I cannot connect to via unixODBC in Centos 6.5. This is what the log looks like if I run --- isql -v pgdb-cdr dbuser dbpass --- : --- < 2015-07-21 09:05:11.187 SAST >LOG: 00000: connection received: host=[local] < 2015-07-21 09:05:11.187 SAST >LOCATION: BackendInitialize, postmaster.c:4007 < 2015-07-21 09:05:11.188 SAST >LOG: 00000: connection authorized: user=dbuser database=dbpass < 2015-07-21 09:05:11.188 SAST >LOCATION: PerformAuthentication, postinit.c:258 < 2015-07-21 09:05:11.193 SAST >LOG: 08006: could not send data to client: Broken pipe < 2015-07-21 09:05:11.193 SAST >LOCATION: internal_flush, pqcomm.c:1368 < 2015-07-21 09:05:11.193 SAST >FATAL: 08006: connection to client lost < 2015-07-21 09:05:11.193 SAST >LOCATION: ProcessInterrupts, postgres.c:2885 < 2015-07-21 09:05:11.193 SAST >LOG: 00000: disconnection: session time: 0:00:00.006 user=dbuser database=dbpass host=[local] < 2015-07-21 09:05:11.193 SAST >LOCATION: log_disconnections, postgres.c:4511 --- This generates on the commandline --- # isql -v pgdb-cdr dbuser dbpass [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect --- so the real problem is a broken UNIX pipe when isql is trying to connect to Postgres. Any idea what I can do with this new information in order to solve the ODBC connection problem from isql? Thanks Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi List With the new increased verbosity I managed to turn on, I've found something interesting. This was my definition in /etc/odbc.ini: --- [ODBC Data Sources] pgdb=PostgreSQL [pgdb-cdr] Driver = /usr/pgsql-9.4/lib/psqlodbc.so ServerName=/tmp Port=5432 UserName=asteriskcdruser Password=1ndestruc# Database=asteriskcdrdb ReadOnly=No Protocol=9.4 Trace=Yes TraceFile=/tmp/odbc.log ForceTrace=Yes ConnSettings= DSN=pgdb ServerType=Postgres Debug=1 DebugFile=/var/log/postgresql_debug.log --- running --- isql -v pgdb-cdr dbuser pass --- gives in pg_log/postgresql-Tue.log the relevant error --- < 2015-07-21 09:04:35.850 SAST >LOCATION: PerformAuthentication, postinit.c:258 < 2015-07-21 09:04:35.856 SAST >LOG: 08006: could not send data to client: Broken pipe --- However, removing these two lines from /etc/odbc.ini --- DSN=pgdb ServerType=Postgres --- changes the logged output to this --- < 2015-07-21 09:21:24.018 SAST >LOG: 00000: connection received: host=[local] < 2015-07-21 09:21:24.018 SAST >LOCATION: BackendInitialize, postmaster.c:4007 < 2015-07-21 09:21:24.019 SAST >LOG: 00000: connection authorized: user=asteriskcdruser database=asteriskcdrdb < 2015-07-21 09:21:24.019 SAST >LOCATION: PerformAuthentication, postinit.c:258 < 2015-07-21 09:21:24.022 SAST >LOG: 00000: disconnection: session time: 0:00:00.004 user=asteriskcdruser database=asteriskcdrdb host=[local] < 2015-07-21 09:21:24.022 SAST >LOCATION: log_disconnections, postgres.c:4511 --- e. g. now the "broken pipe" error is gone, but I just get a virtually instant disconnection. The isql output remains the same: --- # isql -v pgdb-cdr dbuser dbname [S1000][unixODBC]The database does not exist on the server or user authentication failed. [ISQL]ERROR: Could not SQLConnect --- but it appears that the REASON is now gone, e. g. no "broken pipe" logged by Postgres? This mean anything to anybody? Thanks Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
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? Thanks Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Faith, Jeremy"
Date:
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 psqlwas 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 thereshould 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 commandlineclient 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? Thanks Stefan -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc Tyco Safety Products/CEM Systems Ltd. ________________________________ This e-mail contains privileged and confidential information intended for the use of the addressees named above. If you arenot the intended recipient of this e-mail, you are hereby notified that you must not disseminate, copy or take any actionin respect of any information contained in it. If you have received this e-mail in error, please notify the senderimmediately by e-mail and immediately destroy this e-mail and its attachments.
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
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? Thanks Stefan -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc Tyco Safety Products/CEM Systems Ltd. ________________________________ This e-mail contains privileged and confidential information intended for the use of the addressees named above. If you are not the intended recipient of this e-mail, you are hereby notified that you must not disseminate, copy or take any action in respect of any information contained in it. If you have received this e-mail in error, please notify the sender immediately by e-mail and immediately destroy this e-mail and its attachments.
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Tom Lane
Date:
"Stefan Viljoen" <viljoens@verishare.co.za> writes:
> This was my definition in /etc/odbc.ini:
Hm, I just noticed this bit:
> Protocol=9.4
Others with more ODBC experience can correct me, but I suspect that the
ODBC driver has no idea what to do with that value. The PG wire protocol
hasn't changed since 7.4, and I thought that "7.4" was generally the right
thing to use there.
Also, have you tried looking into the resulting TraceFile and/or
DebugFile? You'd probably have to modify
> DebugFile=/var/log/postgresql_debug.log
as I sure hope /var/log is not writable to you.
regards, tom lane
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
Adrian Klaver
Date:
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
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Tom Thanks for replying. >Hm, I just noticed this bit: > Protocol=9.4 >Others with more ODBC experience can correct me, but I suspect that the ODBC driver has no idea what to do with that value. The PG wire protocol hasn't changed since 7.4, and I thought that "7.4" was generally the right thing to use there. Ok, I replaced the Protocol=9.4 line with Protocol=7.4 and then tried isql again, still receiving the same error. --- [S1000][unixODBC]The database does not exist on the server or user authentication failed. --- >Also, have you tried looking into the resulting TraceFile and/or DebugFile? You'd probably have to modify >DebugFile=/var/log/postgresql_debug.log >as I sure hope /var/log is not writable to you. I changed these paths to point to TraceFile=/tmp/odbc_trace.log DebugFile=/tmp/odbc_debug.log and then tried to run isql again, error remains the same, AND neither of these log files are created. I did notice though that there is a file /tmp/mylog_postgres23427.log that was last written on the 17th (that's when I started to try to get Postgres 9.4 to communicate over ODBC) but hasn't been updated since. That file contains: --- [140298388883200]calling getDSNdefaults [140298388883200]CC_connect: entering... [140298388883200]sslmode=disable [140298388883200]original_CC_connect: entering... [140298388883200]Driver Version='09.03.0400,Apr 5 2015' [140298388883200]original_CC_connect: DSN = 'pgdb-cdr', server = 'localhost', port = '5432', database = 'asteriskcdrdb', username = 'asteriskcdruser', password='xxxxx' [140298388883200]connecting to the server socket... [140298388883200](0)(null) ERRNO=115 [140298388883200]connection to the server socket succeeded. [140298388883200]sizeof startup packet = 292 [140298388883200]sent the authentication block successfully. [140298388883200]gonna do authentication [140298388883200]read -1, global_socket_buffersize=4096 [140298388883200]Lasterror=11 [140298388883200]!!! poll ret=1 revents=1 [140298388883200]read 9, global_socket_buffersize=4096 [140298388883200]auth got 'R' [140298388883200]areq = 5 salt=fcc6a94100 [140298388883200]in AUTH_REQ_MD5 [140298388883200]read -1, global_socket_buffersize=4096 [140298388883200]Lasterror=11 [140298388883200]!!! poll ret=1 revents=1 [140298388883200]read 15, global_socket_buffersize=4096 [140298388883200]auth got 'R' [140298388883200]areq = 0 salt=0000000000 [140298388883200]sending an empty query... [140298388883200]CC_send_query: conn=0x18aaf20, query=' ' [140298388883200]send_query: done sending query 3bytes flushed [140298388883200]in QR_Constructor [140298388883200]exit QR_Constructor [140298388883200]send_query: got id = 'K' [140298388883200]CC_error_statements: self=0x18aaf20 [140298388883200]CONN ERROR: func=CC_send_query, desc='', errnum=106, errmsg='Unexpected protocol character from backend (send_query)' [140298388883200]CC_on_abort in [140298388883200]SOCK_Destructor [140298388883200]send_query: error - Unexpected protocol character from backend (send_query) [140298388883200]CC_on_abort in [140298388883200]QResult: enter DESTRUCTOR [140298388883200]QResult: in QR_close_result [140298388883200]QResult: free memory in, fcount=0 [140298388883200]QResult: free memory out [140298388883200]QResult: exit close_result [140298388883200]QResult: exit DESTRUCTOR [140298388883200]CC_error_statements: self=0x18aaf20 [140298388883200]CONN ERROR: func=original_CC_connect, desc='', errnum=105, errmsg='The database does not exist on the server or user authentication failed.' [140298388883200]QResult: enter DESTRUCTOR [140298388883200]CONN ERROR: func=PGAPI_Connect, desc='Error on CC_connect', errnum=105, errmsg='The database does not exist on the server or user authentication failed.' [140298388883200]PGAPI_Connect: returning..-1. [140298388883200][[SQLGetDiagRec]] [140298388883200]PGAPI_GetDiagRec entering type=2 rec=1 [140298388883200]**** PGAPI_ConnectError: hdbc=0x18aaf20 <513> [140298388883200]enter CC_get_error [140298388883200]enter CC_create_errormsg [140298388883200]msg = 'The database does not exist on the server or user authentication failed.' [140298388883200]exit CC_create_errormsg [140298388883200]exit CC_get_error [140298388883200]CC_get_error: status = 105, msg = #The database does not exist on the server or user authentication failed.# [140298388883200] szSqlState = 'S1000',len=72, szError='The database does not exist on the server or user authentication failed.' [140298388883200]PGAPI_GetDiagRec exiting 0 [140298388883200][[SQLGetDiagRec]] [140298388883200]PGAPI_GetDiagRec entering type=2 rec=2 [140298388883200]**** PGAPI_ConnectError: hdbc=0x18aaf20 <513> [140298388883200]PGAPI_GetDiagRec exiting 100 [140298388883200][[SQLFreeHandle]][140298388883200]PGAPI_FreeConnect: entering... [140298388883200]**** in PGAPI_FreeConnect: hdbc=0x18aaf20 [140298388883200]enter CC_Destructor, self=0x18aaf20 [140298388883200]in CC_Cleanup, self=0x18aaf20 [140298388883200]after SOCK destructor [140298388883200]CC_conninfo_init opt=1 [140298388883200]exit CC_Cleanup [140298388883200]after CC_Cleanup [140298388883200]after free statement holders [140298388883200]exit CC_Destructor [140298388883200]PGAPI_FreeConnect: returning... [140298388883200][[SQLFreeHandle]][140298388883200]**** in PGAPI_FreeEnv: env = 0x18a9a40 ** [140298388883200]in EN_Destructor, self=0x18a9a40 [140298388883200]clearing conns count=128 [140298388883200]exit EN_Destructor: rv = 1 [140298388883200] ok --- This looks like pretty relevant (don't know why it does not have amore recent date though?) but relevant errors appear to be: --- [140298388883200]send_query: got id = 'K' [140298388883200]CC_error_statements: self=0x18aaf20 [140298388883200]CONN ERROR: func=CC_send_query, desc='', errnum=106, errmsg='Unexpected protocol character from backend (send_query)' [140298388883200]CC_on_abort in [140298388883200]SOCK_Destructor [140298388883200]send_query: error - Unexpected protocol character from backend (send_query) [140298388883200]CC_on_abort in --- and this clearly is happening on my connection attempts? This line [140298388883200]send_query: error - Unexpected protocol character from backend (send_query) does eem to indicated that somehow the ODBC driver Postgres provides is incompatible on protocol level with unixODBC's isql application, at least in my instance here...? Thanks Regards Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Adrian Thanks for replying! >> 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... >Not sure, but here is what I see. >First on my machine openSUSE 13.1 >unixODBC 2.3.1-3.1.2 Ok that is much more recent than my unixODBC as provided by the official Centos 6.5 repositories, mine is unixODBC 2.2.14, even after a yum update unixodbc >psqlodbc 09.03.0400 See below, I have this same 0400 version installed via yum from the repository URL indicated below. >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 Ok, I changed my driver to refer to psqlodbcw.so, NOT psqlodbc.so. >odbc.ini >[Production] >Description = Postgres >Driver = Postgres >Host = localhost >Database = production >Port = Ok, I lacked a "Host" line (having a "ServerName=localhost" line instead) so I added a "Host" line like this one above... >aklaver@panda:~> isql -v Production >+---------------------------------------+ >| Connected! | >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. Agreed. >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 Ok, I changed mine to psqlodbcw.so as well. No change. >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 Correct. >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 Ok... >So is it possible you have a version mismatch in the Postgres ODBC driver and the Postgres server? Ok, this is completely possible... >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? I have postgresql94-odbc-09.03.0400-1PGDG.rhel6.x86_64 e. g. I have the 0400 version like you have. postgresql94-odbc.x86_64 is coming from the above URL you listed - yum.postgresql.org/9.4/redhat/rhel-6-x86_64/ I know this due to how I installed Postgres 9.4 on my Centos 6.5 box: - I edited /etc/yum.repos.d/CentOS-Base.repo and added a line exclude=postgresql* to the [base] and [updates] sections of CentOS-Base.repo - I added a file pgdg-94-centos.repo to /etc/yum.repos.d - This file contains --- [pgdg94] name=PostgreSQL 9.4 $releasever - $basearch baseurl=http://yum.postgresql.org/9.4/redhat/rhel-$releasever-$basearch enabled=1 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94 [pgdg94-source] name=PostgreSQL 9.4 $releasever - $basearch - Source failovermethod=priority baseurl=http://yum.postgresql.org/srpms/9.4/redhat/rhel-$releasever-$basearc h enabled=0 gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG-94 --- - I then ran yum install postgresql94 yum install postgresql94-odbc yum install postgresql94-devel yum install postgresql94-libs yum install postgresql94-contrib yum install postgresql94-docs to install postgres. So it appears my problem is an old unixODBC installation? Should I uninstall the yum ODBC package in Centos 6.5 and try to compile and install the current unixODBC version from source? Wonder why the latest MariaDB is working fine with the 2.2 version of unixODBC I have, while postgres 9.4 won't.... Thanks for the help. Regards Stefan
Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
scottkensai
Date:
I had the same error. Luckily I had a second system that was working. I downgraded from postgresql90-odbc-09.03.0400-1PGDG.rhel6.x86_64 postgresql90-odbc-09.00.0310-1PGDG.rhel6.x86_64 and my isql worked again. -- View this message in context: http://postgresql.nabble.com/Postgres-9-4-unixODBC-on-Centos-6-5-problem-connecting-localhost-postgres-instance-with-isql-ODBC-cot-tp5858263p5861364.html Sent from the PostgreSQL - odbc mailing list archive at Nabble.com.
Re: Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client
From
"Stefan Viljoen"
Date:
Hi Scott Yep, I downgraded from 94 to 91 and my setup started working fine. Thanks for the reply. Kind regards, -----Original Message----- From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of scottkensai Sent: Friday,August 7, 2015 09:56 PM To: pgsql-odbc@postgresql.org Subject: [ODBC] Re: Postgres 9.4 + unixODBC on Centos 6.5 problem connecting localhost postgres instance with isql ODBC commandline client I had the same error. Luckily I had a second system that was working. I downgraded from postgresql90-odbc-09.03.0400-1PGDG.rhel6.x86_64 postgresql90-odbc-09.00.0310-1PGDG.rhel6.x86_64 and my isql worked again. -- View this message in context: http://postgresql.nabble.com/Postgres-9-4-unixODBC-on-Centos-6-5-problem-con necting-localhost-postgres-instance-with-isql-ODBC-cot-tp5858263p5861364.htm l Sent from the PostgreSQL - odbc mailing list archive at Nabble.com. -- Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-odbc