Django connection to PostgreSQL: "Peer authentication failed"

OperationalError at /admin/


FATAL:  Peer authentication failed for user "myuser"

This is the error I am receiving when I try to get to my Django admin site. I had been using MySQL database no problem. I am new to PostgreSQL, but decided to switch because the host I ultimately plan to use for this project does not have MySQL.

Therefore, I figured I could go through the process of installing PostgreSQL, run a syncdb and be all set.

Problem is that I cannot seem to get my app to connect to the database. I can login to PostgreSQL via command line or desktop app that I downloaded. Just not in the script.

Also, I can use manage.py shell to access the db just fine.

Any thoughts?

57901 次浏览
小开
最佳答案

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
}
}
小开

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
小开

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

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

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).

小开

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

提交答案