Re: postgres_fdw, dblink, and CREATE SUBSCRIPTION security

On Thu, Mar 9, 2023 at 6:17 AM Robert Haas <robertmhaas@gmail.com> wrote:
> That seems like a circular argument. If you call the problem the
> confused deputy problem then the issue must indeed be that the deputy
> is confused, and needs to talk to someone else to get un-confused. But
> why is the deputy necessarily confused in the first place? Our deputy
> is confused because our code to decide whether to proxy a connection
> or not is super-dumb,

No, I think our proxy is confused because it doesn't know what power
it has, and it can't tell the server what power it wants to use. That
problem is independent of the decision to proxy. You're suggesting
strengthening the code that makes that decision -- adding an oracle
(in the form of a DBA) that knows about the confusion and actively
mitigates it. That's guaranteed to work if the oracle is perfect,
because "perfect" is somewhat tautologically defined as "whatever
ensures secure operation". But the oracle doesn't reduce the
confusion, and DBAs aren't perfect.

If you want to add a Sheriff Andy to hold Barney Fife's hand [1], that
will absolutely make Barney less of a problem, and I'd like to have
Andy around regardless. But Barney still doesn't know what's going on,
and when Andy makes a mistake, there will still be trouble. I'd like
to teach Barney some useful stuff.

> but if there's an intrinsic reason it can't be
> smarter, I don't understand what it is.

Well... I'm not well-versed enough in this to prove non-existence of a
solution. Can you find a solution, using the current protocol, that
doesn't make use of perfect out-of-band knowledge? We have a client
that will authenticate using any method the server asks it to, even if
its user intended to use something else. And we have a server that can
eagerly skip client authentication, and then eagerly run code on its
behalf, without first asking the client what it's even trying to do.
That would be an inherently hostile environment for *any* proxy, not
just ours.

Thanks,
--Jacob

[1] https://en.wikipedia.org/wiki/The_Andy_Griffith_Show#Premise_and_characters