Re: Non-superuser subscription owners

Hi,

On 2023-01-23 10:27:27 -0800, Jacob Champion wrote:
> On Mon, Jan 23, 2023 at 8:35 AM Robert Haas <robertmhaas@gmail.com> wrote:
> > 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.
> 
> It seems to me like that's _the_ primary attack vector. I think I
> agree with you that the password requirement is an overly large
> hammer, but I don't think it's right (or safe/helpful to DBAs reading
> along) to describe it as a manufactured concern.

+1


> > 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.
> 
> As of SYSTEM_USER, I think this is no longer the case -- after
> connection establishment, you can ask the server who was authenticated
> and why. (It doesn't explain why you were authorized to be that
> particular user, but that seems maybe less important wen you're trying
> to disallow ambient authentication.)

There's not enough documentation for SYSTEM_USER imo.



> You could even go a step further and disable ambient transport
> authentication (sslcertmode=disable gssencmode=disable), which keeps a
> proxied connection from making use of a client cert or a Kerberos cache. But
> for postgres_fdw, at least, that carries a risk of disabling current use
> cases. Stephen and I had a discussion about one such case in the Kerberos
> delegation thread [1].

I did not find that very convincing for today's code. The likelihood of
something useful being prevented seems far far lower than preventing privilege
leakage...


> It doesn't help you if you want to differentiate one form of ambient
> auth (trust/peer/etc.) from another, since they look the same to the
> protocol. But for e.g. postgres_fdw I'm not sure why you would want to
> differentiate between those cases, because they all seem bad.

It might be possible to teach libpq to differentiate peer from trust (by
disabling passing the current user), or we could tell the server via an option
to disable peer. But as you say, I don't think it'd buy us much.

Greetings,

Andres Freund