connect to a DB using psycopg2 without password

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?

Answers


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


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.


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;'

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!


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

Need Your Help

"gem install therubyracer -v '0.10.2'" on osx mavericks not installing

ruby-on-rails macos gem therubyracer osx-mavericks

Trying to install therubyracer on mavericks using "gem install therubyracer -v '0.10.2'" but i am getting the following error:

How to exclude file only from root folder in Git

git gitignore

I am aware of using .gitignore file to exclude some files being added, but I have several config.php files in source tree and I need to exclude only one, located in the root while other keep under