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

Using Django Deployment
1

I posted this on SA but haven’t received any answers yet.

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.

2

What versions of Python and Django are you using?

3

Django 4.0.2, Python 3.8.2

Edit: Also, Postgres 14.2 if it matters.

4

I don’t know for sure, and I don’t have a Windows environment for Django handy to test, but you might try setting the environment variable for the location of the service file to see if that helps - or possibly try using it as a per-user file and not a global file.

1 Like
5

I already had a pg_service.conf file in my C:\Program Files\PostgreSQL\14\etc folder and a .pg_service.conf file in my C:\Users\myusername folder. However, setting the PGSERVICEFILE environment variable got me a little further. Now I’m getting these exceptions:

psycopg2.OperationalError: connection to server at “localhost” (::1), port 5432 failed: fe_sendauth: no password supplied

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

I’m now playing with the password-file-related environment variables to see if I can find the right recipe. No luck thus far, but I’ll try different scenarios and report back what I see.

6

OK, I finally got it to work with service and password files rather than listing them directly in Django’s settings.py. Here is what I needed to do:

  1. I had to create 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.
  2. I had to modify my database record in settings.py as follows (which is different from the Django docs):
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'localhost',
        'NAME': 'MyDbName',
        'OPTIONS': {
            'service': 'test_svc_1',
        },
    },
}

Two differences from the Django docs:

  • I needed to include the database name.
  • I couldn’t get it to work with a ‘passfile’ option, but it does work when I delete that.

Ken, thanks for pointing me in the right direction.

7

I was having similar issues with this as well but on linux. The impression you are left with reading the postgresql docs is that putting the service file into “/etc/postgresql-common” should just work. However, as per above, I only got it working once I set the environment variables PGSERVICEFILE and PGPASSFILE to point to their respective files. Setting these environment variables allows you place the files anywhere you like.

Once this was done, it worked fine as per the django docs.

1 Like
8

Clarify my earlier comment. PGSERVICEFILE seems to allow you to store the service file anywhere. PGPASSFILE seems to be ignored by django. The .pgpass needs to be in the same project folder as manage.py. I had assumed that because .pgass was not in my user home directory, it was using the environment variable. Moving .pgpass to a different location disabused that assumption.

9

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.

1 Like
10

Thanks a lot. It worked

11

If anyone is having issues with this on the linux side. check these

  • your .pg_service.conf is in your ~ dir (/home/user/.)
  • your .pgpass file is in the same dir as your manage.py OR full path specified in settings.py
  • your .pgpass file has the proper permissions set, chmod 600
2 Likes
13

Since this is not directly related to the original post, I suggest you open a new topic to discuss this.

Thanks!

14

thanks for your reply! I just created a new topic → django can't connect to postgresql database on Mac

15

This fixed my issue. I thought it was missing in the PSQL documentation at first, but I was looking at previous documentation. If you go to the current version (v16), it does say the correct file path.