calculating the MD5 hash of role passwords in C

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...

https://www.postgresql.org/docs/current/pgcrypto.html
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 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 free to 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