Search code examples
linuxpostgresqlodbc

unixODBC/Postgres password mysterious login error

I have written some software in C++ that uses ODBC to work with a database remotely. In this case, the database is running within a Postgres engine, and my software is running on an Ubuntu server machine with unixODBC and the odbc-postgres driver installed.

I used odbcinst with the correct template file to generate my .odbc.ini file. I know it's correct, because I used it to connect to a different Postgres database on a different server with a different username/password combination. "Works on my development machine," as the saying goes.

On this target machine, I have changed the server, username, and password to match where I want it to connect.

So, on a bash command line, I test with:

$ isql -v myDSN

That yields:

[28000][unixODBC]FATAL:  password authentication failed for user "myUser"
[ISQL]ERROR: Could not SQLConnect

Er, okay, let's try this:

$ isql -v myDSN myUser myPassword

This yields:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

Neato, but rather mysterious when 'myPassword' is literally cut and pasted to ensure it's the same.

Okay, so, maybe in my binary, instead of using 'DSN=myDSN' for my connection string passed into the command line, I can use:

$ my_bin --myArgs --dsn Driver={PostgreSQL\ Unicode}\;Servername=myServer\;Database=myDatabase\;UID=myUser\;PWD=myPassword

But, this generates an ODBC error which I send to stdout:

[unixODBC]FATAL:  password authentication failed for user "myUser"

(Of course, using --dsn DSN=myDSN fails, too, in the same exact way).

This password is correct. I can establish the connection and work with it in isql if I call using all three command line arguments. But, for some reason, in this environment, something else is preventing me from doing this correctly.

In the actual password for this connection, I use a '#' character. It's the only remotely peculiar character that could in any way cause a problem. I know bash isn't doing anything weird with it on the command line, because I see it quoted in the stdout from my executable, and I've tried prepending it with \ to ensure it was escaped properly in case bash was doing something unsavory with it.

I can't use isql for what I want to do... the dataset I may request needs some C++ (or the like) loving.

Edit:

Investigating some more, I did the following:

  1. Changed the .odbc.ini file to match the dev environment. This lead to a successful connection. So nothing within the client environment is wrong.
  2. Changed the .odbc.ini file to use the same server and database, but changed the login to match the failing one. I then added that role/password to the dev Postgres engine, matching the target account on the target Postgres engine. This one fails. Consequently, I think the problem may involve the password.

Any suggestions on where to go from here?

Solution

  • In the end, I added another user with a password that doesn't have a '#' character in it, and changed my .odbc.ini file to match the new user and password.

    This connected properly.

    Perhaps this driver has a bug.