postgres_fdw, dblink, and CREATE SUBSCRIPTION security

[ Changing subject line to something more appropriate: This is
branched from the "Non-superuser subscription owners" thread, but the
topic has become connection security more generally for outbound
connections from a PostgreSQL instance, the inadequacies of just
trying to require that such connections always use a password, and
related problems. I proposed some kind of "reverse pg_hba.conf file"
as a way of allowing configurable limits on such outbound connections.
]

On Tue, Jan 24, 2023 at 2:18 PM Jacob Champion <jchampion@timescale.com> wrote:
> - It's completely reasonable to let a proxy operator restrict how that
> proxy is used. I doubt very much that a typical DBA wants to be
> operating an open proxy.

That's very well put. It's precisely what I was thinking, but
expressed much more clearly.

> - I think the devil will be in the details of the configuration
> design. Lists of allowed destination authorities (in the URI sense),
> options that must be present/absent/overridden, those sound great. But
> your initial examples of allow-loopback and require-passwords options
> are in the "make the DBA deal with it" line of thinking, IMO. I think
> it's difficult for someone to reason through those correctly the first
> time, even for experts. I'd like to instead see the core problem --
> that *any* ambient authentication used by a proxy is inherently risky
> -- exposed as a highly visible concept in the config, so that it's
> hard to make mistakes.

I find the concept of "ambient authentication" problematic. I don't
know exactly what you mean by it. I hope you'll tell me, but I think
that I won't like it even after I know, because as I said before, it's
difficult to know why anyone else makes a decision, and asking an
untrusted third-party why they're deciding something is sketchy at
best. I think that the problems we have in this area can be solved by
either (a) restricting the open proxy to be less open or (b)
encouraging people to authenticate users in some way that won't admit
connections from an open proxy. The former needs to be configurable by
the DBA, and the latter is also a configuration choice by the DBA. We
can provide tools here that make it less likely that people will shoot
themselves in the foot, and we can ship default configurations that
reduce the chance of inadvertent foot-shooting, and we can write
documentation that says "don't shoot yourself in the foot," but we
cannot actually prevent people from shooting themselves in the foot
except, perhaps, by massively nerfing the capabilities of the system.

What I was thinking about in terms of a "reverse pg_hba.conf" was
something in the vein of, e.g.:

SOURCE_COMPONENT SOURCE_DATABASE SOURCE_USER DESTINATION_SUBNET
DESTINATION_DATABASE DESTINATION_USER OPTIONS ACTION

e.g.

all all all local all all - deny # block access through UNIX sockets
all all all 127.0.0.0/8 all all - deny # block loopback interface via IPv4

Or:

postgres_fdw all all all all all authentication=cleartext,md5,sasl
allow # allow postgres_fdw with password-ish authentication

Disallowing loopback connections feels quite tricky. You could use
127.anything.anything.anything, but you could also loop back via IPv6,
or you could loop back via any interface. But you can't use
subnet-based ACLs to rule out loop backs through IP/IPv6 interfaces
unless you know what all your system's own IPs are. Maybe that's an
argument in favor of having a dedicated deny-loopback facility built
into the system instead of relying on IP ACLs. But I am not sure that
really works either: how sure are we that we can discover all of the
local IP addresses? Maybe it doesn't matter anyway, since the point is
just to disallow anything that would be likely to use "trust" or
"ident" authentication, and that's probably not going to include any
non-loopback network interfaces. But ... is that true in general? What
about on Windows?

> - I'm inherently skeptical of solutions that require all clients --
> proxies, in this case -- to be configured correctly in order for a
> server to be able to protect itself. (But I also have a larger
> appetite for security options that break compatibility when turned on.
> :D)

I (still) don't think that restricting the proxy is required, but you
can't both not restrict the proxy and also allow passwordless loopback
superuser connections. You have to pick one or the other. The reason I
keep harping on the role of the DBA is that I don't think we can make
that choice unilaterally on behalf of everyone. We've tried doing that
with the current rules and we've discussed the weaknesses of that
approach already.

> > 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.
>
> Okay. I think this thread has applicable lessons -- if connection
> establishment itself leads to side effects, all actors in the
> ecosystem (bouncers, proxies) have to be hardened against making those
> connections passively. I know we're very different from HTTP, but it
> feels similar to their concept of method safety and the consequences
> of violating it.

I am not familiar with that concept in detail but that sounds right to me.

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