10

Postgres' C library libpq documentation talks about a more secure way to connect to a DB without specifying password in source code.

I was not able to find any examples of how to do it. How to make my Postgre Server use this file? Please help.

legends2k
  • 31,634
  • 25
  • 118
  • 222
ovod
  • 1,118
  • 4
  • 18
  • 33

4 Answers4

30

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.

khampson
  • 14,700
  • 4
  • 41
  • 43
  • 1
    But how psycopg2 connection in python program should looks like to make it look into ~/.pgpass file? – ovod Mar 02 '15 at 08:18
  • 3
    I simply need to add, that if you are going to this on your *nix box, you must also run the command `chmod 0600 ~/.pgpass` If the permissions are less strict than this, the file will be ignored. Running this command along with khampson's answer was the solution for me. – Corey Levinson Apr 11 '19 at 18:12
  • and what if the file is not in a default location? `passfile` does not seem to pick it up. – thomi Oct 07 '22 at 15:00
0

just adding to @khampson's answer, I could only get this to work (in windows) if I added the PGPASSFILE environment variable (and subsequently restart pycharm) even though my pgpass file is in the default location (%APPDATA%\postgresql\pgpass.conf).

QwertyHarry
  • 11
  • 2
-1

Well. You asked for .pgpass but...

There is also "The Connection Service File"

By default, the per-user service file is named ~/.pg_service.conf. A different file name can be specified by setting the environment variable PGSERVICEFILE

Look at https://www.postgresql.org/docs/15/libpq-pgservice.html

Format is ".INI". Parameters are connection parameters. Example:

# this is a comment in my service file
[a_service]
host=dbserver.loc
port=5432
dbname=thedbname
user=thebest
password=bho
application_name=ifyoulike

# Here is another service
[another_service]
host=etc
port=etc

Access via psql:

psql service=a_service

Access via psycopg2:

db = psycopg2.connect('service=a_service')

Access via sqlalchemy:

eng = sqlalchemy.create_engine('postgresql:///?service=another_service')

Marco Vaccari
  • 1
  • 2
-7
#!/usr/bin/python
import psycopg2
import sys
import pprint

def main():
    conn_string = "host='127.0.0.1' dbname='yourdatabsename'  user='yourusername' password='yourpassword'"
print "Connecting to database\n ->%s" % (conn_string)
conn = psycopg2.connect(conn_string)
cursor = conn.cursor()
cursor.execute("SELECT * FROM tablename")


records = cursor.fetchall()
pprint.pprint(records)

if __name__ == "__main__":
    main()
Sam Divya Kumar
  • 11
  • 3
  • Please consider editing your post to add more explanation about what your code does and why it will solve the problem. An answer that mostly just contains code (even if it's working) usually wont help the OP to understand their problem. – Drenmi Dec 12 '15 at 15:05
  • 1
    The question clearly states that the password should not be specified in the code and this example does exactly the opposite. – Saket Oct 30 '19 at 13:07