python connect to postgresql with libpq-pgpass

Question

I read there is a more secure way to connect to postgresql db without specifying password in source code using http://www.postgresql.org/docs/9.2/static/libpq-pgpass.html. But unfortunatelly I was not able to find any examples of how to import it to my python program and how made my postgresql server to use this file. Please help.

Answer 1

You don't import it into your Python program. The point of .pgpass is that it is a regular file subject to the system's file permissions, and the libpq driver which libraries such as psycopg2 use to connect to Postgres will look to this file for the password instead of requiring the password to be in the source code or prompting for it.

Also, this is not a server-side file, but a client-side one. So, on a *nix box, you would have a ~/.pgpass file containing the credentials for the various connections you want to be able to make.

Edit in response to comment from OP:

Two main things need to happen in order for psycopg2 to correctly authenticate via .pgpass:

1. Do not specify a password in the string passed to psycopg2.connect
2. Make sure the correct entry is added to the .pgpass file for the user who will be connecting via psycopg2.

For example, to make this work for all databases for a particular user on localhost port 5432, you would add the following line to that user's .pgpass file:

localhost:5432:*:<username>:<password>

And then the connect call would be of this form:

conn = psycopg2.connect("host=localhost dbname=<dbname> user=<username>")

The underlying libpq driver that psycopg2 uses will then utilize the .pgpass file to get the password.