Search code examples
pythonpostgresqlpyodbcunixodbc

Pgpass not working in unixODBC/pyodbc

So I can't seem to get PostgreSQL's ".pgpass" functionality working using unixODBC/pyodbc in Python.

I have a working ~/.pgpass file, as confirmed by the command-line tool psql, and I have a working odbc.ini entry as confirmed by my Python script. The problem here is that I want the script to pick up the password via the ~/.pgpass file as I don't want to have to specify the password in either the DSN entry nor the odbc.ini entry (either of which works).

I've tried setting the local and host entries in pg_hba.conf to various values including md5, password, ident, peer, and trust. Trust of course works, but that puts security at a level I'm not comfortable with since it's not actually doing any authentication.

Has anyone gotten this to work? Here's some relevant entries:

pg_hba.conf

local   all             all                                     md5
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5
host    all             all             ::/0                    md5

odbc.ini

[MyEntry]
Driver                  = PostgreSQL
Server                  = myServer
Port                    = 5432
Database                = myDB
Username                = myUser
Trace                   = Yes

.pgpass

myServer:5432:*:myUser:myPassword
Solution

  • .pgpass file used by libpq. You can not use it from odbc.