Psycopg2 peer authentication for user postgres

Question

I seem to have correctly installed PostgreSQL 9.5.5. and Psycopg2 on Ubuntu 16.04, and can log in via:

sudo -u postgres psql

If I then issue \conninfo, I get the following:

You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".

Surely I should be able to connect via psycopg2 in the same fashion as shown here, but the script:

#!/usr/bin/python
import psycopg2
conn = psycopg2.connect("dbname=postgres user=postgres") 
conn.close()

gives me:

psycopg2.OperationalError: FATAL:  Peer authentication failed for user "postgres"

I only want PostgreSQL for personal usage, so I don't want to enable TCP authentication.

How do I correctly use peer authentication with user "postgres" in Psycopg2?

Thanks, running as `sudo -u postgres` fixes everything. Can you post this as an answer, so that I can accept? — Yuri Lifanov, Nov 10 '16 at 22:37

score 27 · Answer 1 · answered Apr 11 '17 at 08:47

27

You need to supply the host

conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")

answered Apr 11 '17 at 08:47

moohaad

1

Question specifies `PEER` authentication. – user1071847 Jan 24 '18 at 20:51
1

This is not the correct answer for the user "postgres" but explicitly adding "localhost" (rather than an empty string) as the host in django's settings.py made the difference for me. – mightypile Nov 13 '18 at 13:06
1

For me it also made the difference just to add `host='localhost'`, now it's working. The other answer saying "run your python script with `sudo -u postgres`" is not my preferred solution because I want this authentification to happen within the python script, or even be able to run it interactively in a jupyter notebook or iPython. – Andreas L. Feb 01 '21 at 18:43

score 21 · Accepted Answer · answered Nov 11 '16 at 02:39

21

Peer authentication works by comparing the Postgres username in your connection string to the name of the Linux user who is running the script.

Try running your Python script with sudo -u postgres.

answered Nov 11 '16 at 02:39

Nick Barnes

What if your script needs as well to write some files (such as log files) out to a directory owned by $USER, the logged in user, which is not the `postgres` user? Because when running the script with `sudo -u postgres` you will hit the following error: `PermissionError: [Errno 13] Permission denied: /home/${USER}/.../my_log_file`. – swiss_knight Feb 05 '20 at 14:54
@s.k: If you created a database user called `$USER`, then you could use peer authentication without the `sudo`. You could also separate the file-writing and the database access (e.g. write a sub-script which logs to `stdout`, call the script with `sudo` and redirect the output). If you really need to connect as `postgres`, but can't run the rest of the script as `postgres`, then you'll need to enable a different authentication method (though the security implications of this are a bit much to cover in comments). – Nick Barnes Feb 06 '20 at 09:42

Illusionist · Answer 3 · 2016-11-10T21:36:41.183

3

This is sort of how yoru call should look like.

!/usr/bin/python
import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="postgres", port=5432)

conn.close()

edited Nov 10 '16 at 21:36

answered Nov 10 '16 at 21:30

Illusionist

This gives me `FATAL: password authentication failed for user "postgres"` and I thought password implies TCP authentication. How do I set up localhost? – Yuri Lifanov Nov 10 '16 at 21:34
1

Now I'm back to `psycopg2.OperationalError: FATAL: Peer authentication failed for user "postgres"` – Yuri Lifanov Nov 10 '16 at 21:37
Can you enable password less login and remove the pw field from the connection? sudo -u postgres psql postgres – Illusionist Nov 10 '16 at 21:41
Look here to change postgres pw - http://stackoverflow.com/questions/12720967/how-to-change-postgresql-user-password – Illusionist Nov 10 '16 at 21:41
I've read that putting a password on the default postgres user changes the authentication, and I'd like to avoid that. Thanks – Yuri Lifanov Nov 10 '16 at 22:38
Question specifies `PEER` authentication. – user1071847 Jan 24 '18 at 20:53

score 2 · Answer 4 · answered Jan 24 '20 at 16:12

these are the some authentication method of postgresql

peer means it will trust the identity (authenticity) of UNIX user. So not asking for a password.

md5 means it will always ask for a password, and validate it after hashing with MD5.

trust means it will never ask for a password, and always trust any connection.

I your case youe have to cahnge like this:

host all all 127.0.0.1/32 ident

to

host all all 127.0.0.1/32 md5

and

host all all ::1/128 ident

to

host all all ::1/128 md5

4 Answers4