Horton

Re: [PoC] Federated Authn/z with OAUTHBEARER

ivan.kush@tantorlabs.com

View thread
From: Ivan Kush <ivan.kush@tantorlabs.com>
To: Jacob Champion <jacob.champion@enterprisedb.com>, Wolfgang Walther <walther@technowledgy.de>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2025-04-20T17:12:01Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. meson: Fix install-quiet after clean

  2. oauth: Run Autoconf tests with correct compiler flags

  3. Link libpq with libdl if the platform needs that.

  4. Doc: correct spelling of meson switch.

  5. oauth: Correct SSL dependency for libpq-oauth.a

  6. oauth: Fix Autoconf build on macOS

  7. oauth: Move the builtin flow into a separate module

  8. Remove a stray "pgrminclude" annotation

  9. oauth: Simplify copy of PGoauthBearerRequest

  10. oauth: Improve validator docs on interruptibility

  11. oauth: Disallow synchronous DNS in libcurl

  12. oauth: Fix postcondition for set_timer on macOS

  13. oauth: Use IPv4-only issuer in oauth_validator tests

  14. Work around OAuth/EVFILT_TIMER quirk on NetBSD.

  15. oauth: Fix incorrect const markers in struct

  16. Add missing entry to oauth_validator test .gitignore

  17. cirrus: Temporarily fix libcurl link error

  18. Add support for OAUTHBEARER SASL mechanism

  19. libpq: Handle asynchronous actions during SASL

  20. require_auth: prepare for multiple SASL mechanisms

  21. Move PG_MAX_AUTH_TOKEN_LENGTH to libpq/auth.h

  22. Make SASL max message length configurable

  23. jsonapi: fully initialize dummy lexer

  24. common/jsonapi: support libpq as a client

  25. Remove fe_memutils from libpgcommon_shlib

  26. Revert ECPG's use of pnstrdup()

  27. Explicitly require password for SCRAM exchange

  28. Refactor SASL exchange to return tri-state status

Hello!

I'm testing OAuth Device Flow implementation on Google. Met several 
problems.

Postgres from master branch, commit 764d501d24b
Google Device Flow API
https://developers.google.com/identity/protocols/oauth2/limited-input-device

1) In Device Authorization Request Google returns 428 code on pending
https://developers.google.com/identity/protocols/oauth2/limited-input-device#authorization-pending

Source code handles only 400/401 error codes, they are in the Section 
5.2 RFC6749
* An error response uses either 400 Bad Request or 401 Unauthorized.
* There are references online to implementations using 403 for error
* return which would violate the specification.

-----------------
I suggest to add a GUC in postgresql.conf that contains additional 
non-standard error codes for a specific service.
oauth_add_error_codes = [
   {
          issuer: google
         add_err_codes: [428],
     },
     {
         issuer: someservice
         add_err_code: [403],
     }
]
So Google can contain 400,401,428

Additionally write parsing of such json-like config-values. Will be cool 
to create serializer, that matches struct to such json-like GUC.

Or we can create a separate file oauth.conf where json-like data will 
be. And postgresql.conf may contain link to this file, name oauth_conf GUC

oauth_conf = /var/lib/postgres/data/oauth.conf


=================

2) Google requires client_secret only in the Device Access Token Request 
(Section 3.3 RFC-8628). Also note that secret is in a body of a request
https://developers.google.com/identity/protocols/oauth2/limited-input-device#step-4:-poll-googles-authorization-server

curl -d "client_id=client_id&client_secret=client_secret& \
          device_code=device_code& \
grant_type=urn%3Aietf%3Aparams%3Aoauth%3Agrant-type%3Adevice_code" \
          -H "Content-Type: application/x-www-form-urlencoded" \
          https://oauth2.googleapis.com/token

Not Device Authorization Request (Section 3.1 RFC-8628)
https://developers.google.com/identity/protocols/oauth2/limited-input-device#step-2:-handle-the-authorization-server-response

curl -d "client_id=client_id&scope=email%20profile" \
         https://oauth2.googleapis.com/device/code

But Postgres sends client_secret in both request, also in Device 
Authorization Request. See calls of a func add_client_identification in 
funs start_device_authz & start_token_request
Azure also use secret only in Device Access Token Request 
https://learn.microsoft.com/en-us/entra/identity-platform/v2-oauth2-device-code#device-authorization-request
-----------------

I suggest to remove send secret on Device Authorization Request.

=================
3) Additionally if secret exists PG sends it only using Basic Auth. But 
RFC contain only MAY word about Basic Auth. Section 2.3.1 RFC 6749,
if (conn->oauth_client_secret) /* Zero-length secrets are permitted! */
{
username = urlencode(conn->oauth_client_id);
password = urlencode(conn->oauth_client_secret);
...
CHECK_SETOPT(actx, CURLOPT_HTTPAUTH, CURLAUTH_BASIC, goto cleanup);
CHECK_SETOPT(actx, CURLOPT_USERNAME, username, goto cleanup);
CHECK_SETOPT(actx, CURLOPT_PASSWORD, password, goto cleanup);
actx->used_basic_auth = true;
}
Also this section contains words about body, Google use such approach

Alternatively, the authorization server MAY support including the client 
credentials in the request-body using the following parameters: 
client_id REQUIRED. The client identifier issued to the client during 
the registration process described by Section 2.2 
<https://datatracker.ietf.org/doc/html/rfc6749#section-2.2>. 
client_secret REQUIRED. The client secret. The client MAY omit the 
parameter if the client secret is an empty string.
https://developers.google.com/identity/protocols/oauth2/limited-input-device#step-2:-handle-the-authorization-server-response

-----------------
I suggest to set such cases in config. Let's create a json-like oauth 
array config. Field auth_scheme shows what scheme we want to use. (see 
GUC description in pt1 of this email).
oauth = [
   {
          issuer: google
         add_err_codes: [428],
         auth_scheme: body
     },
     {
     issuer: someservice
         add_err_code: [403],
         auth_scheme: basic
     }

]

-- 
Best wishes,
Ivan Kush
Tantor Labs LLC