Re: Automatic upgrade of passwords from md5 to scram-sha256
On Sun, 12 Jan 2025 at 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. It happens upon user login. If the user's password is hashed with an old algorithm, it is re-hashed during login when the Django application running on the Web server has the password sent by the user: https://docs.djangoproject.com/en/5.1/topics/auth/passwords/#password-upgrading But of course this only works if the old method in use involves sending the password to the server.
Re: Automatic upgrade of passwords from md5 to scram-sha256
On 2025-01-12 17:59:20 -0500, 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.
It's a server-side web framework, and it doesn't require JavaScript in
the browser. So the only way it can authenticate the user is by
receiving username and password in a POST request (except for HTTP Basic
or Digest authentication which are both worse, IMHO).
SCRAM could be implemented in an authentication module, it just needs a
SCRAM implementation in JavaScript which can be included in the login
page. Somebody has probably already implemented this, but it's not in
the core distribution.
Anyway, Django is just the inspiration for the idea.
> > Can PostgreSQL do that, too? (I haven't found anything)
>
> No. The server has only the hashed password, it can't reconstruct
> the original.
But it could get the original during login.
> > 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.
A way to transparently upgrade from MD5 to SCRAM would IMHO be useful
for that. Requesting the clear text password once is IMHO preferable to
being stuck with MD5 until the users decide (or can be forced) to change
their passwords (oh, and if you send an "alter user password" command
the server will also see the clear text password unless the client can
and does) compute the hash).
PostgreSQL's MD5 hash is, as far as I can see, password equivalent. You
don't actually need the original password, only the stored MD5
hash for a successful login:
concat('md5', md5(concat(md5(concat(password, username)), random-salt)))
md5(concat(password, username)) is stored in the pg_shadow table.
That's not good.
> It's already possible to configure libpq to refuse such requests
> (see require_auth parameter), although that hasn't been made the
> default.
If it's not the default there's a decent chance that the users haven't
changed it.
hp
--
_ | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| | | h...@hjp.at |-- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
signature.asc
Description: PGP signature
Re: Alter table fast
On 2025-01-09 20:52:27 +0100, sham...@gmx.net wrote: > Am 09.01.25 um 20:17 schrieb veem v: > > > Out of curiosity, why NUMERIC(15,0) instead of BIGINT? > > > > It's for aligning the database column types to the data model and > > it's happening across all the upstream downstream systems. I was > > thinking if this can be made faster with the single line alter > > statement "Alter table alter column type > > numeric(15,0) USING ::NUMERIC(15,0);" > > Hmm, I would rather change numeric(15,0) to bigint if I had to "align" types > across systems. I'm also wondering what "the data model" is. If I have numeric(15,0) in an abstract data model, that means that I expect values larger than 99,999,999,999,999 but at most 999,999,999,999,999. That seems to be oddly specific and also somewhat at odds with reality when until now there apparently haven't been any values larger than 2,147,483,647. What kind of real world value could suddenly jump by more than 5 orders of magnitude but certainly not by 7? A bigint is much less precise (more than 2,147,483,647 but not more than 9,223,372,036,854,775,807) and therefore more suitable for values where you don't really know the range. However, for the problem at hand, I doubt it makes any difference. Surely converting a few million values takes much less time than rewriting a 50 GB table and all its indexes. So there isn't really a faster way to do what Veem wants. There may however be less disruptive way: He could create a new column with the new values (which takes at least as long but can be done in the background) and then switch it over and drop the old column. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Alter table fast
This is the right approach, Peter J. Holzer, from a well season DBA perspective "ALTER TABLE working_table ADD COLUMN B INTEGER ; UPDATE working_table SET B = A;" Bare in mind the indexes or existing references to an from other tables and act accordingly-- define the new and drop the old. Good luck. On Sun, Jan 12, 2025, 2:20 PM Peter J. Holzer wrote: > On 2025-01-09 20:52:27 +0100, sham...@gmx.net wrote: > > Am 09.01.25 um 20:17 schrieb veem v: > > > > Out of curiosity, why NUMERIC(15,0) instead of BIGINT? > > > > > > It's for aligning the database column types to the data model and > > > it's happening across all the upstream downstream systems. I was > > > thinking if this can be made faster with the single line alter > > > statement "Alter table alter column type > > > numeric(15,0) USING ::NUMERIC(15,0);" > > > > Hmm, I would rather change numeric(15,0) to bigint if I had to "align" > types across systems. > > I'm also wondering what "the data model" is. > > If I have numeric(15,0) in an abstract data model, that means that I > expect values larger than 99,999,999,999,999 but at most > 999,999,999,999,999. That seems to be oddly specific and also somewhat > at odds with reality when until now there apparently haven't been any > values larger than 2,147,483,647. What kind of real world value could > suddenly jump by more than 5 orders of magnitude but certainly not by 7? > > A bigint is much less precise (more than 2,147,483,647 but not more > than 9,223,372,036,854,775,807) and therefore more suitable for values > where you don't really know the range. > > However, for the problem at hand, I doubt it makes any difference. > Surely converting a few million values takes much less time than > rewriting a 50 GB table and all its indexes. > > So there isn't really a faster way to do what Veem wants. There may > however be less disruptive way: He could create a new column with the > new values (which takes at least as long but can be done in the > background) and then switch it over and drop the old column. > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
Re: Automatic upgrade of passwords from md5 to scram-sha256
"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. regards, tom lane
Automatic upgrade of passwords from md5 to scram-sha256
I have a PostgreSQL instance where the majority of the passwords is still stored as MD5 hashes. I'm not particularly worried because they are all randomly generated and should be reasonably secure against brute force attacks even with a weak hash, and they're not that valuable anyway, but it would still be nice if I could upgrade them to SCRAM-SHA256. The web framework Django will automatically and transparently rehash any password with the currently preferred algorithm if it isn't stored that way already. Can PostgreSQL do that, too? (I haven't found anything) If not, would this feature be of general interest? Looking through chapter 53 of manual I think it would have to implemented like this: 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). The client sends an PasswordMessage with the cleartext password. The server first checks the password against the stored MD5 hash and (assuming it's correct) then computes and stores the SCRAM-SHA256 hash, just as if the user had issued an "alter user password" command. Finally it replies with an AuthenticationOk message as normal. The next time the client connects, the server will find and and use the SCRAM-SHA256 hash. This feature should only be enabled by a GUC. Additional question: Do current clients (especially the ODBC client) even support AuthenticationCleartextPassword by default? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Automatic upgrade of passwords from md5 to scram-sha256
On Sun, Jan 12, 2025 at 05:59:20PM -0500, Tom Lane wrote: > > 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. Agreed. I think weakening the MD5 handshake to switch to a more secure hash algorithm is unwise. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Do not let urgent matters crowd out time for investment in the future.
