Обсуждение: OAuth client code doesn't work with Google OAuth

Hello Hackers,

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.
It requires two small changes:

* The device code request only includes the OAuth Client ID in the
request body if the user doesn't specify a client secret (if the
secret is specified, the client ID is only sent as part of the basic
auth header), but Google OAuth always expects it in the body
* The wait loop for the authorization only expects HTTP 400 and 401,
but the Google endpoint responds with HTTP 428 (Precondition required)

Both issues are testable/verifiable without a properly working
validator, as they happen on the client side, before invoking the
validator logic.

I  attached a small patch which fixes both.

In the previous email I attached a git diff not a proper patch file, I
added the correct attachment to this email.

On Sun, Sep 7, 2025 at 8:02 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
>
> Hello Hackers,
>
> 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.
> It requires two small changes:
>
> * The device code request only includes the OAuth Client ID in the
> request body if the user doesn't specify a client secret (if the
> secret is specified, the client ID is only sent as part of the basic
> auth header), but Google OAuth always expects it in the body
> * The wait loop for the authorization only expects HTTP 400 and 401,
> but the Google endpoint responds with HTTP 428 (Precondition required)
>
> Both issues are testable/verifiable without a properly working
> validator, as they happen on the client side, before invoking the
> validator logic.
>
> I  attached a small patch which fixes both.

> On 7 Sep 2025, at 21:02, Zsolt Parragi <zsolt.parragi@percona.com> wrote:

> * The device code request only includes the OAuth Client ID in the
> request body if the user doesn't specify a client secret (if the
> secret is specified, the client ID is only sent as part of the basic
> auth header), but Google OAuth always expects it in the body

AFAICT adding this would not violate the RFC but it is "NOT RECOMMENDED".
There is also this comment a few lines up from your change which makes it
problematic.

    * client_id is not added to the request body in this case. Not only
    * would it be redundant, but some providers in the wild (e.g. Okta)
    * refuse to accept it.

We clearly want to be able to support Google as an OAuth provider, but it seems
we need to operate in different modes here?

> * The wait loop for the authorization only expects HTTP 400 and 401,
> but the Google endpoint responds with HTTP 428 (Precondition required)

It doesn't seem in line with the specification, which error are they sending
428 for? Do they use 401 for invalid_client?

--
Daniel Gustafsson

> AFAICT adding this would not violate the RFC but it is "NOT RECOMMENDED".

I didn't test Okta yet, but it worked with all other providers I tried
so far. I try to verify this with Okta and modify it if it doesn't
work, but I think this isn't clear in the RFCs:

RFC 8628 states that "The authorization server MUST ignore
unrecognized request parameters.", and RFC 6749 states that the
client_id is not a secret credential, so I assumed this should work
with all providers - if some doesn't use it, the RFC requires it to
ignore the parameter.

Also in RFC  6749, it states:

> The authorization server MAY establish a client authentication method
> with public clients.  However, the authorization server MUST NOT rely
> on public client authentication for the purpose of identifying the
>  client.

And later it explicitly states that clients may repeat the client id
parameter, and in some cases, they have to:

> A client MAY use the "client_id" request parameter to identify itself
> when sending requests to the token endpoint.  In the
> "authorization_code" "grant_type" request to the token endpoint, an
> unauthenticated client MUST send its "client_id" to prevent itself
> from inadvertently accepting a code intended for a client with a
> different "client_id".



> It doesn't seem in line with the specification, which error are they sending
> 428 for? Do they use 401 for invalid_client?

During the wait for the user to enter the device code. It's documented here:

https://developers.google.com/identity/protocols/oauth2/limited-input-device#authorization-pending

On Mon, Sep 8, 2025 at 10:11 AM Daniel Gustafsson <daniel@yesql.se> wrote:
>
> > On 7 Sep 2025, at 21:02, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
>
> > * The device code request only includes the OAuth Client ID in the
> > request body if the user doesn't specify a client secret (if the
> > secret is specified, the client ID is only sent as part of the basic
> > auth header), but Google OAuth always expects it in the body
>
> AFAICT adding this would not violate the RFC but it is "NOT RECOMMENDED".
> There is also this comment a few lines up from your change which makes it
> problematic.
>
>     * client_id is not added to the request body in this case. Not only
>     * would it be redundant, but some providers in the wild (e.g. Okta)
>     * refuse to accept it.
>
> We clearly want to be able to support Google as an OAuth provider, but it seems
> we need to operate in different modes here?
>
> > * The wait loop for the authorization only expects HTTP 400 and 401,
> > but the Google endpoint responds with HTTP 428 (Precondition required)
>
> It doesn't seem in line with the specification, which error are they sending
> 428 for? Do they use 401 for invalid_client?
>
> --
> Daniel Gustafsson
>

> On 8 Sep 2025, at 11:46, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
>
>> AFAICT adding this would not violate the RFC but it is "NOT RECOMMENDED".
>
> I didn't test Okta yet, but it worked with all other providers I tried
> so far. I try to verify this with Okta and modify it if it doesn't
> work

Great, thanks!

> , but I think this isn't clear in the RFCs:
>   ...

Unfortunately thats true for most of the OAuth related RFCs, they are in places
wishy washy at best.

>> It doesn't seem in line with the specification, which error are they sending
>> 428 for? Do they use 401 for invalid_client?
>
> During the wait for the user to enter the device code. It's documented here:
>
> https://developers.google.com/identity/protocols/oauth2/limited-input-device#authorization-pending

Thanks for the reference, I'm not sure we should handle it equally to 400/401
(need to think about that, and am looking foward to Jacob's wisdom on it) but
it should regardless be quite doable to support.

--
Daniel Gustafsson

The comment is unfortunately correct, Okta refuses to authenticate if
the client_id is specified at two places with secret authentication.

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.

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)?

On Mon, Sep 8, 2025 at 11:58 AM Daniel Gustafsson <daniel@yesql.se> wrote:
>
> > On 8 Sep 2025, at 11:46, Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> >
> >> AFAICT adding this would not violate the RFC but it is "NOT RECOMMENDED".
> >
> > I didn't test Okta yet, but it worked with all other providers I tried
> > so far. I try to verify this with Okta and modify it if it doesn't
> > work
>
> Great, thanks!
>
> > , but I think this isn't clear in the RFCs:
> >   ...
>
> Unfortunately thats true for most of the OAuth related RFCs, they are in places
> wishy washy at best.
>
> >> It doesn't seem in line with the specification, which error are they sending
> >> 428 for? Do they use 401 for invalid_client?
> >
> > During the wait for the user to enter the device code. It's documented here:
> >
> >
https://url.avanan.click/v2/r01/___https://developers.google.com/identity/protocols/oauth2/limited-input-device%23authorization-pending___.YXAzOnBlcmNvbmE6YTpnOjE1MDdmOTlmYjY1ZTJlYzM3NWE2NjE4MDNlOTMyNDQ3Ojc6MTRhYTo5ZGNlNmI2YzMyYTI3ZTY3NDQzNDMxZDJlMWViY2I0Y2JmMTQyYzMwM2JjZWFmMTRhYzEzMjllMGFhYTg1MDgxOnA6VDpO
>
> Thanks for the reference, I'm not sure we should handle it equally to 400/401
> (need to think about that, and am looking foward to Jacob's wisdom on it) but
> it should regardless be quite doable to support.
>
> --
> Daniel Gustafsson
>

On Sun, Sep 7, 2025 at 12:03 PM Zsolt Parragi <zsolt.parragi@percona.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@yesql.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.parragi@percona.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.parragi@percona.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://accounts.google.com`. 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://www.postgresql.org/message-id/705e7eb8-29ee-a707-5a67-d2acfb2f3fad%40timescale.com
[2] https://www.postgresql.org/message-id/CAOYmi%2BmnmzUXm8rUXU%3DSO5NtXjUtYT%3DaJWrALB4ZNow5eo49jg%40mail.gmail.com
[3] https://www.postgresql.org/message-id/CAOYmi%2B%3D8rkKh_8o9iyGQk_J4MQRCfpq3Qj3-dDyrnJPQ96bHYQ%40mail.gmail.com
[4] https://www.postgresql.org/docs/18/oauth-validator-design.html#OAUTH-VALIDATOR-DESIGN-RESPONSIBILITIES
[5] https://datatracker.ietf.org/doc/draft-ietf-oauth-v2-1/13/ , Section 2.4.1

> 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.champion@enterprisedb.com> wrote:
>
> On Sun, Sep 7, 2025 at 12:03 PM Zsolt Parragi <zsolt.parragi@percona.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@yesql.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.parragi@percona.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.parragi@percona.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.
Soif 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 

On Tue, Sep 9, 2025 at 1:16 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> 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 think this is focusing on authentication again. I mean that you need
to authorize the _client_ -- libpq itself. What does the consent
screen say for a user logging in, in your proposed architecture?

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

Maybe for the authentication switch (header vs request body), but IMO,
428 is a definite Googleism that doesn't need to be made generic. If
we can avoid adding knobs, then future compatibility tweaks remain
(potentially) backportable. And I think we need to be designing for
compatibility backports in the long term, in the same way that we
might need to update LDAP or RADIUS or etc. from time to time.

--Jacob

> I think this is focusing on authentication again. I mean that you need
> to authorize the _client_ -- libpq itself. What does the consent
> screen say for a user logging in, in your proposed architecture?

I tried to implement my idea, and apparently it doesn't work the way I
described it. Limited devices are restricted in what they can do, and
this information is too sensitive for them, the API doesn't allow
access to them.

What can work:
* basic oauth authentication with just the "email" scope. (consent
form only shows that application X wants to access your email address)
* for simple use cases, the validator checks the domain of the user,
and if it matches the required domain, it allows the connection
* for more complex use cases (actual permission verification within
the organization) a server side (google specific) validator account
could access the more restricted google APIs, and verify if the user
is allowed to connect or not

So this is not strictly OAuth now, but still within what can be
implemented in a validator library.

On Tue, Sep 9, 2025 at 4:13 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Tue, Sep 9, 2025 at 1:16 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> > 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 think this is focusing on authentication again. I mean that you need
> to authorize the _client_ -- libpq itself. What does the consent
> screen say for a user logging in, in your proposed architecture?
>
> > > 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.
>
> Maybe for the authentication switch (header vs request body), but IMO,
> 428 is a definite Googleism that doesn't need to be made generic. If
> we can avoid adding knobs, then future compatibility tweaks remain
> (potentially) backportable. And I think we need to be designing for
> compatibility backports in the long term, in the same way that we
> might need to update LDAP or RADIUS or etc. from time to time.
>
> --Jacob

On Wed, Sep 10, 2025 at 2:12 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> > I think this is focusing on authentication again. I mean that you need
> > to authorize the _client_ -- libpq itself. What does the consent
> > screen say for a user logging in, in your proposed architecture?
>
> I tried to implement my idea, and apparently it doesn't work the way I
> described it. Limited devices are restricted in what they can do, and
> this information is too sensitive for them, the API doesn't allow
> access to them.

That doesn't help with my uncertainty here. If Google restricts its
device flow clients more than other providers are doing, perhaps we
should not be giving the Postgres clients that are talking to Google
more privileges than Google's own servers would give out... But see
below.

> What can work:
> * basic oauth authentication with just the "email" scope. (consent
> form only shows that application X wants to access your email address)

That's unfortunately not sufficient, in the general case. Let me
outline a possible attack; maybe you know of countermeasures that are
in place to prevent it.

Let's say that you and I both have GMail addresses, and we are both
given OAuth access to the same Postgres server. We'll both be given
the same client ID to use when contacting that server. (We might also
be given a secret, but you and I both know that this is a public flow,
so secrets have no particular power here.)

If the validator only asks for "email" scope, then all I have to do to
steal your credentials and log into Postgres with your identity is to
stand up my own public web application server, for some ostensibly
benign purpose, and add Log in with Google to that service. When I see
that _you_ are logging in to that service, I can switch the OAuth
handshake from my application's legitimate client ID to our shared
Postgres client ID. Google's consent screen in your browser will look
almost completely normal -- asking for your authenticated email
address is a very common request these days! -- and the only chance
you'll have to reject the theft is to notice that the client name
looks different from usual. (If I catch you on the first login, you
might not even know what "usual" is.)

This is possible due to a failure to perform Step 2 in the validator
checklist [1], and in my opinion, that's CVE-worthy. Clients must ask
for permission to access Postgres on behalf of the user, or else the
validator must authorize the client out-of-band via some architectural
magic. The latter is not generally possible if the untrusted users are
in control of the client, as far as I am aware.

> * for more complex use cases (actual permission verification within
> the organization) a server side (google specific) validator account
> could access the more restricted google APIs, and verify if the user
> is allowed to connect or not

Right -- the implementer of the authorization server is free to do
pretty much anything.

I'm not sure that's enough reason to support a nonstandard flow for
v1, though, if the risks I described above are accurate. (And again,
if I'm wrong and/or outvoted, I believe an issuer-specific
compatibility tweak should be low-risk and backportable for 18.x...
But I'd like to know if Daniel agrees with that.)

--Jacob

[1] https://www.postgresql.org/docs/18/oauth-validator-design.html#OAUTH-VALIDATOR-DESIGN-RESPONSIBILITIES

> and the only chance
> you'll have to reject the theft is to notice that the client name
> looks different from usual. (If I catch you on the first login, you
> might not even know what "usual" is.)

Isn't this also true with custom scopes? Similarly the only thing
protecting users if they notice that something is strange in the
request, and similarly they might not notice it if it's the first
login.

I agree that this is a possible attack vector, but I don't see how it
is specific to Google, and how it can't happen with Azure or some
other provider which is already supported by the current code. Azure
simply logged me in with a custom scope, without displaying the
requested scopes at all.


On Wed, Sep 10, 2025 at 5:03 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Wed, Sep 10, 2025 at 2:12 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
> > > I think this is focusing on authentication again. I mean that you need
> > > to authorize the _client_ -- libpq itself. What does the consent
> > > screen say for a user logging in, in your proposed architecture?
> >
> > I tried to implement my idea, and apparently it doesn't work the way I
> > described it. Limited devices are restricted in what they can do, and
> > this information is too sensitive for them, the API doesn't allow
> > access to them.
>
> That doesn't help with my uncertainty here. If Google restricts its
> device flow clients more than other providers are doing, perhaps we
> should not be giving the Postgres clients that are talking to Google
> more privileges than Google's own servers would give out... But see
> below.
>
> > What can work:
> > * basic oauth authentication with just the "email" scope. (consent
> > form only shows that application X wants to access your email address)
>
> That's unfortunately not sufficient, in the general case. Let me
> outline a possible attack; maybe you know of countermeasures that are
> in place to prevent it.
>
> Let's say that you and I both have GMail addresses, and we are both
> given OAuth access to the same Postgres server. We'll both be given
> the same client ID to use when contacting that server. (We might also
> be given a secret, but you and I both know that this is a public flow,
> so secrets have no particular power here.)
>
> If the validator only asks for "email" scope, then all I have to do to
> steal your credentials and log into Postgres with your identity is to
> stand up my own public web application server, for some ostensibly
> benign purpose, and add Log in with Google to that service. When I see
> that _you_ are logging in to that service, I can switch the OAuth
> handshake from my application's legitimate client ID to our shared
> Postgres client ID. Google's consent screen in your browser will look
> almost completely normal -- asking for your authenticated email
> address is a very common request these days! -- and the only chance
> you'll have to reject the theft is to notice that the client name
> looks different from usual. (If I catch you on the first login, you
> might not even know what "usual" is.)
>
> This is possible due to a failure to perform Step 2 in the validator
> checklist [1], and in my opinion, that's CVE-worthy. Clients must ask
> for permission to access Postgres on behalf of the user, or else the
> validator must authorize the client out-of-band via some architectural
> magic. The latter is not generally possible if the untrusted users are
> in control of the client, as far as I am aware.
>
> > * for more complex use cases (actual permission verification within
> > the organization) a server side (google specific) validator account
> > could access the more restricted google APIs, and verify if the user
> > is allowed to connect or not
>
> Right -- the implementer of the authorization server is free to do
> pretty much anything.
>
> I'm not sure that's enough reason to support a nonstandard flow for
> v1, though, if the risks I described above are accurate. (And again,
> if I'm wrong and/or outvoted, I believe an issuer-specific
> compatibility tweak should be low-risk and backportable for 18.x...
> But I'd like to know if Daniel agrees with that.)
>
> --Jacob
>
> [1]
https://url.avanan.click/v2/r01/___https://www.postgresql.org/docs/18/oauth-validator-design.html%23OAUTH-VALIDATOR-DESIGN-RESPONSIBILITIES___.YXAzOnBlcmNvbmE6YTpnOmI3ODZmMjZmMmU0YTAyMDc3ZGZhMTVlYmIxMjZiZGYxOjc6MTNhYzphODZjZTRhMTA3NzNiMmY3NDVmYTU1ZDE2MWU3MTU3ZTkwZmNkNjExZGNlMTFmNWViMDBhNTU4ZTIxMjEwMWU1OnA6VDpO

On Wed, Sep 10, 2025 at 11:50 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:
> > and the only chance
> > you'll have to reject the theft is to notice that the client name
> > looks different from usual. (If I catch you on the first login, you
> > might not even know what "usual" is.)
>
> Isn't this also true with custom scopes?

I would say "no", because the point of the consent screen is to review
the privileges that you are granting to a third party. If you're
comfortable letting a third party retrieve your email address, that's
your choice. But that third party should be not be able to upgrade an
email-scoped token to "allow access to your Postgres database" without
your express consent.

> Similarly the only thing
> protecting users if they notice that something is strange in the
> request

If a malicious service asks clearly and politely for read/write access
to all of your data, and you grant that access, an OAuth resource
server is unfortunately not at fault if it is then told to delete all
your data. (Clickthrough fatigue is a very real problem, but it's not
one I know how to unilaterally fix inside our client implementation.)

By contrast, if a malicious service asks for OpenID, and the user is
comfortable granting that, and then a validator silently upgrades the
issued token's scope and lets the malicious service do a bunch of
other non-OpenID stuff too? That validator is absolutely at fault.
OAuth systems are supposed to protect against this.

> I agree that this is a possible attack vector,

Okay, good.

> but I don't see how it
> is specific to Google,

I don't think it's specific to Google. It would apply to any provider
that doesn't let you identify a scope for your database access and
make users consent to it. But it's a validator's job to catch that
omission and fail the connection. Please do not lie to Postgres and
set ValidatorModuleResult.authorized if the client has not actually
been authorized to connect by the user.

(So -- if we become quite certain that it's impossible to implement a
client authorization step for a provider, maybe it'd be a bad idea for
me to implement a client compatibility hack for that same provider
right this instant? If nothing else, the incompatibility has already
led to valuable discussions with you and other validator authors on
the list.)

> and how it can't happen with Azure or some
> other provider which is already supported by the current code. Azure
> simply logged me in with a custom scope, without displaying the
> requested scopes at all.

That is, IMHO, its own separate problem. From

https://arstechnica.com/information-technology/2025/02/russian-spies-use-device-code-phishing-to-hijack-microsoft-accounts/
:

> The effectiveness of [Azure device code phishing] is, in large part, the result of the
> ambiguity in the user interface of the device code authorization
> process. That means it's important for people to pay close attention to
> links and the pages they lead to.  Microsoft Azure prompts users to
> confirm they're signing into the app they expect. People should look for
> it and be suspicious of messages where this option is missing.

So, MS Entra does something that's kind of the reverse of the
situation I described above: it just yells at you loudly that a device
is going to "have access to your account", even if the device isn't
actually requesting access scopes, and demands that you double-check
the trustworthiness of the device. That would certainly be enough to
scare me away in the situation I described upthread, so... I guess
it's not a CVE? MS is clearly aware that their device flow does not
present a scope consent screen.

But this introduces more user fatigue, because what Entra is telling
me is _also_ a lie, and I can't see what access is actually being
requested. Which leads to the problem reported on by the Ars article
above. So I guess I just have to be hypervigilant as an Entra user.
(And what was the point of going through the custom scope
registration, if Entra won't display them in all the situations
they're being requested?)

So I don't like that, either, but it's still different from
silent-scope-upgrade via validator. The Entra problem is not something
we can catch; the Google problem is.

--Jacob

> So, MS Entra does something that's kind of the reverse of the
> situation I described above: it just yells at you loudly that a device
> is going to "have access to your account", even if the device isn't
> actually requesting access scopes, and demands that you double-check
> the trustworthiness of the device. That would certainly be enough to
> scare me away in the situation I described upthread, so... I guess
> it's not a CVE? MS is clearly aware that their device flow does not
> present a scope consent screen.

It just says that:

> Once you enter the code displayed on your app or device, it will have access to your account.
> Do not enter codes from sources you don't trust.

And that's all. And not loudly, this isn't written with a big font
size with a warning sign or anything like that, it's just quietly
mentioned.
After that, in the next step it confirms the application name.

> By contrast, if a malicious service asks for OpenID, and the user is
> comfortable granting that, and then a validator silently upgrades the
> issued token's scope and lets the malicious service do a bunch of
> other non-OpenID stuff too? That validator is absolutely at fault.

Actually it's quite similar to what Azure does, Google also doesn't
display anything at all, even if I request email/profile scopes, it
just says that I'm trying to log in to application "X". It doesn't
mention scopes at all.

And regarding your previous email/attack scenario:

> then all I have to do to
> steal your credentials and log into Postgres with your identity is to
> stand up my own public web application server, for some ostensibly
> benign purpose, and add Log in with Google to that service

I just realized that this won't work - not with Google, and not with
other providers.

In the case of Google, a client id is either for a web application or
a limited device, it can't be both. A client id used for a limited
device like postgres can't be used for a web based login flow, it will
result in an error.
So if somebody wants to do this attack, they have to do something
similar to the last link with Microsoft, or create a malicious oauth
application that people for some reason will trust.

 And with other providers like Azure, you can limit the redirect URL
for the web login flow. If somebody tries to use a postgres client id
with their own redirect endpoint, that won't work.

On Wed, Sep 10, 2025 at 10:47 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Wed, Sep 10, 2025 at 11:50 AM Zsolt Parragi
> <zsolt.parragi@percona.com> wrote:
> > > and the only chance
> > > you'll have to reject the theft is to notice that the client name
> > > looks different from usual. (If I catch you on the first login, you
> > > might not even know what "usual" is.)
> >
> > Isn't this also true with custom scopes?
>
> I would say "no", because the point of the consent screen is to review
> the privileges that you are granting to a third party. If you're
> comfortable letting a third party retrieve your email address, that's
> your choice. But that third party should be not be able to upgrade an
> email-scoped token to "allow access to your Postgres database" without
> your express consent.
>
> > Similarly the only thing
> > protecting users if they notice that something is strange in the
> > request
>
> If a malicious service asks clearly and politely for read/write access
> to all of your data, and you grant that access, an OAuth resource
> server is unfortunately not at fault if it is then told to delete all
> your data. (Clickthrough fatigue is a very real problem, but it's not
> one I know how to unilaterally fix inside our client implementation.)
>
> By contrast, if a malicious service asks for OpenID, and the user is
> comfortable granting that, and then a validator silently upgrades the
> issued token's scope and lets the malicious service do a bunch of
> other non-OpenID stuff too? That validator is absolutely at fault.
> OAuth systems are supposed to protect against this.
>
> > I agree that this is a possible attack vector,
>
> Okay, good.
>
> > but I don't see how it
> > is specific to Google,
>
> I don't think it's specific to Google. It would apply to any provider
> that doesn't let you identify a scope for your database access and
> make users consent to it. But it's a validator's job to catch that
> omission and fail the connection. Please do not lie to Postgres and
> set ValidatorModuleResult.authorized if the client has not actually
> been authorized to connect by the user.
>
> (So -- if we become quite certain that it's impossible to implement a
> client authorization step for a provider, maybe it'd be a bad idea for
> me to implement a client compatibility hack for that same provider
> right this instant? If nothing else, the incompatibility has already
> led to valuable discussions with you and other validator authors on
> the list.)
>
> > and how it can't happen with Azure or some
> > other provider which is already supported by the current code. Azure
> > simply logged me in with a custom scope, without displaying the
> > requested scopes at all.
>
> That is, IMHO, its own separate problem. From
>
https://url.avanan.click/v2/r01/___https://arstechnica.com/information-technology/2025/02/russian-spies-use-device-code-phishing-to-hijack-microsoft-accounts/___.YXAzOnBlcmNvbmE6YTpnOmUzZGVhNDI4OThiNzQzNzY5NmE0Njk0Mzc5Y2ZjNjA2Ojc6NTcxNDozMjZjZmZmODdhNmE0ZDkwYTUxNTZlNDk4NmFmZjU0OTYzMDdiMDc1NDQ3ZTNmMmE5MWNiMDI3YTIwMmM0NzI1OnA6VDpO
> :
>
> > The effectiveness of [Azure device code phishing] is, in large part, the result of the
> > ambiguity in the user interface of the device code authorization
> > process. That means it's important for people to pay close attention to
> > links and the pages they lead to.  Microsoft Azure prompts users to
> > confirm they're signing into the app they expect. People should look for
> > it and be suspicious of messages where this option is missing.
>
> So, MS Entra does something that's kind of the reverse of the
> situation I described above: it just yells at you loudly that a device
> is going to "have access to your account", even if the device isn't
> actually requesting access scopes, and demands that you double-check
> the trustworthiness of the device. That would certainly be enough to
> scare me away in the situation I described upthread, so... I guess
> it's not a CVE? MS is clearly aware that their device flow does not
> present a scope consent screen.
>
> But this introduces more user fatigue, because what Entra is telling
> me is _also_ a lie, and I can't see what access is actually being
> requested. Which leads to the problem reported on by the Ars article
> above. So I guess I just have to be hypervigilant as an Entra user.
> (And what was the point of going through the custom scope
> registration, if Entra won't display them in all the situations
> they're being requested?)
>
> So I don't like that, either, but it's still different from
> silent-scope-upgrade via validator. The Entra problem is not something
> we can catch; the Google problem is.
>
> --Jacob

On Thu, Sep 11, 2025 at 12:42 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:
> It just says that:
>
> > Once you enter the code displayed on your app or device, it will have access to your account.
> > Do not enter codes from sources you don't trust.
>
> And that's all. And not loudly, this isn't written with a big font
> size with a warning sign or anything like that, it's just quietly
> mentioned.

YMMV; on my screen the last sentence is in bolded font and quite
front-and-center, but they haven't used <blink> tags or red boxes or
anything. In any case I agree it's not enough.

But I don't have control over them. If a hypothetical provider (and I
am not saying Entra does this, just to be very clear) were to skip the
consent screen entirely, and immediately grant all requested scopes,
neither the client nor the validator would be able to figure that out.
That would be a provider-side problem, and we're trusting the provider
not to do that, or else to completely own the ramifications of it.
It's not relevant to the problem of upgrading scopes on the resource
server (i.e. the Postgres validator).

> > By contrast, if a malicious service asks for OpenID, and the user is
> > comfortable granting that, and then a validator silently upgrades the
> > issued token's scope and lets the malicious service do a bunch of
> > other non-OpenID stuff too? That validator is absolutely at fault.
>
> Actually it's quite similar to what Azure does, Google also doesn't
> display anything at all, even if I request email/profile scopes, it
> just says that I'm trying to log in to application "X". It doesn't
> mention scopes at all.

I hear what you're saying, but I don't see how it relates to the
paragraph you replied to.

(Also, I've seen that it's unfortunately common for OpenID scopes to
be described using "logging in" language; have you tried requesting a
write scope for something?)

> And regarding your previous email/attack scenario:
>
> > then all I have to do to
> > steal your credentials and log into Postgres with your identity is to
> > stand up my own public web application server, for some ostensibly
> > benign purpose, and add Log in with Google to that service
>
> I just realized that this won't work - not with Google, and not with
> other providers.
>
> In the case of Google, a client id is either for a web application or
> a limited device, it can't be both.

1) I didn't know Google did that; thanks. So I'm going to spend some
time spinning up a test environment and looking harder at what they do
in practice.

2) I don't yet understand how this helps you as a validator author,
because you shouldn't know what flow was in use. Forget about device
flow for a minute. How does this new information help you if the
organization you're installed in is using only PKCE-flow libpq from
Postgres 25 or whatever?

3) This could all change tomorrow because providers can do what they
want and RFCs keep getting written. Your job as a validator is to
authorize the client first, then authenticate the user. We still
haven't established how you plan to do the first half (and I've
personally never spent any time thinking about the security of a
resource server that doesn't first establish the OAuth layer before
using the OpenID claims riding on top of it).

> So if somebody wants to do this attack, they have to do something
> similar to the last link with Microsoft, or create a malicious oauth
> application that people for some reason will trust.

But why wouldn't I trust an OAuth application with my email address?
An attacker isn't going to name their malicious OAuth app "Don't Trust
Me I'm Stealing All Your Data", and your email address should not
carry the power to modify your databases.

> And with other providers like Azure, you can limit the redirect URL
> for the web login flow. If somebody tries to use a postgres client id
> with their own redirect endpoint, that won't work.

(Is that still true for PKCE? In general, we're not running
confidential clients here; it'd be a lot easier if we were. Microsoft
lists a bunch of flows that don't require redirects [1], and I have
not evaluated any of them in the context of this attack.)

Thanks,
--Jacob

[1] https://learn.microsoft.com/en-us/entra/identity-platform/reply-url

> I hear what you're saying, but I don't see how it relates to the
> paragraph you replied to.

I realized something incorrect in my last email: I wrote that google
doesn't ask permission for the email/profile scope. That's not true,
it didn't show the scope screen because I already gave permission for
the application and it remembered it.

So that made me wonder: how does Okta handle it? And turns out it's the same.

1. I defined a custom scope in Okta, with required explicit consent
from the user
2. I logged in for the first time with psql
3. As expected, Okta showed the consent screen and made me explicitly
accept that I'm granting "testScope"
4. I logged in for the second time with psql
5. I entered the code to the okta device login page, and the next
screen immediately was "Device activated"

Not even a single page of warning, nothing about
scopes/permissions/etc, it didn't even verify/tell me which
application I'm logging into. The first page just said "activate your
device" (It couldn't know where I want to login, as that's the generic
enter your code page), and the next page was:

"Device activated
Follow the instructions on your device for next steps"

That was all the text on it. It still didn't even tell me which device
(application) I logged into. Just that I succeeded.

There's a "prompt=consent" parameter to the API, and a similar setting
in the admin interface, but it doesn't seem to do anything with device
flow.

So in this sense, this is much worse than Google/Azure... More
searching tells me that typically oidc providers work this way, and
seems like the RFC again doesn't specify when and how often the
providers have to show the consent screen. RFC 6819 warns against
this, but that's all I could find, and that's a separate RFC.

This makes me question again: how does the scope help pg security in
practice? (I know that it's the oidc provider's fault, but if all
providers are like this, how does that help in practice?)

Also, the internet(/android/etc) is full of "login with google"
buttons without special scopes, and generally, if somebody logs in to
site/app X with google, he/she can expect to modify things within that
app. I'm not saying that this is ideal, but this is how it works in
practice, and how people are used to it.

Anyway, I  also understand why you don't agree with this, so if you
don't want to include google specific handling, I understand, I won't
argue more for it.


> 2) I don't yet understand how this helps you as a validator author,
> because you shouldn't know what flow was in use. Forget about device
> flow for a minute. How does this new information help you if the
> organization you're installed in is using only PKCE-flow libpq from
> Postgres 25 or whatever?

Yes, as a validator author, I don't know if somebody is using libpq, a
fork or libpq, or a completely different implementation of the
protocol (that does or does not support the not entirely compliant
google oauth).

But the administrator configuring the Postgres instance / the
validator should be aware of how the authentication flow is
configured, I wouldn't want to restrict the options by saying that
something is not supported, especially not blocking one of the most
popular services.


> How does this new information help you if the
> organization you're installed in is using only PKCE-flow libpq from
> Postgres 25 or whatever?

> Is that still true for PKCE? In general, we're not running
> confidential clients here; it'd be a lot easier if we were.

I've been thinking about this for the last few days, but shouldn't a
proper PKCE implementation require a protocol change, moving part of
the logic to the server side? And that would solve these scenarios we
are talking about, there would be no question who and how created the
access token. Naive PKCE support only on the client side, and still
only sending an access token to the server wouldn't help the security
of the server too much.

On Thu, Sep 11, 2025 at 4:55 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Thu, Sep 11, 2025 at 12:42 AM Zsolt Parragi
> <zsolt.parragi@percona.com> wrote:
> > It just says that:
> >
> > > Once you enter the code displayed on your app or device, it will have access to your account.
> > > Do not enter codes from sources you don't trust.
> >
> > And that's all. And not loudly, this isn't written with a big font
> > size with a warning sign or anything like that, it's just quietly
> > mentioned.
>
> YMMV; on my screen the last sentence is in bolded font and quite
> front-and-center, but they haven't used <blink> tags or red boxes or
> anything. In any case I agree it's not enough.
>
> But I don't have control over them. If a hypothetical provider (and I
> am not saying Entra does this, just to be very clear) were to skip the
> consent screen entirely, and immediately grant all requested scopes,
> neither the client nor the validator would be able to figure that out.
> That would be a provider-side problem, and we're trusting the provider
> not to do that, or else to completely own the ramifications of it.
> It's not relevant to the problem of upgrading scopes on the resource
> server (i.e. the Postgres validator).
>
> > > By contrast, if a malicious service asks for OpenID, and the user is
> > > comfortable granting that, and then a validator silently upgrades the
> > > issued token's scope and lets the malicious service do a bunch of
> > > other non-OpenID stuff too? That validator is absolutely at fault.
> >
> > Actually it's quite similar to what Azure does, Google also doesn't
> > display anything at all, even if I request email/profile scopes, it
> > just says that I'm trying to log in to application "X". It doesn't
> > mention scopes at all.
>
> I hear what you're saying, but I don't see how it relates to the
> paragraph you replied to.
>
> (Also, I've seen that it's unfortunately common for OpenID scopes to
> be described using "logging in" language; have you tried requesting a
> write scope for something?)
>
> > And regarding your previous email/attack scenario:
> >
> > > then all I have to do to
> > > steal your credentials and log into Postgres with your identity is to
> > > stand up my own public web application server, for some ostensibly
> > > benign purpose, and add Log in with Google to that service
> >
> > I just realized that this won't work - not with Google, and not with
> > other providers.
> >
> > In the case of Google, a client id is either for a web application or
> > a limited device, it can't be both.
>
> 1) I didn't know Google did that; thanks. So I'm going to spend some
> time spinning up a test environment and looking harder at what they do
> in practice.
>
> 2) I don't yet understand how this helps you as a validator author,
> because you shouldn't know what flow was in use. Forget about device
> flow for a minute. How does this new information help you if the
> organization you're installed in is using only PKCE-flow libpq from
> Postgres 25 or whatever?
>
> 3) This could all change tomorrow because providers can do what they
> want and RFCs keep getting written. Your job as a validator is to
> authorize the client first, then authenticate the user. We still
> haven't established how you plan to do the first half (and I've
> personally never spent any time thinking about the security of a
> resource server that doesn't first establish the OAuth layer before
> using the OpenID claims riding on top of it).
>
> > So if somebody wants to do this attack, they have to do something
> > similar to the last link with Microsoft, or create a malicious oauth
> > application that people for some reason will trust.
>
> But why wouldn't I trust an OAuth application with my email address?
> An attacker isn't going to name their malicious OAuth app "Don't Trust
> Me I'm Stealing All Your Data", and your email address should not
> carry the power to modify your databases.
>
> > And with other providers like Azure, you can limit the redirect URL
> > for the web login flow. If somebody tries to use a postgres client id
> > with their own redirect endpoint, that won't work.
>
> (Is that still true for PKCE? In general, we're not running
> confidential clients here; it'd be a lot easier if we were. Microsoft
> lists a bunch of flows that don't require redirects [1], and I have
> not evaluated any of them in the context of this attack.)
>
> Thanks,
> --Jacob
>
> [1]
https://url.avanan.click/v2/r01/___https://learn.microsoft.com/en-us/entra/identity-platform/reply-url___.YXAzOnBlcmNvbmE6YTpnOjBkMTE0ZWUyYjY1OWEyODIwOTRhODIyYTM1ODUxNzkyOjc6OTU2ZjpjMzNjZjhiZWRlZDQyZmU5YjQ4ZWViMzM2NjAwZmU4ZjJmYjFkNTczY2M2ZDUwMzQ3NzAzNjQzYWViYmUyNTlmOnA6VDpO

On Fri, Sep 12, 2025 at 12:02 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:
> I realized something incorrect in my last email: I wrote that google
> doesn't ask permission for the email/profile scope. That's not true,
> it didn't show the scope screen because I already gave permission for
> the application and it remembered it.

Is the result any different for more privileged scopes? Keep in mind
that "retrieve my email address" is not considered a particularly
sensitive scope for many people.

That ties into what I'll expand on below: if you, as a validator, do
not enforce a privileged scope here, providers may make incorrect
decisions about the overall risks of a specific client request. Very
few people are going to worry about a standard OpenID request, and
I've seen some providers give special treatment to those low-risk
scopes.

> So that made me wonder: how does Okta handle it? And turns out it's the same.

Let me try to take a closer look at Okta here on my end. (Might take a
while; there are a bunch of Okta custom policies in play, I think,
which I am not familiar with.)

> This makes me question again: how does the scope help pg security in
> practice?

Scopes are a central part of OAuth design. You may not ignore them.
They're a promise, by the provider, that the provider and/or the user
have authorized specific actions to be taken by the client you're
talking to. A bearer token without privileges should not be able to do
anything at all, unless the provider tells you otherwise.

You, as a validator, have no idea what flow was used, and the client
does not authenticate with you as part of the exchange. So you have to
*authorize* the client instead by checking the scopes, or else receive
magic knowledge of the client's trustworthiness. (And the latter is
probably not possible with a public flow, because in that case the
client is very nearly anonymous.)

> (I know that it's the oidc provider's fault, but if all
> providers are like this, how does that help in practice?)

I don't think all providers are like that. In my local testing,
Keycloak shows you the whole list of scopes every time for a device
flow. Entra shows you the scary (scopeless) screen every time.

> Also, the internet(/android/etc) is full of "login with google"
> buttons without special scopes, and generally, if somebody logs in to
> site/app X with google, he/she can expect to modify things within that
> app. I'm not saying that this is ideal, but this is how it works in
> practice, and how people are used to it.

Right. Those are OpenID Relying Parties, and their Bearer tokens just
allow them to get an ID token from you when you log in. As long as the
resource servers on the backend aren't letting them do anything other
than get ID tokens, it's working as designed.

As a validator, you are an OAuth Resource Server (not an OpenID
Relying Party). The demands on you are much higher, and there's a lot
less public documentation on it, in my experience, because it's just
assumed you understand OAuth. Do not allow one of those Relying
Parties to forward their token to you and silently impersonate their
end user.

> Anyway, I  also understand why you don't agree with this, so if you
> don't want to include google specific handling, I understand, I won't
> argue more for it.

Okay. I'm currently not intending to veto anyone's device flow quirks
in general; I'm just personally wary of implementing Google's right
now. A safe validator design for Google, specifically, would help
motivate that, if you or anyone reading along happens to design one.

To put it another way: Google's slightly custom device flow is
documented [1] as "OAuth 2.0 authorization to access Google APIs",
which is explicitly not what we would be using it for. I would prefer
to see some indication that this custom flow is safe for us to use for
Postgres, and currently I believe that it is not, based on the current
conversation.

> But the administrator configuring the Postgres instance / the
> validator should be aware of how the authentication flow is
> configured,

Yes. How does that help them in this case? They're still allowed to
expect that OAuth validators follow OAuth rules.

> I wouldn't want to restrict the options by saying that
> something is not supported, especially not blocking one of the most
> popular services.

But if it's not safe... what good does it do to support it?

> I've been thinking about this for the last few days, but shouldn't a
> proper PKCE implementation require a protocol change, moving part of
> the logic to the server side?

No, I expect to be able to use PKCE-enabled flows via OAUTHBEARER
(though I have not yet tested it). And libpq will remain a public
client, as far as I'm aware.

> And that would solve these scenarios we
> are talking about, there would be no question who and how created the
> access token.

I don't think this is true, because there's currently no question of
who creates an access token. That's what the cryptographic signatures
are for.

> Naive PKCE support only on the client side, and still
> only sending an access token to the server wouldn't help the security
> of the server too much.

PKCE _is_ client-only, no? There's no spec-mandated interaction
between PKCE and access token contents, as far as I am aware.

Thanks,
--Jacob

[1] https://developers.google.com/identity/protocols/oauth2/limited-input-device

> Let me try to take a closer look at Okta here on my end. (Might take a
> while; there are a bunch of Okta custom policies in play, I think,
> which I am not familiar with.)

I tried a few more things with Okta, but I wasn't able to convince it
to show me the scope screen every time. There might still be some
setting somewhere, but I couldn't figure out where.

> Scopes are a central part of OAuth design. You may not ignore them.

> Yes. How does that help them in this case? They're still allowed to
> expect that OAuth validators follow OAuth rules.

I'm not saying that I want to ignore them, I check them properly in
the validator code. But users can configure postgres not to require
any specific scope, it's their choice. Postgres doesn't stop with an
error if I just specify "openid" as the required scope in pg_hba.

> I don't think this is true, because there's currently no question of
> who creates an access token. That's what the cryptographic signatures
> are for.

> PKCE _is_ client-only, no? There's no spec-mandated interaction
> between PKCE and access token contents, as far as I am aware.

I meant this as an answer to the attack scenario you mentioned earlier
- where a third party application uses the postgres client id /
secret, and gets access without the user realizing it. Even if the
oauth provider shows scopes properly (and we can see that's not always
the case), if we make the assumption that the user doesn't notice the
mismatch in the application name, the scopes are just as easy to
ignore. Some people have this "click next, next, next" habit without
reading properly.

PKCE was originally intended for client side, but it is also used on
backends, which is a similar setup to the postgres server-client. It
would be possible to implement a flow such as:

* Generate a code verifier + challenge on the backend
* Send the code challenge to the client
* Client proceeds to implement some PKCE OAuth flow with the code challenge
* When the client receives the authorization token, it sends it to the backend
* Backend polls the oauth provider to receive the id/access tokens
using the code verifier + authorization token

In this flow:
* The server can be sure that the login process was initiated by this
postgres connection, and not something else
* The server is the only participant that has all the information to
retrieve the access token

Of course it is still possible for an attacker to manually initiate a
connection, and somehow convince the user through email/phone/... to
enter the device verification code, but nothing can prevent that one.

On Tue, Sep 16, 2025 at 8:17 PM Jacob Champion
<jacob.champion@enterprisedb.com> wrote:
>
> On Fri, Sep 12, 2025 at 12:02 AM Zsolt Parragi
> <zsolt.parragi@percona.com> wrote:
> > I realized something incorrect in my last email: I wrote that google
> > doesn't ask permission for the email/profile scope. That's not true,
> > it didn't show the scope screen because I already gave permission for
> > the application and it remembered it.
>
> Is the result any different for more privileged scopes? Keep in mind
> that "retrieve my email address" is not considered a particularly
> sensitive scope for many people.
>
> That ties into what I'll expand on below: if you, as a validator, do
> not enforce a privileged scope here, providers may make incorrect
> decisions about the overall risks of a specific client request. Very
> few people are going to worry about a standard OpenID request, and
> I've seen some providers give special treatment to those low-risk
> scopes.
>
> > So that made me wonder: how does Okta handle it? And turns out it's the same.
>
> Let me try to take a closer look at Okta here on my end. (Might take a
> while; there are a bunch of Okta custom policies in play, I think,
> which I am not familiar with.)
>
> > This makes me question again: how does the scope help pg security in
> > practice?
>
> Scopes are a central part of OAuth design. You may not ignore them.
> They're a promise, by the provider, that the provider and/or the user
> have authorized specific actions to be taken by the client you're
> talking to. A bearer token without privileges should not be able to do
> anything at all, unless the provider tells you otherwise.
>
> You, as a validator, have no idea what flow was used, and the client
> does not authenticate with you as part of the exchange. So you have to
> *authorize* the client instead by checking the scopes, or else receive
> magic knowledge of the client's trustworthiness. (And the latter is
> probably not possible with a public flow, because in that case the
> client is very nearly anonymous.)
>
> > (I know that it's the oidc provider's fault, but if all
> > providers are like this, how does that help in practice?)
>
> I don't think all providers are like that. In my local testing,
> Keycloak shows you the whole list of scopes every time for a device
> flow. Entra shows you the scary (scopeless) screen every time.
>
> > Also, the internet(/android/etc) is full of "login with google"
> > buttons without special scopes, and generally, if somebody logs in to
> > site/app X with google, he/she can expect to modify things within that
> > app. I'm not saying that this is ideal, but this is how it works in
> > practice, and how people are used to it.
>
> Right. Those are OpenID Relying Parties, and their Bearer tokens just
> allow them to get an ID token from you when you log in. As long as the
> resource servers on the backend aren't letting them do anything other
> than get ID tokens, it's working as designed.
>
> As a validator, you are an OAuth Resource Server (not an OpenID
> Relying Party). The demands on you are much higher, and there's a lot
> less public documentation on it, in my experience, because it's just
> assumed you understand OAuth. Do not allow one of those Relying
> Parties to forward their token to you and silently impersonate their
> end user.
>
> > Anyway, I  also understand why you don't agree with this, so if you
> > don't want to include google specific handling, I understand, I won't
> > argue more for it.
>
> Okay. I'm currently not intending to veto anyone's device flow quirks
> in general; I'm just personally wary of implementing Google's right
> now. A safe validator design for Google, specifically, would help
> motivate that, if you or anyone reading along happens to design one.
>
> To put it another way: Google's slightly custom device flow is
> documented [1] as "OAuth 2.0 authorization to access Google APIs",
> which is explicitly not what we would be using it for. I would prefer
> to see some indication that this custom flow is safe for us to use for
> Postgres, and currently I believe that it is not, based on the current
> conversation.
>
> > But the administrator configuring the Postgres instance / the
> > validator should be aware of how the authentication flow is
> > configured,
>
> Yes. How does that help them in this case? They're still allowed to
> expect that OAuth validators follow OAuth rules.
>
> > I wouldn't want to restrict the options by saying that
> > something is not supported, especially not blocking one of the most
> > popular services.
>
> But if it's not safe... what good does it do to support it?
>
> > I've been thinking about this for the last few days, but shouldn't a
> > proper PKCE implementation require a protocol change, moving part of
> > the logic to the server side?
>
> No, I expect to be able to use PKCE-enabled flows via OAUTHBEARER
> (though I have not yet tested it). And libpq will remain a public
> client, as far as I'm aware.
>
> > And that would solve these scenarios we
> > are talking about, there would be no question who and how created the
> > access token.
>
> I don't think this is true, because there's currently no question of
> who creates an access token. That's what the cryptographic signatures
> are for.
>
> > Naive PKCE support only on the client side, and still
> > only sending an access token to the server wouldn't help the security
> > of the server too much.
>
> PKCE _is_ client-only, no? There's no spec-mandated interaction
> between PKCE and access token contents, as far as I am aware.
>
> Thanks,
> --Jacob
>
> [1]
https://url.avanan.click/v2/r01/___https://developers.google.com/identity/protocols/oauth2/limited-input-device___.YXAzOnBlcmNvbmE6YTpnOjBhNDcyZjhiZmI2OTNiYzVhNjdkOGVlZWE0YzA4OTU1Ojc6NmYwMjowNjc5NjNjYmQ3OTIyMzU2NjNhOTljNDUyYjRiZGQ5MDE0MzBiZjc1YzE5NDdmMzI1NWU5MWU0YTBhMTViY2I3OnA6VDpO

On Fri, Sep 26, 2025 at 10:41 AM Zsolt Parragi
<zsolt.parragi@percona.com> wrote:
> > Let me try to take a closer look at Okta here on my end. (Might take a
> > while; there are a bunch of Okta custom policies in play, I think,
> > which I am not familiar with.)
>
> I tried a few more things with Okta, but I wasn't able to convince it
> to show me the scope screen every time. There might still be some
> setting somewhere, but I couldn't figure out where.

(I'm also stuck, with my own org policies preventing testing for now :/ )

> > Scopes are a central part of OAuth design. You may not ignore them.
>
> I'm not saying that I want to ignore them, I check them properly in
> the validator code. But users can configure postgres not to require
> any specific scope, it's their choice.

They can. And your validator should complain about that, and refuse to
let anyone in, if it doesn't know how to authorize the clients.

> Postgres doesn't stop with an
> error if I just specify "openid" as the required scope in pg_hba.

If your provider hypothetically gave you a way to authorize a client
out-of-band, I don't think we should stop you from doing that;
providers can put all sorts of information into these tokens. But
you'd need to 1) have a way, and 2) provide configuration for that on
your end, and 3) not just allow the connection by default if the user
doesn't put effective scopes into the HBA.

> PKCE was originally intended for client side, but it is also used on
> backends, which is a similar setup to the postgres server-client. It
> would be possible to implement a flow such as: <snip>

I don't think we should depart from the RFCs. That's going to make it
difficult for people to figure out what's going on and audit our code,
and it isolates us from the ecosystem instead of making us part of it.

Thanks,
--Jacob