Re: OAuth client code doesn't work with Google OAuth

> My overall position is this, let me know what you think about it: I'm
> fine with supporting the Google-flavor handshake. That's life, and we
> already have some code that deals with variant spellings. But if the
> end result is a system that still can't safely authorize clients, I'm
> much less excited about adding the support, because I'm afraid
> someone's going to walk right into a CVE.

You still can't add custom scopes to google. But in the most basic use
case for this if you have an organization setup, you can restrict
authentication to that organization. After that there still has to be
a postgres user, and possibly a usermap entry mapping to it.
There's also the customAttributes in userinfo, where an organization
admin can store custom information about any user, and the validator
logic can check this, and require specific fields / values there.

> I would personally prefer a "quirks mode" rather than a configuration
> knob. More specifically: the last time I looked into this, it appeared
> that Google required all of its Device Grant clients to go through the
> hardcoded issuer

Yes, that's also how I deal with it on the validation side, I just
wasn't sure if adding provider specific hacks to the client is a good
idea, if we can make it generic that works with anything.

On Mon, Sep 8, 2025 at 7:08 PM Jacob Champion
<jacob(dot)champion(at)enterprisedb(dot)com> wrote:
>
> On Sun, Sep 7, 2025 at 12:03 PM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
> >
> > Hello Hackers,
>
> Thank you for the report!
>
> > While working on an OAuth validator for PG18 I noticed that currently
> > the client code doesn't work when using Google as the OAuth provider.
>
> Yep, this came up a couple of times previously [1, 2]. Supporting this
> flavor wouldn't be a problem for the client, IMO. But I'm concerned
> about the second piece of the puzzle discussed in [2]: how do you plan
> to _authorize_ libpq with Google? There aren't any custom scopes
> available to us, are there?
>
> (The importance of authorization of the client prior to authentication
> of the user is discussed at length in [3], as well as the
> documentation at [4], but I'm more than happy to offer further
> clarification, and/or improvement of those docs. This is one of the
> hardest things for me to explain to people about OAuth.)
>
> My overall position is this, let me know what you think about it: I'm
> fine with supporting the Google-flavor handshake. That's life, and we
> already have some code that deals with variant spellings. But if the
> end result is a system that still can't safely authorize clients, I'm
> much less excited about adding the support, because I'm afraid
> someone's going to walk right into a CVE. Unless, you already have an
> architecture that _can_ handshake safely with Google? If so, can you
> tell us what it is? If not, maybe it's worth having a discussion with
> someone at Google to see whether we can't get those scopes sorted
> out...
>
> And I see now that Ivan replied to my last email on this topic, but I
> don't have his mail in my inbox. :( CC'd Ivan, and I'm sorry I didn't
> mail back. Hopefully we can get it figured out here.
>
> On Mon, Sep 8, 2025 at 3:58 AM Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:
> > Unfortunately thats true for most of the OAuth related RFCs, they are in places
> > wishy washy at best.
>
> Yeah, it's the cat-herding problem. Spec writers want to match
> reality, and the reality is incredibly messy.
>
> On Mon, Sep 8, 2025 at 2:46 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
> > RFC 8628 states that "The authorization server MUST ignore
> > unrecognized request parameters."
>
> I think that sentence is standard forwards compatibility language.
> client_id is definitely "recognized" in that sense; the server is just
> choosing to refuse it. The sentence directly after says
>
> Request and response parameters MUST NOT be included more than once.
>
> and I suspect that Okta have chosen to treat this as a prohibited
> duplication. I wouldn't blame them, really.
>
> Note that this recommendation appears to be changing _again_ for OAuth
> 2.1 [5], so we're probably going to get to revisit this eventually.
>
> On Mon, Sep 8, 2025 at 8:31 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
> > On the other hand okta also doesn't require secrets for native apps,
> > where the secret is public anyway. Even with these changes okta is
> > usable, just without a client secret.
>
> Yeah, but we should support secrets.
>
> > But I also agree that making this configurable is a better approach.
> > Seems like while putting both the client_id and client_secret into the
> > postdata is not recommended, it is also supported by all
> > implementations I tested so far, including both google and okta.
> >
> > What do you think about adding an `oauth_authentication_method`
> > parameter to the frontend, which defaults to `basic`, but can be
> > changed to `post` (or `http_basic` and `request_body`, as the rfc
> > refers to them)?
>
> I would personally prefer a "quirks mode" rather than a configuration
> knob. More specifically: the last time I looked into this, it appeared
> that Google required all of its Device Grant clients to go through the
> hardcoded issuer `https://url.avanan.click/v2/r01/___https://accounts.google.com`___.YXAzOnBlcmNvbmE6YTpnOjU4MTc2N2ZhOWQxMDE3YWYzNGYyNThiZjljNjJmNWEzOjc6ZjNiNjpjMzBjYzcyOGI3YmFiNzVhZjQ2NDY4YThmNTgyYTM4OWNjMWU4NjYzNTU1ODdiMTUzODNhMGE2MjFjYTY5NzI4OnA6VDpO. So if that issuer is
> in use, that seems like a solid indication that we can safely switch
> the client_id location and start looking for the nonstandard response
> codes.
>
> If I'm wrong, and we start seeing more reasons to allow users to
> switch between HTTP auth methods on the fly, we can always add the
> knob. Especially if OAuth 2.1 makes things more complicated. But we
> can't take a knob away, and I think most people would rather it Just
> Work as opposed to needing configuration.
>
> WDYT?
>
> --Jacob
>
> [1] https://url.avanan.click/v2/r01/___https://www.postgresql.org/message-id/705e7eb8-29ee-a707-5a67-d2acfb2f3fad*40timescale.com___.YXAzOnBlcmNvbmE6YTpnOjU4MTc2N2ZhOWQxMDE3YWYzNGYyNThiZjljNjJmNWEzOjc6MTgwYzozZWUwODA2MWUzMjQ1NjljNTMyNmZmMDNhNTk5MGM4OTVjNmE3ZThmYzA3ZTMxMTFiZDM4NjI1NjNlZjk4Mjg0OnA6VDpO
> [2] https://url.avanan.click/v2/r01/___https://www.postgresql.org/message-id/CAOYmi*2BmnmzUXm8rUXU%3DSO5NtXjUtYT%3DaJWrALB4ZNow5eo49jg*40mail.gmail.com___.YXAzOnBlcmNvbmE6YTpnOjU4MTc2N2ZhOWQxMDE3YWYzNGYyNThiZjljNjJmNWEzOjc6NjYxMDozNDA3Mzc4ZWNkZjlhMWQwZTJmMDUzZTE4YjBhMWMzYTNlMDVlZjMwYzIxMzc3NjQyMDlhZDk1Njc4ZTZiNmI0OnA6VDpO
> [3] https://url.avanan.click/v2/r01/___https://www.postgresql.org/message-id/CAOYmi*2B%3D8rkKh_8o9iyGQk_J4MQRCfpq3Qj3-dDyrnJPQ96bHYQ*40mail.gmail.com___.YXAzOnBlcmNvbmE6YTpnOjU4MTc2N2ZhOWQxMDE3YWYzNGYyNThiZjljNjJmNWEzOjc6OTRkMTpkYzhlZmM4OTg5NTc3NzZmMmViYzA5MDllNTQ1NWE5N2YyMjg1NDllMGQ5ZGEwYzBkMDAxYmZjYzg5ZWRkYzNkOnA6VDpO
> [4] https://url.avanan.click/v2/r01/___https://www.postgresql.org/docs/18/oauth-validator-design.html%23OAUTH-VALIDATOR-DESIGN-RESPONSIBILITIES___.YXAzOnBlcmNvbmE6YTpnOjU4MTc2N2ZhOWQxMDE3YWYzNGYyNThiZjljNjJmNWEzOjc6YjA5NjowNDJhNTUyZjI0NzA5MDEyNWRmYTNhOGMzMmI3NjM2MTE5YTc2ZTUzZjhmZjU3YWQ1MmQ5YWFlMjhkN2NhZDFlOnA6VDpO
> [5] https://url.avanan.click/v2/r01/___https://datatracker.ietf.org/doc/draft-ietf-oauth-v2-1/13/___.YXAzOnBlcmNvbmE6YTpnOjU4MTc2N2ZhOWQxMDE3YWYzNGYyNThiZjljNjJmNWEzOjc6YjU1YjozNTg2M2Y2MTI4NjFmMDExMmQ5ZmQ2NmM4NzlhZDY3YjQyYTg0M2RhMGNjMjFlNDQ1OTQyOWQ0MmE1MTEzZjE4OnA6VDpO , Section 2.4.1