public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jonathan Gonzalez V. <[email protected]>
To: Daniel Gustafsson <[email protected]>
Cc: Jacob Champion <[email protected]>
Cc: Zsolt Parragi <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
Date: Mon, 09 Mar 2026 20:59:45 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAOYmi+=fbZNJSkHVci=GpR8XPYObK=H+2ERRha0LDTS+ifsWnw@mail.gmail.com>
<CAN4CZFPhm2NCRWzZpX=kRLqyxu4Ps-d0xE5W75a-iDoKrLbXBw@mail.gmail.com>
<CAOYmi+=HcXJub1rDsQ7vpKMHuBB6NTA2Z5T=zAkaFdRThf+9zg@mail.gmail.com>
<[email protected]>
<CAOYmi+mMx1DnNpKG8RdknH0-GuPR9jv+G9r2iFND=Yve7DOF6g@mail.gmail.com>
<[email protected]>
<CAOYmi+nQawWHzC4mRhJnzZzzqjnUDg-yxN3f3ZqPX=+jpKU+zg@mail.gmail.com>
<[email protected]>
<CAOYmi+mTHahYXqBZH-bE1Z3Yc5SJ0gTHsM69LSVna2h7ftgVzQ@mail.gmail.com>
<[email protected]>
<CAOYmi+kNGJXy3YqPDoceb1doNfA-S6fmdKv-AH3j0PPUicyUQQ@mail.gmail.com>
<[email protected]>
<[email protected]>
Hello!
Since the requested work [1] about adding the test has already been
merged, I'm attaching the updated version of the patch with the proper
test for the options.
I may need to change the patch a lot after the ABI stabilization
patches are merged, but this helps to keep the patch in good shape.
Regards!
[1]
https://www.postgresql.org/message-id/flat/8a296a2c128aba924bff0ae48af2b88bf8f9188d.camel%40gmail.co...
--
Jonathan Gonzalez V.
EDB: https://www.enterprisedb.com
Attachments:
[text/x-patch] v3-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch (12.3K, 2-v3-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch)
download | inline diff:
From e477c6ed6f6fd71d5d8cd74f0b89df3e53571f76 Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <[email protected]>
Date: Wed, 29 Oct 2025 16:54:42 +0100
Subject: [PATCH v3 1/1] libpq-oauth: allow changing the CA when not in debug
mode
Allowing to set a CA enables users environment like companies with
internal CA or developers working on their own local system while
using a self-signed CA and don't need to see all the debug messages
while testing inside an internal environment.
Signed-off-by: Jonathan Gonzalez V. <[email protected]>
---
doc/src/sgml/libpq.sgml | 23 ++++++++---
src/interfaces/libpq-oauth/oauth-curl.c | 25 +++++-------
src/interfaces/libpq-oauth/oauth-utils.c | 3 ++
src/interfaces/libpq-oauth/oauth-utils.h | 2 +
src/interfaces/libpq/fe-auth-oauth.c | 3 ++
src/interfaces/libpq/fe-connect.c | 4 ++
src/interfaces/libpq/libpq-int.h | 1 +
.../modules/oauth_validator/t/001_server.pl | 40 ++++++++++++++++++-
.../modules/oauth_validator/t/OAuth/Server.pm | 2 +-
src/tools/pgindent/typedefs.list | 1 +
10 files changed, 80 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 6db823808fc..24fda826dd1 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -10620,12 +10620,6 @@ typedef struct
permits the use of unencrypted HTTP during the OAuth provider exchange
</para>
</listitem>
- <listitem>
- <para>
- allows the system's trusted CA list to be completely replaced using the
- <envar>PGOAUTHCAFILE</envar> environment variable
- </para>
- </listitem>
<listitem>
<para>
prints HTTP traffic (containing several critical secrets) to standard
@@ -10647,6 +10641,23 @@ typedef struct
</para>
</warning>
</sect2>
+ <sect2 id="libpq-oauth-environment">
+ <title>Environment variables</title>
+ <para>
+ The behavior of the OAuth calls may be affected by the following variables:
+ <variablelist>
+ <varlistentry>
+ <term><envar>PGOAUTHCAFILE</envar></term>
+ <listitem>
+ <para>
+ Allows to specify the path to a CA file that will be used by the client
+ to verify the certificate from the OAuth server side.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </sect2>
</sect1>
diff --git a/src/interfaces/libpq-oauth/oauth-curl.c b/src/interfaces/libpq-oauth/oauth-curl.c
index 2c147f98d0d..11145daa451 100644
--- a/src/interfaces/libpq-oauth/oauth-curl.c
+++ b/src/interfaces/libpq-oauth/oauth-curl.c
@@ -56,6 +56,7 @@
#define conn_oauth_discovery_uri(CONN) (CONN->oauth_discovery_uri)
#define conn_oauth_issuer_id(CONN) (CONN->oauth_issuer_id)
#define conn_oauth_scope(CONN) (CONN->oauth_scope)
+#define conn_oauth_ca_file(CONN) (CONN->oauth_ca_file)
#define conn_sasl_state(CONN) (CONN->sasl_state)
#define set_conn_altsock(CONN, VAL) do { CONN->altsock = VAL; } while (0)
@@ -1706,8 +1707,10 @@ debug_callback(CURL *handle, curl_infotype type, char *data, size_t size,
* start_request().
*/
static bool
-setup_curl_handles(struct async_ctx *actx)
+setup_curl_handles(struct async_ctx *actx, PGconn *conn)
{
+ const char *ca_path = conn_oauth_ca_file(conn);
+
/*
* Create our multi handle. This encapsulates the entire conversation with
* libcurl for this connection.
@@ -1796,20 +1799,12 @@ setup_curl_handles(struct async_ctx *actx)
}
/*
- * If we're in debug mode, allow the developer to change the trusted CA
- * list. For now, this is not something we expose outside of the UNSAFE
- * mode, because it's not clear that it's useful in production: both libpq
- * and the user's browser must trust the same authorization servers for
- * the flow to work at all, so any changes to the roots are likely to be
- * done system-wide.
+ * Allow to set the CA even if we're not in debug mode, this would make it easy
+ * to work on environments were the CA could be internal and available on every
+ * system, like big companies with airgap systems.
*/
- if (actx->debugging)
- {
- const char *env;
-
- if ((env = getenv("PGOAUTHCAFILE")) != NULL)
- CHECK_SETOPT(actx, CURLOPT_CAINFO, env, return false);
- }
+ if (ca_path != NULL)
+ CHECK_SETOPT(actx, CURLOPT_CAINFO, ca_path, return false);
/*
* Suppress the Accept header to make our request as minimal as possible.
@@ -2802,7 +2797,7 @@ pg_fe_run_oauth_flow_impl(PGconn *conn)
if (!setup_multiplexer(actx))
goto error_return;
- if (!setup_curl_handles(actx))
+ if (!setup_curl_handles(actx, conn))
goto error_return;
}
diff --git a/src/interfaces/libpq-oauth/oauth-utils.c b/src/interfaces/libpq-oauth/oauth-utils.c
index 4ebe7d0948c..52a8599e15d 100644
--- a/src/interfaces/libpq-oauth/oauth-utils.c
+++ b/src/interfaces/libpq-oauth/oauth-utils.c
@@ -41,6 +41,7 @@ conn_oauth_client_secret_func conn_oauth_client_secret;
conn_oauth_discovery_uri_func conn_oauth_discovery_uri;
conn_oauth_issuer_id_func conn_oauth_issuer_id;
conn_oauth_scope_func conn_oauth_scope;
+conn_oauth_ca_file_func conn_oauth_ca_file;
conn_sasl_state_func conn_sasl_state;
set_conn_altsock_func set_conn_altsock;
@@ -70,6 +71,7 @@ libpq_oauth_init(pgthreadlock_t threadlock_impl,
conn_oauth_discovery_uri_func discoveryuri_impl,
conn_oauth_issuer_id_func issuerid_impl,
conn_oauth_scope_func scope_impl,
+ conn_oauth_ca_file_func cafile_impl,
conn_sasl_state_func saslstate_impl,
set_conn_altsock_func setaltsock_impl,
set_conn_oauth_token_func settoken_impl)
@@ -82,6 +84,7 @@ libpq_oauth_init(pgthreadlock_t threadlock_impl,
conn_oauth_discovery_uri = discoveryuri_impl;
conn_oauth_issuer_id = issuerid_impl;
conn_oauth_scope = scope_impl;
+ conn_oauth_ca_file = cafile_impl;
conn_sasl_state = saslstate_impl;
set_conn_altsock = setaltsock_impl;
set_conn_oauth_token = settoken_impl;
diff --git a/src/interfaces/libpq-oauth/oauth-utils.h b/src/interfaces/libpq-oauth/oauth-utils.h
index 9f4d5b692d2..22183f21c6a 100644
--- a/src/interfaces/libpq-oauth/oauth-utils.h
+++ b/src/interfaces/libpq-oauth/oauth-utils.h
@@ -40,6 +40,7 @@ DECLARE_GETTER(char *, oauth_client_secret);
DECLARE_GETTER(char *, oauth_discovery_uri);
DECLARE_GETTER(char *, oauth_issuer_id);
DECLARE_GETTER(char *, oauth_scope);
+DECLARE_GETTER(char *, oauth_ca_file);
DECLARE_GETTER(fe_oauth_state *, sasl_state);
DECLARE_SETTER(pgsocket, altsock);
@@ -59,6 +60,7 @@ extern PGDLLEXPORT void libpq_oauth_init(pgthreadlock_t threadlock,
conn_oauth_discovery_uri_func discoveryuri_impl,
conn_oauth_issuer_id_func issuerid_impl,
conn_oauth_scope_func scope_impl,
+ conn_oauth_ca_file_func cafile_impl,
conn_sasl_state_func saslstate_impl,
set_conn_altsock_func setaltsock_impl,
set_conn_oauth_token_func settoken_impl);
diff --git a/src/interfaces/libpq/fe-auth-oauth.c b/src/interfaces/libpq/fe-auth-oauth.c
index 2aef327c68b..4f09f1930e8 100644
--- a/src/interfaces/libpq/fe-auth-oauth.c
+++ b/src/interfaces/libpq/fe-auth-oauth.c
@@ -839,6 +839,7 @@ DEFINE_GETTER(char *, oauth_client_secret);
DEFINE_GETTER(char *, oauth_discovery_uri);
DEFINE_GETTER(char *, oauth_issuer_id);
DEFINE_GETTER(char *, oauth_scope);
+DEFINE_GETTER(char *, oauth_ca_file);
DEFINE_GETTER(fe_oauth_state *, sasl_state);
DEFINE_SETTER(pgsocket, altsock);
@@ -868,6 +869,7 @@ use_builtin_flow(PGconn *conn, fe_oauth_state *state)
conn_oauth_discovery_uri_func discoveryuri_impl,
conn_oauth_issuer_id_func issuerid_impl,
conn_oauth_scope_func scope_impl,
+ conn_oauth_ca_file_func cafile_impl,
conn_sasl_state_func saslstate_impl,
set_conn_altsock_func setaltsock_impl,
set_conn_oauth_token_func settoken_impl);
@@ -955,6 +957,7 @@ use_builtin_flow(PGconn *conn, fe_oauth_state *state)
conn_oauth_discovery_uri,
conn_oauth_issuer_id,
conn_oauth_scope,
+ conn_oauth_ca_file,
conn_sasl_state,
set_conn_altsock,
set_conn_oauth_token);
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index db9b4c8edbf..9a5452966dc 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -413,6 +413,10 @@ static const internalPQconninfoOption PQconninfoOptions[] = {
"OAuth-Scope", "", 15,
offsetof(struct pg_conn, oauth_scope)},
+ {"oauth_ca_file", "PGOAUTHCAFILE", NULL, NULL,
+ "Oauth-CA-File", "", 64,
+ offsetof(struct pg_conn, oauth_ca_file)},
+
{"sslkeylogfile", NULL, NULL, NULL,
"SSL-Key-Log-File", "D", 64,
offsetof(struct pg_conn, sslkeylogfile)},
diff --git a/src/interfaces/libpq/libpq-int.h b/src/interfaces/libpq/libpq-int.h
index bd7eb59f5f8..1f1fb89e02f 100644
--- a/src/interfaces/libpq/libpq-int.h
+++ b/src/interfaces/libpq/libpq-int.h
@@ -444,6 +444,7 @@ struct pg_conn
char *oauth_client_secret; /* client secret */
char *oauth_scope; /* access token scope */
char *oauth_token; /* access token */
+ char *oauth_ca_file; /* CA file path */
bool oauth_want_retry; /* should we retry on failure? */
/* Optional file to write trace info to */
diff --git a/src/test/modules/oauth_validator/t/001_server.pl b/src/test/modules/oauth_validator/t/001_server.pl
index cdad2ae8011..b66d99dd4bb 100644
--- a/src/test/modules/oauth_validator/t/001_server.pl
+++ b/src/test/modules/oauth_validator/t/001_server.pl
@@ -137,10 +137,46 @@ $node->connect_fails(
expected_stderr =>
qr/failed to fetch OpenID discovery document:.*peer certificate/i);
-# Now we can use our alternative CA.
-$ENV{PGOAUTHCAFILE} = "$ENV{cert_dir}/root+server_ca.crt";
+# Make sure that PGOAUTHDEBUG is not required to specify the certificate
+delete $ENV{PGOAUTHDEBUG};
+# The alternative CA path to use during the tests
+my $alternative_ca = "$ENV{cert_dir}/root+server_ca.crt";
+
+# Make sure we can use oauth_ca_file option to specify the alternative CA path
my $user = "test";
+$node->connect_ok(
+ "user=$user dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635 oauth_ca_file=$alternative_ca",
+ "connect as test",
+ expected_stderr =>
+ qr@Visit https://example\.com/ and enter the code: postgresuser@,
+ log_like => [
+ qr/oauth_validator: token="9243959234", role="$user"/,
+ qr/oauth_validator: issuer="\Q$issuer\E", scope="openid postgres"/,
+ qr/connection authenticated: identity="test" method=oauth/,
+ qr/connection authorized/,
+ ]);
+
+# Make sure that we can use the environment variable without the PGOAUTHDEBUG
+# and use it for the rest of the tests
+$ENV{PGOAUTHCAFILE} = $alternative_ca;
+
+$node->connect_ok(
+ "user=$user dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635",
+ "connect as test",
+ expected_stderr =>
+ qr@Visit https://example\.com/ and enter the code: postgresuser@,
+ log_like => [
+ qr/oauth_validator: token="9243959234", role="$user"/,
+ qr/oauth_validator: issuer="\Q$issuer\E", scope="openid postgres"/,
+ qr/connection authenticated: identity="test" method=oauth/,
+ qr/connection authorized/,
+ ]);
+
+# Enable PGOAUTHDEBUG=UNSAFE to have the proper count later with the `[libpq] total number of polls` messages
+$ENV{PGOAUTHDEBUG} = "UNSAFE";
+
+$user = "test";
$node->connect_ok(
"user=$user dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635",
"connect as test",
diff --git a/src/test/modules/oauth_validator/t/OAuth/Server.pm b/src/test/modules/oauth_validator/t/OAuth/Server.pm
index d923d4c5eb2..62a29c283df 100644
--- a/src/test/modules/oauth_validator/t/OAuth/Server.pm
+++ b/src/test/modules/oauth_validator/t/OAuth/Server.pm
@@ -28,7 +28,7 @@ daemon implemented in t/oauth_server.py. (Python has a fairly usable HTTP server
in its standard library, so the implementation was ported from Perl.)
This authorization server serves HTTPS on 127.0.0.1 (IPv4 only). libpq will need
-to set PGOAUTHDEBUG=UNSAFE and PGOAUTHCAFILE with the right CA.
+to set PGOAUTHCAFILE with the right CA.
=cut
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 3250564d4ff..c5795e7e868 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3590,6 +3590,7 @@ conn_oauth_client_secret_func
conn_oauth_discovery_uri_func
conn_oauth_issuer_id_func
conn_oauth_scope_func
+conn_oauth_ca_file_func
conn_sasl_state_func
contain_aggs_of_level_context
contain_placeholder_references_context
--
2.51.0
[application/pgp-signature] signature.asc (833B, 3-signature.asc)
download
view thread (31+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox