Re: OAuth client code doesn't work with Google OAuth - Mailing list pgsql-hackers

> 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