Thread: calculating the MD5 hash of role passwords in C
Hello,
If I look into the database I see:
sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
rolname | rolpassword
---------+-------------------------------------
sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5
I know the clear text password of the role, it is simple 'sisis123', how
could I calculate the above MD5 hash from the clear text password, for
example in C? Which salt is used for the crypt(3) function?
Thanks
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Attachment
On 1/22/20 10:20 AM, Matthias Apitz wrote: > > Hello, > > If I look into the database I see: > > sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis'; > rolname | rolpassword > ---------+------------------------------------- > sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5 > > I know the clear text password of the role, it is simple 'sisis123', how > could I calculate the above MD5 hash from the clear text password, for > example in C? Which salt is used for the crypt(3) function? https://www.postgresql.org/docs/12/runtime-config-connection.html "Because md5 uses the user name as salt on both the client and server, md5 cannot be used with db_user_namespace." ~/src/common/md5.c /* * Place salt at the end because it may be known by users trying to crack * the MD5 output. */ So: select md5('sisis123sisis'); md5 ---------------------------------- 2f128a1fbbecc4b16462e8fc8dda5cd5 > > Thanks > > matthias > -- Adrian Klaver adrian.klaver@aklaver.com
## Matthias Apitz (guru@unixarea.de): > sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis'; > rolname | rolpassword > ---------+------------------------------------- > sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5 > > I know the clear text password of the role, it is simple 'sisis123', how > could I calculate the above MD5 hash from the clear text password, for > example in C? Which salt is used for the crypt(3) function? The documentation on pg_authid has the details: "The MD5 hash will be of the user's password concatenated to their user name." https://www.postgresql.org/docs/12/catalog-pg-authid.html Regards, Christoph -- Spare Space
El día miércoles, enero 22, 2020 a las 07:58:47p. m. +0100, Christoph Moench-Tegeder escribió: > ## Matthias Apitz (guru@unixarea.de): > > > sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis'; > > rolname | rolpassword > > ---------+------------------------------------- > > sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5 > > > > I know the clear text password of the role, it is simple 'sisis123', how > > could I calculate the above MD5 hash from the clear text password, for > > example in C? Which salt is used for the crypt(3) function? > > The documentation on pg_authid has the details: > "The MD5 hash will be of the user's password concatenated to their user name." > https://www.postgresql.org/docs/12/catalog-pg-authid.html Thanks to all who replied. This is still not exactly what I was looking for. But has an interesting detail (salting the role password by adding the role name to it). An implementation with UNIX crypt(3) for MD5 would need an additional salt like '$1$salt' to encrypt 'sisis123sisis'. For sure the next place to look is the implementation of the PostgreSQL's md5() function. Thanks again matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland! Germany out of NATO! NATO out of Germany! Peace with Russia! ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
-----Original Message----- From: Matthias Apitz [mailto:guru@unixarea.de] Sent: Wednesday, January 22, 2020 2:41 PM To: pgsql-general@lists.postgresql.org Subject: Re: calculating the MD5 hash of role passwords in C .................... ..................... -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland! Germany out of NATO! NATO out of Germany! Peace with Russia! ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia! _____________________________________________________________________________________________________________ I don't think that political slogans in your signature are appropriate for this forum.
El día miércoles, enero 22, 2020 a las 07:52:51p. m. +0000, Igor Neyman escribió: > -----Original Message----- > From: Matthias Apitz [mailto:guru@unixarea.de] > Sent: Wednesday, January 22, 2020 2:41 PM > To: pgsql-general@lists.postgresql.org > Subject: Re: calculating the MD5 hash of role passwords in C > > > -- > Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub > > Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland! > Germany out of NATO! NATO out of Germany! Peace with Russia! > ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia! > > _____________________________________________________________________________________________________________ > > I don't think that political slogans in your signature are appropriate for this forum. > This is technical just a signature and normally I delete it when posting to groups, I forgot it in this case. Said that, in any case, you are free to "think" whatever you want, as I am free to write whatever I think. And you are free to just ignore it. matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Not sure what your after but here is more information regarding how to store passwords in Postgresql, not related to database roles but for storing passwords for things like websites...
section F.25.2.XXX
On Wed, Jan 22, 2020 at 2:41 PM Matthias Apitz <guru@unixarea.de> wrote:
El día miércoles, enero 22, 2020 a las 07:58:47p. m. +0100, Christoph Moench-Tegeder escribió:
> ## Matthias Apitz (guru@unixarea.de):
>
> > sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis';
> > rolname | rolpassword
> > ---------+-------------------------------------
> > sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5
> >
> > I know the clear text password of the role, it is simple 'sisis123', how
> > could I calculate the above MD5 hash from the clear text password, for
> > example in C? Which salt is used for the crypt(3) function?
>
> The documentation on pg_authid has the details:
> "The MD5 hash will be of the user's password concatenated to their user name."
> https://www.postgresql.org/docs/12/catalog-pg-authid.html
Thanks to all who replied.
This is still not exactly what I was looking for. But has an interesting
detail (salting the role password by adding the role name to it). An
implementation with UNIX crypt(3) for MD5 would need an additional salt
like '$1$salt' to encrypt 'sisis123sisis'. For sure the next place to
look is the implementation of the PostgreSQL's md5() function.
Thanks again
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland!
Germany out of NATO! NATO out of Germany! Peace with Russia!
¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia!
El día miércoles, enero 22, 2020 a las 03:32:17p. m. -0500, Justin escribió:
> Not sure what your after but here is more information regarding how to
> store passwords in Postgresql, ...
I just want to write a piece of C-code to generate the same string as
stored in rolpassword based on the roles password (and as I learned
added the rolname), i.e. how to generate md52f128a1fbbecc4b16462e8fc8dda5cd5
from sisis123 (password) and sisis (rolename). And this, not to brute
force credentials, but to understand the hash.
matthias
--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
On 1/22/20 12:58 PM, Matthias Apitz wrote: > El día miércoles, enero 22, 2020 a las 03:32:17p. m. -0500, Justin escribió: > >> Not sure what your after but here is more information regarding how to >> store passwords in Postgresql, ... > > I just want to write a piece of C-code to generate the same string as > stored in rolpassword based on the roles password (and as I learned > added the rolname), i.e. how to generate md52f128a1fbbecc4b16462e8fc8dda5cd5 > from sisis123 (password) and sisis (rolename). And this, not to brute > force credentials, but to understand the hash. The way Postgres does it: https://doxygen.postgresql.org/src_2common_2md5_8c.html#ad1cda4632643f79bbb60f0466fec0e41 > > matthias > > -- Adrian Klaver adrian.klaver@aklaver.com
-----Original Message----- From: Matthias Apitz [mailto:guru@unixarea.de] Sent: Wednesday, January 22, 2020 3:05 PM To: Igor Neyman <ineyman@perceptron.com> Cc: pgsql-general@lists.postgresql.org Subject: Re: calculating the MD5 hash of role passwords in C > -- > Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ > +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub > > Deutschland raus aus der NATO! NATO raus aus Deutschland! Frieden mit Russland! > Germany out of NATO! NATO out of Germany! Peace with Russia! > ¡Alemania fuera de OTAN! ¡OTAN fuera de Alemania! ¡Paz con Rusia! > > ______________________________________________________________________ > _______________________________________ > > I don't think that political slogans in your signature are appropriate for this forum. > This is technical just a signature and normally I delete it when posting to groups, I forgot it in this case. Said that, in any case, you are free to "think" whatever you want, as I am free to write whatever I think. And you are freeto just ignore it. matthias _____________________________________________________________________________________________________________ So, I'm free to "think", but you are free to write. Interesting distinction... Igor N.
## Matthias Apitz (guru@unixarea.de): > > The documentation on pg_authid has the details: > > "The MD5 hash will be of the user's password concatenated to their user name." > > https://www.postgresql.org/docs/12/catalog-pg-authid.html > > This is still not exactly what I was looking for. But has an interesting > detail (salting the role password by adding the role name to it). An > implementation with UNIX crypt(3) for MD5 would need an additional salt > like '$1$salt' to encrypt 'sisis123sisis'. It's not crypt(3). It's "the MD5 hash of the user's password concatenated to their user name". Try: perl -MDigest::MD5 -e 'print("md5" . Digest::MD5::md5_hex("sisis123" . "sisis") . "\n");' Regards, Christoph -- Spare Space.
On Thu, Jan 23, 2020 at 05:15:37PM +0100, Christoph Moench-Tegeder wrote: > ## Matthias Apitz (guru@unixarea.de): > > > > The documentation on pg_authid has the details: > > > "The MD5 hash will be of the user's password concatenated to their user name." > > > https://www.postgresql.org/docs/12/catalog-pg-authid.html > > > > This is still not exactly what I was looking for. But has an interesting > > detail (salting the role password by adding the role name to it). An > > implementation with UNIX crypt(3) for MD5 would need an additional salt > > like '$1$salt' to encrypt 'sisis123sisis'. > > It's not crypt(3). It's "the MD5 hash of the user's password concatenated > to their user name". > Try: > perl -MDigest::MD5 -e 'print("md5" . Digest::MD5::md5_hex("sisis123" . "sisis") . "\n");' FYI, this is documented: https://www.postgresql.org/docs/12/protocol-flow.html#id-1.10.5.7.3 AuthenticationMD5Password The frontend must now send a PasswordMessage containing the password (with user name) encrypted via MD5, then encrypted again using the 4-byte random salt specified in the AuthenticationMD5Password message. If this is the correct password, the server responds with an AuthenticationOk, otherwise it responds with an ErrorResponse. The actual PasswordMessage can be computed in SQL as concat('md5', --> md5(concat(md5(concat(password, username)), random-salt))). (Keep in mind the md5() function returns its result as a hex string.) -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
El día jueves, enero 23, 2020 a las 05:15:37p. m. +0100, Christoph Moench-Tegeder escribió: > ## Matthias Apitz (guru@unixarea.de): > > > > The documentation on pg_authid has the details: > > > "The MD5 hash will be of the user's password concatenated to their user name." > > > https://www.postgresql.org/docs/12/catalog-pg-authid.html > > > > This is still not exactly what I was looking for. But has an interesting > > detail (salting the role password by adding the role name to it). An > > implementation with UNIX crypt(3) for MD5 would need an additional salt > > like '$1$salt' to encrypt 'sisis123sisis'. > > It's not crypt(3). It's "the MD5 hash of the user's password concatenated > to their user name". > Try: > perl -MDigest::MD5 -e 'print("md5" . Digest::MD5::md5_hex("sisis123" . "sisis") . "\n");' Thanks! Or one can use: $ echo -n sisis123sisis | openssl md5 | sed 's/^.* /md5/' md52f128a1fbbecc4b16462e8fc8dda5cd5 $ perl -MDigest::MD5 -e 'print("md5" . Digest::MD5::md5_hex("sisis123" . "sisis") . "\n");' md52f128a1fbbecc4b16462e8fc8dda5cd5 matthias -- Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Greetings, * Matthias Apitz (guru@unixarea.de) wrote: > If I look into the database I see: > > sisis71=# select rolname, rolpassword from pg_authid where rolname = 'sisis'; > rolname | rolpassword > ---------+------------------------------------- > sisis | md52f128a1fbbecc4b16462e8fc8dda5cd5 > > I know the clear text password of the role, it is simple 'sisis123', how > could I calculate the above MD5 hash from the clear text password, for > example in C? Which salt is used for the crypt(3) function? Didn't see it mentioned here, but it probably should be- newer PG installs really should be using SCRAM and not md5 and the way the validator is built/stored is rather different from the simple md5 that you've probably seen in the past. Thanks, Stephen