Add support for postgresql client certificates and key to dbshell.

[Oleh Mykytyuk]

This bug is very similar to the #28322

A common security procedure for DB access is to require mutual TLS for the DB connection.
This involves specifying a server certificate, client certificate, and client key when connecting.
Django already supports this configuration, it looks like this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.environ.get('POSTGRES_DB_NAME'),
        'USER': os.environ.get('POSTGRES_DB_USER'),
        'HOST': 'postgres',
        'PORT': '5432',
        'SCHEMA': os.environ.get('POSTGRES_DB_SCHEMA'),
        'OPTIONS': {
               'sslmode': 'verify-ca',
               'sslrootcert': os.environ.get('POSTGRES_CLI_SSL_CA', 'ca.crt'),
               'sslcert': os.environ.get('POSTGRES_CLI_SSL_CRT', 'client_cert_chain.crt'),
               'sslkey':  os.environ.get('POSTGRES_CLI_SSL_KEY', 'client_key.key')
        }
    }
}

However the dbshell command does not support the client cert params.
Should be a trivial fix to add in support for the other 'ssl' parameters required here.

[Oleh Mykytyuk]

Added pull request: ​https://github.com/django/django/pull/11239/

[Mariusz Felisiak <felisiak.mariusz@…>]

In 177fa083:

Fixed #30370 -- Added dbshell support for client TLS certificates on PostgreSQL.

[Robert Kisteleki]

I'd like to ask for reconsideration of the severity of this from "optimization" to "security" or such.

As it stands, users connecting to a Postgres server with the CLI (psql), if configured properly, will connect verifiably using TLS, giving the impression that the setup is correct the connection is secured. However, this is a false impression as even if the configuration is perfect, Django settings are such that these options are specified, the actual working *code* will not use a secure channel.

As a consequence, passwords, keys, PII and the like will travel in clear text between the application and the database.

Ultimately, I'd like this patch to be added to the 2.2 LTS release too.

Thanks!

[Oleh Mykytyuk]

Replying to Robert Kisteleki:

I'd like to ask for reconsideration of the severity of this from "optimization" to "security" or such.

As it stands, users connecting to a Postgres server with the CLI (psql), if configured properly, will connect verifiably using TLS, giving the impression that the setup is correct the connection is secured. However, this is a false impression as even if the configuration is perfect, Django settings are such that these options are specified, the actual working *code* will not use a secure channel.

As a consequence, passwords, keys, PII and the like will travel in clear text between the application and the database.

Ultimately, I'd like this patch to be added to the 2.2 LTS release too.

Thanks!

I can't change from optimization to security. Available options for the severity are "normal", "release blocker". Available options for type are: uncategorized/new feature/bug/cleanup/optimization. Can I ask you to advise me on what I have to change?

[Oleh Mykytyuk]

I've changed type to 'bug'

[Mariusz Felisiak]

That's not a bug, it's a new feature. I don't see a security issue in this behavior. dbshell is a utility tool and passwords, keys, etc. will travel in clear text only if your database allows non-ssl connections. It's also ​documented that not all options set in the OPTIONS part of your database configuration in DATABASES are passed to the command-line client.

As it stands, users connecting to a Postgres server with the CLI (psql), if configured properly, will connect verifiably using TLS, giving the impression that the setup is correct the connection is secured. However, this is a false impression as even if the configuration is perfect, ....

dbshell uses a subprocess with ​a copy of the current environment, so if you set PGSSLMODE, PGSSLROOTCERT, etc. in your current environment you will connect using TLS even without this change.

[Robert Kisteleki]

Replying to felixxm:

That's not a bug, it's a new feature. I don't see a security issue in this behavior. dbshell is a utility tool and passwords, keys, etc. will travel in clear text only if your database allows non-ssl connections. It's also ​documented that not all options set in the OPTIONS part of your database configuration in DATABASES are passed to the command-line client.

I understand your argument for considering it a feature instead. My point is that it's a security feature.

As it stands, users connecting to a Postgres server with the CLI (psql), if configured properly, will connect verifiably using TLS, giving the impression that the setup is correct the connection is secured. However, this is a false impression as even if the configuration is perfect, ....

dbshell uses a subprocess with ​a copy of the current environment, so if you set PGSSLMODE, PGSSLROOTCERT, etc. in your current environment you will connect using TLS even without this change.

That is true. However, that requires devs (and/or users) to understand that even though Djanog is configured properly and every day use (via wsgi and such) is fine, if they *ever* ask for a dbshell and not consciously set ENV variables, auth tokens and perhaps PII can be captured on the wire. IMO it's a basic security requirement never to send your password in the clear... So this has vast consequences in environments where you don't control the network.