> On 22 Mar 2023, at 15:06, Jonathan S. Katz <jk...@postgresql.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+bodrygzndaflgfz...@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