Allow postgresql database connections to use postgres services

[levihb]

Postgres offers a way to make database connections through the use of services, which are basically equivalent to MySQL's options files.

Server, database, username, etc information is stored by default in ~/.pg_service.conf and takes a very similar format to MySQL cnf files:

[my_alias]
host=10.0.19.10
user=postgres
dbname=postgres
port=5432

And password can be stored in ~/.pgpass under a different format.

I think being able to just add them to the DATABASES config would be useful, similar to how you can add MySQL cnf files. psycopg2 supports it just fine through the service argument/string connect(service='my_alias') connect('service=my_alias').

At the moment it can be added like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'postgres',
        'OPTIONS': {'service': 'my_alias'}
    }
}

Which works, however it involves repeating the database name. I don't think the database name should be repeated twice because it couples the config and the service file together, and makes it harder to just move it between different environments. I think ideally you would just specify the service, either like this:

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

Or maybe a better way would be?:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'SERVICE': 'my_alias
    }
}

It seems like something that would be super easy to add. I don't mind creating a pull request for it, but would like to know why it hasn't been added, and how it would be recommended to add it.

[Mariusz Felisiak]

Configuration without NAME already works for me, e.g.:

'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'OPTIONS': {
        'service': 'default_django_test'
    }
},

so only setting PGSERVICE for ​the underlying command-line client and docs are missing.

I don't mind creating a pull request for it, but would like to know why it hasn't been added, ...

Because you're the first to suggest it.

[levihb]

Replying to Mariusz Felisiak:

Configuration without NAME already works for me, e.g.:

'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'OPTIONS': {
        'service': 'default_django_test'
    }
},

It doesn't work for me. E.g.:

'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'OPTIONS': {
        'service': 'test_service'
    }
},

Throws the following when it tries to access the database:

Traceback (most recent call last):
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/core/handlers/base.py", line 179, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/redacted_path/postgres_services/mysite/testapp/views.py", line 9, in index
    c = db_conn.cursor()
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/db/backends/base/base.py", line 259, in cursor
    return self._cursor()
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/db/backends/base/base.py", line 235, in _cursor
    self.ensure_connection()
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/db/backends/base/base.py", line 219, in ensure_connection
    self.connect()
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/db/backends/base/base.py", line 199, in connect
    conn_params = self.get_connection_params()
  File "/redacted_path/postgres_services/venv/lib/python3.8/site-packages/django/db/backends/postgresql/base.py", line 157, in get_connection_params
    raise ImproperlyConfigured(
django.core.exceptions.ImproperlyConfigured: settings.DATABASES is improperly configured. Please supply the NAME value.

While if I just add the NAME value it works just fine. This makes total sense as if you check the postgres base file it checks:

if settings_dict['NAME'] == '':
    raise ImproperlyConfigured(
        "settings.DATABASES is improperly configured. "
        "Please supply the NAME value.")
if len(settings_dict['NAME'] or '') > self.ops.max_name_length():
    raise ImproperlyConfigured(
        "The database name '%s' (%d characters) is longer than "
        "PostgreSQL's limit of %d characters. Supply a shorter NAME "
        "in settings.DATABASES." % (
            settings_dict['NAME'],
            len(settings_dict['NAME']),
            self.ops.max_name_length(),
        )
    )

The first if evaluates to true. settings_dict['NAME'] must be getting defaulted to an empty string, because I'm not setting it to empty, I'm entirely leaving it out.

so only setting PGSERVICE for ​the underlying command-line client and docs are missing.

I don't mind adding support for those either.

I don't mind creating a pull request for it, but would like to know why it hasn't been added, ...

Because you're the first to suggest it.

Oh I was sure I wouldn't have been. I've looked up how to use postgres services for many projects, and nearly always find people asking, even when it's for much much smaller projects. That's rather interesting.

[Mariusz Felisiak]

It doesn't work for me. E.g.:

You're right. I've only checked running tests which works fine.

[Hasan Ramezani]

​PR

[Mariusz Felisiak <felisiak.mariusz@…>]

In dcb3ad33:

Fixed #32292 -- Added support for connection by service name to PostgreSQL.

[Mariusz Felisiak]

I've noticed an issue when checking related ticket #32456. Using .pgpass with ​PR doesn't work because dbshell is trying to use the default 'postgres' database when NAME is not set and a service name is in use. It shouldn't pass dbname as in the case of ​connections.

​PR

[GitHub <noreply@…>]

In 8908846:

Refs #32292 -- Made dbshell do not use 'postgres' database when service name is set.

Regression in dcb3ad3319cad5c270a1856fd5f355e37cf9d474.