Thread: calculating the MD5 hash of role passwords in C

calculating the MD5 hash of role passwords in C

From
Matthias Apitz
Date:
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

Re: calculating the MD5 hash of role passwords in C

From
Adrian Klaver
Date:
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



Re: calculating the MD5 hash of role passwords in C

From
Christoph Moench-Tegeder
Date:
## 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



Re: calculating the MD5 hash of role passwords in C

From
Matthias Apitz
Date:
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!



RE: calculating the MD5 hash of role passwords in C

From
Igor Neyman
Date:
-----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.


Re: calculating the MD5 hash of role passwords in C

From
Matthias Apitz
Date:
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



Re: calculating the MD5 hash of role passwords in C

From
Justin
Date:
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!


Re: calculating the MD5 hash of role passwords in C

From
Matthias Apitz
Date:
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



Re: calculating the MD5 hash of role passwords in C

From
Adrian Klaver
Date:
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



RE: calculating the MD5 hash of role passwords in C

From
Igor Neyman
Date:
-----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.

Re: calculating the MD5 hash of role passwords in C

From
Christoph Moench-Tegeder
Date:
## 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.



Re: calculating the MD5 hash of role passwords in C

From
Bruce Momjian
Date:
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 +



Re: calculating the MD5 hash of role passwords in C

From
Matthias Apitz
Date:
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



Re: calculating the MD5 hash of role passwords in C

From
Stephen Frost
Date:
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

Attachment