Re: Non-superuser subscription owners

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.

> 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.)

If my require_auth patchset gets in, you'd be able to improve on this
by rejecting all ambient forms of authentication at the protocol level
(require_auth=password,md5,scram-sha-256). 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].

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.

> 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.

(IMO, you're driving towards a separation of the proxy identity from
the user identity. Other protocols do that too.)

--Jacob

[1]
https://www.postgresql.org/message-id/flat/23337c51-7a48-d5a8-569d-ef3ce6fe235f%40timescale.com#38b4033256d9d95773963ce938cbe3ea