Re: Non-superuser subscription owners

On Mon, Jan 23, 2023 at 7:24 PM Jacob Champion <jchampion@timescale.com> wrote:
> > 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.

To be honest, that was my first instinct here, but I see the problems
better now than I did at the beginning of this discussion.

> 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 think this has some potential, but it's pretty complex, seeming to
require protocol extensions and having backward-compatibility problems
and so on. What do you think about something in the spirit of a
reverse-pg_hba.conf? The idea being that PostgreSQL facilities that
make outbound connections are supposed to ask it whether those
connections are OK to initiate.  Then you could have a default
configuration that basically says "don't allow loopback connections"
or "require passwords all the time" or whatever we like, and the DBA
can change that as desired. We could teach dblink, postgres_fdw, and
CREATE SUBSCRIPTION to use this new thing, and third-party code could
adopt it if it likes.

Even if we do that, some kind of proxy protocol support might be very
desirable. I'm not against that. But I think that DBAs need better
control over what kind of outbound connections they want to permit,
too.

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

I don't think the logon trigger thing is all *that* hypothetical. We
don't have it yet, but there have been patches proposed repeatedly for
many years.

-- 
Robert Haas
EDB: http://www.enterprisedb.com