Re: User functions for building SCRAM secrets

> On 22 Mar 2023, at 15:06, Jonathan S. Katz <jkatz(at)postgresql(dot)org> wrote:
> On 3/22/23 2:48 AM, Michael Paquier wrote:

>> I was reading this thread again, and pondered on this particular
>> point:
>> https://www.postgresql.org/message-id/CAAWbhmhjcFc4oaGA_7YLUhtj6J+rxEY+BoDryGzNdaFLGfZZMg@mail.gmail.com
>> We've had our share of complains over the years that Postgres logs
>> password data in the logs with various DDLs, so I'd tend to agree that
>> this is not a practice we should try to encourage more. The
>> parameterization of the SCRAM verifiers through GUCs (like Daniel's
>> https://commitfest.postgresql.org/42/4201/ for the iteration number)
>> is more promising because it is possible to not have to send the
>> password over the wire with once we let libpq take care of the
>> computation, and the server would not know about that
>
> I generally agree with not allowing password data to be in logs, but in practice, there are a variety of tools and extensions that obfuscate or remove passwords from PostgreSQL logs. Additionally, this function is not targeted for SQL statements directly, but stored procedures.
>
> For example, an extension like "pg_tle" exposes the ability for someone to write a "check_password_hook" directly from PL/pgSQL[1] (and other languages). As we've made it a best practice to pre-hash the password on the client-side, a user who wants to write a check password hook against a SCRAM verifier needs to be able to compare the verifier against some existing set of plaintext criteria, and has to write their own function to do it. I have heard several users who have asked to do this, and the only feedback I can give them is "implement your own SCRAM build secret function."

I'm not sure I follow; doesn't this - coupled with this patch - imply passing
the plaintext password from client to the server, hashing it with a known salt
and comparing this with something in plaintext hashed with the same known salt?
If so, that's admittedly not a usecase I am terribly excited about. My
preference is to bring password checks to the plaintext password, not bring the
plaintext password to the password check.

> And, if my PostgreSQL server _is_ the client, e.g. it's making a dblink call to another PostgreSQL server, the only way it can modify a password is by sending the plaintext credential over the wire.

My experience with dblink setups is too small to have much insight here, but I
(perhaps naively) assumed that dblink setups generally involved two servers
under the same control making such changes be possible out of band.

> Maybe I'm not conveying the problem this is solving -- I'm happy to go one more round trying to make it clearer -- but if this is not clear, it'd be good to at develop an alternative approach to this before withdrawing the patch.

If this is mainly targeting extension use, is there a way in which an extension
could have all this functionality with no: a) not exposing any of it from the
server; b) not having to copy/paste lots into the extension and; c) have a
reasonable way to keep up with any changes made in the backend?

--
Daniel Gustafsson