Re: Non-superuser subscription owners

On Thu, Jan 19, 2023 at 8:46 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > I wouldn't be OK with writing our own connection string parser for
> > this purpose, but using PQconninfoParse seems OK. We still have to
> > embed knowledge of which connection string parameters can trigger
> > local file access, but that doesn't seem like a massive problem to me.
>
> > If we already had (or have) that logic someplace else, it would
> > probably make sense to reuse it
>
> We hve. See at least postgres_fdw's check_conn_params(), dblink's
> dblink_connstr_check() and dblink_security_check().

That's not the same thing. It doesn't know anything about other
parameters that might try to consult a local file, like sslcert,
sslkey, sslrootcert, sslca, sslcrl, sslcrldir, and maybe service.
Maybe you want to argue we don't need that, but that's what the
earlier discussion was about.

> As part of the fix for https://postgr.es/m/20220925232237.p6uskba2dw6fnwj2%40awork3.anarazel.de
> I am planning to introduce a bunch of server side helpers for dealing with
> libpq (for establishing a connection while accepting interrupts). We could try
> to centralize knowledge for those checks there.

Maybe. We could also add something into libpq, as Jeff proposed, e.g.
a new connection parameter
the_other_connection_parameters_might_try_to_trojan_the_local_host=1
blocks all that stuff from doing anything.

> The approach of checking, after connection establishment (see
> dblink_security_check()), that we did in fact use the specified password,
> scares me somewhat. See also below.

Yes, I find that extremely dubious. It blocks things that you might
want to do for legitimate reasons, including things that might be more
secure than using a password. And there's no guarantee that it
accomplishes the intended objective either. The stated motivation for
that restriction was. I believe, that we don't want the outbound
connection to rely on the privileges available from the context in
which PostgreSQL itself is running -- but for all we know the remote
side has an IP filter that only allows the PostgreSQL host and no
others. Moreover, it relies on us knowing what the behavior of the
remote server is, even though we have no way of knowing that that
server shares our security interests.

Worse still, I have always felt that the security vulnerability that
led to these controls being installed is pretty much fabricated: it's
an imaginary problem. Today I went back and found the original CVE at
https://nvd.nist.gov/vuln/detail/CVE-2007-3278 and it seems that at
least one other person agrees. The Red Hat vendor statement on that
page says: "Red Hat does not consider this do be a security issue.
dblink is disabled in default configuration of PostgreSQL packages as
shipped with Red Hat Enterprise Linux versions 2.1, 3, 4 and 5, and it
is a configuration decision whether to grant local users arbitrary
access." I think whoever wrote that has an excellent point. I'm unable
to discern any legitimate security purpose for this restriction. What
I think it mostly does is (a) inconvenience users or (b) force them to
rely on a less-secure authentication method than they would otherwise
have chosen.

> > The basic idea that by looking at which connection string properties are set
> > we can tell what kinds of things the connection string is going to do seems
> > sound to me.
>
> I don't think you *can* check it purely based on existing connection string
> properties, unfortunately. Think of e.g. a pg_hba.conf line of "local all user
> peer" (quite reasonable config) or "host all all 127.0.0.1/32 trust" (less so).
>
> Hence the hack with dblink_security_check().
>
> I think there might be a discussion somewhere about adding an option to force
> libpq to not use certain auth methods, e.g. plaintext password/md5. It's
> possible this could be integrated.

I still think you're talking about a different problem here. I'm
talking about the problem of knowing whether local files are going to
be accessed by the connection string.

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