Обсуждение: Make PGOAUTHCAFILE in libpq-oauth work out of debug mode

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. <jonathan.abdiel@gmail.com>

> On 29 Oct 2025, at 20:19, Jonathan Gonzalez V. <jonathan.abdiel@gmail.com> 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

On Mon, Nov 3, 2025 at 6:24 AM Daniel Gustafsson <daniel@yesql.se> 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

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
<jacob.champion@enterprisedb.com> wrote:
>
> On Mon, Nov 3, 2025 at 6:24 AM Daniel Gustafsson <daniel@yesql.se> 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
>
>

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. <jonathan.abdiel@gmail.com>

> On 4 Nov 2025, at 14:00, Jonathan Gonzalez V. <jonathan.abdiel@gmail.com> 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