Horton

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion <jacob.champion@enterprisedb.com>

View thread
From: Jacob Champion <jacob.champion@enterprisedb.com>
To: Ivan Kush <ivan.kush@tantorlabs.com>
Cc: Wolfgang Walther <walther@technowledgy.de>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2025-04-21T16:57:33Z
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

On Sun, Apr 20, 2025 at 10:12 AM Ivan Kush <ivan.kush@tantorlabs.com> wrote:
> I'm testing OAuth Device Flow implementation on Google. Met several
> problems.

Hi Ivan, thank you for testing and reporting! Unfortunately, yeah,
Google is a known problem [1]. They've taken several liberties with
the spec, as you point out.

We have some options for dealing with them, since their documentation
instructs clients to hardcode their API entry points instead of using
discovery. (That makes it easy for us to figure out when we're talking
to Google, and potentially switch to a quirks mode.)

But! Before we do that: How do you intend to authorize tokens issued
by Google? Last I checked, they still had no way to register an
application-specific scope, making it very dangerous IMO to use a
public flow [2]. Do you have an architecture where this usage is safe,
and/or have they added custom scopes? (I deprioritized handling the
nonstandard behavior when I couldn't prove to myself that it was
possible to use the Google version of Device Authorization safely, but
I'm happy to jump back into that if we have a good use case.)

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

Right. I believe there were other nonstandard errors in other corner
cases, too. :(

> 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

The server config doesn't help us much, since this is a client-side
feature. Any "global" configuration is probably going to be done
through environment variables or a service file [3].

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

I'm not too excited about a separate configuration DSL. I'm guessing
most end users, if they really want Google as their Device
Authorization provider, would rather have us switch over to "Google
mode" once we notice the magic Google endpoint is in use.

> 2) Google requires client_secret only in the Device Access Token Request
> (Section 3.3 RFC-8628).
> ...
> But Postgres sends client_secret in both request, also in Device
> Authorization Request.

Yes. See 3.1 (Device Authorization Request):

   The client authentication requirements of Section 3.2.1 of [RFC6749]
   apply to requests on this endpoint, which means that confidential
   clients (those that have established client credentials) authenticate
   in the same manner as when making requests to the token endpoint, and
   public clients provide the "client_id" parameter to identify
   themselves.

> I suggest to remove send secret on Device Authorization Request.

This breaks Okta, at minimum. We can't do it across the board. (As for
Azure, I haven't figured out how to configure it to *require* a
confidential client secret for the device flow -- which makes a
certain amount of sense since the flow is public -- but its v2
endpoint doesn't mind being *sent* a secret.)

> 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,

>From 2.3.1:

   The authorization server MUST support the HTTP Basic
   authentication scheme for authenticating clients that were issued a
   client password.

We rely on that MUST, at the moment. We can add an exception for a
provider, certainly, but it needs to be limited for safety reasons:
"Including the client credentials in the request-body using the two
parameters is NOT RECOMMENDED and SHOULD be limited to clients unable
to directly utilize the HTTP Basic authentication scheme..."

(Authentication is its own nasty minefield; OAuth introduced its own
encoding requirements on top of HTTP that a bunch of servers ignored,
but in practice we cross our fingers that servers will only issue
ASCII credentials if they're not willing to follow the encoding
rules...)

So to recap: I'm happy to add a Google compatibility mode, but I'd
like to gather some evidence that their device flow can actually
authorize tokens for third parties safely, before we commit to that.
Thoughts?

Thanks!
--Jacob

[1] https://postgr.es/m/CAOYmi%2BkTumP6FHwLnUKX0DVKrTv%3DN9xSOAu7YMH_XKSMP7ozfA%40mail.gmail.com
[2] https://postgr.es/m/CAOYmi%2B%3DMFyrjDps-YNtem3%3DGr3mUsgZ49m7bfMCgr1TDjHL58g%40mail.gmail.com
[3] https://www.postgresql.org/docs/current/libpq-pgservice.html