python connect to postgresql with libpq-pgpass

python postgresql libpq
ovod picture ovod · Mar 2, 2015 · Viewed 8.1k times · Source

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

khampson picture khampson · Mar 2, 2015

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.

Related questions

Can't subtract offset-naive and offset-aware datetimes

I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age. The problem I'm having is that both datetime.datetime.…

Read More
DatabaseError: current transaction is aborted, commands ignored until end of transaction block?

I got a lot of errors with the message : "DatabaseError: current transaction is aborted, commands ignored until end of transaction block" after changed from python-psycopg to python-psycopg2 as Django project's database engine. The code remains the same, just don't know …

Read More
How to set up a PostgreSQL database in Django

I'm new to Python and Django. I'm configuring a Django project using a PostgreSQL database engine backend, But I'm getting errors on each database operation. For example when I run manage.py syncdb, I'm getting: C:\xampp\htdocs\djangodir>…

Read More