Re: dblink: Add SCRAM pass-through authentication

On Wed, Feb 12, 2025 at 11:54 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:
> Currently dblink_connstr_check and dblink_security_check only check if the
> password is present on connection options, in case of not superuser.

They also check for GSS delegation. I think SCRAM passthrough should
ideally be considered a second form of credentials delegation, from
the perspective of those functions.

> I added
> this logic because the password is not required for SCRAM but I agree with you
> that it sounds strange. Maybe these functions could check if the SCRAM is
> being used and then skip the password validation if needed internally?

As long as the end result is to enforce that the credentials must come
from the end user, I think that would be fine in theory.

> I also agree that we should enforce the use of the SCRAM on the remote for
> safety. To do this I think that we could set require_auth=scram-sha-256 on
> connection options if SCRAM pass-through is being used, with this we will get a
> connection error. WYT?

We would need to verify that the user mapping can't overwrite that
with its own (less trusted) `require_auth` setting. (I think that
should be true already, but I'm not 100% sure.)

Hardcoding to scram-sha-256 would also prohibit the use of GSS or
standard password auth, now that I think about it. The docs currently
have a note about being able to choose... Should we add the other
permitted authentication types, i.e. `password,md5,scram-sha-256,gss`?
Or should we prohibit the use of other auth types if you've set
use_scram_passthrough? Or maybe there's an easier way to enforce the
use of the SCRAM keys, that better matches the current logic in
dblink_security_check?

> I can create a new patch to fix this on postgres_fdw too once we define the
> approach to this here on dblink.

Sounds good to me.

Thanks,
--Jacob