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?

Answer 1

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.

Answer 2

You need to supply the host

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

Answer 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()

Answer 4

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