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

On Wed, Sep 10, 2025 at 2:12 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)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