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

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.

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