PostgreSQL connections can now be configured to use SSL/TLS

The PostgreSQL driver now accepts four optional connection options that control SSL/TLS for the database connection: sslmode, sslcert, sslkey and sslrootcert. They are set in the $databases array in settings.php and are passed to libpq unchanged. Previously, the driver only passed the host, database name, port and client encoding, so there was no way to require an encrypted connection, verify the server certificate, or present a client certificate without patching core.

New connection options

• sslmode: how the connection negotiates SSL. See the values below.
• sslcert: path to the client certificate that PHP presents to the server. Only needed when the server requires client certificate authentication.
• sslkey: path to the private key for the certificate in sslcert.
• sslrootcert: path to the trusted CA certificate(s) used to verify the server certificate. Required for verify-ca and verify-full.

The sslmode values are the ones defined by libpq:

• disable: never use SSL.
• allow: try an unencrypted connection first, use SSL only if that fails.
• prefer: try an encrypted connection first, fall back to unencrypted if that fails. This is the libpq default and is what sites got before this change.
• require: always use SSL, but do not verify the server certificate.
• verify-ca: always use SSL and verify that the server certificate is issued by a trusted CA.
• verify-full: always use SSL, verify that the server certificate is issued by a trusted CA and that it matches the host name. This is the recommended setting for a database on another machine.

All four options are optional. When none of them is set, the connection string is the same as before, so existing sites are not affected.

Examples

Require an encrypted connection without certificate verification. This protects against passive eavesdropping only:

$databases['default']['default'] = [
  'driver' => 'pgsql',
  'namespace' => 'Drupal\pgsql\Driver\Database\pgsql',
  'autoload' => 'core/modules/pgsql/src/Driver/Database/pgsql/',
  'database' => 'drupal',
  'username' => 'drupal',
  'password' => 'secret',
  'host' => 'db.example.com',
  'port' => '5432',
  'prefix' => '',
  'sslmode' => 'require',
];

Verify the server certificate against a CA certificate and check that it matches the host name. This is the setting to use for a database server on another machine:

$databases['default']['default'] = [
  'driver' => 'pgsql',
  'namespace' => 'Drupal\pgsql\Driver\Database\pgsql',
  'autoload' => 'core/modules/pgsql/src/Driver/Database/pgsql/',
  'database' => 'drupal',
  'username' => 'drupal',
  'password' => 'secret',
  'host' => 'db.example.com',
  'port' => '5432',
  'prefix' => '',
  'sslmode' => 'verify-full',
  'sslrootcert' => '/etc/drupal/certs/root.crt',
];

Authenticate with a client certificate, for servers that have clientcert=verify-full or the cert authentication method in pg_hba.conf:

$databases['default']['default'] = [
  'driver' => 'pgsql',
  'namespace' => 'Drupal\pgsql\Driver\Database\pgsql',
  'autoload' => 'core/modules/pgsql/src/Driver/Database/pgsql/',
  'database' => 'drupal',
  'username' => 'drupal',
  'password' => 'secret',
  'host' => 'db.example.com',
  'port' => '5432',
  'prefix' => '',
  'sslmode' => 'verify-full',
  'sslcert' => '/etc/drupal/certs/client.crt',
  'sslkey' => '/etc/drupal/certs/client.key',
  'sslrootcert' => '/etc/drupal/certs/root.crt',
];

Explicitly disable SSL, for example for a local socket-less development setup where the server has SSL enabled but a plain connection is wanted:

$databases['default']['default']['sslmode'] = 'disable';

The paths in sslcert, sslkey and sslrootcert are read by libpq in the PHP process, so they must be readable by the web server user. The private key file must not be readable by other users or libpq rejects it.