moving from mysql to pgsql

[Joseph Tam]

mws at alpenjodel.de writes:

> 1) select digest('testing123','sha512');
> result:
> ?\x4120117b3190ba5e24044732b0b09aa9ed50eb1567705abcbfa78431a4e0a96b1152ed7f4925966b1c82325e186a8100e692e6d2fcb6702572765820d25c7e9e
> login fails

I think 2 problems here:

 	1) the encoding should be some modified base64; and

 	2) this appears to compute the SHA512 hash, *not* the
 	SHA512-Crypt hash, which is different.  It involves
 	adding a salt and doing many iterations of SHA512.
 	A totally different algorithm.

Perusing the PostgreSQL man pages, I think you need something like

 	crypt(password,gen_salt('sha512'))

I made the 'sha512' up -- I can't find PostgresSQL docs stating whether
it supports this value.  The docs I found support the older SHA1
($5$) crypt hashes.  If your version doesn't support creating SHA512
($6$) salts, you can create your own by replacing gen_salt() with "$6$"
+ base64(long random value), and feed that to crypt().

Just as long as PostgreSQL uses the system crypt() and not its own
implementation, it should produce a usable hash.

Joseph Tam <jtam.home at gmail.com>