Re: Non-superuser subscription owners

On 1/23/23 11:52, Robert Haas wrote:
> On Mon, Jan 23, 2023 at 2:47 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Second, the reason why I described it as a manufactured issue is
>> because it's a bit like asking someone to stand under a ladder and
>> then complaining when they get hit in the head by a falling object.
>> It's not that I think it's good for people to get a free exploit to
>> superuser, or to get hit in the head by falling objects. It's just
>> that you can't have the things that together lead to some outcome
>> without also getting the outcome.
>
> I left out a sentence here. What I meant to say was we can't both
> allow passwordless loopback connections to the bootstrap superuser and
> also allow postgres_fdw to connect to anything that the user requests
> and then be surprised when that user can get into the superuser
> account. The natural outcome of combining those two things is that
> superuser gets hacked.
>
> The password requirement just *barely* prevents that attack from
> working, almost, maybe, while at the same time managing to block
> things that people want to do for totally legitimate reasons. But
> IMHO, the real problem is that combining those two things is extremely
> dangerous.

I don't disagree. I'm worried that the unspoken conclusion being
presented is "it's such an obvious problem that we should just leave it
to the DBAs," which I very much disagree with, but I may be reading too
much into it.

> It seems to me that we basically let
> the malicious connection to the target host succeed, and then say ...
> oh, never mind, we may have made this connection under false
> pretenses, so we shan't use it after all. What I was attempting to
> argue is that we shouldn't let things get that far. Either the victim
> should be able to protect itself from the malicious connection, or the
> connection attempt shouldn't be allowed in the first place, or both.
> Blocking the connection attempt after the fact feels like too little,
> too late.

Expanding on my previous comment, you could give the client a way to say
"I am a proxy, and I'm connecting on behalf of this user, and here are
both my credentials and their credentials. So if you were planning to,
say, authorize me as superuser based on my IP address... maybe don't do
that?"

(You can sort of implement this today, by giving the proxy a client
certificate for transport authn, having it provide the in-band authn for
the user, and requiring both at the server. It's not very flexible.)

I think this has potential overlap with Magnus' PROXY proposal [1], and
also the case where we want pgbouncer to authenticate itself and then
perform actions on behalf of someone else [2], and maybe SASL's authzid
concept. I don't think one solution will hit all of the desired use
cases, but there are directions that can be investigated.

> I'm not completely sure that this is good enough in terms of blocking
> the attack as early as I think we should. This is all happening in the
> midst of a connection attempt. If the remote server says, "hey, what's
> your password?" and we refuse to answer that question, well that seems
> somewhat OK. But what if we're hoping to be asked for a password and
> the remote server doesn't ask?

require_auth should still successfully mitigate the target_session_attrs
case (going back to the examples you provided). It looks like the SQL is
initiated from the client side, so require_auth will notice that there
was no authentication performed and bail out before we get there.

For the hypothetical logon trigger, or any case where the server does
something on behalf of a user upon connection, I agree it doesn't help you.

--Jacob

[1]
https://www.postgresql.org/message-id/flat/CABUevExJ0ifpUEiX4uOREy0s2kHBrBrb=pXLEHhpMTR1vVR1XA(at)mail(dot)gmail(dot)com
[2]
https://www.postgresql.org/message-id/CAMT0RQR2fxeaPLHXappBCGEjHJiPCBJMPOHoDWiaYLjuieR0sg%40mail.gmail.com