public inbox for [email protected]
help / color / mirror / Atom feedRe: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
15+ messages / 4 participants
[nested] [flat]
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-01-05 18:37 Jacob Champion <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jacob Champion @ 2026-01-05 18:37 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
On Sat, Dec 20, 2025 at 9:53 AM Jonathan Gonzalez V.
<[email protected]> wrote:
> > > > https://wiki.postgresql.org/wiki/Proposal:_Promote_PGOAUTHCAFILE_to_feature
> > >
> > > How can we work on that? because of the above it may be required to
> > > add
> > > even more possibilities.
> >
> > Not sure what you mean. I think we're working on it now, in this
> > thread?
>
> Yes, but having a list of ideas listed, that we all can read may make
> sense, that's because following the threads with all the ideas at once
> it's a big difficult some times!
See https://wiki.postgresql.org/wiki/Category:OAuth_Working_Group for
a current list of tagged [oauth] proposals. Or is that not what you're
asking about?
> In my opinion, "debug" it's not just developers, [...]
> since all the systems now days can run on hundreds
> of servers or containers, no one looks into the logs manually, you have
> automated system for it, that will read, parse, collect and distribute
> your logs into different storage, databases(even PostgreSQL database
> can be used for it) or display system. It is for theses cases that
> having something that can be parsed is always useful.
Sure, but that's not the use case for PGOAUTHDEBUG. It's fine to
develop a feature that handles production logging for client
authentication details -- it's just emphatically not what that envvar
was designed to do. This is a developer feature which turns out to be
hiding another feature that people want to use in production today.
I know the most visible aspect of PGOAUTHDEBUG=UNSAFE is the logging
spray, so that might have contributed to the confusion.
> Well, I think I was misunderstood here, when I was talking about "debug
> levels" I was talking about logs debug levels
Right, and I'm not. I guess that's the main disconnect here: I'm only
talking about enabling and disabling the features exposed by
PGOAUTHDEBUG. I don't think a debug level helps with that, which is
why I proposed a bitmap.
But that's a feature for a different thread name. I think we should
continue this one by adding an oauth_ca_file connection parameter and
documentation, including the default behavior (which defers to Curl).
--Jacob
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-01-06 08:40 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jonathan Gonzalez V. @ 2026-01-06 08:40 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
Hi!
On Mon, 2026-01-05 at 10:37 -0800, Jacob Champion wrote:
>
> See https://wiki.postgresql.org/wiki/Category:OAuth_Working_Group for
> a current list of tagged [oauth] proposals. Or is that not what
> you're
> asking about?
Not specifically, but that will work more than fine for sure! Thank
you!
>
> Right, and I'm not. I guess that's the main disconnect here: I'm only
> talking about enabling and disabling the features exposed by
> PGOAUTHDEBUG. I don't think a debug level helps with that, which is
> why I proposed a bitmap.
>
> But that's a feature for a different thread name. I think we should
> continue this one by adding an oauth_ca_file connection parameter and
> documentation, including the default behavior (which defers to Curl).
>
>
Ok, promoting this to something external to the debug makes a lot of
sense to me, that will help a lot to increase the possible usage of
this parameter.
I will for sure still allow an environment variable too like OAUTH_CA
or OAUTH_CA_FILE, just because environment variable for these
parameters is widely used, just like in curl[1] has cacert_file and
support for CURL_CA_BUNDLE, both options make sure that users may not
be limited.
I already worked a patch (before this one) to add an option to pass the
CA but I discarded that because I didn't thought it was going to be
accepted, I can rework that with all the ideas, but, what do you think
about creating a wiki page with all the ideas to manage the
certificates? probably the CA will require to also add some skip or
insecure options, full bundles and how to build them, etc.
Regards!
[1] https://curl.se/docs/sslcerts.html
--
Jonathan Gonzalez V. <[email protected]>
EnterpriseDB
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-01-06 16:28 Jacob Champion <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jacob Champion @ 2026-01-06 16:28 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2026 at 12:45 AM Jonathan Gonzalez V.
<[email protected]> wrote:
> I will for sure still allow an environment variable too like OAUTH_CA
> or OAUTH_CA_FILE, just because environment variable for these
> parameters is widely used, just like in curl[1] has cacert_file and
> support for CURL_CA_BUNDLE, both options make sure that users may not
> be limited.
Right -- I hadn't meant that you should remove the PGOAUTHCAFILE
envvar from your patch, just that an oauth_ca_file parameter should be
added as well.
> I already worked a patch (before this one) to add an option to pass the
> CA but I discarded that because I didn't thought it was going to be
> accepted, I can rework that with all the ideas, but, what do you think
> about creating a wiki page with all the ideas to manage the
> certificates?
You're more than welcome to add any wiki pages you think would be
useful -- you certainly don't need my permission :D
If you don't have edit access yet, see
https://wiki.postgresql.org/wiki/WikiEditing
> probably the CA will require to also add some skip or
> insecure options, full bundles and how to build them, etc.
I'm not quite sure what you mean by these, but it might be easier to
read the wiki page you had in mind and comment on that.
Thanks!
--Jacob
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-02-17 17:18 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jonathan Gonzalez V. @ 2026-02-17 17:18 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
Hello!
>
> Right -- I hadn't meant that you should remove the PGOAUTHCAFILE
> envvar from your patch, just that an oauth_ca_file parameter should
> be
> added as well.
>
>
I'm attached a v2 of this patch I'm not really sure if this is what you
mean.
I want to add some test for this option that I think it could be really
useful, what do you think?
--
Jonathan Gonzalez V. <[email protected]>
Attachments:
[text/x-patch] v2-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch (9.5K, 2-v2-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch)
download | inline diff:
From ed6b0146d45ded6f0970f9bb8eb9a34d78960f2d Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <[email protected]>
Date: Wed, 29 Oct 2025 16:54:42 +0100
Subject: [PATCH v2 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 +
src/tools/pgindent/typedefs.list | 1 +
8 files changed, 41 insertions(+), 21 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 21e1ba34a4e..f28871d402a 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -10519,12 +10519,6 @@ typedef struct PGoauthBearerRequest
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
@@ -10546,6 +10540,23 @@ typedef struct PGoauthBearerRequest
</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 691e7ec1d9f..0460cce65bb 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)
@@ -1708,8 +1709,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.
@@ -1798,20 +1801,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.
@@ -2804,7 +2799,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 67879d64b39..74fa737e5d3 100644
--- a/src/interfaces/libpq/fe-auth-oauth.c
+++ b/src/interfaces/libpq/fe-auth-oauth.c
@@ -816,6 +816,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);
@@ -845,6 +846,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);
@@ -932,6 +934,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 a0d2f749811..6f5a1006206 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -412,6 +412,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 fb6a7cbf15d..3f799e9b34d 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/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 241945734ec..e09858263e7 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -3587,6 +3587,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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-02-19 00:46 Jacob Champion <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jacob Champion @ 2026-02-19 00:46 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
On Tue, Feb 17, 2026 at 9:23 AM Jonathan Gonzalez V.
<[email protected]> wrote:
> I'm attached a v2 of this patch I'm not really sure if this is what you
> mean.
At a glance, I think so!
> +#define conn_oauth_ca_file(CONN) (CONN->oauth_ca_file)
Arrrghh I hadn't even considered that this thread would conflict with
the changes over at [1]. Well, the silver lining is that I already
know I have to get most of that work in; this just serializes things.
> I want to add some test for this option that I think it could be really
> useful, what do you think?
Definitely. I could see either upgrading the oauth_validator test
suite to use HTTPS throughout, and then setting the new envvar
globally, or just adding a single test that switches it on (but I'm
not sure that's actually less work, since you have to teach
oauth_server.py to speak HTTPS either way).
Thanks!
--Jacob
[1] https://postgr.es/m/CAOYmi%2BmrGg%2Bn_X2MOLgeWcj3v_M00gR8uz_D7mM8z%3DdX1JYVbg%40mail.gmail.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-02-19 14:18 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jonathan Gonzalez V. @ 2026-02-19 14:18 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
Hi!
> > +#define conn_oauth_ca_file(CONN) (CONN->oauth_ca_file)
>
> Arrrghh I hadn't even considered that this thread would conflict with
> the changes over at [1]. Well, the silver lining is that I already
> know I have to get most of that work in; this just serializes things.
Well, it will definitely conflict but I can rebase the work on that
patch, not an issue, since now I understand what you mean it's even
more fun! What do you think? I can do some testing and review on those
patches too while working on a rebase, so I think it's a win-win
> > I want to add some test for this option that I think it could be
> > really
> > useful, what do you think?
>
> Definitely. I could see either upgrading the oauth_validator test
> suite to use HTTPS throughout, and then setting the new envvar
> globally, or just adding a single test that switches it on (but I'm
> not sure that's actually less work, since you have to teach
> oauth_server.py to speak HTTPS either way).
Ok, so probably a new patch to teach oauth_server.py to speak HTTPS
could be good? Since it requires to create certificates and lot of
testing work a different patch could be better right? just to add HTTPS
support.
Thank you!
--
Jonathan Gonzalez V. <[email protected]>
EnterpriseDB
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-02-19 14:22 Daniel Gustafsson <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Daniel Gustafsson @ 2026-02-19 14:22 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Jacob Champion <[email protected]>; Zsolt Parragi <[email protected]>; pgsql-hackers
> On 19 Feb 2026, at 15:18, Jonathan Gonzalez V. <[email protected]> wrote:
> Ok, so probably a new patch to teach oauth_server.py to speak HTTPS
> could be good? Since it requires to create certificates and lot of
> testing work a different patch could be better right? just to add HTTPS
> support.
+1, that work should be considered on its own as it has value independent of
the rest of this patchset.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-09 19:59 Jonathan Gonzalez V. <[email protected]>
parent: Daniel Gustafsson <[email protected]>
0 siblings, 2 replies; 15+ messages in thread
From: Jonathan Gonzalez V. @ 2026-03-09 19:59 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Jacob Champion <[email protected]>; Zsolt Parragi <[email protected]>; pgsql-hackers
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-10 16:43 Jacob Champion <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
1 sibling, 0 replies; 15+ messages in thread
From: Jacob Champion @ 2026-03-10 16:43 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Zsolt Parragi <[email protected]>; pgsql-hackers
On Mon, Mar 9, 2026 at 1:04 PM Jonathan Gonzalez V.
<[email protected]> wrote:
> 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.
Thanks! For a head start, consider locally rebasing over v7-0002 from
this thread:
https://postgr.es/m/CAOYmi%2B%3DPr7AAdkcKXyLw3ycxcrjGKsOV2CTYVV2PKYQw9ecG0Q%40mail.gmail.com
I don't think there will be much rebase pain (or at least, I hope
not); take a look at the handling of `actx->client_id` in v7-0001 for
an example.
--Jacob
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-10 22:40 Zsolt Parragi <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
1 sibling, 1 reply; 15+ messages in thread
From: Zsolt Parragi @ 2026-03-10 22:40 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jacob Champion <[email protected]>; pgsql-hackers
Hello
I only have a few minor comments/questions:
Shouldn't we free oauth_ca_file in freePGconn?
Would a test case with an invalid/incorrect CA file be also useful, or
is that too much testing of curl internals?
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
Shouldn't the doc update also include oauth_ca_file?
+ {"oauth_ca_file", "PGOAUTHCAFILE", NULL, NULL,
+ "Oauth-CA-File", "", 64,
+ offsetof(struct pg_conn, oauth_ca_file)}
That should be OAuth-CA-File
+ * 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.
where the CA
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-17 06:41 Zsolt Parragi <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Zsolt Parragi @ 2026-03-17 06:41 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jacob Champion <[email protected]>; pgsql-hackers
Hello
These variables are still not freed, I am missing something why it
isn't required?
+ char *oauth_ca_file; /* CA file path */
Shouldn't we free this in freePGconn?
+ char *ca_file; /* oauth_ca_file */
Similarly with free_async_ctx?
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-17 21:37 Jonathan Gonzalez V. <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jonathan Gonzalez V. @ 2026-03-17 21:37 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jacob Champion <[email protected]>; pgsql-hackers
Hello!!
On Tue, 2026-03-17 at 06:41 +0000, Zsolt Parragi wrote:
> Hello
>
> These variables are still not freed, I am missing something why it
> isn't required?
>
> + char *oauth_ca_file; /* CA file path */
>
> Shouldn't we free this in freePGconn?
>
>
> + char *ca_file; /* oauth_ca_file */
>
> Similarly with free_async_ctx?
I totally miss this comments, and I had it noted
Attaching v5
--
Jonathan Gonzalez V.
EDB: https://www.enterprisedb.com
Attachments:
[text/x-patch] v5-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch (8.6K, 2-v5-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch)
download | inline diff:
From a27875e770b9f74481cf839b168ea187e040d279 Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <[email protected]>
Date: Wed, 29 Oct 2025 16:54:42 +0100
Subject: [PATCH v5 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.
Reviewed-by: Zsolt Parragi <zsolt,[email protected]>
Signed-off-by: Jonathan Gonzalez V. <[email protected]>
---
doc/src/sgml/libpq.sgml | 23 ++++++++---
src/interfaces/libpq-oauth/oauth-curl.c | 27 +++++++------
src/interfaces/libpq/fe-connect.c | 5 +++
src/interfaces/libpq/libpq-int.h | 1 +
.../modules/oauth_validator/t/001_server.pl | 40 ++++++++++++++++++-
.../modules/oauth_validator/t/OAuth/Server.pm | 2 +-
6 files changed, 76 insertions(+), 22 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 052ecd32da2..561875d6db1 100644
--- a/src/interfaces/libpq-oauth/oauth-curl.c
+++ b/src/interfaces/libpq-oauth/oauth-curl.c
@@ -17,6 +17,7 @@
#include <curl/curl.h>
#include <math.h>
+#include <string.h>
#include <unistd.h>
#if defined(HAVE_SYS_EPOLL_H)
@@ -216,6 +217,7 @@ struct async_ctx
/* relevant connection options cached from the PGconn */
char *client_id; /* oauth_client_id */
char *client_secret; /* oauth_client_secret (may be NULL) */
+ char *ca_file; /* oauth_ca_file */
/* options cached from the PGoauthBearerRequest (we don't own these) */
const char *discovery_uri;
@@ -336,6 +338,7 @@ free_async_ctx(struct async_ctx *actx)
free(actx->client_id);
free(actx->client_secret);
+ free(actx->ca_file);
free(actx);
}
@@ -1834,20 +1837,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 where 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 (actx->ca_file != NULL)
+ CHECK_SETOPT(actx, CURLOPT_CAINFO, actx->ca_file, return false);
/*
* Suppress the Accept header to make our request as minimal as possible.
@@ -3125,6 +3120,12 @@ pg_start_oauthbearer(PGconn *conn, PGoauthBearerRequestV2 *request)
if (!actx->client_secret)
goto oom;
}
+ else if (strcmp(opt->keyword, "oauth_ca_file") == 0)
+ {
+ actx->ca_file = strdup(opt->val);
+ if (!actx->ca_file)
+ goto oom;
+ }
}
PQconninfoFree(conninfo);
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index db9b4c8edbf..4f3af722881 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)},
@@ -5158,6 +5162,7 @@ freePGconn(PGconn *conn)
free(conn->oauth_discovery_uri);
free(conn->oauth_client_id);
free(conn->oauth_client_secret);
+ free(conn->oauth_ca_file);
free(conn->oauth_scope);
/* Note that conn->Pfdebug is not ours to close or free */
free(conn->events);
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
--
2.51.0
[application/pgp-signature] signature.asc (833B, 3-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-19 20:15 Zsolt Parragi <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Zsolt Parragi @ 2026-03-19 20:15 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jacob Champion <[email protected]>; pgsql-hackers
Thanks, v5 looks good!
(One documentation comment I missed previously: the oauth_ca_file
connection parameter should also be documented, but that's just the
same documentation repeated at one more place)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-20 20:17 Jonathan Gonzalez V. <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Jonathan Gonzalez V. @ 2026-03-20 20:17 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jacob Champion <[email protected]>; pgsql-hackers
Hello!
On Thu, 2026-03-19 at 20:15 +0000, Zsolt Parragi wrote:
> Thanks, v5 looks good!
>
> (One documentation comment I missed previously: the oauth_ca_file
> connection parameter should also be documented, but that's just the
> same documentation repeated at one more place)
Good point! attached with the new doc and updated reviewers list (sorry
Jacob I forgot you the first time)
Regards!
--
Jonathan Gonzalez V.
EDB: https://www.enterprisedb.com
Attachments:
[text/x-patch] v6-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch (9.2K, 2-v6-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch)
download | inline diff:
From 32f3f1163a061c3a512e05dfe55e7c643e73fcf8 Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <[email protected]>
Date: Wed, 29 Oct 2025 16:54:42 +0100
Subject: [PATCH v6 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.
Reviewed-by: Jacob Champion <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Signed-off-by: Jonathan Gonzalez V. <[email protected]>
---
doc/src/sgml/libpq.sgml | 33 ++++++++++++---
src/interfaces/libpq-oauth/oauth-curl.c | 27 +++++++------
src/interfaces/libpq/fe-connect.c | 5 +++
src/interfaces/libpq/libpq-int.h | 1 +
.../modules/oauth_validator/t/001_server.pl | 40 ++++++++++++++++++-
.../modules/oauth_validator/t/OAuth/Server.pm | 2 +-
6 files changed, 86 insertions(+), 22 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 6db823808fc..cb836abc978 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -2585,6 +2585,16 @@ postgresql://%2Fvar%2Flib%2Fpostgresql/dbname
</listitem>
</varlistentry>
+ <varlistentry id="libpq-connect-oauth-ca-file" xreflabel="oauth_ca_file">
+ <term><literal>oauth_ca_file</literal></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>
@@ -10620,12 +10630,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 +10651,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 052ecd32da2..561875d6db1 100644
--- a/src/interfaces/libpq-oauth/oauth-curl.c
+++ b/src/interfaces/libpq-oauth/oauth-curl.c
@@ -17,6 +17,7 @@
#include <curl/curl.h>
#include <math.h>
+#include <string.h>
#include <unistd.h>
#if defined(HAVE_SYS_EPOLL_H)
@@ -216,6 +217,7 @@ struct async_ctx
/* relevant connection options cached from the PGconn */
char *client_id; /* oauth_client_id */
char *client_secret; /* oauth_client_secret (may be NULL) */
+ char *ca_file; /* oauth_ca_file */
/* options cached from the PGoauthBearerRequest (we don't own these) */
const char *discovery_uri;
@@ -336,6 +338,7 @@ free_async_ctx(struct async_ctx *actx)
free(actx->client_id);
free(actx->client_secret);
+ free(actx->ca_file);
free(actx);
}
@@ -1834,20 +1837,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 where 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 (actx->ca_file != NULL)
+ CHECK_SETOPT(actx, CURLOPT_CAINFO, actx->ca_file, return false);
/*
* Suppress the Accept header to make our request as minimal as possible.
@@ -3125,6 +3120,12 @@ pg_start_oauthbearer(PGconn *conn, PGoauthBearerRequestV2 *request)
if (!actx->client_secret)
goto oom;
}
+ else if (strcmp(opt->keyword, "oauth_ca_file") == 0)
+ {
+ actx->ca_file = strdup(opt->val);
+ if (!actx->ca_file)
+ goto oom;
+ }
}
PQconninfoFree(conninfo);
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index db9b4c8edbf..4f3af722881 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)},
@@ -5158,6 +5162,7 @@ freePGconn(PGconn *conn)
free(conn->oauth_discovery_uri);
free(conn->oauth_client_id);
free(conn->oauth_client_secret);
+ free(conn->oauth_ca_file);
free(conn->oauth_scope);
/* Note that conn->Pfdebug is not ours to close or free */
free(conn->events);
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
--
2.51.0
[application/pgp-signature] signature.asc (833B, 3-signature.asc)
download
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2026-03-30 21:33 Jacob Champion <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Jacob Champion @ 2026-03-30 21:33 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
On Fri, Mar 20, 2026 at 1:17 PM Jonathan Gonzalez V.
<[email protected]> wrote:
> Good point! attached with the new doc and updated reviewers list (sorry
> Jacob I forgot you the first time)
No worries -- I think most committers add themselves as they see fit.
This is now committed; thank you! I did munge the documentation and
comments to better match (IMHO) current style, and I reorganized the
new tests a bit. Some additional refactoring will be needed in
001_server soon, I think.
Now off to rebase the PGOAUTHDEBUG patch...
--Jacob
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-03-30 21:33 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-05 18:37 Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode Jacob Champion <[email protected]>
2026-01-06 08:40 ` Jonathan Gonzalez V. <[email protected]>
2026-01-06 16:28 ` Jacob Champion <[email protected]>
2026-02-17 17:18 ` Jonathan Gonzalez V. <[email protected]>
2026-02-19 00:46 ` Jacob Champion <[email protected]>
2026-02-19 14:18 ` Jonathan Gonzalez V. <[email protected]>
2026-02-19 14:22 ` Daniel Gustafsson <[email protected]>
2026-03-09 19:59 ` Jonathan Gonzalez V. <[email protected]>
2026-03-10 16:43 ` Jacob Champion <[email protected]>
2026-03-10 22:40 ` Zsolt Parragi <[email protected]>
2026-03-17 06:41 ` Zsolt Parragi <[email protected]>
2026-03-17 21:37 ` Jonathan Gonzalez V. <[email protected]>
2026-03-19 20:15 ` Zsolt Parragi <[email protected]>
2026-03-20 20:17 ` Jonathan Gonzalez V. <[email protected]>
2026-03-30 21:33 ` Jacob Champion <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox