Layer properties dialog: PostgreSQL connection does not use QGIS certificate database when sslmode=verify-full

[miceg]

What is the bug or the crash?

When opening the layer properties dialog on a PostgreSQL connection configured with sslmode=verify-full and using a self-signed or cloud-provider-signed certificate loaded into QGIS' certificate store, QGIS shows an authentication dialog with an error:

connection to server at "..." (IP), port 5432 failed: root certificate file ".../AppData/Roaming/postgresql/root.crt" does not exist

Either provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification.

Copying the server's CA certificates to that path (or ~/.postgresql/root.crt on macOS) mitigates the issue, but this shouldn't be necessary – QGIS should be configuring libpq correctly!

Other functionality with the layer seems to work correctly.

Steps to reproduce the issue

1. Set up a PostgreSQL server with self-signed or cloud-provider-signed certificates, which are not in QGIS/browser CA bundles.

2. Setup QGIS to connect to the server using Basic authentication and sslmode=verify-full.

   I've got QGIS configured to store layer metadata and projects in PostgreSQL too – but that doesn't seem to be needed to reproduce the issue.

3. Add a table from that PostgreSQL connection as a layer to your QGIS project.

4. Right-click the layer, and press "layer properties".

You're now prompted for a user name and password, with that error message.

You can also press cancel here – it's not clear to me what context this is actually used in.

Versions

QGIS version
3.38.0-Grenoble
QGIS code revision
37aa6188bc
Qt version
5.15.13
Python version
3.12.4
Compiled against GDAL/OGR
3.9.0
Running against GDAL/OGR
3.9.1
PROJ version
9.4.0
EPSG Registry database version
v11.004 (2024-02-24)
GEOS version
3.12.2-CAPI-1.18.2
SQLite version
3.45.1
PDAL version
2.6.3
PostgreSQL client version
16.2
SpatiaLite version
5.1.0
QWT version
6.2.0
QScintilla2 version
2.14.1
OS version
Windows 11 Version 2009


Active Python plugins
db_manager
0.1.20
grassprovider
2.12.99
MetaSearch
0.3.6
processing
2.12.99

I'm also able to reproduce the issue with the same version of QGIS on macOS.

Supported QGIS version

• I'm running a supported QGIS version according to the roadmap.

New profile

• I tried with a new QGIS profile

Additional context

Possibly related: #39648

PostgreSQL connections are insecure by default, and sslmode=verify-full is the only mode which is actually secure – so this should work out of the box. 😄

It looks like QgsAuthBasicMethod::updateDataSourceUriItems() should add in all custom certificates to the connection configuration when using a Basic authCfg by exporting the certificates to a temporary file.

If I look at the SQL log in debugging and development tools, I see this (some details redacted)

{
  "Error": "connection to server at \"...\" (...), port 5432 failed: root certificate file \"C:\\Users\\...\\AppData\\Roaming/postgresql/root.crt\" does not exist\nEither provide the file, use the system's trusted roots with sslrootcert=system, or change sslmode to disable server certificate verification.\n",
  "Initiator": "QgsPostgresConn",
  "Location": "src\\providers\\postgres\\qgspostgresconn.cpp:355 (QgsPostgresConn::QgsPostgresConn)",
  "Provider": "postgres",
  "SQL": "libpq::PQconnectdb()",
  "URI": "dbname='...' host=... port=5432 user='...' password='...' sslmode=verify-full connect_timeout=30 client_encoding='UTF-8'"
}

That log line comes from here, and stepping through the lines before it:

QGIS/src/providers/postgres/qgspostgresconn.cpp

Lines 336 to 356 in d69b60b

	QgsDebugMsgLevel( QStringLiteral( "New PostgreSQL connection for " ) + conninfo, 2 );
	// expand connectionInfo
	QString expandedConnectionInfo = mUri.connectionInfo( true );
	auto addDefaultTimeoutAndClientEncoding = []( QString & connectString )
	{
	if ( !connectString.contains( QStringLiteral( "connect_timeout=" ) ) )
	{
	// add default timeout
	QgsSettings settings;
	int timeout = settings.value( QStringLiteral( "PostgreSQL/default_timeout" ), PG_DEFAULT_TIMEOUT, QgsSettings::Providers ).toInt();
	connectString += QStringLiteral( " connect_timeout=%1" ).arg( timeout );
	}
	connectString += QLatin1String( " client_encoding='UTF-8'" );
	};
	addDefaultTimeoutAndClientEncoding( expandedConnectionInfo );
	std::unique_ptr<QgsDatabaseQueryLogWrapper> logWrapper = std::make_unique<QgsDatabaseQueryLogWrapper>( QStringLiteral( "libpq::PQconnectdb()" ), expandedConnectionInfo.toUtf8(), QStringLiteral( "postgres" ), QStringLiteral( "QgsPostgresConn" ), QGS_QUERY_LOG_ORIGIN_PG_CON );
	mConn = PQconnectdb( expandedConnectionInfo.toUtf8() );

mUri is a QgsDataSourceUri, so mUri.connectionInfo(true) references QgsDataSourceUri::connectionInfo().

That only calls updateDataSourceUriItems() if !mAuthConfigId.isEmpty() && expandAuthCfg, and the debugging output suggests that there is no authCfg at that point – it looks like something else has already expanded the authCfg into a user and password field, but dropped the certificate configs.

The fact that the server certificate configs are included by QgsBasicAuthMethod (and QgsAuthIdentCertMethod, QgsAuthPkcs12Method and QgsAuthPkiPathsMethod) seems very strange to me, and probably led to this bug.

I think that this should be part of QgsDataSourceUri instead, because then it's consistent regardless of authentication provider.

There may be other reasons for this, but this is what I got from spending a little bit of time reading the code (ie: only static analysis, no debugger).

[miceg]

This is still an issue in QGIS 3.38.1.

QGIS version
3.38.1-Grenoble
QGIS code revision
3d4177afc6
Qt version
5.15.13
Python version
3.12.4
GDAL/OGR version
3.9.1
PROJ version
9.4.0
EPSG Registry database version
v11.004 (2024-02-24)
GEOS version
3.12.2-CAPI-1.18.2
SQLite version
3.45.1
PDAL version
2.6.3
PostgreSQL client version
16.2
SpatiaLite version
5.1.0
QWT version
6.2.0
QScintilla2 version
2.14.1
OS version
Windows 11 Version 2009

Active Python plugins
db_manager
0.1.20
grassprovider
2.12.99
MetaSearch
0.3.6
processing
2.12.99