public inbox for [email protected]
help / color / mirror / Atom feedFrom: Jacob Champion <[email protected]>
To: Jonathan Gonzalez V. <[email protected]>
Cc: [email protected]
Subject: Re: [oauth] Add TLS support to OAuth tests
Date: Mon, 2 Mar 2026 11:31:31 -0800
Message-ID: <CAOYmi+=wFNp6PaTFkoXjV37dNKHT5wTPeLjcdjLQ8uva7CK+Hw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAOYmi+kASL+i0iaHzurBrx=mU83pP4MBX1Xm-sVnx+QvHSp_=w@mail.gmail.com>
<[email protected]>
On Fri, Feb 27, 2026 at 9:09 PM Jonathan Gonzalez V.
<[email protected]> wrote:
> That's indeed good to know, there's a way to know which is the oldest
> version of Python used?
We have a partial list of minimum dependencies here:
https://wiki.postgresql.org/wiki/Minimum_Dependency_Versions
> What do you think about adding this information
> on top of the `oauth_server.py` file?
I think if we put it into the repo somewhere (and honestly, we
probably should), it should be centralized rather than per-file. It'll
rot really quickly otherwise.
So I've pulled in part of your -0003, done some more comment-smithing,
and squashed it all down for commit. Attached in v2.
Thanks!
--Jacob
Attachments:
[application/octet-stream] v2-0001-oauth-Add-TLS-support-for-oauth_validator-tests.patch (13.5K, ../CAOYmi+=wFNp6PaTFkoXjV37dNKHT5wTPeLjcdjLQ8uva7CK+Hw@mail.gmail.com/2-v2-0001-oauth-Add-TLS-support-for-oauth_validator-tests.patch)
download | inline diff:
From 243cb478f3f512b459873f55530614338e821aa9 Mon Sep 17 00:00:00 2001
From: Jacob Champion <[email protected]>
Date: Mon, 2 Mar 2026 10:59:04 -0800
Subject: [PATCH v2] oauth: Add TLS support for oauth_validator tests
The oauth_validator tests don't currently support HTTPS, which makes
testing PGOAUTHCAFILE difficult. Add a localhost certificate to
src/test/ssl and make use of it in oauth_server.py.
In passing, explain the hardcoded use of IPv4 in our issuer identifier,
after intermittent failures on NetBSD led to commit 8d9d5843b. (The new
certificate is still set up for IPv6, to make it easier to improve that
behavior in the future.)
Patch by Jonathan Gonzalez V., with some additional tests and tweaks by
me.
Author: Jonathan Gonzalez V. <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/test/modules/oauth_validator/meson.build | 1 +
src/test/modules/oauth_validator/Makefile | 1 +
.../modules/oauth_validator/t/001_server.pl | 53 +++++++++++++++----
.../modules/oauth_validator/t/OAuth/Server.pm | 7 ++-
.../modules/oauth_validator/t/oauth_server.py | 22 +++++++-
.../conf/server-localhost-alt-names.config | 20 +++++++
.../ssl/ssl/server-localhost-alt-names.crt | 20 +++++++
.../ssl/ssl/server-localhost-alt-names.key | 28 ++++++++++
src/test/ssl/sslfiles.mk | 1 +
9 files changed, 137 insertions(+), 16 deletions(-)
create mode 100644 src/test/ssl/conf/server-localhost-alt-names.config
create mode 100644 src/test/ssl/ssl/server-localhost-alt-names.crt
create mode 100644 src/test/ssl/ssl/server-localhost-alt-names.key
diff --git a/src/test/modules/oauth_validator/meson.build b/src/test/modules/oauth_validator/meson.build
index c4b73e05297..506a9894b8d 100644
--- a/src/test/modules/oauth_validator/meson.build
+++ b/src/test/modules/oauth_validator/meson.build
@@ -80,6 +80,7 @@ tests += {
'PYTHON': python.full_path(),
'with_libcurl': oauth_flow_supported ? 'yes' : 'no',
'with_python': 'yes',
+ 'cert_dir': meson.project_source_root() / 'src/test/ssl/ssl',
},
'deps': [oauth_hook_client],
},
diff --git a/src/test/modules/oauth_validator/Makefile b/src/test/modules/oauth_validator/Makefile
index cb64f0f1437..0b39a88fd9f 100644
--- a/src/test/modules/oauth_validator/Makefile
+++ b/src/test/modules/oauth_validator/Makefile
@@ -36,5 +36,6 @@ include $(top_srcdir)/contrib/contrib-global.mk
export PYTHON
export with_libcurl
export with_python
+export cert_dir=$(top_srcdir)/src/test/ssl/ssl
endif
diff --git a/src/test/modules/oauth_validator/t/001_server.pl b/src/test/modules/oauth_validator/t/001_server.pl
index 6b649c0b06f..cdad2ae8011 100644
--- a/src/test/modules/oauth_validator/t/001_server.pl
+++ b/src/test/modules/oauth_validator/t/001_server.pl
@@ -71,9 +71,31 @@ END
$? = $exit_code;
}
+# To test against HTTPS with our custom CA, we need to enable PGOAUTHDEBUG and
+# PGOAUTHCAFILE. But first, check to make sure the client refuses HTTP and
+# untrusted HTTPS connections by default.
my $port = $webserver->port();
my $issuer = "http://127.0.0.1:$port";
+unlink($node->data_dir . '/pg_hba.conf');
+$node->append_conf(
+ 'pg_hba.conf', qq{
+local all test oauth issuer="$issuer" scope="openid postgres"
+});
+$node->reload;
+
+my $log_start = $node->wait_for_log(qr/reloading configuration files/);
+
+$node->connect_fails(
+ "user=test dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635",
+ "HTTPS is required without debug mode",
+ expected_stderr =>
+ qr@OAuth discovery URI "\Q$issuer\E/.well-known/openid-configuration" must use HTTPS@
+);
+
+# Switch to HTTPS.
+$issuer = "https://127.0.0.1:$port";
+
unlink($node->data_dir . '/pg_hba.conf');
$node->append_conf(
'pg_hba.conf', qq{
@@ -83,7 +105,8 @@ local all testparam oauth issuer="$issuer/param" scope="openid postgres"
});
$node->reload;
-my $log_start = $node->wait_for_log(qr/reloading configuration files/);
+$log_start =
+ $node->wait_for_log(qr/reloading configuration files/, $log_start);
# Check pg_hba_file_rules() support.
my $contents = $bgconn->query_safe(
@@ -96,17 +119,27 @@ is( $contents,
3|oauth|\{issuer=$issuer/param,"scope=openid postgres",validator=validator\}},
"pg_hba_file_rules recreates OAuth HBA settings");
-# To test against HTTP rather than HTTPS, we need to enable PGOAUTHDEBUG. But
-# first, check to make sure the client refuses such connections by default.
-$node->connect_fails(
- "user=test dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635",
- "HTTPS is required without debug mode",
- expected_stderr =>
- qr@OAuth discovery URI "\Q$issuer\E/.well-known/openid-configuration" must use HTTPS@
-);
-
+# Make sure PGOAUTHDEBUG=UNSAFE doesn't disable certificate verification.
$ENV{PGOAUTHDEBUG} = "UNSAFE";
+$node->connect_fails(
+ "user=test dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635",
+ "HTTPS trusts only system CA roots by default",
+ # Note that the latter half of this error message comes from Curl, which has
+ # had a few variants since 7.61:
+ #
+ # - SSL peer certificate or SSH remote key was not OK
+ # - Peer certificate cannot be authenticated with given CA certificates
+ # - Issuer check against peer certificate failed
+ #
+ # Key off of the "peer certificate" portion, since that seems to have
+ # remained constant over a long period of time.
+ 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";
+
my $user = "test";
$node->connect_ok(
"user=$user dbname=postgres oauth_issuer=$issuer oauth_client_id=f02c6361-0635",
diff --git a/src/test/modules/oauth_validator/t/OAuth/Server.pm b/src/test/modules/oauth_validator/t/OAuth/Server.pm
index 89ea7ab4a4c..d923d4c5eb2 100644
--- a/src/test/modules/oauth_validator/t/OAuth/Server.pm
+++ b/src/test/modules/oauth_validator/t/OAuth/Server.pm
@@ -15,7 +15,7 @@ OAuth::Server - runs a mock OAuth authorization server for testing
$server->run;
my $port = $server->port;
- my $issuer = "http://127.0.0.1:$port";
+ my $issuer = "https://127.0.0.1:$port";
# test against $issuer...
@@ -27,9 +27,8 @@ This is glue API between the Perl tests and the Python authorization server
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 does not use TLS (it implements a nonstandard, unsafe
-issuer at "http://127.0.0.1:<port>"), so libpq in particular will need to set
-PGOAUTHDEBUG=UNSAFE to be able to talk to it.
+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.
=cut
diff --git a/src/test/modules/oauth_validator/t/oauth_server.py b/src/test/modules/oauth_validator/t/oauth_server.py
index c70783ecbe4..6df8c2ca5df 100755
--- a/src/test/modules/oauth_validator/t/oauth_server.py
+++ b/src/test/modules/oauth_validator/t/oauth_server.py
@@ -11,12 +11,17 @@ import functools
import http.server
import json
import os
+import ssl
import sys
import time
import urllib.parse
from collections import defaultdict
from typing import Dict
+ssl_dir = os.getenv("cert_dir")
+ssl_cert = ssl_dir + "/server-localhost-alt-names.crt"
+ssl_key = ssl_dir + "/server-localhost-alt-names.key"
+
class OAuthHandler(http.server.BaseHTTPRequestHandler):
"""
@@ -295,7 +300,11 @@ class OAuthHandler(http.server.BaseHTTPRequestHandler):
def config(self) -> JsonObject:
port = self.server.socket.getsockname()[1]
- issuer = f"http://127.0.0.1:{port}"
+ # XXX This IPv4-only Issuer can't be changed to "localhost" unless our
+ # server also listens on the corresponding IPv6 port when available.
+ # Otherwise, other processes with ephemeral sockets could accidentally
+ # interfere with our Curl client, causing intermittent failures.
+ issuer = f"https://127.0.0.1:{port}"
if self._alt_issuer:
issuer += "/alternate"
elif self._parameterized:
@@ -408,9 +417,18 @@ def main():
Starts the authorization server on localhost. The ephemeral port in use will
be printed to stdout.
"""
-
+ # XXX Listen exclusively on IPv4. Listening on a dual-stack socket would be
+ # more true-to-life, but every OS/Python combination in the buildfarm and CI
+ # would need to provide the functionality first.
s = http.server.HTTPServer(("127.0.0.1", 0), OAuthHandler)
+ # Speak HTTPS.
+ # TODO: switch to HTTPSServer with Python 3.14
+ ssl_context = ssl.SSLContext(ssl.PROTOCOL_TLS_SERVER)
+ ssl_context.load_cert_chain(ssl_cert, ssl_key)
+
+ s.socket = ssl_context.wrap_socket(s.socket, server_side=True)
+
# Attach a "cache" dictionary to the server to allow the OAuthHandlers to
# track state across token requests. The use of defaultdict ensures that new
# entries will be created automatically.
diff --git a/src/test/ssl/conf/server-localhost-alt-names.config b/src/test/ssl/conf/server-localhost-alt-names.config
new file mode 100644
index 00000000000..1c41c1ecb78
--- /dev/null
+++ b/src/test/ssl/conf/server-localhost-alt-names.config
@@ -0,0 +1,20 @@
+# An OpenSSL format CSR config file for creating a server certificate.
+#
+# This certificate contains SANs for localhost (DNS, IPv4, and IPv6).
+
+[ req ]
+distinguished_name = req_distinguished_name
+req_extensions = v3_req
+prompt = no
+
+[ req_distinguished_name ]
+OU = PostgreSQL test suite
+
+# For Subject Alternative Names
+[ v3_req ]
+subjectAltName = @alt_names
+
+[ alt_names ]
+DNS.1 = localhost
+IP.1 = 127.0.0.1
+IP.2 = ::1
diff --git a/src/test/ssl/ssl/server-localhost-alt-names.crt b/src/test/ssl/ssl/server-localhost-alt-names.crt
new file mode 100644
index 00000000000..106dc34d7bb
--- /dev/null
+++ b/src/test/ssl/ssl/server-localhost-alt-names.crt
@@ -0,0 +1,20 @@
+-----BEGIN CERTIFICATE-----
+MIIDVjCCAj6gAwIBAgIIICYCJxRTBwAwDQYJKoZIhvcNAQELBQAwQjFAMD4GA1UE
+Aww3VGVzdCBDQSBmb3IgUG9zdGdyZVNRTCBTU0wgcmVncmVzc2lvbiB0ZXN0IHNl
+cnZlciBjZXJ0czAgFw0yNjAyMjcyMjUzMDdaGA8yMDUzMDcxNTIyNTMwN1owIDEe
+MBwGA1UECwwVUG9zdGdyZVNRTCB0ZXN0IHN1aXRlMIIBIjANBgkqhkiG9w0BAQEF
+AAOCAQ8AMIIBCgKCAQEA3k/aT/OV8sbJrvhtSgz5eNMCuv7RKdUQw+f52DpZTs85
+lTXIRs+l3mXoKRjN1gqzqlHInnJlhxQipqGiJfz4Li8L6jma2yZztFHH+f+YF8Ke
+5fCYP1qMxbghqeIRkKgrCEjHUnOhbN5oMi/Ndt9AXWGG/39uk5Xec/Y/J5aZkPVV
+blqWYyQQ+4U783lwZs1EUWdfiTVRp8fYADT/2lHjaZaX08vAE5VvCbBv6mPhPfno
+F9FIaW+CRuwORisFK8Bd1q/0r5aPZGPi0lokCdaB/cRUHwJK1/HHgyB3N+Lk4swf
+z+MfSqj4IaNPW7zn3EV9hgpVwSmB5ES8rzojiGtMDQIDAQABo3AwbjAsBgNVHREE
+JTAjgglsb2NhbGhvc3SHBH8AAAGHEAAAAAAAAAAAAAAAAAAAAAEwHQYDVR0OBBYE
+FOZ8KClKVbeYecn8lvAldBXOjQz6MB8GA1UdIwQYMBaAFPKPOmZAUGRIItcugv9W
+nsKz7nQKMA0GCSqGSIb3DQEBCwUAA4IBAQDE1FGw20H0Flo3gAGN0ND9G/6wDxWM
+MldbXRjqc1E0/+7+Zs6v1jPrNUNEvxy5kHWevUJCIt6y4SYt01JxE4wqEPJ3UBAv
+cM0p08mohmN/CHc/lswXx12MZMfaLA1/WRPqvtiGFOrOOPvaRKHO4ORiT1KWmtOO
+FgcW9E1Q1iJFK28xdz9NEEBWEurEIr5KGAsCwf9DfQxPJXiS9n98BDI8gPwlse7t
+VqyhGVSj+EPbdY2kqkSuPXacdnUGfO6EWo9PFKqhxWMxABLuK0UZzH6/1lMOh1m9
+Mm+gtwO5RLBX22V+KIs1uuDTNcveQ2DsZnMZh7lGD05eHYG9hwnC6GNZ
+-----END CERTIFICATE-----
diff --git a/src/test/ssl/ssl/server-localhost-alt-names.key b/src/test/ssl/ssl/server-localhost-alt-names.key
new file mode 100644
index 00000000000..4499a11928d
--- /dev/null
+++ b/src/test/ssl/ssl/server-localhost-alt-names.key
@@ -0,0 +1,28 @@
+-----BEGIN PRIVATE KEY-----
+MIIEvQIBADANBgkqhkiG9w0BAQEFAASCBKcwggSjAgEAAoIBAQDeT9pP85Xyxsmu
++G1KDPl40wK6/tEp1RDD5/nYOllOzzmVNchGz6XeZegpGM3WCrOqUciecmWHFCKm
+oaIl/PguLwvqOZrbJnO0Ucf5/5gXwp7l8Jg/WozFuCGp4hGQqCsISMdSc6Fs3mgy
+L81230BdYYb/f26Tld5z9j8nlpmQ9VVuWpZjJBD7hTvzeXBmzURRZ1+JNVGnx9gA
+NP/aUeNplpfTy8ATlW8JsG/qY+E9+egX0Uhpb4JG7A5GKwUrwF3Wr/Svlo9kY+LS
+WiQJ1oH9xFQfAkrX8ceDIHc34uTizB/P4x9KqPgho09bvOfcRX2GClXBKYHkRLyv
+OiOIa0wNAgMBAAECggEAFchiPkJCV4r12RCbeM2DpjyawGLWcNBhN6jjuLWi6Y9x
+d3bRHGsdOAjpMhmtlYLv7sjbrPbNjupAqO4eerVqRfAzLSyeyUlfvfPjcdIC/5UA
+x8wGxvJi576ugbxWd0ObD9E9woz07LtwHzbC3ZprbprvRNqiJZDiPp+KuaDOhD7u
+6XAM8JilFqfiDN8+xbH2dWdVkdt2OD5wctJbqy6moH9VFVsWsMQr3/vJkSdUPLxa
+8ATUubFhO/sqE+KsMZESq5W1Xbj3NwMkvnA92yG9+ED60NPjFzgheZZWSmXe1B/c
+XB3G/upvCoHEgKbrnYt05b/ryUbXAZkvi5oL4fp9OwKBgQD4d+Qm4GiKEWvjZ5II
+ROfHEyoWOHw9z8ydJIrtOL8ICh5RH8D/v2IaMAacWV5eLoJ7aYC6yIYuWdHQljAi
+zltNFrsLFmWXLy91IWfUzIGnFLWeqOmI50vlM8xU54rD/cZ3qtvr2Qk9HHs0dsyB
+6cGRf0BPJi04aAEqSZqc8HCXAwKBgQDlDP0MW57bHpqQROQDLIgEX9/rzUNo48Z/
+1f27bCkKP+CpizE9eWvGs5rQmUxCNzWULFxIuBbgsubuVP7jO3piY6bRGnvSE6nD
+mW0V1mSypVO22Ci/Q8ekkY2+0ZVp3qLPO/cwtI/Ye8kp4xu41I2XgJE8Mo0hEEyJ
+N1/1vUJbrwKBgBp3gukVPG2An5JwpOCWnm3ZP8FwMOPQr8YJb3cHdWng0gvoKwHT
+HBsYBIxBBMlZgPKucVT0KT7kuHHUnboHazhR9Iig0R+CmjaK4WmMgz8N+K625XF8
+2dvHYbulkmWAMdTrcVO1IcPNtd4HzY8FHGZoPKxxr51zjrQ3dO3EuumLAoGATho2
+sx8OtPLji2wiP77QhoVWqmYspTh9+Bs00NLZz6fmaImQ+cBMcs3NbXHIYg/HUkYq
+FZXIH0iBnCUZYMxoN+J5AHZCYGjaC1tmqfqYDZ54RDHC+y0Wh1QmfDmk9Bu5cmal
+LFN1dUEIYCMT0duQiGeLnnYyT2LqZiOesgGd/fsCgYEA2GbKteq+io6HAEt2/yry
+xZGaRR8Twg0B8XtD9NHCbgizmZiD/mADgyhkgjUsDIkcMzEt+sA4IK9ORgIYqS+/
+q2eY1QRKpoZgJJfE8dU88B35YGqdZuXENR4I7w+JrKCCCk5jSiwylvsBsi1HX8Qu
+EdQBBRiwkRnxQ83hqRI3ymw=
+-----END PRIVATE KEY-----
diff --git a/src/test/ssl/sslfiles.mk b/src/test/ssl/sslfiles.mk
index ecb40588c87..f32c53a76a1 100644
--- a/src/test/ssl/sslfiles.mk
+++ b/src/test/ssl/sslfiles.mk
@@ -31,6 +31,7 @@ SERVERS := server-cn-and-alt-names \
server-ip-in-dnsname \
server-single-alt-name \
server-multiple-alt-names \
+ server-localhost-alt-names \
server-no-names \
server-revoked
CLIENTS := client client-dn client-revoked client_ext client-long \
--
2.34.1
view thread (6+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: [oauth] Add TLS support to OAuth tests
In-Reply-To: <CAOYmi+=wFNp6PaTFkoXjV37dNKHT5wTPeLjcdjLQ8uva7CK+Hw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox