On Sat, Jan 21, 2023 at 5:01 PM Andres Freund <and...@anarazel.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
