Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django connection to PostgreSQL: "Peer authentication failed"

I took a peek at the exception, noticed it had to do with my connection settings. Went back to settings.py, and saw I did not have a Host setup. Add localhost and voila.

My settings.py did not have a HOST for MySQL database, but I needed to add one for PostgreSQL to work.

In my case, I added localhost to the HOST setting and it worked.

Here is the DATABASES section from my settings.py.

DATABASES = { 
    'default': { 
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': '<MYDATABASE>', 
        'USER': '<MYUSER>', 
        'PASSWORD': '<MYPASSWORD>', 
        'HOST': 'localhost', # the missing piece of the puzzle 
        'PORT': '', # optional, I don't need this since I'm using the standard port
    } 
}

That is probably because your script is running under some other user than the one you are trying to connect with (myuser here). In this case, peer authentication will fail. Your solution with HOST: "localhost" works because you are not using peer auth anymore. However, it is slower than HOST: "" because instead of using unix sockets, you use TCP connections. From django docs:

If you’re using PostgreSQL, by default (empty HOST), the connection to the database is done through UNIX domain sockets (‘local’ lines in pg_hba.conf). If you want to connect through TCP sockets, set HOST to ‘localhost’ or ‘127.0.0.1’ (‘host’ lines in pg_hba.conf). On Windows, you should always define HOST, as UNIX domain sockets are not available.

If you want to keep using sockets, correct settings in pg_hba.conf are needed. The most simple is:

local   all         all                               trust

while commenting out all other local lines in the config. Note that reloading postgres is needed for this change to take effect.

But if multi-user production machine is in question, you might want to use something more secure like md5 (see here for explanation of various authentication methods).


Better than fully trust is just to set it to md5.

# "local" is for Unix domain socket connections only
local   all         all                           md5

I fixed this by editing the bottom of /etc/postgres/9.1/main/pg_hba.conf to be (changing md5 to trust; NOTE this means there will be no database password, which may not be what you want)

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               trust
# IPv4 local connections:
host    all         all         127.0.0.1/32          trust
# IPv6 local connections:
host    all         all         ::1/128               trust

I just stumbled upon the same problem but wanted to use unix sockets as clime said, but by still using the peer method. I mapped my system-username with the postgres-username inside the pg_hba.conf, which is working with the peer method.

Inside pg_hba.conf i added:

local all all peer map=map-name

Inside pg_ident.conf i added:

map-name mysystem-username mypostgres-username