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

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

pythondjangopostgresql


Solution 1:[1]

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.

Solution 2:[2]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 DaveB
Solution 2 Rodrigo Achcar

Related Questions