20

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?

Improve this question
2
  • 2
    Are you using sudo -u postgres to run your Python script? Commented Nov 10, 2016 at 22:33
  • Thanks, running as sudo -u postgres fixes everything. Can you post this as an answer, so that I can accept? Commented Nov 10, 2016 at 22:37

4 Answers 4

Reset to default
27

You need to supply the host

conn = psycopg2.connect("dbname='template1' user='dbuser' host='localhost' password='dbpass'")
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Question specifies PEER authentication.
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.
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.
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.

Improve this answer

2 Comments

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.
@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).
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()
Improve this answer

6 Comments

This gives me FATAL: password authentication failed for user "postgres" and I thought password implies TCP authentication. How do I set up localhost?
Now I'm back to psycopg2.OperationalError: FATAL: Peer authentication failed for user "postgres"
Can you enable password less login and remove the pw field from the connection? sudo -u postgres psql postgres
Look here to change postgres pw - stackoverflow.com/questions/12720967/…
I've read that putting a password on the default postgres user changes the authentication, and I'd like to avoid that. Thanks
|
2

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.