Re: User functions for building SCRAM secrets - Mailing list pgsql-hackers

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