Re: Non-superuser subscription owners

On Sat, Jan 21, 2023 at 5:01 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> There are good reasons to have 'peer' authentication set up for the user
> running postgres, so admin scripts can connect without issues. Which
> unfortunately then also means that postgres_fdw etc can connect to the current
> database as superuser, without that check. Which imo clearly is an issue.
>
> Why do you think this is a fabricated issue?

Well, if I have a bunch of PostgreSQL machines on the network that all
allow each other to log in without requiring anything much in the way
of passwords or closely-guarded SSL certificates or anything, and then
I grant to the users on those machines the right to make connections
to the other machines using arbitrary connection strings, whose fault
is it when security is compromised? We seem to be taking the policy
that it's PostgreSQL's fault if it doesn't block something bad from
happening there, but it seems to me that if you gate incoming
PostgreSQL connections only by source IP address and then also give
unprivileged users the ability to choose their source IP address, you
should expect to have a problem.

I will admit that this is not an open-and-shut case, because a
passwordless login back to the bootstrap superuser account from the
local machine is a pretty common scenario and doesn't feel
intrinsically unreasonable to me, and I hadn't thought about that as a
potential attack vector.

However, I still think there's a problem with putting all the
responsibility on PostgreSQL. The problem, specifically, is that we're
speculating wildly as to the user's intent. If we say, as we currently
do, that we're only going to allow connections if they require a
password, then we're making a judgement that the superuser couldn't
have intended to allow the postgres_fdw to make a passwordless
connection. On the other hand, if we say, as we also currently do,
that the postgres_fdw user is free to set the sslcert parameter to
anything they like, then we're making a judgement that the superuser
is totally OK with that being set to any file on the local filesystem.
Neither of those conclusions seems sound to me. The superuser may, or
may not, have intended to allow passwordless logins, and they may, or
may not, have intended for any SSL certificates stored locally to be
usable by outbound connection attempts.

And that's what I really dislike about the you-must-use-a-password
rule that we have right now. It embeds a policy decision about what
users do or do not want to allow. We've uncritically copied that
policy decision around to more and more places, and we've added
workarounds in some places for the fact that, well, you know, it might
not actually be what everybody wants (6136e94d), but it doesn't seem
like we've ever really acknowledged that we *made* a policy decision.
And that means we haven't really had a debate about the merits of this
*particular* rule, which seems to me to be highly debatable. It looks
to me like there's both stuff you might not want to allow that this
rule does not block, and also stuff you might want to allow that this
rule does block, and also that different people can want different
things yet this rule applies uniformly to everyone.

> > 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.
>
> Why is this only about local files, rather than e.g. also using the local
> user?

Because there's nothing you can do about the local-user case.

If I'm asked to attempt to connect to a PostgreSQL server, and I
choose to do that, and the connection succeeds, all I know is that the
connection actually succeeded. I do not know why the remote machine
chose to accept the connection. If I supplied a password or an SSL
certificate or some such thing, then it seems likely that the remote
machine accepted that connection because I supplied that particular
password or SSL certificate, but it could also be because the remote
machine accepts all connections from Robert, or all connections
whatsoever, or all connections on Mondays. I just don't know. If I'm
worried that the person is asking me to make the connection is trying
to trick me into doing something that they can't do themselves, I
could refuse to read a password from a local password store or an SSL
certificate from a local certificate file or otherwise refuse to do
anything special to try to get their connection request accepted, and
then if it does get accepted anyway, I know that they weren't relying
on any of those resources that I refused to use. But, if I attempt a
plain vanilla, totally password-less connection and it works, I have
no way of knowing whether that happened because I'm Robert or for some
other reason.

To put that another way, if I'm making a connection on behalf of an
untrusted party, I can choose not to supply an SSL certificate, or not
to supply a password. But I cannot choose to not be myself.

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