Re: User functions for building SCRAM secrets

On 3/22/23 2:48 AM, Michael Paquier wrote:
> On Tue, Feb 14, 2023 at 06:16:18PM -0500, Jonathan S. Katz wrote:
>> I opted for the approach in [2]. v5 contains the branching logic for the
>> UTF8 only tests, and the corresponding output files. I tested locally on
>> macOS against both UTF8 + C locales.
>
> 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."

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.

I don't see how the parameterization work applies here -- would we allow
salts to be parameterized? -- and it still would not allow the server to
build out a SCRAM secret for these cases.

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.

Thanks,

Jonathan

[1]
https://github.com/aws/pg_tle/blob/main/docs/06_plpgsql_examples.md#example-password-check-hook-against-bad-password-dictionary