Client/server certificates verification support on Android platform - Mailing list pgsql-general

Greetings,

I’m writing with regards to client/server certificates verification support on Android platform, where storage access is increasingly limited and often happens through a dedicated system user that differs from the user that runs applications.

A bit of background: we develop QField, an open source spatial and surveying application built on top of QGIS focused on mobile devices. While we support multiple platforms these days, our largest bank of users are on our original supported platform, namely Android with over 1 million play store installations.

On that platform, we have long supported the possibility of defining PostgreSQL connections via a pg_service.conf file users can drop within the application’s data directory (e.g. <storage root>/Android/data/ch.opengis.qfield/files) via a USB cable transfer. However, when users want to define a service that utilizes certificates to authentication users (https://www.postgresql.org/docs/17/libpq-ssl.html#LIBPQ-SSL-CLIENTCERT), they will hit a permission blockage whereas the owner of the copied file will often not be the user running the application. This also makes it virtually impossible to manually tweak the file permission to match the current u=rw (0600) requirement.

To work around this issue, we have come up with some code which copies the certificate copied onto the device by the user to another location, where we then set the file ownership to the current user running the application and limit the permission to match the requirement (https://github.com/opengisch/QField/blob/4c7bb7feec00af3bd7e52a522c40a2cd62af69e6/src/app/main.cpp#L294-L305).

While this leads to successful authentication, we were wondering whether any thoughts were given by the PostgreSQL community on the possibility to allow for more relaxed permission conditions through whitelisting specific location or via environment variables for platforms such as Android where permission management is not a straightforward as on Linux systems.

For example, in the documentation page linked above, it mentions that permissions check is not conducted on Windows as the %APPDATA%\postgresql is presumed secure. That matches relevant code logic which disables permission check altogether for the windows platform (e.g https://github.com/postgres/postgres/blob/1546e17f9d067e714e066fcdd57d5f56c14f4174/src/backend/libpq/be-secure-common.c#L154-L174, and https://github.com/postgres/postgres/blob/1546e17f9d067e714e066fcdd57d5f56c14f4174/src/interfaces/libpq/fe-secure-openssl.c#L1260-L1270) 

Would it make sense for other operating systems beyond Windows to also have relaxed permissions within specific application-specific folders? On Android, the application’s data directory would certainly match a similar set of secure assumptions as the OS restricts its access.

Alternatively, if others on this mailing list have had experience dealing with client / server certificate authentication of services on Android and have best practices to share, we’d be more than happy to read those :)

Regards,

Mathieu Pellerin

QField project owner

OPENGIS.ch