52

I have a postgres database on my localhost I can access without a password

$ psql -d mwt
psql (8.4.12)
Type "help" for help.

mwt=# SELECT * from vatid;
  id   | requester_vatid |...
  -----+-----------------|...   
  1719 | IT00766780266   |...

I want to access that database from django. So I put in DATABASES

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mwt',
        'USER': 'shaoran',
        'HOST': 'localhost'
    }
}

Since I don't need a password to access my test database I didn't provide any PASSWORD value in the settings.

$ ./manage.py shell
>>> from polls.models import Vatid
>>> Vatid.objects.all()
  connection_factory=connection_factory, async=async)
  OperationalError: fe_sendauth: no password supplied

I tried using PASSWORD: '' but I get the same error message. I tried to use PASSWORD: None but that didn't help either.

I've been searching the django documentation about this but I cannot find anything useful. It is possible to configure django.db.backends.postgresql_psycopg2 to accept empty password?

j-i-l
  • 10,281
  • 3
  • 53
  • 70
Pablo
  • 13,271
  • 4
  • 39
  • 59
  • You can configure this either over `localhost` or Unix domain sockets. [Here](https://stackoverflow.com/a/48910290/1622937) is explained how both cases work. – j-i-l Apr 17 '18 at 23:08
  • @jojo well thanks for the comment. It so long ago, I don't even remember what I did to fix it, the project is long dead anyway. – Pablo Apr 18 '18 at 18:22
  • Let's hope it will be of use for others then! In contrary to the mentioned project, the potential pitfalls with this configuration are still up and running, both facts are certainly unfortunate. – j-i-l Apr 18 '18 at 19:20
  • @jojo based on the number of upvotes for all the answers and the question, this question has definitely helped other people as well. And now I remember, the problem was (as you stated in your answer) that I used the `HOST` parameter. – Pablo Apr 18 '18 at 19:34

5 Answers5

71

Surprisingly, the answer is to not specify a host at all. If you do this,

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mwt',
    }
}

Then psycopg2 will connect using a Unix socket in the same manner as psql. When you specify a HOST, psycopg2 will connect with TCP/IP, which requires a password.

joerick
  • 16,078
  • 4
  • 53
  • 57
  • 1
    still failed for me: `django.db.utils.OperationalError: FATAL: Peer authentication failed for user "www-data"`; had to add `local all www-data trust` in hba.conf *before* the default `local all postgres peer` line. – jcomeau_ictx Dec 24 '15 at 02:34
  • 1
    @jcomeau_ictx I [added a complete explanation](https://stackoverflow.com/a/48910290/1622937) of how this works via Unix domain (as implicitly suggested in this answer) as well as via `localhost` as suggested by [the accepted answer](https://stackoverflow.com/a/11192485/1622937). – j-i-l Apr 17 '18 at 23:04
  • I could login using PGAdmin after setting postgres to trust but Django still failed to make a connection and posed fe_authentication error. Only this solution worked! Thanks! – ratulotron Feb 09 '19 at 13:05
30

To avoid using a password in Django settings.py change md5 to trust in this line of pg_hba.conf:

host    all             all             127.0.0.1/32            trust

For a detailed understanding of the postgres security configurations read this doc.

To locate this file:

sudo -u postgres psql -c 'SHOW hba_file;'
b_dev
  • 2,568
  • 6
  • 34
  • 43
13

Check your pg_hba.conf to allow connection from localhost by user shaoran, then either provide the password of shaoran in Django settings or trust the user in pg_hba.conf

The fact that you could connect through psql is because psql -d mwt uses some default connection values which are set as trusted in pg_hba.conf. For example, on my machine, the default host is local socket instead of localhost

okm
  • 23,575
  • 5
  • 83
  • 90
  • I don't think this is an problem with pg_hba.conf. Actually this mwt database is used in a rails application where I don't specify any username nor any password in my rails config and I don't have any issue with rails accessing my database. My Rails config file is: # config/database.yml development: adapter: postgresql database: mwt I'm quite new to django and I wanted to make a quick test of multiple database connections and that's why I tried using a database I already had on my system. – Pablo Jun 25 '12 at 15:45
  • @Pablo The key here is you've specified `host=localhost` and `user=shaoran`, which is probably different from the DSN used by `psql` and `Rails` – okm Jun 25 '12 at 15:51
  • Upps, I didn't realize that the code blocks don't work inside the comment field. Anyway, I made a huge mistake because my current user @ work is yanez and not shaoran (my user @ home). I changed USER to "yanez" but I still get the same error. I even deleted the **USER** entry from settings.py so that the configuration matches the one of rails but I still get that error :( – Pablo Jun 25 '12 at 15:51
  • @Pablo I've no idea how does Rails treat this, Django has [own defaults](https://docs.djangoproject.com/en/dev/ref/settings/#databases) – okm Jun 25 '12 at 15:53
  • 1
    thank for your help. Apart from the stupid error with the wrong user names the problems here was the **host** entry. I assume that without it psql, rails and psycopg2 access to the local database through a file socket instead of network socket. – Pablo Jun 25 '12 at 15:56
  • I have a similar issue, if I connect using psql -U postgres it prompts me for a password but when using psycopg2 from python, it doesn't ask for password – radtek Apr 17 '14 at 04:40
  • @radtek You may have different settings for `psql` and `psycopg2`: in shell check the `Connection options` section of the output of `psql --help`; in Python shell check the `dsn` property of `psycopg2` `connection` object. – okm Apr 18 '14 at 02:09
  • It actually wasn't that, I forgot I had the .pgpass file in my home directory. If you don't specify a password, psycopg2 uses the info stored in .pgpass as does psql shell – radtek Apr 21 '14 at 14:21
8

As someone facing the same problem and only finding a solution after combining various parts from the here present answers and other findings from google-ing trials, I decided to put together a, hopefully, complete answer:

First thing to note:

Both putting 'HOST' or omitting it in settings.py are viable options. However, whether you put 'HOST' or not affects how you have to setup the postgresql configuration.

Omitting 'HOST' as in joerick's answer leads to psycopg2 trying to connect by Unix domain socket. On the other hand, if your configuration contains the 'HOST' key, psycopg2 will attempt to connect over IPv4/6 localhost. This makes a great difference as postgresql authentication configuration (/etc/postgresql/x.x/main/pg_hba.conf) is specific to either of those ways to connect.

Take home message:

Make sure to choose the connection type you also configured in your postgresql authentication configuration.

Second thing to note:

The postgresql authentication configuration (/etc/postgresql/x.x/main/pg_hba.conf) cares about the order of entries.

The docs are actually very clear about this, (yet I managed to fall in the local all all peer trap):

The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

Take home message:

Make sure that any specific rule comes BEFORE broader rules.

Now that we know all of that, here is how to get access without password, once with 'HOST' (so over localhost) and once without (so over Unix socket).

Connection over localhost

Specify 'HOST': 'localhost' in the database configuration of your settings.py:

# ...
'HOST': 'localhost',
# ...

'PASSWORD' is not needed and can be omitted.

The rule you need to set in your postgresql authentication configuration (/etc/postgresql/x.x/main/pg_hba.conf) is for TYPE host.

Mind the ordering of the rules. So, if you have a user 'my_user' that should be able to access the database 'my_database' without a password, a correct configuration would look like this:

# RIGHT WAY...
host my_database my_user 127.0.0.1/32 trust
host my_database my_user ::1/128 trust
# ...
host all all 127.0.0.1/32 peer
# ...

Inverting the ordering will, result in a no password supplied error.

Connecting over Unix domain socket

Do not put the 'HOST' key in your settings. 'PASSWORD' is not needed either.

In the postgresql authentication configuration, the access over Unix domain sockets is managed with rules of TYPE local.

If 'my_user' should get trusted (no password required) access to a database 'my_database' you need a line like this:

local my_database my_user trust

Concerning where to put this line, the rule here is that you need to put it before any broader rule in terms of DATABASE and USER. To be safe, I recommend putting it at the beginning of /etc/postgresql/x.x/main/pg_hba.conf. If your pg_hba.conf file looks like this:

# RIGHT WAY...
local my_database my_user trust
# ...
local all all peer
# ...

you are good to go without password. However, if it looks like this: 

# WRONG WAY! ...
local all all peer
# ...
local my_database my_user trust
# ...

you'll need to provide a password.

Final note:

Don't forget to restart the postgresql service after modification of /etc/postgresql/x.x/pg_hba.conf:

sudo service postgresql restart

 

         Hope this was helpful. Happy coding!

j-i-l
  • 10,281
  • 3
  • 53
  • 70
2

I live with 'local all all peer' only. The connection string should be without host, user and password: postgres:///mydbname.

Without environ module it looks so:

DATABASES = {
    'default': {'NAME': 'mydatabase', 'USER': '', 'PASSWORD': '', 'HOST': '', 'PORT': '', 'ENGINE': 'django.db.backends.postgresql_psycopg2'}
}

With environ module:

import environ
env = environ.Env()
DATABASES = {
    'default': env.db('DATABASE_URL', default='postgres:///mydatabase'),
}

where .env file contains no DATABASE_URL setting.

Only for user 'postgres' I use md5, but from psql/pgadmin3 only, not from django code.

# /etc/postgresql/version/cluster/pg_hba.conf:
local all postgres md5
local all all peer
mirek
  • 1,140
  • 11
  • 10