Re: Password leakage avoidance

On 12/24/23 10:14 AM, Joe Conway wrote:
> On 12/23/23 11:00, Tom Lane wrote:
>> Joe Conway <mail(at)joeconway(dot)com> writes:
>>> The attached patch set moves the guts of \password from psql into the
>>> libpq client side -- PQchangePassword() (patch 0001).
>>
>> Haven't really read the patch, just looked at the docs, but here's
>> a bit of bikeshedding:
>
> Thanks!

Prior to bikeshedding -- thanks for putting this together. This should
generally helpful, as it allows libpq-based drivers to adopt this method
and provide a safer mechanism for setting/changing passwords! (which we
should also promote once availbale).

>> * This seems way too eager to promote the use of md5.  Surely the
>> default ought to be SCRAM, full stop.  I question whether we even
>> need an algorithm parameter.  Perhaps it's a good idea for
>> future-proofing, but we could also plan that the function would
>> make its own decisions based on noting the server's version.
>> (libpq is far more likely to be au courant about what to do than
>> the calling application, IMO.)

We're likely to have new algorithms in the future, as there is a draft
RFC for updating the SCRAM hashes, and already some regulatory bodies
are looking to deprecate SHA256. My concern with relying on the
"encrypted_password" GUC (which is why PQencryptPasswordConn takes
"conn") makes it any easier for users to choose the algorithm, or if
they need to rely on the server/session setting.

I guess in its current state, it does, and drivers could mask some of
the complexity.

>>> One thing I have not done but, considered, is adding an additional
>>> optional parameter to allow "VALID UNTIL" to be set. Seems like it
>>> would be useful to be able to set an expiration when setting a new
>>> password.
>>
>> No strong opinion about that.
>
> Thanks -- hopefully others will weigh in on that.

I think this is reasonable to add.

I think this is a good start and adds something that's better than what
we have today. However, it seems like we also need something for "CREATE
ROLE", otherwise we're either asking users to set passwords in two
steps, or allowing for the unencrypted password to leak to the logs via
CREATE ROLE.

Maybe we need a PQcreaterole that provide the mechanism to set passwords
safely. It'd likely need to take all the options need for creating a
role, but that would at least give the underlying mechanism to ensure
we're always sending a hashed password to the server.

Jonathan