Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uzYCx-004o9x-UO for pgsql-general@arkaria.postgresql.org; Fri, 19 Sep 2025 10:19:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uzYCv-005pM4-1j for pgsql-general@arkaria.postgresql.org; Fri, 19 Sep 2025 10:19:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uzYCu-005pLw-Bs for pgsql-general@lists.postgresql.org; Fri, 19 Sep 2025 10:19:12 +0000 Received: from mail-lf1-x12e.google.com ([2a00:1450:4864:20::12e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uzYCp-001gFV-1w for pgsql-general@lists.postgresql.org; Fri, 19 Sep 2025 10:19:11 +0000 Received: by mail-lf1-x12e.google.com with SMTP id 2adb3069b0e04-55f98e7782bso2332709e87.0 for ; Fri, 19 Sep 2025 03:19:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=opengis.ch; s=google; t=1758277143; x=1758881943; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=hMugeoqA39UjCWn9aYFBA1dThstctGBOz181FyBm7TI=; b=IF/VPr2CHsajBXj9kRxo6xUDiIQN13fRMm8w/wouku1H3i1GK9Te0YyunC4iQYy9ff j05nFxYAHGNIgMSz1cjM6MNCdyRQL1vcZvyPjQIyPnk3rsJ2D/cjGJa3h6u0+/YGAaNo BIsxx9xD6LR6es2+wCJHnyu+PMM8OwW7YbdWa+pHBFvv9xR8ewjKlVxb1hcu9Aypbi2r l21XbrgDRODv6JSHxa4ankRF1ls+G83+gL/sGEXE3OlK9Iofi17Bur5fDu2fqztKU3wJ yscqrRDXyTOM+2j9FQO91Jp3gsea2Iye0UwSUA8nioLan9zCXv0PlKKMXfEH0elDKvW0 MeiA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1758277143; x=1758881943; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=hMugeoqA39UjCWn9aYFBA1dThstctGBOz181FyBm7TI=; b=K92seKMbV6R8N2cCCIXymVbiWUIwtCQ7ZLtpPAT/bZUD44LIeJNtMpbIT6klVTfUEb DLtGJO8HyP9/BDMiV27pXzrTD3heKNTbwiADDp3ke+d7K9bLxPtbOyE59mR/BBlAoE6S bNvOuMD9eLzkTVxVSF5PegLBiV14rS98uTZ/xx0nPV6faa9jtyqUyjIfyuXJh3SXB39k 3lNAQcwKKjRqHkv40RySjOhQZUvNveWoUV5NyRMojWsllS7oWczeW3C2Gv5lkif+30iH Z/qf2Ademwqo/YrdJ7BfQuiSfDi0QvMDNIP19bDlycC7Un6od9dHBEm/m+cfAHD4WIEQ YUZg== X-Gm-Message-State: AOJu0Yzhsl6fMDfcgYOTKnqqAbYdN97msK1KyMImJNyTCNv1j0E9DCjV 6V4Y6QTCbq/et7/7Ikm29J6gFZsFEMNm9KCRjvyDrW72qAp8dKrRwmrJELwHQa+PTk363JNoZXK cPaJYhc4iW+gJVbyy9jRb8+DOERxp82y74ao/qdQGTvxqR47G5SrWj31rNw== X-Gm-Gg: ASbGnctgbxW3+gVMLIkmv8e34aWWr9duoeFejUiVRE7Xc5w0ySFLUa8s5nY6qbOWTiz TfgIbWX7sl73kHYUBoVYwu4hs1zMvB5/ystQsksXjFYuW47sBp8OgB1dEzf5An9WlJgIMPq6URk oyocp7qL5JAZt7Xozywjqmw479fTmYp8tAPDNCQUVrITEHzyJXAnvzqB2JrV9fFzl43nClTi35F RdbvjQ= X-Google-Smtp-Source: AGHT+IE7bTazNlvThtTdx9MpfX4QGjSIDkfmtUSgd1UtqCxIzqsHMhDZlzdSnaZEtAqOP9Yv48JDvuEwSX0UqCyOaoo= X-Received: by 2002:a05:6512:639b:10b0:55f:3c0b:9ca9 with SMTP id 2adb3069b0e04-579e1b6a4f7mr886248e87.1.1758277142641; Fri, 19 Sep 2025 03:19:02 -0700 (PDT) MIME-Version: 1.0 From: Mathieu Pellerin Date: Fri, 19 Sep 2025 17:18:51 +0700 X-Gm-Features: AS18NWBx8Smn3iNls0rmPnmwLKX4FeukTEDDXx2n3u1rFowyLrJ0qlLMykuaPzc Message-ID: Subject: Client/server certificates verification support on Android platform To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000081114a063f24ccf6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000081114a063f24ccf6 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Greetings, I=E2=80=99m 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=E2=80=99s data directory (e.g. /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=3Drw (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/4c7bb7feec00af3bd7e52a522c40a2cd62= af69e6/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/1546e17f9d067e714e066fcdd57d5f56c= 14f4174/src/backend/libpq/be-secure-common.c#L154-L174, and https://github.com/postgres/postgres/blob/1546e17f9d067e714e066fcdd57d5f56c= 14f4174/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=E2=80=99s data directory would certainly match a s= imilar 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=E2=80=99d be more than happy to read those= :) Regards, Mathieu Pellerin QField project owner OPENGIS.ch --=20 [image: OG] *Mathieu Pellerin* Mr. Ordinato QField Product Owner | UX/UI Expert Team QField [image: email] mathieu@opengis.ch [image: www] https://opengis.ch [image: linkedin] [image: mastodon] [image: github] --00000000000081114a063f24ccf6 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

Greetings,


I=E2=80=99m writing with regards= to client/server certificates verification support on Android platform, wh= ere storage access is increasingly limited and often happens through a dedi= cated 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. Whil= e we support multiple platforms these days, our largest bank of users are o= n our original supported platform, namely Android with over 1 million play = store installations.


On that platform, we have long supporte= d the possibility of defining PostgreSQL connections via a pg_service.conf = file users can drop within the application=E2=80=99s data directory (e.g. &= lt;storage root>/Android/data/ch.opengis.qfield/files) via a USB cable t= ransfer. However, when users want to define a service that utilizes certifi= cates to authentication users (https://www.postgresql.org/docs/17/l= ibpq-ssl.html#LIBPQ-SSL-CLIENTCERT), they will hit a permission blockag= e whereas the owner of the copied file will often not be the user running t= he application. This also makes it virtually impossible to manually tweak t= he file permission to match the current u=3Drw (0600) requirement.

To work around this issue, we have come up with some code which copi= es 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 applic= ation and limit the permission to match the requirement (https://github.com/opengisch/QField/blob/4c7bb7= feec00af3bd7e52a522c40a2cd62af69e6/src/app/main.cpp#L294-L305).<= /p>

While this leads to successful authentication, we were wondering wh= ether any thoughts were given by the PostgreSQL community on the possibilit= y to allow for more relaxed permission conditions through whitelisting spec= ific location or via environment variables for platforms such as Android wh= ere permission management is not a straightforward as on Linux systems.


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


Would it make sense for oth= er operating systems beyond Windows to also have relaxed permissions within= specific application-specific folders? On Android, the application=E2=80= =99s data directory would certainly match a similar set of secure assumptio= ns as the OS restricts its access.


Alternatively, if others = on this mailing list have had experience dealing with client / server certi= ficate authentication of services on Android and have best practices to sha= re, we=E2=80=99d be more than happy to read those :)


Regar= ds,


Mathieu Pellerin

QField project owner<= /p>

OPENGIS.ch



--
<= /tbody>
3D"OG"= =C2=A0
=20 =20 Mathieu Pellerin
Mr. Ordinato

QField Product Owner | UX/UI Expert
Team QField

=C2=A0
<= td style=3D"padding-right:5px">3D"linkedin"
3D"masto=3D"github"
--00000000000081114a063f24ccf6--