Обсуждение: Does psqlODBC actually work on osx?

Hello,

We have a client trying to connect to PostgreSQL server 9.2 from an osx
client with our software via ODBC, he has asked for instructions to
assist him setting up.

I have attempted the setup myself using psqlODBC and no matter what I do
configuration wise, the driver fails to connect (via iodbctestw and
iodbc administrator) stating that the password is incorrect - I know
this is not the case because I am using identical configuration to my
linux machine where it works fine.


Has anybody actually tested psqlODBC on OSX recently? Does it actually
work? Is there some known issue that causes this password thing, if so
what is the workaround?
(I'm trying on Snow Leopard myself but even information about a more
recent version would be great to know)

Thanks,
Malcolm MacLeod

On 11/04/2014 07:54 AM, Malcolm MacLeod wrote:
> Hello,
>
> We have a client trying to connect to PostgreSQL server 9.2 from an osx
> client with our software via ODBC, he has asked for instructions to
> assist him setting up.
>
> I have attempted the setup myself using psqlODBC and no matter what I do
> configuration wise, the driver fails to connect (via iodbctestw and
> iodbc administrator) stating that the password is incorrect - I know
> this is not the case because I am using identical configuration to my
> linux machine where it works fine.

What is the exact error message you are getting?

Are you connecting from within the same network as your Linux machine?

Just trying to eliminate the possibility that it is a pg_hba.conf issue.

>
>
> Has anybody actually tested psqlODBC on OSX recently? Does it actually
> work? Is there some known issue that causes this password thing, if so
> what is the workaround?
> (I'm trying on Snow Leopard myself but even information about a more
> recent version would be great to know)
>
> Thanks,
> Malcolm MacLeod
>
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

> > We have a client trying to connect to PostgreSQL server 9.2 from an osx
> > client with our software via ODBC, he has asked for instructions to
> > assist him setting up.
> >
> > I have attempted the setup myself using psqlODBC and no matter what I do
> > configuration wise, the driver fails to connect (via iodbctestw and
> > iodbc administrator) stating that the password is incorrect - I know
> > this is not the case because I am using identical configuration to my
> > linux machine where it works fine.
> What is the exact error message you are getting?
> Are you connecting from within the same network as your Linux machine?
> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
All on same internal network.
Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
machine 10.0.0.26
I've tried also setting the pg_hba.conf to 'trust' and even then it
doesn't seem to work.

Various config info and traces below.



Snippet from configuration (Although I've played with various other
options SSLmode etc. here as well)

[test]
Driver=psqlODBC
Server=10.0.0.3
Database=todo
Username=postgres
Password=postgres13




iodbctestw error messsage:
1: SQLDriverConnectW = FATAL: password authentication failed for user
"postgres" (210) SQLSTATE=28P01



Trace:
** Trace started on Tue Nov 04 18:17:38 2014
** Driver Manager: 03.52.0607.1008


[000000.001003]
iodbctestw      7FFF70390CC0 ENTER SQLAllocHandle
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHANDLE         0x0 (SQL_NULL_HANDLE)
        SQLHANDLE       * 0x100046340

[000000.001049]
iodbctestw      7FFF70390CC0 EXIT  SQLAllocHandle with return code 0 (SQL_SUCCESS)
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHANDLE         0x0 (SQL_NULL_HANDLE)
        SQLHANDLE       * 0x100046340 (0x100110c70)

[000000.001093]
iodbctestw      7FFF70390CC0 ENTER SQLSetEnvAttr
        SQLHENV           0x100110c70
        SQLINTEGER        200 (SQL_ATTR_ODBC_VERSION)
        SQLPOINTER        0x3
        SQLINTEGER      * 4294967291 (SQL_IS_UINTEGER)

[000000.001149]
iodbctestw      7FFF70390CC0 EXIT  SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
        SQLHENV           0x100110c70
        SQLINTEGER        200 (SQL_ATTR_ODBC_VERSION)
        SQLPOINTER        0x3
        SQLINTEGER      * 4294967291 (SQL_IS_UINTEGER)

[000000.001192]
iodbctestw      7FFF70390CC0 ENTER SQLAllocHandle
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHANDLE         0x100110c70
        SQLHANDLE       * 0x100046348

[000000.001238]
iodbctestw      7FFF70390CC0 EXIT  SQLAllocHandle with return code 0 (SQL_SUCCESS)
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHANDLE         0x100110c70
        SQLHANDLE       * 0x100046348 (0x100110f20)

[000000.001279]
iodbctestw      7FFF70390CC0 ENTER SQLSetConnectOptionW
        SQLHDBC           0x100110f20
        SQLUSMALLINT      1051 (unknown connection attribute)
        SQLLEN            4295220960

[000000.001322]
iodbctestw      7FFF70390CC0 EXIT  SQLSetConnectOptionW with return code 0 (SQL_SUCCESS)
        SQLHDBC           0x100110f20
        SQLUSMALLINT      1051 (unknown connection attribute)
        SQLLEN            4295220960

[000000.001366]
iodbctestw      7FFF70390CC0 ENTER SQLGetInfoW
        SQLHDBC           0x100110f20
        SQLUSMALLINT      171 (SQL_DM_VER)
        SQLPOINTER        0x7fff5fbfeb80
        SQLSMALLINT       1020
        SQLSMALLINT     * 0x7fff5fbff60c

[000000.001438]
iodbctestw      7FFF70390CC0 EXIT  SQLGetInfoW with return code 0 (SQL_SUCCESS)
        SQLHDBC           0x100110f20
        SQLUSMALLINT      171 (SQL_DM_VER)
        SQLPOINTER        0x7fff5fbfeb80
                  | 03.52.0607.1008                          |
        SQLSMALLINT       1020
        SQLSMALLINT     * 0x7fff5fbff60c (15)

[000000.002935]
iodbctestw      7FFF70390CC0 ENTER SQLDriverConnectW
        SQLHDBC           0x100110f20
        SQLPOINTER        0x0
        SQLWCHAR        * 0x7fff5fbfdb80
                  | DSN=test;UID=postgres;PWD=**********     |
        SQLSMALLINT       -3 (SQL_NTS)
        SQLWCHAR        * 0x100048840
        SQLSMALLINT       4096
        SQLSMALLINT     * 0x7fff5fbff60e
        SQLUSMALLINT      1 (SQL_DRIVER_COMPLETE)

[000000.017832]
iodbctestw      7FFF70390CC0 EXIT  SQLDriverConnectW with return code -1 (SQL_ERROR)
        SQLHDBC           0x100110f20
        SQLPOINTER        0x0
        SQLWCHAR        * 0x7fff5fbfdb80
        SQLSMALLINT       -3 (SQL_NTS)
        SQLWCHAR        * 0x100048840
        SQLSMALLINT       4096
        SQLSMALLINT     * 0x7fff5fbff60e
        SQLUSMALLINT      1 (SQL_DRIVER_COMPLETE)

[000000.017936]
iodbctestw      7FFF70390CC0 ENTER SQLGetDiagRecW
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbfdad0
        SQLINTEGER      * 0x7fff5fbfdb0c
        SQLWCHAR        * 0x7fff5fbfd2d0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.018047]
iodbctestw      7FFF70390CC0 EXIT  SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbfdad0
                  | 28P01                                    |
        SQLINTEGER      * 0x7fff5fbfdb0c (210)
        SQLWCHAR        * 0x7fff5fbfd2d0
                  | FATAL: password authentication failed fo |
                  | r user "postgres"                        |
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.018306]
iodbctestw      7FFF70390CC0 ENTER SQLGetDiagRecW
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20
        SQLSMALLINT       2
        SQLWCHAR        * 0x7fff5fbfdad0
        SQLINTEGER      * 0x7fff5fbfdb0c
        SQLWCHAR        * 0x7fff5fbfd2d0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.018480]
iodbctestw      7FFF70390CC0 EXIT  SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20
        SQLSMALLINT       2
        SQLWCHAR        * 0x7fff5fbfdad0
        SQLINTEGER      * 0x7fff5fbfdb0c
        SQLWCHAR        * 0x7fff5fbfd2d0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.018544]
iodbctestw      7FFF70390CC0 ENTER SQLGetDiagRecW
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHENV           0x100110c70
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbfdad0
        SQLINTEGER      * 0x7fff5fbfdb0c
        SQLWCHAR        * 0x7fff5fbfd2d0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.018833]
iodbctestw      7FFF70390CC0 EXIT  SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHENV           0x100110c70
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbfdad0
        SQLINTEGER      * 0x7fff5fbfdb0c
        SQLWCHAR        * 0x7fff5fbfd2d0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.018940]
iodbctestw      7FFF70390CC0 ENTER SQLGetDiagRecW
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbff9e0
        SQLINTEGER      * 0x7fff5fbffa1c
        SQLWCHAR        * 0x7fff5fbff1e0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.019015]
iodbctestw      7FFF70390CC0 EXIT  SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbff9e0
        SQLINTEGER      * 0x7fff5fbffa1c
        SQLWCHAR        * 0x7fff5fbff1e0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.019083]
iodbctestw      7FFF70390CC0 ENTER SQLGetDiagRecW
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHENV           0x100110c70
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbff9e0
        SQLINTEGER      * 0x7fff5fbffa1c
        SQLWCHAR        * 0x7fff5fbff1e0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.019149]
iodbctestw      7FFF70390CC0 EXIT  SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHENV           0x100110c70
        SQLSMALLINT       1
        SQLWCHAR        * 0x7fff5fbff9e0
        SQLINTEGER      * 0x7fff5fbffa1c
        SQLWCHAR        * 0x7fff5fbff1e0
        SQLSMALLINT       512
        SQLSMALLINT     * 0x0

[000000.019216]
iodbctestw      7FFF70390CC0 ENTER SQLFreeHandle
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20

[000000.019263]
iodbctestw      7FFF70390CC0 EXIT  SQLFreeHandle with return code 0 (SQL_SUCCESS)
        SQLSMALLINT       2 (SQL_HANDLE_DBC)
        SQLHDBC           0x100110f20

[000000.019297]
iodbctestw      7FFF70390CC0 ENTER SQLFreeHandle
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHENV           0x100110c70

[000000.019328]
iodbctestw      7FFF70390CC0 EXIT  SQLFreeHandle with return code 0 (SQL_SUCCESS)
        SQLSMALLINT       1 (SQL_HANDLE_ENV)
        SQLHENV           0x100110c70

** Trace finished on Tue Nov 04 18:17:38 2014


>
> >
> >
> > Has anybody actually tested psqlODBC on OSX recently? Does it actually
> > work? Is there some known issue that causes this password thing, if so
> > what is the workaround?
> > (I'm trying on Snow Leopard myself but even information about a more
> > recent version would be great to know)

On 11/04/2014 08:23 AM, Malcolm MacLeod wrote:
>
>>> We have a client trying to connect to PostgreSQL server 9.2 from an osx
>>> client with our software via ODBC, he has asked for instructions to
>>> assist him setting up.
>>>
>>> I have attempted the setup myself using psqlODBC and no matter what I do
>>> configuration wise, the driver fails to connect (via iodbctestw and
>>> iodbc administrator) stating that the password is incorrect - I know
>>> this is not the case because I am using identical configuration to my
>>> linux machine where it works fine.
>> What is the exact error message you are getting?
>> Are you connecting from within the same network as your Linux machine?
>> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
> All on same internal network.
> Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
> machine 10.0.0.26
> I've tried also setting the pg_hba.conf to 'trust' and even then it
> doesn't seem to work.
>
> Various config info and traces below.
>
>
>
> Snippet from configuration (Although I've played with various other
> options SSLmode etc. here as well)
>
> [test]
> Driver=psqlODBC
> Server=10.0.0.3
> Database=todo
> Username=postgres
> Password=postgres13
>
>
>
>
> iodbctestw error messsage:
> 1: SQLDriverConnectW = FATAL: password authentication failed for user
> "postgres" (210) SQLSTATE=28P01

Well it is not liking that password.

Are you sure you do not have conflicting configurations?

What file is the configuration you show above coming from?

What is in your odbc.ini file?

How are you running the test configuration?

>
>



--
Adrian Klaver
adrian.klaver@aklaver.com

Malcolm MacLeod <malcolm.macleod@tshwanedje.com> writes:
> 1: SQLDriverConnectW = FATAL: password authentication failed for user
> "postgres" (210) SQLSTATE=28P01

Looking into the postmaster log to see what it logged about this might
prove informative.  It's not unusual for the log to contain info that's
intentionally not reported to the client.

            regards, tom lane

> >>> We have a client trying to connect to PostgreSQL server 9.2 from an osx
> >>> client with our software via ODBC, he has asked for instructions to
> >>> assist him setting up.
> >>>
> >>> I have attempted the setup myself using psqlODBC and no matter what I do
> >>> configuration wise, the driver fails to connect (via iodbctestw and
> >>> iodbc administrator) stating that the password is incorrect - I know
> >>> this is not the case because I am using identical configuration to my
> >>> linux machine where it works fine.
> >> What is the exact error message you are getting?
> >> Are you connecting from within the same network as your Linux machine?
> >> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
> > All on same internal network.
> > Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
> > machine 10.0.0.26
> > I've tried also setting the pg_hba.conf to 'trust' and even then it
> > doesn't seem to work.
> >
> > Various config info and traces below.
> >
> >
> >
> > Snippet from configuration (Although I've played with various other
> > options SSLmode etc. here as well)
> >
> > [test]
> > Driver=psqlODBC
> > Server=10.0.0.3
> > Database=todo
> > Username=postgres
> > Password=postgres13
> >
> >
> >
> >
> > iodbctestw error messsage:
> > 1: SQLDriverConnectW = FATAL: password authentication failed for user
> > "postgres" (210) SQLSTATE=28P01
> Well it is not liking that password.
I can connect using the exact same password from the exact same machine
using pgadmin directly, as well as from multiple (non osx) machines
using it also.
Occams razor tells me that it is far more likely that psqlodbcw.so has
some or other issue than that the password somehow has something wrong
with it or that I am suddenly incapable of doing basic configurations
correctly. I would love to be wrong and find out that it is a
configuration issue as that would solve a lot of headaches but I just
don't see where or how it could be possible.

> Are you sure you do not have conflicting configurations?
Yes, this is a fresh snow leopard install on which I have install postgres, iodbc administrator and nothing else.
Also changing various settings e.g. SSL in the config file change the
behavior of iodbctest thus indicating that this is where it is looking.

> What file is the configuration you show above coming from?
/Library/ODBC/odbc.ini
- Which is the only odbc.ini file in existence on the machine, all other
odbc.ini and .odbc.ini have been deleted, and I have only put
configuration into this one file.

> What is in your odbc.ini file?
Exactly what I posted above plus the below for tracing:
[ODBC]
Trace=1
TraceFile=/Users/test/odbc.log



> How are you running the test configuration?
iodbctestw "DSN=test;UID=postgres;PWD=postgres13" and various similar
incantations e.g. with the user and password left out...
Also the test button in iODBC administrator


> Looking into the postmaster log to see what it logged about this might
> prove informative.  It's not unusual for the log to contain info
> that's
> intentionally not reported to the client.
2014-11-04 18:26:07 CAT    FATAL    password authentication failed for user
"postgres"
2014-11-04 18:26:07 CAT    DETAIL    Connection matched pg_hba.conf line 81:
"host    all             all             10.0.0.0/24             md5"

Thats all of interest that postmaster has to say.
i.e. the password is wrong, except of course this is obviously not the
case. I doubt the server is lying about this, so I'm pretty sure that
the blame lies with psqlODBC (or with libiodbc and/or the interface
between the two).
Could it be incorrectly passing the UTF32 (or is it 16?) password to a
UTF8 system/iodbc call or similar thereby corrupting the password?
I find it hard to believe that something this fundamental could be
wrong/broken but I don't know what else to think - which is why I ask if
anyone can verify having actually ever used psqlODBCw on osx recently
without issue...

Thanks,
Malcolm MacLeod

> We use psqlODBC daily on OS X, on a mix of Mountain Lion and Mavericks
> machines for a fair amount of development activity, and have for a
> couple of years now. I maintain (somewhat haphazardly) the MacPorts
> port for psqlODBC. Which reminds me I should probably push the latest
> version. ;)
>
> So, yes, it most definitely works on OS X.
Thanks, have you used the one in the "enterprise db" installer at all or
only the MacPorts one?
Can you confirm if it works with iodbctestw? Do you use an "ODBC
adminstrator" e.g. "Openlink ODBC Administrator" or only config files?

- Malcolm MacLeod

>
> On Tue, Nov 4, 2014 at 10:15 AM, Malcolm MacLeod
> <malcolm.macleod@tshwanedje.com> wrote:
>         > >>> We have a client trying to connect to PostgreSQL server
>         9.2 from an osx
>         > >>> client with our software via ODBC, he has asked for
>         instructions to
>         > >>> assist him setting up.
>         > >>>
>         > >>> I have attempted the setup myself using psqlODBC and no
>         matter what I do
>         > >>> configuration wise, the driver fails to connect (via
>         iodbctestw and
>         > >>> iodbc administrator) stating that the password is
>         incorrect - I know
>         > >>> this is not the case because I am using identical
>         configuration to my
>         > >>> linux machine where it works fine.
>         > >> What is the exact error message you are getting?
>         > >> Are you connecting from within the same network as your
>         Linux machine?
>         > >> Just trying to eliminate the possibility that it is a
>         pg_hba.conf issue.
>         > > All on same internal network.
>         > > Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25
>         etc. broken
>         > > machine 10.0.0.26
>         > > I've tried also setting the pg_hba.conf to 'trust' and
>         even then it
>         > > doesn't seem to work.
>         > >
>         > > Various config info and traces below.
>         > >
>         > >
>         > >
>         > > Snippet from configuration (Although I've played with
>         various other
>         > > options SSLmode etc. here as well)
>         > >
>         > > [test]
>         > > Driver=psqlODBC
>         > > Server=10.0.0.3
>         > > Database=todo
>         > > Username=postgres
>         > > Password=postgres13
>         > >
>         > >
>         > >
>         > >
>         > > iodbctestw error messsage:
>         > > 1: SQLDriverConnectW = FATAL: password authentication
>         failed for user
>         > > "postgres" (210) SQLSTATE=28P01
>         > Well it is not liking that password.
>
>         I can connect using the exact same password from the exact
>         same machine
>         using pgadmin directly, as well as from multiple (non osx)
>         machines
>         using it also.
>         Occams razor tells me that it is far more likely that
>         psqlodbcw.so has
>         some or other issue than that the password somehow has
>         something wrong
>         with it or that I am suddenly incapable of doing basic
>         configurations
>         correctly. I would love to be wrong and find out that it is a
>         configuration issue as that would solve a lot of headaches but
>         I just
>         don't see where or how it could be possible.
>
>         > Are you sure you do not have conflicting configurations?
>         Yes, this is a fresh snow leopard install on which I have
>         install postgres, iodbc administrator and nothing else.
>         Also changing various settings e.g. SSL in the config file
>         change the
>         behavior of iodbctest thus indicating that this is where it is
>         looking.
>
>         > What file is the configuration you show above coming from?
>         /Library/ODBC/odbc.ini
>         - Which is the only odbc.ini file in existence on the machine,
>         all other
>         odbc.ini and .odbc.ini have been deleted, and I have only put
>         configuration into this one file.
>
>         > What is in your odbc.ini file?
>         Exactly what I posted above plus the below for tracing:
>         [ODBC]
>         Trace=1
>         TraceFile=/Users/test/odbc.log
>
>
>
>         > How are you running the test configuration?
>         iodbctestw "DSN=test;UID=postgres;PWD=postgres13" and various
>         similar
>         incantations e.g. with the user and password left out...
>         Also the test button in iODBC administrator
>
>
>         > Looking into the postmaster log to see what it logged about
>         this might
>         > prove informative.  It's not unusual for the log to contain
>         info
>         > that's
>         > intentionally not reported to the client.
>         2014-11-04 18:26:07 CAT FATAL   password authentication failed
>         for user
>         "postgres"
>         2014-11-04 18:26:07 CAT DETAIL  Connection matched pg_hba.conf
>         line 81:
>         "host    all             all             10.0.0.0/24
>            md5"
>
>         Thats all of interest that postmaster has to say.
>         i.e. the password is wrong, except of course this is obviously
>         not the
>         case. I doubt the server is lying about this, so I'm pretty
>         sure that
>         the blame lies with psqlODBC (or with libiodbc and/or the
>         interface
>         between the two).
>         Could it be incorrectly passing the UTF32 (or is it 16?)
>         password to a
>         UTF8 system/iodbc call or similar thereby corrupting the
>         password?
>         I find it hard to believe that something this fundamental
>         could be
>         wrong/broken but I don't know what else to think - which is
>         why I ask if
>         anyone can verify having actually ever used psqlODBCw on osx
>         recently
>         without issue...
>
>         Thanks,
>         Malcolm MacLeod
>
>
>
>         --
>         Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-odbc
>
>
>

>         Thanks, have you used the one in the "enterprise db" installer
>         at all or
>         only the MacPorts one?
>         Can you confirm if it works with iodbctestw? Do you use an
>         "ODBC
>         adminstrator" e.g. "Openlink ODBC Administrator" or only
>         config files?
> Only MacPorts, through unixODBC. I have not had much luck in the past
> with the Openlink stuff, or iODBC, which is why we moved to MacPorts.
> FWIW I had the same problems with the MySQL ODBC drivers.
Thanks, good to know that the unixODBC part at least works.. However
unfortunately the application in question only currently use iODBC.

What you say about myODBC was true up until about a year ago (or maybe
even more recently my memory is failing me) but the latest myODBC
drivers actually work pretty well and do so with iodbc so its definitely
possible to have working iodbc drivers on osx.

- Malcolm MacLeod

On 11/04/2014 10:15 AM, Malcolm MacLeod wrote:
>>>>> We have a client trying to connect to PostgreSQL server 9.2 from an osx
>>>>> client with our software via ODBC, he has asked for instructions to
>>>>> assist him setting up.
>>>>>
>>>>> I have attempted the setup myself using psqlODBC and no matter what I do
>>>>> configuration wise, the driver fails to connect (via iodbctestw and
>>>>> iodbc administrator) stating that the password is incorrect - I know
>>>>> this is not the case because I am using identical configuration to my
>>>>> linux machine where it works fine.
>>>> What is the exact error message you are getting?
>>>> Are you connecting from within the same network as your Linux machine?
>>>> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
>>> All on same internal network.
>>> Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
>>> machine 10.0.0.26
>>> I've tried also setting the pg_hba.conf to 'trust' and even then it
>>> doesn't seem to work.
>>>
>>> Various config info and traces below.
>>>
>>>
>>>
>>> Snippet from configuration (Although I've played with various other
>>> options SSLmode etc. here as well)
>>>
>>> [test]
>>> Driver=psqlODBC
>>> Server=10.0.0.3
>>> Database=todo
>>> Username=postgres
>>> Password=postgres13
>>>
>>>
>>>
>>>
>>> iodbctestw error messsage:
>>> 1: SQLDriverConnectW = FATAL: password authentication failed for user
>>> "postgres" (210) SQLSTATE=28P01
>> Well it is not liking that password.
> I can connect using the exact same password from the exact same machine
> using pgadmin directly, as well as from multiple (non osx) machines
> using it also.
> Occams razor tells me that it is far more likely that psqlodbcw.so has
> some or other issue than that the password somehow has something wrong
> with it or that I am suddenly incapable of doing basic configurations
> correctly. I would love to be wrong and find out that it is a
> configuration issue as that would solve a lot of headaches but I just
> don't see where or how it could be possible.

I do not use iodbc, so the following may be answered some where else
that I have not found.

What has me confused is that you have:

[test]
Driver=psqlODBC
Server=10.0.0.3
Database=todo
Username=postgres
Password=postgres13


but this:

http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/FAQ

says:
"
The Driver= parameter should be a full name to a shared-library
implementing the driver for the backend database to which you're connecting

  Note that other driver-managers permit use of { } for quotations and
symbolic names for the driver (referencing the odbcinst.ini file), such as

Driver = {SQL Server}

Currently iODBC implements neither of these; the Driver= parameter must
be a full path to the shared library directly.
"

>

>
>> How are you running the test configuration?
> iodbctestw "DSN=test;UID=postgres;PWD=postgres13" and various similar
> incantations e.g. with the user and password left out...
> Also the test button in iODBC administrator
>
>
>> Looking into the postmaster log to see what it logged about this might
>> prove informative.  It's not unusual for the log to contain info
>> that's
>> intentionally not reported to the client.
> 2014-11-04 18:26:07 CAT    FATAL    password authentication failed for user
> "postgres"
> 2014-11-04 18:26:07 CAT    DETAIL    Connection matched pg_hba.conf line 81:
> "host    all             all             10.0.0.0/24             md5"
>
> Thats all of interest that postmaster has to say.
> i.e. the password is wrong, except of course this is obviously not the
> case. I doubt the server is lying about this, so I'm pretty sure that
> the blame lies with psqlODBC (or with libiodbc and/or the interface
> between the two).
> Could it be incorrectly passing the UTF32 (or is it 16?) password to a
> UTF8 system/iodbc call or similar thereby corrupting the password?
> I find it hard to believe that something this fundamental could be
> wrong/broken but I don't know what else to think - which is why I ask if
> anyone can verify having actually ever used psqlODBCw on osx recently
> without issue...

Just for grins you might want to try the straight iodbctest.

>
> Thanks,
> Malcolm MacLeod
>


--
Adrian Klaver
adrian.klaver@aklaver.com

(2014/11/05 1:23), Malcolm MacLeod wrote:
>
>>> We have a client trying to connect to PostgreSQL server 9.2 from an osx
>>> client with our software via ODBC, he has asked for instructions to
>>> assist him setting up.
>>>
>>> I have attempted the setup myself using psqlODBC and no matter what I do
>>> configuration wise, the driver fails to connect (via iodbctestw and
>>> iodbc administrator) stating that the password is incorrect - I know
>>> this is not the case because I am using identical configuration to my
>>> linux machine where it works fine.
>> What is the exact error message you are getting?
>> Are you connecting from within the same network as your Linux machine?
>> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
> All on same internal network.
> Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
> machine 10.0.0.26
> I've tried also setting the pg_hba.conf to 'trust' and even then it
> doesn't seem to work.
>
> Various config info and traces below.
>
>
>
> Snippet from configuration (Although I've played with various other
> options SSLmode etc. here as well)
>
> [test]
> Driver=psqlODBC
> Server=10.0.0.3

The driver seems to ignore the above entry.
Could you please try Servername instead of Server?

regards,
Hiroshi Inoue

> Database=todo
> Username=postgres
> Password=postgres13


--
I am using the free version of SPAMfighter.
SPAMfighter has removed 12883 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen

> >>> We have a client trying to connect to PostgreSQL server 9.2 from an osx
> >>> client with our software via ODBC, he has asked for instructions to
> >>> assist him setting up.
> >>>
> >>> I have attempted the setup myself using psqlODBC and no matter what I do
> >>> configuration wise, the driver fails to connect (via iodbctestw and
> >>> iodbc administrator) stating that the password is incorrect - I know
> >>> this is not the case because I am using identical configuration to my
> >>> linux machine where it works fine.
> >> What is the exact error message you are getting?
> >> Are you connecting from within the same network as your Linux machine?
> >> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
> > All on same internal network.
> > Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
> > machine 10.0.0.26
> > I've tried also setting the pg_hba.conf to 'trust' and even then it
> > doesn't seem to work.
> >
> > Various config info and traces below.
> >
> >
> >
> > Snippet from configuration (Although I've played with various other
> > options SSLmode etc. here as well)
> >
> > [test]
> > Driver=psqlODBC
> > Server=10.0.0.3
>
> The driver seems to ignore the above entry.
> Could you please try Servername instead of Server?
Hrm, you are right using ServerName seems to fix the issue, it is a
relief that it is something so simple in the end. Thanks!

However it is not as clear cut as "Server" being ignored, as mentioned
in the other messages it does actually attempt to connect to the right
server, postmaster on the server even acknowledges the failed connection
attempt. If I turn ssl support on the server off and on in the
connection string than I get a message that the server refuses SSL etc.
etc.

Further if I check the code "CopyAttributes" checks for both INI_SERVER
(ServerName) and SPEC_SERVER (Server) and fills ci->server in
identically regardless.
So rather than a case of "Server" being ignored it seems instead that it
is not ignored, but that somehow some part of the auth code is doing
something wrong; scanning manually for servername again instead of using
ci->server or similar?; I don't know the driver code well enough to know
where to look but as I've already set everything up for
compiling/testing if someone can point me to some key places this might
happen I'd be happy to test quickly.

- Malcolm MacLeod

I've tracked this down to the getDSNInfo function inside dlg_specific.c
It checks only INI_SERVER and not SPEC_SERVER - while other parts of the
code check both, this leads to inconsistent behaviour.

If I use e.g. iodbctestw
"DSN=test;UID=postgres;PWD=postgres13;SERVERNAME=10.0.0.3" it bypasses
the ini reading and works as expected.

I attempted a quick patch to make getDSNInfo check both and after doing
that it works as expected, personally I think this should be fixed to
check for both.

Thanks for all the help.
- Malcolm MacLeod

> > >>> We have a client trying to connect to PostgreSQL server 9.2 from an osx
> > >>> client with our software via ODBC, he has asked for instructions to
> > >>> assist him setting up.
> > >>>
> > >>> I have attempted the setup myself using psqlODBC and no matter what I do
> > >>> configuration wise, the driver fails to connect (via iodbctestw and
> > >>> iodbc administrator) stating that the password is incorrect - I know
> > >>> this is not the case because I am using identical configuration to my
> > >>> linux machine where it works fine.
> > >> What is the exact error message you are getting?
> > >> Are you connecting from within the same network as your Linux machine?
> > >> Just trying to eliminate the possibility that it is a pg_hba.conf issue.
> > > All on same internal network.
> > > Server 10.0.0.3, working machine(s) 10.0.0.24, 10.0.0.25 etc. broken
> > > machine 10.0.0.26
> > > I've tried also setting the pg_hba.conf to 'trust' and even then it
> > > doesn't seem to work.
> > >
> > > Various config info and traces below.
> > >
> > >
> > >
> > > Snippet from configuration (Although I've played with various other
> > > options SSLmode etc. here as well)
> > >
> > > [test]
> > > Driver=psqlODBC
> > > Server=10.0.0.3
> >
> > The driver seems to ignore the above entry.
> > Could you please try Servername instead of Server?
> Hrm, you are right using ServerName seems to fix the issue, it is a
> relief that it is something so simple in the end. Thanks!
>
> However it is not as clear cut as "Server" being ignored, as mentioned
> in the other messages it does actually attempt to connect to the right
> server, postmaster on the server even acknowledges the failed connection
> attempt. If I turn ssl support on the server off and on in the
> connection string than I get a message that the server refuses SSL etc.
> etc.
>
> Further if I check the code "CopyAttributes" checks for both INI_SERVER
> (ServerName) and SPEC_SERVER (Server) and fills ci->server in
> identically regardless.
> So rather than a case of "Server" being ignored it seems instead that it
> is not ignored, but that somehow some part of the auth code is doing
> something wrong; scanning manually for servername again instead of using
> ci->server or similar?; I don't know the driver code well enough to know
> where to look but as I've already set everything up for
> compiling/testing if someone can point me to some key places this might
> happen I'd be happy to test quickly.
>
> - Malcolm MacLeod
>
>
>
>

(2014/11/05 15:54), Malcolm MacLeod wrote:

> I've tracked this down to the getDSNInfo function inside dlg_specific.c
> It checks only INI_SERVER and not SPEC_SERVER - while other parts of the
> code check both, this leads to inconsistent behaviour.

It's never inconsistent.
The driver allows short form keywords ABBR_XXXX of keywords INI_XXXX in
connection
strings but doesn't  allow them in DSN entries.
The short form keywords are for applications which can handle only short
connection
strings.

SPEC_SERVER is a short form of INI_SERVER.

regards,
Hiroshi Inoue

> If I use e.g. iodbctestw
> "DSN=test;UID=postgres;PWD=postgres13;SERVERNAME=10.0.0.3" it bypasses
> the ini reading and works as expected.

> I attempted a quick patch to make getDSNInfo check both and after doing
> that it works as expected, personally I think this should be fixed to
> check for both.
>
> Thanks for all the help.
> - Malcolm MacLeo


--
I am using the free version of SPAMfighter.
SPAMfighter has removed 12891 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

Do you have a slow PC? Try a Free scan
http://www.spamfighter.com/SLOW-PCfighter?cid=sigen