Re: Non-superuser subscription owners

On Mon, Jan 23, 2023 at 3:50 PM Jeff Davis <pgsql@j-davis.com> wrote:
> I believe your patch conflates two use cases:
>
> (A) Tightly-coupled servers that are managed by the administrator. In
> this case, there are a finite number of connection strings to make, and
> the admin knows about all of them. Validation is a poor solution for
> this use case, because we get into the weeds trying to figure out
> what's safe or not, overriding the admin's better judgement in some
> cases and letting through connection strings that might be unsafe. A
> much better solution is to simply declare the connection strings as
> some kind of object (perhaps a SERVER object), and hand out privileges
> or inherit them from a predefined role. Having connection string
> objects is also just a better UI: it allows changes to connection
> strings over time to adapt to changing security needs, and allows a
> simple name that is much easier to type and read.
>
> (B) Loosely-coupled servers that the admin doesn't know about, but
> which might be perfectly safe to access. Validation is useful here, but
> it's a long road of fine-grained privileges around acceptable hosts,
> IPs, authentication types, file access, password sources, password
> protocols, connection options, etc. The right solution here is to
> identify the sub-usecases of loosely-coupled servers, and enable them
> (with the appropriate controls) one at a time. Arguably, that's already
> what's happened by demanding a password (even if we don't like the
> mechanism, it does seem to work for some important cases).
>
> Is your patch targeted at use case (A), (B), or both?

I suppose that I would say that the patch is a better fit for (B),
because I'm not proposing to add any kind of intermediate object of
the type you postulate in (A). However, I don't really agree with the
way you've split this up, either. It seems to me that the relevant
question isn't "are the servers tightly coupled?" but rather "could
some user make a mess if we let them use any arbitrary connection
string?".

If you're running all of the machines involved on a private network
that is well-isolated from the Internet and in which only trusted
actors operate, you could use what I'm proposing here for either (A)
or (B) and it would be totally fine. If your server is sitting out on
the public Internet and is adequately secured against malicious
loopback connections, you could also probably use it for either (A) or
(B), unless you've got users who are really shady and you're worried
that the outbound connections that they make from your machine might
get you into trouble, in which case you probably can't use it for
either (A) or (B). Basically, the patch is suitable for cases where
you don't really need to restrict what connection strings people can
use, and unsuitable for cases where you do, but that doesn't have much
to do with whether the servers involved are loosely or tightly
coupled.

I think that you're basically trying to make an argument that some
sort of complex outbound connection filtering is mandatory, and I
still don't really agree with that. We ship postgres_fdw with
something extremely minimal - just a requirement that the password get
used - and the same for dblink. I think those rules suck and are
probably bad and insecure in quite a number of cases, and overly
strict in others, but I can think of no reason why CREATE SUBSCRIPTION
should be held to a higher standard than anything else. The
connections that you can make using CREATE SUBSCRIPTION are strictly
weaker than the ones you can make with dblink, which permits arbitrary
SQL execution. It cannot be right to suppose that a less-exploitable
system needs to be held to a higher security standard than a similar
but more-exploitable system.

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