public inbox for [email protected]
help / color / mirror / Atom feedMake PGOAUTHCAFILE in libpq-oauth work out of debug mode
24+ messages / 4 participants
[nested] [flat]
* Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-10-29 19:19 Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Jonathan Gonzalez V. @ 2025-10-29 19:19 UTC (permalink / raw)
To: pgsql-hackers
Hi,
While working on a validator for keycloak[1] with libpq-oauth I find
out that to allow a self-signed certificated I had to set the CA on the
client but for this was required to also set the PGOAUTHDEBUG=UNSAFE
which generated a lot of information on the client side that I didn't
need for my testing and work.
This patch basically remove the need of setting the PGOAUTHDEBUG=UNSAFE
to be able to use PGOAUTHCAFILE.
I'm not sure if where I put the documentation is the right place, I
would like to have some opinions on that matter too.
[1] https://github.com/cloudnative-pg/postgres-keycloak-oauth-validator
--
Jonathan Gonzalez V. <[email protected]>
Attachments:
[text/x-patch] v1-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch (3.6K, 2-v1-0001-libpq-oauth-allow-changing-the-CA-when-not-in-deb.patch)
download | inline diff:
From b32a1ad93f933fa319ff29e15299659d67de4d22 Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <[email protected]>
Date: Wed, 29 Oct 2025 16:54:42 +0100
Subject: [PATCH v1 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 | 20 +++++++-------------
2 files changed, 24 insertions(+), 19 deletions(-)
diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml
index 5bf59a19855..c3fe9d5478a 100644
--- a/doc/src/sgml/libpq.sgml
+++ b/doc/src/sgml/libpq.sgml
@@ -10520,12 +10520,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
@@ -10547,6 +10541,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 aa50b00d053..b27a269c962 100644
--- a/src/interfaces/libpq-oauth/oauth-curl.c
+++ b/src/interfaces/libpq-oauth/oauth-curl.c
@@ -1704,6 +1704,8 @@ debug_callback(CURL *handle, curl_infotype type, char *data, size_t size,
static bool
setup_curl_handles(struct async_ctx *actx)
{
+ const char *env;
+
/*
* Create our multi handle. This encapsulates the entire conversation with
* libcurl for this connection.
@@ -1792,20 +1794,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 change the trusted CA even if we're not in debug mode, this help
+ * to make it easy to work on environments were the CA could internal and
+ * not 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 ((env = getenv("PGOAUTHCAFILE")) != NULL)
+ CHECK_SETOPT(actx, CURLOPT_CAINFO, env, return false);
/*
* Suppress the Accept header to make our request as minimal as possible.
--
2.51.0
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-03 14:24 Daniel Gustafsson <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Daniel Gustafsson @ 2025-11-03 14:24 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: pgsql-hackers
> On 29 Oct 2025, at 20:19, Jonathan Gonzalez V. <[email protected]> wrote:
> This patch basically remove the need of setting the PGOAUTHDEBUG=UNSAFE
> to be able to use PGOAUTHCAFILE.
If we do allow this (IIRC we did discuss during development to allow this but
erred on the side of caution) it should probably be made into a env var *and*
connection param setting like how libpq is otherwise configured?
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-03 16:24 Jacob Champion <[email protected]>
parent: Daniel Gustafsson <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Jacob Champion @ 2025-11-03 16:24 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; +Cc: pgsql-hackers
On Mon, Nov 3, 2025 at 6:24 AM Daniel Gustafsson <[email protected]> wrote:
> If we do allow this (IIRC we did discuss during development to allow this but
> erred on the side of caution)
Yeah, the replaced comment explains it. The assumption is that
whatever device you're using to log in (presumably a browser, not
Curl) has to have the certificates figured out for production use, so
overriding it for Curl alone is probably only good enough for dev use.
But I ran into this annoyance (wanted to override the CA for temporary
development purposes, got sprayed with debug output) during a demo
just last month, so I'm in favor of doing something to make this
easier.
> it should probably be made into a env var *and*
> connection param setting like how libpq is otherwise configured?
I'm still not quite sure about the target audience. If it's just for
developers, I don't necessarily see a need to take up connection
string space (or provide our proxies with yet another setting to worry
about).
Jonathan, the patch itself claims to handle two cases. What's the
production use case where a company has its own CA isolated from the
Internet but isn't willing to add that CA to the system trust?
The reason I ask is that we'd briefly talked about splitting
PGOAUTHDEBUG into more granular settings than just "off" and "UNSAFE".
So if this is a developer-only thing, we could maybe put some more
design work into the list of debug features. That list currently
includes the stderr spray, turning off HTTPS, allowing sub-second ping
intervals, overriding the CA, debugging libpq-oauth link failures,
counting the calls to the flow -- all of which run the gamut from
"completely unsafe" to "completely safe".
Thanks!
--Jacob
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-03 16:53 Zsolt Parragi <[email protected]>
parent: Jacob Champion <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Zsolt Parragi @ 2025-11-03 16:53 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; pgsql-hackers
I was thinking about asking something similar.
In our case, we have two problematic use cases: quick demo setups and CI.
When you start up a simple keycloak instance, you have two easy
options: either use http, or self-signed certificates.
For a CI setup, I can create disposable containers, generate
self-signed certificates, make the OS trust them, and run the tests
that way. But it's complex, and even if it were simple, it's not ideal
for a quick "how to set up a test environment" guide. I also
considered creating a demo docker-compose setup, but even with that, I
can't make the user's browser trust the certificates.
I also do not want to instruct users to specify this variable, as it
provides tons of debug output, some of that is sensitive tokens, and
the users might now know that.
> The reason I ask is that we'd briefly talked about splitting
> PGOAUTHDEBUG into more granular settings than just "off" and "UNSAFE".
That's more similar to the direction I considered going, I was
thinking about adding a PGOAUTHDEBUG=http option. That way there's no
need for self signed certificates, and it's easier to explain to users
that this just allows a less secure quick http setup.
On Mon, Nov 3, 2025 at 4:25 PM Jacob Champion
<[email protected]> wrote:
>
> On Mon, Nov 3, 2025 at 6:24 AM Daniel Gustafsson <[email protected]> wrote:
> > If we do allow this (IIRC we did discuss during development to allow this but
> > erred on the side of caution)
>
> Yeah, the replaced comment explains it. The assumption is that
> whatever device you're using to log in (presumably a browser, not
> Curl) has to have the certificates figured out for production use, so
> overriding it for Curl alone is probably only good enough for dev use.
>
> But I ran into this annoyance (wanted to override the CA for temporary
> development purposes, got sprayed with debug output) during a demo
> just last month, so I'm in favor of doing something to make this
> easier.
>
> > it should probably be made into a env var *and*
> > connection param setting like how libpq is otherwise configured?
>
> I'm still not quite sure about the target audience. If it's just for
> developers, I don't necessarily see a need to take up connection
> string space (or provide our proxies with yet another setting to worry
> about).
>
> Jonathan, the patch itself claims to handle two cases. What's the
> production use case where a company has its own CA isolated from the
> Internet but isn't willing to add that CA to the system trust?
>
> The reason I ask is that we'd briefly talked about splitting
> PGOAUTHDEBUG into more granular settings than just "off" and "UNSAFE".
> So if this is a developer-only thing, we could maybe put some more
> design work into the list of debug features. That list currently
> includes the stderr spray, turning off HTTPS, allowing sub-second ping
> intervals, overriding the CA, debugging libpq-oauth link failures,
> counting the calls to the flow -- all of which run the gamut from
> "completely unsafe" to "completely safe".
>
> Thanks!
> --Jacob
>
>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-04 13:00 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
1 sibling, 2 replies; 24+ messages in thread
From: Jonathan Gonzalez V. @ 2025-11-04 13:00 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; Daniel Gustafsson <[email protected]>; +Cc: pgsql-hackers
Hi!
On Mon, 2025-11-03 at 08:24 -0800, Jacob Champion wrote:
>
> But I ran into this annoyance (wanted to override the CA for
> temporary
> development purposes, got sprayed with debug output) during a demo
> just last month, so I'm in favor of doing something to make this
> easier.
I was creating some demo too, at the beginning was really useful, but
after some seconds, I used to lose the URL and the code, the URL wasn't
an issue later, but the code it was.
>
>
> Jonathan, the patch itself claims to handle two cases. What's the
> production use case where a company has its own CA isolated from the
> Internet but isn't willing to add that CA to the system trust?
Well, there's a couple of cases, I figure out after the first email,
thanks to Alvaro, that I wasn't clear in the comments, probably I
should change it, will try to describe a few cases that I've seen over
the years.
* In Kubernetes, even with a network isolation, people use to prefer
having TLS connections, just because it's the standard, but in internal
communications (between namespaces and pods), these domains contain the
format: <service>.<namespace>.svc.<clustername>.local, as you can
already imagine, this kind of domain cannot be verified by an external
CA, but they can be generated and verified with an internal CA. Now the
question is, why they don't add this CA to every distribution? The
defacto standard way to do this in Kubernetes is to take the CA from a
ConfigMap or Secret (objects that can provide content inside the
infrastructure) and deploy this dynamically inside the Pod, so, to
indicate the path to this file, the standard is to use an environment
variable, in this case, if the content of the ConfigMap or Secret
changes, this will be refreshed inside the Pod too.
* Big companies like those managing credit cards or big banks, use to
have air gap environment, which may have exactly the same problem while
communicating internally, the CA cannot verify an internal domain, on
these cases the CA is usually moved around and installed in a specific
path and installed on specific path and not the system path (usually
because of compliant reasons), meaning that you will actually have to
provide with a variable/configuration/environment the path to the CA.
* Development cases, I think this is clear, but even when you're doing
development, you'll be using a self-signed certificate, but doing
developing and losing URL and the code it can be really common, it
happened to me many times and it wasn't nice looking for the code.
* CI cases, here, you'll not have time to get a certificate to just
trigger an action against a one time domain, usually with a random
domain to not conflict with other CI running at the same time, and you
should never expose sensitive information on the CI output like the one
exposes when enabling PGOAUTHDEBUG="UNSAFE"
> The reason I ask is that we'd briefly talked about splitting
> PGOAUTHDEBUG into more granular settings than just "off" and
> "UNSAFE".
I was thinking the same for another patch that will require discussion
for sure, but it's something similar to add some levels of debug, for
example, when you want to have the tokens or when you only want to see
the URLs used to negotiate (which are really useful when working with
the OAuth flows) or the deep one when you want to see the tokens.
> So if this is a developer-only thing, we could maybe put some more
> design work into the list of debug features. That list currently
> includes the stderr spray, turning off HTTPS, allowing sub-second
> ping
> intervals, overriding the CA, debugging libpq-oauth link failures,
> counting the calls to the flow -- all of which run the gamut from
> "completely unsafe" to "completely safe".
Ho! where can I see this list? I'd love to help with something here!
I'm more than open to keep discussing this, because I can see that many
people will be affected by the same, specially in the Kubernetes world.
Thank your for looking at this!
--
Jonathan Gonzalez V. <[email protected]>
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-04 13:26 Daniel Gustafsson <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
1 sibling, 0 replies; 24+ messages in thread
From: Daniel Gustafsson @ 2025-11-04 13:26 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Jacob Champion <[email protected]>; pgsql-hackers
> On 4 Nov 2025, at 14:00, Jonathan Gonzalez V. <[email protected]> wrote:
> Ho! where can I see this list? I'd love to help with something here!
There is no documented list as far as I can remember, but look for calls to
oauth_unsafe_debugging_enabled() in:
https://github.com/postgres/postgres/blob/master/src/interfaces/libpq/fe-auth-oauth.c
And conditionals checking the actx->debugging variable in:
https://github.com/postgres/postgres/blob/master/src/interfaces/libpq-oauth/oauth-curl.c
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-19 18:55 Jacob Champion <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Jacob Champion @ 2025-11-19 18:55 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; pgsql-hackers; Zsolt Parragi <[email protected]>
On Tue, Nov 4, 2025 at 5:02 AM Jonathan Gonzalez V.
<[email protected]> wrote:
> * In Kubernetes, even with a network isolation, people use to prefer
> having TLS connections, just because it's the standard, but in internal
> communications (between namespaces and pods), these domains contain the
> format: <service>.<namespace>.svc.<clustername>.local, as you can
> already imagine, this kind of domain cannot be verified by an external
> CA, but they can be generated and verified with an internal CA. Now the
> question is, why they don't add this CA to every distribution? The
> defacto standard way to do this in Kubernetes is to take the CA from a
> ConfigMap or Secret (objects that can provide content inside the
> infrastructure) and deploy this dynamically inside the Pod, so, to
> indicate the path to this file, the standard is to use an environment
> variable, in this case, if the content of the ConfigMap or Secret
> changes, this will be refreshed inside the Pod too.
Okay, that's good to know. But I'm still missing how the end user (a
human) trusts that magic CA within the browser or device they use to
finish the actual flow?
> * Big companies like those managing credit cards or big banks, use to
> have air gap environment, which may have exactly the same problem while
> communicating internally, the CA cannot verify an internal domain, on
> these cases the CA is usually moved around and installed in a specific
> path and installed on specific path and not the system path (usually
> because of compliant reasons), meaning that you will actually have to
> provide with a variable/configuration/environment the path to the CA.
Same question as above, but I'm slowly being convinced that this
thread needs to remain separate from the PGOAUTHDEBUG split
discussion, even if they're related.
This might be a silly-small example, but I've added a stub spec:
https://wiki.postgresql.org/wiki/Proposal:_Promote_PGOAUTHCAFILE_to_feature
> * Development cases, I think this is clear, but even when you're doing
> development, you'll be using a self-signed certificate, but doing
> developing and losing URL and the code it can be really common, it
> happened to me many times and it wasn't nice looking for the code.
Right.
> * CI cases, here, you'll not have time to get a certificate to just
> trigger an action against a one time domain, usually with a random
> domain to not conflict with other CI running at the same time, and you
> should never expose sensitive information on the CI output like the one
> exposes when enabling PGOAUTHDEBUG="UNSAFE"
Who's running the CI, and how do OAuth and Device Authorization factor
into it? (And why would a human user be okay with feeding their
privileges into an authorization server with a random-looking host
name every time they run it?)
> > The reason I ask is that we'd briefly talked about splitting
> > PGOAUTHDEBUG into more granular settings than just "off" and
> > "UNSAFE".
>
> I was thinking the same for another patch that will require discussion
> for sure, but it's something similar to add some levels of debug, for
> example, when you want to have the tokens or when you only want to see
> the URLs used to negotiate (which are really useful when working with
> the OAuth flows) or the deep one when you want to see the tokens.
I think that's reached critical mass, then.
> Thank your for looking at this!
Thanks for the discussion!
--Jacob
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-11-19 19:26 Jacob Champion <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 2 replies; 24+ messages in thread
From: Jacob Champion @ 2025-11-19 19:26 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; pgsql-hackers
On Mon, Nov 3, 2025 at 8:53 AM Zsolt Parragi <[email protected]> wrote:
> On Mon, Nov 3, 2025 at 4:25 PM Jacob Champion
> <[email protected]> wrote:
> > The reason I ask is that we'd briefly talked about splitting
> > PGOAUTHDEBUG into more granular settings than just "off" and "UNSAFE".
>
> That's more similar to the direction I considered going,
I've added a stub summary for this, too:
https://wiki.postgresql.org/wiki/Proposal:_Split_up_PGOAUTHDEBUG
> I was
> thinking about adding a PGOAUTHDEBUG=http option. That way there's no
> need for self signed certificates, and it's easier to explain to users
> that this just allows a less secure quick http setup.
I think it's important to keep unsafe options labelled as such, but I
agree this would be helpful.
I'm not sure if we have prior art for expressing bitflags in Postgres
envvars, other than maybe PGREQUIREAUTH. A comma-separated list would
be easy to do. We could name these things according to whether they're
unsafe or not, like
PGOAUTHDEBUG=UNSAFE-http,UNSAFE-trace,print-counts
Or maybe that's too verbose, and we could say that to use any of the
unsafe options, you have to say it up front:
# http and trace are dangerous
PGOAUTHDEBUG=UNSAFE:http,trace,print-counts
# these two are safe
PGOAUTHDEBUG=print-counts,print-plugin-errors
Or something else? Since this is developer-facing, I don't think it
has to necessarily be intuitive for end users, as long as the lack of
safety remains obvious to them. We can just focus on ergonomics for
us.
Thanks,
--Jacob
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-12 11:05 Zsolt Parragi <[email protected]>
parent: Jacob Champion <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Zsolt Parragi @ 2025-12-12 11:05 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; pgsql-hackers
Hello
I implemented a simple patch based on the above suggestion
(PGOAUTHDEBUG=UNSAFE:http...). I did not update the documentation yet,
let's see what everyone thinks about it before that, and I also have
some concerns/questions.
I added the new functions into a common source file which gets
included in both the oauth module and libpq. I'm not entirely happy
about this, but I didn't see a better way without duplicating the
code.
My concern, which is also there with the current version: is an
environment variable the best way to control these settings in a
library included into many applications? Wouldn't it be better to make
these settings in libpq (or the oauth module), and only add the
environment variables to psql?
This can be used to inject a CA into an application without the user
noticing it, or without the application developer being aware of the
possibility. With the current single-value variable, it is already
possible, and in an application without a visible standard output, it
is already hidden. But by splitting the setting into multiple flags,
this can go unnoticed even in a console application.
Another question is what to do with the CA file - currently it remains
a separate (environment) variable, but maybe it could be included in
the option string instead:
PGOAUTHDEBUG=UNSAFE:custom-ca=/path/to/the/file
What do you think about it?
Attachments:
[application/octet-stream] 0001-Split-PGOAUTHDEBUG-UNSAFE-into-multiple-options.patch (13.5K, 2-0001-Split-PGOAUTHDEBUG-UNSAFE-into-multiple-options.patch)
download | inline diff:
From 1632eb23a16954a53b325e3336cdddba19db5439 Mon Sep 17 00:00:00 2001
From: Zsolt Parragi <[email protected]>
Date: Thu, 11 Dec 2025 23:56:08 +0000
Subject: [PATCH] Split PGOAUTHDEBUG=UNSAFE into multiple options
---
src/interfaces/libpq-oauth/Makefile | 12 +-
src/interfaces/libpq-oauth/meson.build | 6 +-
src/interfaces/libpq-oauth/oauth-curl.c | 18 +--
src/interfaces/libpq-oauth/oauth-utils.c | 11 --
src/interfaces/libpq-oauth/oauth-utils.h | 2 +-
src/interfaces/libpq/Makefile | 3 +-
src/interfaces/libpq/fe-auth-oauth-debug.c | 142 +++++++++++++++++++++
src/interfaces/libpq/fe-auth-oauth.c | 16 +--
src/interfaces/libpq/fe-auth-oauth.h | 19 ++-
src/interfaces/libpq/meson.build | 1 +
10 files changed, 192 insertions(+), 38 deletions(-)
create mode 100644 src/interfaces/libpq/fe-auth-oauth-debug.c
diff --git a/src/interfaces/libpq-oauth/Makefile b/src/interfaces/libpq-oauth/Makefile
index 51145f085a8..1ae7b00e816 100644
--- a/src/interfaces/libpq-oauth/Makefile
+++ b/src/interfaces/libpq-oauth/Makefile
@@ -30,15 +30,25 @@ override CFLAGS += $(PTHREAD_CFLAGS)
OBJS = \
$(WIN32RES)
-OBJS_STATIC = oauth-curl.o
+OBJS_STATIC = \
+ oauth-curl.o \
+ fe-auth-oauth-debug.o
# The shared library needs additional glue symbols.
OBJS_SHLIB = \
oauth-curl_shlib.o \
oauth-utils.o \
+ fe-auth-oauth-debug_shlib.o
oauth-utils.o: override CPPFLAGS += -DUSE_DYNAMIC_OAUTH
oauth-curl_shlib.o: override CPPFLAGS_SHLIB += -DUSE_DYNAMIC_OAUTH
+fe-auth-oauth-debug_shlib.o: override CPPFLAGS_SHLIB += -DUSE_DYNAMIC_OAUTH
+
+fe-auth-oauth-debug.o: $(libpq_srcdir)/fe-auth-oauth-debug.c
+ $(CC) $(CFLAGS) $(CPPFLAGS) -c $< -o $@
+
+fe-auth-oauth-debug_shlib.o: $(libpq_srcdir)/fe-auth-oauth-debug.c
+ $(CC) $(CFLAGS) $(CFLAGS_SL) $(CPPFLAGS) $(CPPFLAGS_SHLIB) -c $< -o $@
# Add shlib-/stlib-specific objects.
$(shlib): override OBJS += $(OBJS_SHLIB)
diff --git a/src/interfaces/libpq-oauth/meson.build b/src/interfaces/libpq-oauth/meson.build
index 505e1671b86..5c916b25e81 100644
--- a/src/interfaces/libpq-oauth/meson.build
+++ b/src/interfaces/libpq-oauth/meson.build
@@ -6,6 +6,7 @@ endif
libpq_oauth_sources = files(
'oauth-curl.c',
+ '../libpq/fe-auth-oauth-debug.c',
)
# The shared library needs additional glue symbols.
@@ -50,7 +51,10 @@ libpq_oauth_so = shared_module(libpq_oauth_name,
libpq_oauth_test_deps = []
-oauth_test_sources = files('test-oauth-curl.c') + libpq_oauth_so_sources
+oauth_test_sources = files(
+ 'test-oauth-curl.c',
+ '../libpq/fe-auth-oauth-debug.c',
+) + libpq_oauth_so_sources
if host_system == 'windows'
oauth_test_sources += rc_bin_gen.process(win32ver_rc, extra_args: [
diff --git a/src/interfaces/libpq-oauth/oauth-curl.c b/src/interfaces/libpq-oauth/oauth-curl.c
index bd0a656a166..185dbb64bab 100644
--- a/src/interfaces/libpq-oauth/oauth-curl.c
+++ b/src/interfaces/libpq-oauth/oauth-curl.c
@@ -277,7 +277,7 @@ struct async_ctx
int running; /* is asynchronous work in progress? */
bool user_prompted; /* have we already sent the authz prompt? */
bool used_basic_auth; /* did we send a client secret? */
- bool debugging; /* can we give unsafe developer assistance? */
+ oauth_debug_flags debug_flags; /* can we give developer assistance */
int dbg_num_calls; /* (debug mode) how many times were we called? */
};
@@ -978,7 +978,7 @@ parse_interval(struct async_ctx *actx, const char *interval_str)
parsed = ceil(parsed);
if (parsed < 1)
- return actx->debugging ? 0 : 1;
+ return actx->debug_flags.fast_retry ? 0 : 1;
else if (parsed >= INT_MAX)
return INT_MAX;
@@ -1753,7 +1753,7 @@ setup_curl_handles(struct async_ctx *actx)
*/
CHECK_SETOPT(actx, CURLOPT_NOSIGNAL, 1L, return false);
- if (actx->debugging)
+ if (actx->debug_flags.trace)
{
/*
* Set a callback for retrieving error information from libcurl, the
@@ -1785,7 +1785,7 @@ setup_curl_handles(struct async_ctx *actx)
const long unsafe = CURLPROTO_HTTPS | CURLPROTO_HTTP;
#endif
- if (actx->debugging)
+ if (actx->debug_flags.http)
protos = unsafe;
CHECK_SETOPT(actx, popt, protos, return false);
@@ -1799,7 +1799,7 @@ setup_curl_handles(struct async_ctx *actx)
* the flow to work at all, so any changes to the roots are likely to be
* done system-wide.
*/
- if (actx->debugging)
+ if (actx->debug_flags.custom_ca)
{
const char *env;
@@ -2265,7 +2265,7 @@ check_for_device_flow(struct async_ctx *actx)
* decent time to bail out if we're not using HTTPS for the endpoints
* we'll use for the flow.
*/
- if (!actx->debugging)
+ if (!actx->debug_flags.http)
{
if (pg_strncasecmp(provider->device_authorization_endpoint,
HTTPS_SCHEME, strlen(HTTPS_SCHEME)) != 0)
@@ -2787,8 +2787,8 @@ pg_fe_run_oauth_flow_impl(PGconn *conn)
actx->mux = PGINVALID_SOCKET;
actx->timerfd = -1;
- /* Should we enable unsafe features? */
- actx->debugging = oauth_unsafe_debugging_enabled();
+ /* Parse debug flags from environment */
+ actx->debug_flags = oauth_get_debug_flags();
state->async_ctx = actx;
@@ -3068,7 +3068,7 @@ pg_fe_run_oauth_flow(PGconn *conn)
actx = state->async_ctx;
Assert(actx || result == PGRES_POLLING_FAILED);
- if (actx && actx->debugging)
+ if (actx && actx->debug_flags.poll_counts)
{
actx->dbg_num_calls++;
if (result == PGRES_POLLING_OK || result == PGRES_POLLING_FAILED)
diff --git a/src/interfaces/libpq-oauth/oauth-utils.c b/src/interfaces/libpq-oauth/oauth-utils.c
index 45fdc7579f2..719208fead3 100644
--- a/src/interfaces/libpq-oauth/oauth-utils.c
+++ b/src/interfaces/libpq-oauth/oauth-utils.c
@@ -142,17 +142,6 @@ libpq_gettext(const char *msgid)
#endif /* ENABLE_NLS */
-/*
- * Returns true if the PGOAUTHDEBUG=UNSAFE flag is set in the environment.
- */
-bool
-oauth_unsafe_debugging_enabled(void)
-{
- const char *env = getenv("PGOAUTHDEBUG");
-
- return (env && strcmp(env, "UNSAFE") == 0);
-}
-
/*
* Duplicate SOCK_ERRNO* definitions from libpq-int.h, for use by
* pq_block/reset_sigpipe().
diff --git a/src/interfaces/libpq-oauth/oauth-utils.h b/src/interfaces/libpq-oauth/oauth-utils.h
index f4ffefef208..1762db7d3be 100644
--- a/src/interfaces/libpq-oauth/oauth-utils.h
+++ b/src/interfaces/libpq-oauth/oauth-utils.h
@@ -76,7 +76,7 @@ typedef enum
} PGTernaryBool;
extern void libpq_append_conn_error(PGconn *conn, const char *fmt,...) pg_attribute_printf(2, 3);
-extern bool oauth_unsafe_debugging_enabled(void);
+extern oauth_debug_flags oauth_get_debug_flags(void);
extern int pq_block_sigpipe(sigset_t *osigset, bool *sigpipe_pending);
extern void pq_reset_sigpipe(sigset_t *osigset, bool sigpipe_pending, bool got_epipe);
diff --git a/src/interfaces/libpq/Makefile b/src/interfaces/libpq/Makefile
index 9fe321147fc..157f80cfb5b 100644
--- a/src/interfaces/libpq/Makefile
+++ b/src/interfaces/libpq/Makefile
@@ -44,7 +44,8 @@ OBJS = \
legacy-pqsignal.o \
libpq-events.o \
pqexpbuffer.o \
- fe-auth.o
+ fe-auth.o \
+ fe-auth-oauth-debug.o
# File shared across all SSL implementations supported.
ifneq ($(with_ssl),no)
diff --git a/src/interfaces/libpq/fe-auth-oauth-debug.c b/src/interfaces/libpq/fe-auth-oauth-debug.c
new file mode 100644
index 00000000000..56bc05e6820
--- /dev/null
+++ b/src/interfaces/libpq/fe-auth-oauth-debug.c
@@ -0,0 +1,142 @@
+/*-------------------------------------------------------------------------
+ *
+ * fe-auth-oauth-debug.c
+ * Parsing logic for PGOAUTHDEBUG environment variable
+ *
+ * This file contains pure string parsing logic with no dependencies on
+ * libpq or libpq-oauth implementation details. It's compiled into both
+ * libraries to avoid code duplication.
+ *
+ * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/interfaces/libpq/fe-auth-oauth-debug.c
+ *
+ *-------------------------------------------------------------------------
+ */
+
+#include "postgres_fe.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+
+#include "fe-auth-oauth.h"
+
+/*
+ * Parse a single debug option from PGOAUTHDEBUG.
+ * Updates the flag if the option is recognized and allowed.
+ * Prints warnings for unrecognized options or unsafe options without permission.
+ */
+static void
+parse_debug_option(const char *option, oauth_debug_flags *flags, bool allow_unsafe)
+{
+ bool *flag_ptr = NULL;
+ bool is_unsafe = false;
+
+ if (strcmp(option, "http") == 0)
+ {
+ flag_ptr = &flags->http;
+ is_unsafe = true;
+ }
+ else if (strcmp(option, "trace") == 0)
+ {
+ flag_ptr = &flags->trace;
+ is_unsafe = true;
+ }
+ else if (strcmp(option, "custom-ca") == 0)
+ {
+ flag_ptr = &flags->custom_ca;
+ is_unsafe = true;
+ }
+ else if (strcmp(option, "fast-retry") == 0)
+ {
+ flag_ptr = &flags->fast_retry;
+ }
+ else if (strcmp(option, "poll-counts") == 0)
+ {
+ flag_ptr = &flags->poll_counts;
+ }
+ else if (strcmp(option, "print-plugin-errors") == 0)
+ {
+ flag_ptr = &flags->print_plugin_errors;
+ }
+
+ if (!flag_ptr)
+ {
+ fprintf(stderr,
+ "WARNING: PGOAUTHDEBUG: unrecognized debug option \"%s\" (ignored)\n",
+ option);
+ return;
+ }
+
+ if (is_unsafe && !allow_unsafe)
+ {
+ fprintf(stderr,
+ "WARNING: PGOAUTHDEBUG: unsafe option \"%s\" requires UNSAFE: prefix (ignored)\n"
+ "Use: PGOAUTHDEBUG=UNSAFE:%s\n",
+ option, option);
+ return;
+ }
+
+ *flag_ptr = true;
+}
+
+/*
+ * Parses the PGOAUTHDEBUG environment variable and returns debug flags.
+ *
+ * Supported formats:
+ * PGOAUTHDEBUG=UNSAFE - legacy format, enables all features
+ * PGOAUTHDEBUG=option1,option2 - enable safe features only
+ * PGOAUTHDEBUG=UNSAFE:opt1,opt2 - enable unsafe and/or safe features
+ *
+ * Prints a warning and skips the invalid option if:
+ * - An unrecognized option is specified
+ * - An unsafe option is specified without the UNSAFE: prefix
+ */
+oauth_debug_flags
+oauth_get_debug_flags(void)
+{
+ oauth_debug_flags flags = {0};
+ const char *env = getenv("PGOAUTHDEBUG");
+ char *options_str;
+ char *option;
+ char *saveptr = NULL;
+ bool unsafe_prefix = false;
+
+ if (!env || env[0] == '\0')
+ return flags;
+
+ if (strcmp(env, "UNSAFE") == 0)
+ {
+ flags.http = true;
+ flags.trace = true;
+ flags.custom_ca = true;
+ flags.fast_retry = true;
+ flags.poll_counts = true;
+ flags.print_plugin_errors = true;
+ return flags;
+ }
+
+ if (strncmp(env, "UNSAFE:", 7) == 0)
+ {
+ unsafe_prefix = true;
+ env += 7;
+ }
+
+ options_str = strdup(env);
+ if (!options_str)
+ return flags;
+
+ option = strtok_r(options_str, ",", &saveptr);
+ while (option != NULL)
+ {
+ parse_debug_option(option, &flags, unsafe_prefix);
+ option = strtok_r(NULL, ",", &saveptr);
+ }
+
+ free(options_str);
+
+ return flags;
+}
diff --git a/src/interfaces/libpq/fe-auth-oauth.c b/src/interfaces/libpq/fe-auth-oauth.c
index d146c5f567c..96878c59369 100644
--- a/src/interfaces/libpq/fe-auth-oauth.c
+++ b/src/interfaces/libpq/fe-auth-oauth.c
@@ -376,7 +376,7 @@ issuer_from_well_known_uri(PGconn *conn, const char *wkuri)
authority_start = wkuri + strlen(HTTPS_SCHEME);
if (!authority_start
- && oauth_unsafe_debugging_enabled()
+ && oauth_get_debug_flags().http
&& pg_strncasecmp(wkuri, HTTP_SCHEME, strlen(HTTP_SCHEME)) == 0)
{
/* Allow http:// for testing only. */
@@ -870,7 +870,7 @@ use_builtin_flow(PGconn *conn, fe_oauth_state *state)
*
* Note that POSIX dlerror() isn't guaranteed to be threadsafe.
*/
- if (oauth_unsafe_debugging_enabled())
+ if (oauth_get_debug_flags().print_plugin_errors)
fprintf(stderr, "failed dlopen for libpq-oauth: %s\n", dlerror());
return false;
@@ -884,7 +884,7 @@ use_builtin_flow(PGconn *conn, fe_oauth_state *state)
* This is more of an error condition than the one above, but due to
* the dlerror() threadsafety issue, lock it behind PGOAUTHDEBUG too.
*/
- if (oauth_unsafe_debugging_enabled())
+ if (oauth_get_debug_flags().print_plugin_errors)
fprintf(stderr, "failed dlsym for libpq-oauth: %s\n", dlerror());
dlclose(state->builtin_flow);
@@ -1385,13 +1385,3 @@ pqClearOAuthToken(PGconn *conn)
conn->oauth_token = NULL;
}
-/*
- * Returns true if the PGOAUTHDEBUG=UNSAFE flag is set in the environment.
- */
-bool
-oauth_unsafe_debugging_enabled(void)
-{
- const char *env = getenv("PGOAUTHDEBUG");
-
- return (env && strcmp(env, "UNSAFE") == 0);
-}
diff --git a/src/interfaces/libpq/fe-auth-oauth.h b/src/interfaces/libpq/fe-auth-oauth.h
index 0d59e91605b..1f0cbea7eeb 100644
--- a/src/interfaces/libpq/fe-auth-oauth.h
+++ b/src/interfaces/libpq/fe-auth-oauth.h
@@ -42,8 +42,25 @@ typedef struct
void *builtin_flow;
} fe_oauth_state;
+/*
+ * Debug flags for PGOAUTHDEBUG environment variable.
+ * Each flag controls a specific debug feature.
+ */
+typedef struct oauth_debug_flags
+{
+ /* potentially UNSAFE features */
+ bool http;
+ bool trace;
+ bool custom_ca;
+
+ /* SAFE features */
+ bool fast_retry;
+ bool poll_counts;
+ bool print_plugin_errors;
+} oauth_debug_flags;
+
extern void pqClearOAuthToken(PGconn *conn);
-extern bool oauth_unsafe_debugging_enabled(void);
+extern oauth_debug_flags oauth_get_debug_flags(void);
extern bool use_builtin_flow(PGconn *conn, fe_oauth_state *state);
/* Mechanisms in fe-auth-oauth.c */
diff --git a/src/interfaces/libpq/meson.build b/src/interfaces/libpq/meson.build
index b259c998fa2..19508b39a67 100644
--- a/src/interfaces/libpq/meson.build
+++ b/src/interfaces/libpq/meson.build
@@ -2,6 +2,7 @@
libpq_sources = files(
'fe-auth-oauth.c',
+ 'fe-auth-oauth-debug.c',
'fe-auth-scram.c',
'fe-auth.c',
'fe-cancel.c',
--
2.43.0
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-14 11:08 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Jonathan Gonzalez V. @ 2025-12-14 11:08 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; pgsql-hackers; Zsolt Parragi <[email protected]>
Hi!
Sorry for the delayed answer!
>
> Okay, that's good to know. But I'm still missing how the end user (a
> human) trusts that magic CA within the browser or device they use to
> finish the actual flow?
More than the end user "trusting" a "magic" CA, it's about what company
will tell you to use a CA, most of the time you don't have option as a
developer or even as a sysadmin since these kind of things are coming
strictly from InfoSec departments or are just some instruction to
accomplish some certification like ISO9001 or ISO27001, which for
isolated environments are required to be CA managed internally, and
must of the times the users may not see this CA, this will be actually
pushed by another external application installed in the company
workstation like CrowdStrike does, I'm not saying this will be the
case, but it's an example of how companies work now days with the CA's
>
>
> Same question as above, but I'm slowly being convinced that this
> thread needs to remain separate from the PGOAUTHDEBUG split
> discussion, even if they're related.
Totally agree, now I'm thinking the same, it should be a feature
because there's more examples that I've been thinking about that may
require this to be even a bit more flexible, for example, when working
with edge computing, if you want (in the future because now it's not
possible, yet) authenticate a device against PostgreSQL it may require
to have that CA as a encoded string int he variable, not just as a
file, wild thought I know, but it may make sense
> This might be a silly-small example, but I've added a stub spec:
>
>
> 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.
>
>
> Who's running the CI, and how do OAuth and Device Authorization
> factor
> into it? (And why would a human user be okay with feeding their
> privileges into an authorization server with a random-looking host
> name every time they run it?)
>
With that I was thinking more in the future than what you can do now,
the OAuth flow provides many features that can be implemented in the
future and I was just looking ahead with the CI example.
> > > The reason I ask is that we'd briefly talked about splitting
> > > PGOAUTHDEBUG into more granular settings than just "off" and
> > > "UNSAFE".
> >
> > I was thinking the same for another patch that will require
> > discussion
> > for sure, but it's something similar to add some levels of debug,
> > for
> > example, when you want to have the tokens or when you only want to
> > see
> > the URLs used to negotiate (which are really useful when working
> > with
> > the OAuth flows) or the deep one when you want to see the tokens.
>
> I think that's reached critical mass, then.
>
More than happy to help with this!
--
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-14 11:15 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
1 sibling, 1 reply; 24+ messages in thread
From: Jonathan Gonzalez V. @ 2025-12-14 11:15 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; Zsolt Parragi <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; pgsql-hackers
Hi!
>
> I'm not sure if we have prior art for expressing bitflags in Postgres
> envvars, other than maybe PGREQUIREAUTH. A comma-separated list would
> be easy to do. We could name these things according to whether
> they're
> unsafe or not, like
>
> PGOAUTHDEBUG=UNSAFE-http,UNSAFE-trace,print-counts
>
> Or maybe that's too verbose, and we could say that to use any of the
> unsafe options, you have to say it up front:
>
> # http and trace are dangerous
> PGOAUTHDEBUG=UNSAFE:http,trace,print-counts
> # these two are safe
> PGOAUTHDEBUG=print-counts,print-plugin-errors
>
> Or something else? Since this is developer-facing, I don't think it
> has to necessarily be intuitive for end users, as long as the lack of
> safety remains obvious to them. We can just focus on ergonomics for
> us.
I will for sure try to avoid this kind of format with comma separated
options, this mainly because are really hard to parse and manage in an
automated way, and sometimes, are hard to read when there's too many
options, and at some point, there could be many options since the flows
can start getting really complicated.
Why not keep something with debug levels? Even if it sounds really
classic, for parsing reasons are really good.
Now, if what is required it's counts or HTTP calls, probably this could
be like a "flow debug" an option like "PGOAUTHFLOWDEBUG" that depending
on the levels (info, debug, trace) can print from the hosts and/or url
calls, to the headers sent and received from the hosts.
The debug of a flow can be an entire set of levels due to the current
complexity and that may or may not increase in time, what do you think?
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-16 18:48 Jacob Champion <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Jacob Champion @ 2025-12-16 18:48 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; pgsql-hackers
On Fri, Dec 12, 2025 at 3:05 AM Zsolt Parragi <[email protected]> wrote:
> I implemented a simple patch based on the above suggestion
> (PGOAUTHDEBUG=UNSAFE:http...).
Thank you!
> I added the new functions into a common source file which gets
> included in both the oauth module and libpq. I'm not entirely happy
> about this, but I didn't see a better way without duplicating the
> code.
Yeah, I'm not entirely happy about it either. Let me think about some
alternatives... pgcommon is a possibility, as is reworking the API so
that it's more ephemeral (these probably don't need to be super
performant, so an `inline static` header implementation that reparses
the envvar each time might work).
> My concern, which is also there with the current version: is an
> environment variable the best way to control these settings in a
> library included into many applications? Wouldn't it be better to make
> these settings in libpq (or the oauth module), and only add the
> environment variables to psql?
1) Do you plan to be setting debug variables in production?
2) Do you want these settings to be part of a postgres:// URI?
For me the answer to both is "no", so I'm not too excited about adding
these as libpq connection parameters. Did you have another idea in
mind?
> This can be used to inject a CA into an application without the user
> noticing it,
I don't disagree. But at this point in these conversations, the
question posed is typically "is the new risk/reward tradeoff any worse
than PGSSLROOTCERT or PGSSLMODE or PGSERVICEFILE (or LD_LIBRARY_PATH
or PATH)?" I'd say no, not enough to introduce a new way of
configuring things for this particular setting.
I've argued in the other direction before [1], but I still feel good
about that, because I think a global keylogfile is more sensitive than
this.
> or without the application developer being aware of the
> possibility.
Mmm... I'd say that application developers always have to be aware of
user environment changes in the context of any Linux programming, let
alone libpq client development. The user is generally in partial
control of the linker. Nearly every libpq setting is accessible via
the environment. (setuid programming is its own specialized skillset
for a reason.)
Now, if there's any appetite to make the situation better, continuing
to add security-critical settings into the environment makes things
worse for anyone who wants to propose an alternative. But that's where
we stood as of the last related conversation I was involved in.
> But by splitting the setting into multiple flags,
> this can go unnoticed even in a console application.
Because it's not obviously spraying output all the time, you mean? We
could perhaps be noisier when any UNSAFE setting is in use.
> Another question is what to do with the CA file - currently it remains
> a separate (environment) variable, but maybe it could be included in
> the option string instead:
> PGOAUTHDEBUG=UNSAFE:custom-ca=/path/to/the/file
I think I've been convinced by the existence of this thread that we
should split PGOAUTHCAFILE out of debug options entirely, pending the
resolution of some of the open questions.
Thanks,
--Jacob
[1] https://postgr.es/m/CAOYmi%2BmY7zBXTqJT6EYP_6sdk7ro8L8ByToKb4f-hU5qnpOxhw%40mail.gmail.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-16 19:16 Jacob Champion <[email protected]>
parent: Jonathan Gonzalez V. <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Jacob Champion @ 2025-12-16 19:16 UTC (permalink / raw)
To: Jonathan Gonzalez V. <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
On Sun, Dec 14, 2025 at 3:13 AM Jonathan Gonzalez V.
<[email protected]> wrote:
> > Okay, that's good to know. But I'm still missing how the end user (a
> > human) trusts that magic CA within the browser or device they use to
> > finish the actual flow?
>
> More than the end user "trusting" a "magic" CA, it's about what company
> will tell you to use a CA
Sure, but my question isn't about the trust model. Just to confirm:
you're saying that it's common for enterprise provisioning apps
(CrowdStrike et al) to push CAs directly into a browser trust store,
but _not_ to the system trust paths?
> Totally agree, now I'm thinking the same, it should be a feature
> because there's more examples that I've been thinking about that may
> require this to be even a bit more flexible, for example, when working
> with edge computing, if you want (in the future because now it's not
> possible, yet) authenticate a device against PostgreSQL it may require
> to have that CA as a encoded string int he variable, not just as a
> file, wild thought I know, but it may make sense
I think we want to keep these on disk; no reason to run up against
resource limits on the environment.
> > 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?
On Sun, Dec 14, 2025 at 3:17 AM Jonathan Gonzalez V.
<[email protected]> wrote:
> I will for sure try to avoid this kind of format with comma separated
> options, this mainly because are really hard to parse and manage in an
> automated way
I feel _very_ strongly that the "debug" options are for people.
Specifically developers who are debugging. What use case do you have
for automation and parsing outside of libpq?
> and sometimes, are hard to read when there's too many
> options, and at some point, there could be many options since the flows
> can start getting really complicated.
Can you explain more about what kinds of use cases would lead to
option explosion? When I'm developing I typically want to export an
interesting group of options once, and then not think about it for a
while. When debugging in production I typically want one particular
thing at a time.
> Why not keep something with debug levels? Even if it sounds really
> classic, for parsing reasons are really good.
I would say: because there's no natural order to the settings. It's a
bunch of on/off behaviors, some of which are safety-critical. What is
the "debug level" of disabling encryption compared to the debug level
of printing secrets or turning off parameter validation?
--Jacob
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-17 13:15 Zsolt Parragi <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Zsolt Parragi @ 2025-12-17 13:15 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; pgsql-hackers
> I don't disagree. But at this point in these conversations, the
> question posed is typically "is the new risk/reward tradeoff any worse
> than PGSSLROOTCERT or PGSSLMODE or PGSERVICEFILE (or LD_LIBRARY_PATH
> or PATH)?" I'd say no, not enough to introduce a new way of
> configuring things for this particular setting.
Those are also bad, but there are also parameter alternatives for all of them.
> 2) Do you want these settings to be part of a postgres:// URI?
Not for debug settings, but if everyone agrees on splitting the CA
into its own setting, it could behave the same way as
sslrootcert/PGSSLROOTCERT.
> Because it's not obviously spraying output all the time, you mean? We
> could perhaps be noisier when any UNSAFE setting is in use.
Yes, mainly that. And as you mentioned there's already existing
behavior like that in the code, so it's nothing new.
> Yeah, I'm not entirely happy about it either. Let me think about some
> alternatives...
I'll try these suggestions and see what they look like - and I'll
start a separate thread with it so that this thread can focus on the
CA variable.
> Mmm... I'd say that application developers always have to be aware of
> user environment changes in the context of any Linux programming, let
> alone libpq client development. The user is generally in partial
> control of the linker. Nearly every libpq setting is accessible via
> the environment. (setuid programming is its own specialized skillset
> for a reason.)
My concern is not somebody developing libpq directly on Linux, but
more complex situations.
For example:
1. there is libpq
2. libpq is used by scripting language bindings for python/ruby/etc
3. language libraries are used in ORM frameworks, which have their own
configuration interface
4. ORM frameworks are used in web frameworks / other libraries
5. those frameworks/libraries get used by somebody writing an actual
webpage/application
6. And that webpage/application gets installed/maintained by an
administrator/user, who might or might not be aware of this
And we also have Windows/other platforms, where environment variables
are less visible.
> Now, if there's any appetite to make the situation better, continuing
> to add security-critical settings into the environment makes things
> worse for anyone who wants to propose an alternative
This is also probably a separate discussion, but what do you think
about introducing a parameter that disables environment variable
fallbacks? Both for existing variables like PGSSLROOTCERT and
new/debug variables like PGOAUTHCAFILE. (by default everything works
as currently; when specified environment variables are ignored)
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-18 17:51 Jacob Champion <[email protected]>
parent: Zsolt Parragi <[email protected]>
0 siblings, 0 replies; 24+ messages in thread
From: Jacob Champion @ 2025-12-18 17:51 UTC (permalink / raw)
To: Zsolt Parragi <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Jonathan Gonzalez V. <[email protected]>; pgsql-hackers
On Wed, Dec 17, 2025 at 5:15 AM Zsolt Parragi <[email protected]> wrote:
> > I don't disagree. But at this point in these conversations, the
> > question posed is typically "is the new risk/reward tradeoff any worse
> > than PGSSLROOTCERT or PGSSLMODE or PGSERVICEFILE (or LD_LIBRARY_PATH
> > or PATH)?" I'd say no, not enough to introduce a new way of
> > configuring things for this particular setting.
>
> Those are also bad, but there are also parameter alternatives for all of them.
But if the concern is security, the existence of the parameter
alternatives doesn't really seem to help you.
> > 2) Do you want these settings to be part of a postgres:// URI?
>
> Not for debug settings, but if everyone agrees on splitting the CA
> into its own setting, it could behave the same way as
> sslrootcert/PGSSLROOTCERT.
I don't know if everyone does, but I've seen no pushback yet, so I
plan to do so (parameter + envvar).
I only nested it under debug settings because I thought there was no
production use case, and now that Jonathan is saying "yes, I have a
production case", that seems good enough. I don't think I need to
raise the bar any more than that.
> > Mmm... I'd say that application developers always have to be aware of
> > user environment changes in the context of any Linux programming, let
> > alone libpq client development. The user is generally in partial
> > control of the linker. Nearly every libpq setting is accessible via
> > the environment. (setuid programming is its own specialized skillset
> > for a reason.)
>
> My concern is not somebody developing libpq directly on Linux, but
> more complex situations.
Understood (and I'd like to make this better too). But see below.
> This is also probably a separate discussion, but what do you think
> about introducing a parameter that disables environment variable
> fallbacks? Both for existing variables like PGSSLROOTCERT and
> new/debug variables like PGOAUTHCAFILE. (by default everything works
> as currently; when specified environment variables are ignored)
I like the idea of more application control over this, generally. I
think the concern that's been raised before, for example with the
superuser-can-do-too-much conversations, is: Piecemeal improvements,
without a consensus on the end goal, can paradoxically make things
less secure. Because now users have a harder time reasoning about the
system's behavior and designing for it. It's even worse if committers
can't reason about it and start working at cross purposes.
For example, if we lock down our envvars and then immediately farm
security-critical decisions out to Kerberos or Curl or PAM or etc.
which use their own envvars, in what cases is that better than telling
application developers that hey, you always need to be careful about
sanitizing your environment if you somehow don't trust it? I don't
really know (hand-wavy), and I think proposals would need to provide
good arguments in favor. Definitely a separate discussion.
Thanks,
--Jacob
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode
@ 2025-12-20 17:48 Jonathan Gonzalez V. <[email protected]>
parent: Jacob Champion <[email protected]>
0 siblings, 1 reply; 24+ messages in thread
From: Jonathan Gonzalez V. @ 2025-12-20 17:48 UTC (permalink / raw)
To: Jacob Champion <[email protected]>; +Cc: Zsolt Parragi <[email protected]>; Daniel Gustafsson <[email protected]>; pgsql-hackers
Hi!
On Tue, 2025-12-16 at 11:16 -0800, Jacob Champion wrote:
>
> Sure, but my question isn't about the trust model. Just to confirm:
> you're saying that it's common for enterprise provisioning apps
> (CrowdStrike et al) to push CAs directly into a browser trust store,
> but _not_ to the system trust paths?
Yes! that use case it's more usual than one will expect, for example,
if you're routing different traffic for different apps into different
VPNs or routes, even between different browsers, this is also really
common now days because of the jailed-apps, something that in Linux
systems is pretty common, like snap, that create these "jailed"
environment so they can be supported across systems.
>
> > Totally agree, now I'm thinking the same, it should be a feature
> > because there's more examples that I've been thinking about that
> > may
> > require this to be even a bit more flexible, for example, when
> > working
> > with edge computing, if you want (in the future because now it's
> > not
> > possible, yet) authenticate a device against PostgreSQL it may
> > require
> > to have that CA as a encoded string int he variable, not just as a
> > file, wild thought I know, but it may make sense
>
> I think we want to keep these on disk; no reason to run up against
> resource limits on the environment.
No questions about it! was just an option that someone may come up
with! I've seen so many weird things that people does, specially on
shell scripts.
>
> > > 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!
>
> I feel _very_ strongly that the "debug" options are for people.
> Specifically developers who are debugging. What use case do you have
> for automation and parsing outside of libpq?
Well, I have something to say about.
In my opinion, "debug" it's not just developers, helps a lot when
running and managing system, specially when using new technologies
(like this one specifically), helps to understand the flow and also to
realize what's going on and tune the configurations, this it's always
very useful when managing small or large systems.
On the other hand, 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.
>
> > and sometimes, are hard to read when there's too many
> > options, and at some point, there could be many options since the
> > flows
> > can start getting really complicated.
>
> Can you explain more about what kinds of use cases would lead to
> option explosion? When I'm developing I typically want to export an
> interesting group of options once, and then not think about it for a
> while. When debugging in production I typically want one particular
> thing at a time.
Like an explicitly situations, I can imagine handling many different
environments to connect to and changing, not just OAuth config, but all
the configurations related to libpq on mixed different configurations,
even different authentication methods.
>
> > Why not keep something with debug levels? Even if it sounds really
> > classic, for parsing reasons are really good.
>
> I would say: because there's no natural order to the settings. It's a
> bunch of on/off behaviors, some of which are safety-critical. What is
> the "debug level" of disabling encryption compared to the debug level
> of printing secrets or turning off parameter validation?
Well, I think I was misunderstood here, when I was talking about "debug
levels" I was talking about logs debug levels, now, disabling the
encryption, I'm guessing you mean HTTPS vs HTTP, if that's the case,
well, that should be controlled by the user when setting the endpoint,
I don't think it's something that should be controlled in another way
than just the endpoint protocol.
Now I'm confused about what we talk about when we write "debug level",
can you clarify what does it mean to you?
Regards!
^ permalink raw reply [nested|flat] 24+ 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: Jonathan Gonzalez V. <[email protected]>
0 siblings, 2 replies; 24+ 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] 24+ 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; 24+ 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] 24+ 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; 24+ 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] 24+ 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; 24+ 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] 24+ 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; 24+ 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] 24+ 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; 24+ 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] 24+ 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; 24+ 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] 24+ 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; 24+ 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] 24+ messages in thread
end of thread, other threads:[~2026-03-30 21:33 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-10-29 19:19 Make PGOAUTHCAFILE in libpq-oauth work out of debug mode Jonathan Gonzalez V. <[email protected]>
2025-11-03 14:24 ` Daniel Gustafsson <[email protected]>
2025-11-03 16:24 ` Jacob Champion <[email protected]>
2025-11-03 16:53 ` Zsolt Parragi <[email protected]>
2025-11-19 19:26 ` Jacob Champion <[email protected]>
2025-12-12 11:05 ` Zsolt Parragi <[email protected]>
2025-12-16 18:48 ` Jacob Champion <[email protected]>
2025-12-17 13:15 ` Zsolt Parragi <[email protected]>
2025-12-18 17:51 ` Jacob Champion <[email protected]>
2025-12-14 11:15 ` Jonathan Gonzalez V. <[email protected]>
2025-12-16 19:16 ` Jacob Champion <[email protected]>
2025-12-20 17:48 ` Jonathan Gonzalez V. <[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]>
2025-11-04 13:00 ` Jonathan Gonzalez V. <[email protected]>
2025-11-04 13:26 ` Daniel Gustafsson <[email protected]>
2025-11-19 18:55 ` Jacob Champion <[email protected]>
2025-12-14 11:08 ` Jonathan Gonzalez V. <[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