Can't connect to Postgres from Django using a connection service file (on Windows)

Question

[NOTE: This is using Django 4.0.2, Python 3.8.2, and Postgres 14.2.]

I have successfully set up Django and Postgres, and I can get them to work together when I put all of the Postgres parameters in the Django settings.py file. However, as shown in the Django documentation, I want to use a Postgres connection service file instead. I've created a service file (C:\Program Files\PostgreSQL\14\etc\pg_service.conf) that looks like this:

[test_svc_1]
host=localhost
user=django_admin
dbname=MyDbName
port=5432

Launching Postgres from the command line with this file seems to work fine, as it prompts me for the password:

> psql service=test_svc_1
Password for user django_admin:

However, when I try to make migrations with Django, I get the following error:

Traceback (most recent call last):
File "C:\...\django\db\backends\base\base.py", line 219, in ensure_connection
   self.connect()
File "C:\...\django\utils\asyncio.py", line 26, in inner
   return func(*args, **kwargs)
File "C:\...\django\db\backends\base\base.py", line 200, in connect
   self.connection = self.get_new_connection(conn_params)
File "C:\...\django\utils\asyncio.py", line 26, in inner
   return func(*args, **kwargs)
File "C:\...\django\db\backends\postgresql\base.py", line 187, in get_new_connection
   connection = Database.connect(**conn_params)
File "C:\Users\...\psycopg2\__init__.py",line 122, in connect
   conn = _connect(dsn, connection_factory=connection_factory, **kwasync) 
psycopg2.OperationalError: definition of service "test_svc_1" not found

There were other exceptions related to this one, such as:

django.db.utils.OperationalError: definition of service "test_svc_1" not found

but they all pointed back to not finding the service "test_svc_1".

Here is an excerpt from my Django settings.py file. Adding the NAME parameter got me a little further along, but I shouldn't need to include it once Django(?) finds the connection service file.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'localhost',
        'NAME': 'MyDbName',
        'OPTIONS': {
            'service': 'test_svc_1',
            'passfile': '.my_pgpass',
        },
    },
}

Any thoughts as to what I'm missing? Worst case, I guess that I can revert to using environment variables and have the settings.py file refer to them. But I'd like to understand what I'm doing wrong rather than giving up.

Thanks for any guidance.

Answer 1

I report my solution as it can be helpful for someone else. I discovered that Windows created the file as "pg_service.conf.txt" so you need to rename it to "pg_service.conf" without the ".txt" Pretty simple but it worked.

Answer 2

I was facing the same challenge but I manage to solve it with the following steps: Run the following steps in the command line

 - psql -U postgres
 - alter user username with encrypted password 'password';
 - psql -U username -d database_name

So what happens is that, you login into your postgresql database using the default superuser, postgres, then depending on the user you are using in your django settings.py under the database, so in my case I use username, you alter the user with encrypted password, so you put your password in the specified password portion, and lastly try logging in again using the username and the database name you specified in your django settings.py.

Answer 3

Using Process Monitor I found that the location of the file is in %APPDATA%\Roaming\postgresql\.pg_service.conf Putting the file in that location didn't have any problems and I didn't have to set any system variables. In the file I put the host, port, user and password and in django settings I only specified the database and the service. The postgresql documentation is apparently not correct.

Answer 4

In case anyone runs across this question, I finally got it to work with service and password files thanks to some guidance from Ken Whitesell on the Django forum. Here is what I needed to do:

I had to create Windows environment variables for PGPASSFILE and PSERVICEFILE that point to the two files. I placed them both in C:\Program Files\PostgreSQL\14\etc, although I’m guessing that they can go into other directories as long as the environment variables point to them. Next, I had to modify my database record in Django's settings.py as follows (which is different from what the Django docs specify):

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'localhost',
        'NAME': 'MyDbName',
        'OPTIONS': {
            'service': 'test_svc_1',
        },
    },
}

The service name in OPTIONS needs to match the service name in the PGSERVICEFILE.

Two differences from the Django docs:

• I needed to include the database name.
• I couldn’t get it to work with a ‘passfile’ entry in the OPTIONS dictionary, but it does work when I delete that. Perhaps I was doing something wrong, but I tried several different options and folders.

EDIT: Let me add some explanation points to avoid misunderstanding.

Django documentation is correct, but I would add one note about defining path on Windows (with forward slashes /) as they noted in other documents, where need to write path to file.

Your way above (without passfile key is working because you added PGPASSFILE environment variable and psycopg2 reads path from it.

But you can specify path to pgpass.conf directly (or retrieve it from environment variable, see commented line below).

Also DB host and DB name should be placed in .pg_service.conf file. See all parameter key words in PostgreSQL docs.

Considering this, the following files example should work (taking your settings values):

# C:\Program Files\PostgreSQL\14\etc\.pg_service.conf

[test_svc_1]
host=localhost
user=django_admin
dbname=MyDbName
port=5432

# Django `settings.py`
import os

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'service': 'test_svc_1',
            # 'passfile': os.getenv("PGPASSFILE"),
            'passfile': "C:/Program Files/PostgreSQL/14/etc/pgpass.conf",
        },
    }
}

And of course, line with correct credentials in pgpass.conf must be present:

#hostname:port:database:username:password
localhost:5432:MyDbName:django_admin:your_secret

Answer 5

Turns out that the service name on the ~/.pg_service.conf file must be the database name, i.e: [djangodb] . After that it was not necessary to pass DBNAME. And the environment variable should call the service name only, not the file path. I also was having problems in Django 4 to find my env vars on the settings.py file with os.environ.get('VAR'). After changing to str(os.getenv('VAR')) things got smooth. Bye.