Re: postgres_fdw, dblink, and CREATE SUBSCRIPTION security - Mailing list pgsql-hackers

On Fri, Jan 27, 2023 at 1:08 PM Robert Haas <robertmhaas@gmail.com> wrote:
> > 1) Forwarding the original ambient context along with the request, so
> > the server can check it too.
>
> Right, so a protocol extension. Reasonable idea, but a big lift. Not
> only do you need everyone to be running a new enough version of
> PostgreSQL, but existing proxies like pgpool and pgbouncer need
> updates, too.

Right.

> > 2) Explicitly combining the request with the proof of authority needed
> > to make it, as in capability-based security [3].
>
> As far as I can see, that link doesn't address how you'd make this
> approach work across a network.

The CSRF-token example I gave is one. But that's HTTP-specific
(stateless, server-driven) and probably doesn't make a lot of sense
for our case.

For our case, assuming that connections have side effects, one
solution could be for the client to signal to the server that the
connection should use in-band authentication only; i.e. fail the
connection if the credentials provided aren't good enough by
themselves to authenticate the client. (This has some overlap with
SASL negotiation, maybe.)

But that still requires server support. I don't know if there's a
clever way to tie the authentication to the request on the client side
only, using existing server implementations. (If connections don't
have side effects, require_auth should be sufficient.)

> > 3) Dropping as many implicitly-held privileges as possible before making
> > a request. This doesn't solve the problem but may considerably reduce
> > the practical attack surface.
>
> Right. I definitely don't object to this kind of approach, but I don't
> think it can ever be sufficient by itself.

I agree. (But for the record, I think that an outbound proxy filter is
also insufficient. Someone, somewhere, is going to want to safely
proxy through localhost _and_ have peer authentication set up.)

> > I think this style focuses on absolute configuration flexibility at the
> > expense of usability. It obfuscates the common use cases. (I have the
> > exact same complaint about our HBA and ident configs, so I may be
> > fighting uphill.)
>
> That's probably somewhat true, but on the other hand, it also is more
> powerful than what you're describing. In your system, is there some
> way the DBA can say "hey, you can connect to any of the machines on
> this list of subnets, but nothing else"? Or equally, "hey, you may NOT
> connect to any machine on this list of subnets, but anything else is
> fine"? Or "you can connect to these subnets without SSL, but if you
> want to talk to anything else, you need to use SSL"?

I guess I didn't call it out explicitly, so it was fair to assume that
it did not. I don't think we should ignore those cases.

But if we let the configuration focus on policies instead, and
simultaneously improve the confused-deputy problem, then any IP/host
filter functionality that we provide becomes an additional safety
measure instead of your only viable line of defense. "I screwed up our
IP filter, but we're still safe because the proxy refused to forward
its client cert to the backend." Or, "this other local application
requires peer authentication, but it's okay because the proxy
disallows those connections by default."

> Your idea
> seems to rely on us being able to identify all of the policies that a
> user is likely to want and give names to each one, and I don't feel
> very confident that that's realistic. But maybe I'm misinterpreting
> your idea?

No, that's pretty accurate. But I'm used to systems that provide a
ridiculous number of policies [1, 2] via what's basically a scoped
property bag. "Turn off option 1 and 2 globally. For host A and IP
address B, turn on option 1 as an exception." And I don't really
expect us to need as many options as those systems do.

I think that configuration style evolves well, it focuses on the right
things, and it can still handle IP lists intuitively [3], if that's
the way a DBA really wants to set up policies.

--Jacob

[1] https://httpd.apache.org/docs/2.4/mod/mod_proxy.html
[2] https://www.haproxy.com/documentation/hapee/latest/onepage/#4
[3] https://docs.nginx.com/nginx/admin-guide/security-controls/controlling-access-proxied-tcp/