python can not use .pgpass while connecting to PostgreSQL

Question

I can not use .pgpass file while connecting to postgresql db using python script((( My python script to connect is without password:

conn = psycopg2.connect("dbname='postgres' user='postgres' host='192.168.136.129'");

My .pgpass:

*:*:*:postgres:password

is located in /var/lib/postgresql - home directory for postgres user. However when I connect to db locally:

psql -U postgres

no password is asked. But for python error is raised:

root@debian:/python_codes/Junior/Level1/DB1/Generator_py# python connect_db.py 
Unable to connect to db...
Traceback (most recent call last):
  File "connect_db.py", line 34, in connectdb
    conn = psycopg2.connect("dbname='postgres' user='postgres' host='192.168.136.129'");
  File "/usr/lib/python2.7/dist-packages/psycopg2/__init__.py", line 179, in connect
    connection_factory=connection_factory, async=async)
OperationalError: fe_sendauth: no password supplied

The .pgpass file must be in the home directory of the user launching the script. — Clodoaldo Neto
– Clodoaldo Neto, Commented Mar 8, 2015 at 21:10
If the script is indeed on the another server then I suggest you copy .pgpass file to the home directory of the UNIX user running the script on another server. — Mikko Ohtamaa
– Mikko Ohtamaa, Commented Mar 8, 2015 at 23:02
I coped file in all directories: the same result. Locally it works: no password is asked, but python script does not work((( — ovod
– ovod, Commented Mar 9, 2015 at 9:35

Daniel Vérité · Accepted Answer · 2015-03-09 14:27:14Z

4

Based on the prompt from this output:

root@debian:/python_codes/Junior/Level1/DB1/Generator_py# python connect_db.py

you're running the commmand as Unix user root, so the corresponding .pgpass file must be located in root's HOME directory, which means not /var/lib/postgresql but probably /root or /

Also its permissions must be restricted to readable by the owner, that is, chmod 600 $HOME/.pgpass

answered Mar 9, 2015 at 14:27

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Loh Meng Chee · Accepted Answer · 2021-12-09 23:09:18Z

0

To prevent user from launching pg_dump with a permanent .pgpass or use PGPASSWORD in command, one could create .pgpass just before pg_dump starts and delete it off 3 seconds later (inside a compiled program).

subprocess.Popen(echo 'hostname:port:database:username:password' > /home/user/.pgpass, shell=True).wait()

subprocess.Popen(echo 'sleep 3; rm /home/user/.pgpass; rm /home/user/remove-pgpass.sh' > /home/user/remove-pgpass.sh, shell=True).wait()

subprocess.Popen(chmod 600 /home/user/.pgpass, shell=True).wait()

subprocess.Popen(chmod +x /home/user/remove-pgpass.sh, shell=True).wait()

subprocess.Popen(/home/user/remove-pgpass.sh, shell=True)

subprocess.Popen(pg_dump -h localhost -d user-db -U db-admin > mydbdump.sql, shell=True).wait()

edited Dec 9, 2021 at 23:09

answered Dec 9, 2021 at 4:52

Loh Meng Chee

12 bronze badges

Collectives™ on Stack Overflow

python can not use .pgpass while connecting to PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related