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

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