Support using service names for tests on PostgreSQL.

[Shane Ambler]

In django 4.0 the postgresql DATABASE settings were changed to allow use of a .pg_service.conf and .pgpass file being defined in the OPTIONS value.

Using the old config works fine in both runserver and test.

Using the new config works for runserver but fails for test.

In settings I have :-

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'OPTIONS': {
            'service': 'yfsm_pgdb',
            'passfile': '.yfsm_pgpass',
        },
    },
}

I have the user password entry allowing login to any database (using *) I have also tried adding an entry with test_dbname. I also tried adding an entry in .pg_service.conf as [test_yfsm_pgdb]

running tests I see django.db.utils.OperationalError: fe_sendauth: no password supplied indicating that the password file is not used.

It would appear that when testing, the new OPTIONS settings are ignored.

[Shane Ambler]

Looking at the pull request for the OPTIONS change, I find that I can use the service name django_test. This does not appear to be documented anywhere.

While I wouldn't initially agree with using one login for testing all projects, use of this pg service name needs to be documented.

[Shane Ambler]

I failed to get my setting file right when I checked that, the use of the django_test service name is part of the django project tests.

The use of postgresql service name still appears broken with testing

[Mariusz Felisiak]

If you want to user PostgreSQL's password files, all required databases must be included, i.e. postgres, dbname, test_dbname, e.g. the following configuration works for me

localhost:5432:postgres:postgres_username:SeCrEtPassworD
localhost:5432:dbname:postgres_username:SeCrEtPassworD
localhost:5432:test_dbname:postgres_username:SeCrEtPassworD

settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'localhost',
        'PORT': 5432,
        'NAME': 'dbname',
        'USER': 'postgres_username',
        'OPTIONS': {
            'passfile': '.custompgpass',
        }
    },
}

[Shane Ambler]

Replying to Mariusz Felisiak:

If you want to user PostgreSQL's password files, all required databases must be included, i.e. postgres, dbname, test_dbname, e.g. the following configuration works for me

localhost:5432:postgres:postgres_username:SeCrEtPassworD
localhost:5432:dbname:postgres_username:SeCrEtPassworD
localhost:5432:test_dbname:postgres_username:SeCrEtPassworD

Yes, I tried both the test_dbname and * to match any dbname and it still fails.

settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'HOST': 'localhost',
        'PORT': 5432,
        'NAME': 'dbname',
        'USER': 'postgres_username',
        'OPTIONS': {
            'passfile': '.custompgpass',
        }
    },
}

While you are maintaining both old and new config variations, all will continue to work for you.

If you remove the old, no longer documented, settings of HOST PORT NAME USER then your setup will work for runserver but not test

The pg docs ​https://docs.djangoproject.com/en/4.0/ref/databases/#postgresql-notes-1 only documents the new method of specifying service and passfile in the OPTIONS - this documented setup does not support running tests with postgresql databases.

[Mariusz Felisiak]

While you are maintaining both old and new config variations, all will continue to work for you.

If you remove the old, no longer documented, settings of HOST PORT NAME USER then your setup will work for runserver but not test
The pg docs ​https://docs.djangoproject.com/en/4.0/ref/databases/#postgresql-notes-1 only documents the new method of specifying service and passfile in the OPTIONS ...

Using ['OPTIONS']['service'] and ['OPTIONS']['passfile'] is not a "new" or only supported way for the DATABASES settings. It's a PostgreSQL-specific option that you may use that's why it's documented only in the "PostgreSQL notes". Using HOST, PORT, NAME, USER, and PASSWORD is still fully supported for PostgreSQL, see ​docs.

... this documented setup does not support running tests with postgresql databases.

NAME is used in many places for test setup, including cloning a test database, that's why it doesn't work with only service specified.

[Shane Ambler]

Replying to Mariusz Felisiak:

Using ['OPTIONS']['service'] and ['OPTIONS']['passfile'] is not a "new" or only supported way for the DATABASES settings.

It looks like it has been available in "main" for a bit over a year, but it is still "new" as it has only been available in one public release. Yes, the old (or generic) method still works and will work properly if it is the only config docs that you find.

Using ['DATABASE']['NAME'] and ['DATABASE']['USER'] will work for runserver as well as test and they will override any service file settings.

We shouldn't need to duplicate login details in two places to get basic functionality.

I see having tests fail on the first tutorial as a big entry barrier for new users.

[Mariusz Felisiak]

It looks like it has been available in "main" for a bit over a year, but it is still "new" as it has only been available in one public release.

Right, good point, it's a new feature so this ticket should be marked as a release blocker.

[Shane Ambler]

Just a suggestion for whoever looks into this :

I expect that the service name is being passed to psycopg2, making altering the dbname awkward, in that case I think prepending 'test_' to the service name would be a viable solution. Then the docs would show two service entries to support testing in the example.

[Mariusz Felisiak]

Replying to Shane Ambler:

Just a suggestion for whoever looks into this :

I expect that the service name is being passed to psycopg2, making altering the dbname awkward, in that case I think prepending 'test_' to the service name would be a viable solution. Then the docs would show two service entries to support testing in the example.

Agreed, I was thinking about the same solution. Unfortunately it's quite tricky to implement this duality :|

[Mariusz Felisiak]

I've tried to implement this but all mechanism related with creating/cloning test databases are based on the database name. It's feasible, however, my draft patch is quite complex and the risk of introducing regressions in a stable version is too great, IMO. I'd document that service names cannot be currently used for testing purposes and treat this as a new feature request.

For example:

docs/ref/databases.txt

@@ -165 +165 @@
     Support for connecting by a service name, and specifying a password file
     was added.
 
+.. warning::
+
+    Using a service name for testing purposes is not supported. This
+    :ticket:`may be implemented later <33685>`.
+
 Optimizing PostgreSQL's configuration
 -------------------------------------
 

[Hasan Ramezani]

however, my draft patch is quite complex and the risk of introducing regressions in a stable version is too great,

Agree with you.

I'd document that service names cannot be currently used for testing purposes and treat this as a new feature request.

Sound good to me.

[GitHub <noreply@…>]

In 64748016:

Refs #33685 -- Doc'd that using PostgreSQL's service names for testing purposes is not supported.

[Mariusz Felisiak <felisiak.mariusz@…>]

In de9c08c0:

[4.0.x] Refs #33685 -- Doc'd that using PostgreSQL's service names for testing purposes is not supported.

Backport of 647480166bfe7532e8c471fef0146e3a17e6c0c9 from main

[Joshua Lusk]

Was messing around with this and found the immediate issue to be that the .pop() call in get_connection_params() was destructive to self.settings_dict, since assigning settings_dict = self.settings_dict means both still reference the same underlying dictionary. This means that after _nodb_cursor is done creating the test database, acquiring the normal cursor will fail since ['OPTIONS']['service'] has been removed from [OPTIONS] entirely. I was able to get tests to work by removing the destructive call to .pop() and specifying service as None specifically in the conn_params for this case:

django/db/backends/postgresql/base.py

@@ -194 +194 @@
             }
         elif settings_dict["NAME"] is None:
             # Connect to the default 'postgres' db.
-            settings_dict.get("OPTIONS", {}).pop("service", None)
-            conn_params = {"database": "postgres", **settings_dict["OPTIONS"]}
+            conn_params = {"database": "postgres", "service": None, **settings_dict["OPTIONS"]}
         else:
             conn_params = {**settings_dict["OPTIONS"]}
 

But to Mariusz Felisiak's point:

I've tried to implement this but all mechanism related with creating/cloning test databases are based on the database name. It's feasible, however, my draft patch is quite complex and the risk of introducing regressions in a stable version is too great, IMO. I'd document that service names cannot be currently used for testing purposes and treat this as a new feature request.

There might be more to this than meets the eye, especially if their are code paths other than the instance of DatabaseWrapper created by _nodb_cursor that use this branch of get_connection_params() that I am not aware of.