Django 4 connection to Postgresql using passfile: "fe_sendauth: no password supplied"

Question

Hello SO & Django community,

My problem is related to Django 4, as the feature to use passfile to connect to Postgres has appeared in this version. Though I have went through the similar error message related questions about previous versions, I had no success in solving my problem.

What I am trying to do

I want to connect Postgres database DB_MyProject to a django MyProject. In Django 4, you may use a passfile instead of providing all user/password information in the settings.py. The documentation about this new feature is here. The concept of password file in Postgres is explained here, you may also read about connection service here.

Having followed these docs to my best understanding, I have done the following:

1. Created the following DATABASES entry in settings.py of the Django project, as advised here:

    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'service': 'db_service',
            'passfile': '.pgpass',
        },
    }
} 

2. In the pg configuration directory (pg_config --sysconfdir), created file pg_service.conf with following information, as in pg docs and django docs:

[db_service]
host=localhost
port=5432
dbname=DB_MyProject
user=my_postgres_user

3. Created a .pgpass file, as in pg docs:

localhost:5432:DB_MyProject:my_postgres_user:my_passwd

Now, this .pgpass file exists in several locations, as a result of the quest to make this work:

• in pg configuration directory (pg_config --sysconfdir)
• in my regular user home directory ~/
• in django MyProject root directory

all of these files are exact copies with the same permission level:

-rw-------  1 my_regular_user  my_group  52 Mar  2 18:47 .pgpass

4. I also created a DB in PGAdmin with the specified name, and made sure user has permission with the specified password.

5. Now I assumed it should be working OK. But instead, when I try to makemigrations or migrate or runserver, I get this:

django.db.utils.OperationalError: connection to server at "localhost" (::1), port 5432 failed: fe_sendauth: no password supplied

What have I tried already

• verified compatibility: Django v4.0.3, PostgreSQL v14.0, psycopg2 v2.9.3
• created an environment variable PGPASSFILE with a path to ~./.pgpass
• changed ownership of ~/.pgpass from my_regular_user to my_postgres_user for testing, was given the same result
• installed direnv, created .envrc file in the project root directory, containing:

export PGPASSFILE=~/.pgpass

Thank you in advance for your help. I would also be happy to be pointed out any misconceptions in my thinking, as I am a newbie developer.

Answer 1

1. Created the following DATABASES entry in settings.py of the Django project:

    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'service': 'db_service',
            'passfile': '.pgpass',
        },
    }
} 

2. In user home directory, created file ~/.pg_service.conf with following information:

[db_service]
host=localhost
port=5432
dbname=DB_MyProject
user=my_postgres_user

3. Created a .pgpass file in django MyProject root directory and change the permissions of the file chmod 0600 MyProject/.pgpass:

localhost:5432:DB_MyProject:my_postgres_user:my_passwd

Answer 2

Don't have enough reputation to comment so to add to gtlee's answer, I too could only get password file to work if it was in the home directory of the django project.

It appears that whilst setting the environment variable PGSERVICEFILE does allow you to place the service file anywhere on your linux system, the environment variable PGPASSFILE is simply ignored. A bit annoying as it adds another file to add to .gitignore.

Alternative solution is to use the service file and then pass in the password as normal but store it in an environment variable.

Answer 3

The only issue I see with @gtlee answer is that you're storing your passfile inside your project. This is generally not good practice if you plan on pushing your code to a code repository. You want to keep your credential files outside of your codebase. This is what I did:

settings.py

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': os.environ.get('DATABASE_NAME'),
    'USER': os.environ.get('DATABASE_USER'),
    'PASSWORD': os.environ.get('DATABASE_PASSWORD'),
    'HOST': 'localhost',
    'PORT': '5432',
}
}

Make sure you import the os module in your settings.py file:

import os

Then on my Ubuntu server where my PostgreSQL server is hosted I set these environment variables in my ~/.bashrc file at the very end of the file:

export DATABASE_NAME=mydatabase
export DATABASE_USER=myuser 
export DATABASE_PASSWORD=mypassword

Next just run the following command to reset the changes:

source ~/.bashrc

Then once you run python3 manage.py runserver again it should work.

Happy coding! :)

Answer 4

I found that if you do it the way postgresql is configured it'll work without the need to mention the password file in settings.

Django docs
Postgres docs

Create the pgpass.conf file (same location as the .pg_service.conf file) then remove the 'passfile': '.my_pgpass' entry in OPTIONS

Doing this will cause Django to ignore where the password file is and Postgresql will do its default process.

DATABASES = {
"default": {
    "ENGINE": "django.db.backends.postgresql",
    "OPTIONS": {
        "service": "my_service",
    },
}

}