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

Solution 1:

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.

Solution 2:

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

host    allall127.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;'

Solution 3:

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

Solution 4:

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 allall127.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!

Solution 5:

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:

importenvironenv= 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:
localall postgres md5
localallall peer