Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tXNyA-0061g5-A9 for pgsql-general@arkaria.postgresql.org; Mon, 13 Jan 2025 17:11:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tXNy8-00BgMM-Ql for pgsql-general@arkaria.postgresql.org; Mon, 13 Jan 2025 17:11:17 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tXNy8-00BgK9-34 for pgsql-general@lists.postgresql.org; Mon, 13 Jan 2025 17:11:16 +0000 Received: from mail-yb1-xb2e.google.com ([2607:f8b0:4864:20::b2e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tXNy6-000DPK-3A for pgsql-general@postgresql.org; Mon, 13 Jan 2025 17:11:15 +0000 Received: by mail-yb1-xb2e.google.com with SMTP id 3f1490d57ef6-e3c9ec344efso6972841276.2 for ; Mon, 13 Jan 2025 09:11:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; t=1736788273; x=1737393073; darn=postgresql.org; h=content-transfer-encoding:in-reply-to:autocrypt:from :content-language:references:cc:to:subject:user-agent:mime-version :date:message-id:from:to:cc:subject:date:message-id:reply-to; bh=WK4yOHOFWCfA+qse6nZRm94C4c3nrQMVvQFipFFI6Dw=; b=bF4yqcOjFuUfxu9MpsSs18TOaB3uiHcxgTli0JtLNl1NyN92PIdgIKjdVS2fKBWnn3 GAB+NSFdmXFrwU0bWqcfFmnsuJXRV82R/erckMudPtdxHMURbAEBD8baZtJu5oJncC8c mxA1NmDsQ191DnlHsqAYr49c9nPU5/OMHSN72AguNOoctPsdALGai81Ls293c5H7xJ8J gfgMFAzmN0JJSRJmlQVm/37hcqHpX0jr3UZByMIWkF3uLWmkGQo4rbJv4UXC7udrg/8v KGbU8BVIdPoLx5WJkl8mmkeK3MXH9UN5PuGN7fOtiZbtLmRb8oWXR3KxCQ1kp9g9bclD zPcg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1736788273; x=1737393073; h=content-transfer-encoding:in-reply-to:autocrypt:from :content-language:references:cc:to:subject:user-agent:mime-version :date:message-id:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=WK4yOHOFWCfA+qse6nZRm94C4c3nrQMVvQFipFFI6Dw=; b=BlnOnayfEF7JEOBwcIrnrTROXr7HNXE20Bhs3ppdmIA+u0UoOPZnkNitdBDgHzgI3A PR1cLfvzB5Ef5jEBIt2XfI4HyS7jIQQnwWbCJsYP6rLXVtVM/4HrViU7zixvQqSPikw3 7oGkEcgHMqnpqZQExiyfb74qyvgygJ8wnZbbmQz7pivvBtkbl+zpcn5XekNnGdznjtJq 6JES/KrH2bOI0T5bSshFoaIgarLqPdbtrgrsySls++eo+xrb5OgGdS9eqsvaLi0nJ0/Z XMuJg8yynQvq6BclQ9PqJytCTLvjZTkeAsMtNWABAtBTA+hXfHmje5uVavWhXEPhtFNz 8kbA== X-Gm-Message-State: AOJu0YxHGvOV+3GlJSHD7JVyhCVfWjv2ta+620p/Bd1VJ5oOd6/QT63m CSC3LpMzyrjxVWimVtg0o1EBSs91fOYlbQ04Gd04UxYzHZMUiQ+PJWyKXq605hs= X-Gm-Gg: ASbGncsBRjrQtORlhtZKw/OjmzK5hLpJBQJExFbExB/PSL31NylSsS+PLEPv0w8HUHy 8xVb6WbfH7NFx/XP13a76DHW1uzS+ucqmCxXBdZyJ+rvqhPoXgu+drtQ9H44ZU25UQ9TEWNAG8S bjEeo6+Y7JFwiZZWtEWJt3yXuV+gQb51Q3eGtX6xeab0UfS7QKaaG3mz28WamGxzt6++8/IGR7g abddeOEQ8j76MS7G4PjbW1ObrUuQWC/jP7bcd4Y9tXCDMMUaLSNOzXiAc//DHRXL0XDY4uJQePF aeJMElshz2aQqm3130x4JyftKs/5QJlubPGe X-Google-Smtp-Source: AGHT+IFQA7wKYl3qKZN9PVrI6gVkcTMDwBwpqGQNdcRLsh9x5J8BUVOb+bSyl4qNCAU1ZSWq21fl5A== X-Received: by 2002:a05:690c:e1c:b0:6ee:7797:672 with SMTP id 00721157ae682-6f5312206a9mr159309677b3.7.1736788273444; Mon, 13 Jan 2025 09:11:13 -0800 (PST) Received: from [192.168.4.41] (162-239-31-113.lightspeed.dybhfl.sbcglobal.net. [162.239.31.113]) by smtp.gmail.com with ESMTPSA id 00721157ae682-6f68dfa92c3sm6941597b3.122.2025.01.13.09.11.12 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 13 Jan 2025 09:11:13 -0800 (PST) Message-ID: <48002fda-0daa-4287-a852-d289e63e9ccb@joeconway.com> Date: Mon, 13 Jan 2025 12:11:12 -0500 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Automatic upgrade of passwords from md5 to scram-sha256 To: Tom Lane , "Peter J. Holzer" Cc: pgsql-general@postgresql.org References: <20250112222828.b36hpzm3ulfzlkws@hjp.at> <372571.1736722760@sss.pgh.pa.us> Content-Language: en-US From: Joe Conway Autocrypt: addr=mail@joeconway.com; keydata= xsFNBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABzSRKb3NlcGggRSBD b253YXkgPG1haWxAam9lY29ud2F5LmNvbT7CwXoEEwEIACQCGwMCHgECF4AFCwkIBwMFFQoJ CAsFFgIDAQAFAlWTVvUCGQEACgkQMyt+aLaZQ0oPCQ/9HyRewMyvAIJRmoXoLAr8AoFLId6R qBJnNX0Lll0RLZui65aQ0+exwX7aH7TxWR16B2gWX3OmLfGT8XITOoG+zt9zsEpLvNkHchkF T/jyAcbuRj5WX9hamZgMbjXAJeCdlhW+fRA9Upb0w4dgBjqK5OgsqMikASL7t2vogHl9H08j vSoQLW+8wTnSBXBeBTBwB7xLIin5WVivzFHUCrnD2UsjeBIW3fmGdpTAjSxRzG+UPYVwXQ8F FLt7DpEytvLWapmZWMRdj0WZ/Q3SOO/Ed0yFqbzuwKaWcFrQBNeS2Sig+FefBNS98f9Hx7ku H3DW34qX/zSSdDh0jLs7X3PkIgF6BZR2TxaCwHPP9ERDiDaUInC9U7We1iZE1DjW8rLMEVJB hY0ClrrF67pnUKTbcU+uajpPn+2Jl74T0Set/XxpHZ4cezcJuqg31R8vHZgd5cf1WKP0D0pc qiuS02BBFkNCs1jQ+raTWcDuE6F1mUO2nvjUBN9r4y5DUbCNSqLKeAe/aA6JaSDkBpoXKdNS +c4rbzbktWkfUW8EhVlCGzNpy4ezEoVsqV2Ex7fNoxsE2vnSylLT9hycAmYf8ryMvniRZqnD T4JgLenIcQlkhB896T7wApOXfD8OJj1/XFxAfPi6vdlsr81uoxuB4euLp8IyduwLORRUogO9 zmAXG5jOwU0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMva v/KENd3oAtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFV dWnlw7dWVTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5Vu gWNlPZcV5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv 5ZhPRpO79nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc 2OrWo7YMgMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQ bauDQw6VQ38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT 0S5yOgyX0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4e NrxU0u2xAy5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA 81uMj49j0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAHCwV8EGAEI AAkFAkpXMicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4 Fia379JmHwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBG HwLEOTRcfYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZ E0C1xIE43nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6Xlnd FrDlY22tRmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl 0ZJP3sHJ2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcH YebVJZRbUUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs 5egE3szPcIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1 c9JbPJ1Pn8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+ 1iJNEnp9iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q= In-Reply-To: <372571.1736722760@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/12/25 17:59, Tom Lane wrote: > "Peter J. Holzer" writes: >> The web framework Django will automatically and transparently rehash any >> password with the currently preferred algorithm if it isn't stored that >> way already. > > Really? That implies that the framework has access to the original > cleartext password, which is a security fail already. > >> Can PostgreSQL do that, too? (I haven't found anything) > > No. The server has only the hashed password, it can't reconstruct > the original. > >> If the password for the user is stored as an MD5 hash, the server >> replies to the startup message with an AuthenticationCleartextPassword >> respnse to force the client to send the password in the clear >> (obviously you only want to do that if the connection is TLS-encrypted >> or otherwise safe from eavesdropping). > > I think this idea is a nonstarter, TLS or not. We're generally moving > in the direction of never letting the server see cleartext passwords. > It's already possible to configure libpq to refuse such requests > (see require_auth parameter), although that hasn't been made the > default. Given PQchangePassword[1] in pg17, I wonder if the next step could be to have libpq somehow know/detect that an algorithm change is needed and execute that (or some equivalent) from the client side? And presumably we could ask pgjdbc to implement something similar. Joe [1] https://www.postgresql.org/docs/17/libpq-misc.html#LIBPQ-PQCHANGEPASSWORD -- Joe Conway PostgreSQL Contributors Team RDS Open Source Databases Amazon Web Services: https://aws.amazon.com