DevHeads.net

Best practice for mod_authn_dbd + postgresql; really SHA1?

Dear Experts,

I'm trying to determine the best way to implement Apache
authentication using mod_authn_dbd and PostgrSQL; my starting
point has been this documentation:

<a href="https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html" title="https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html">https://httpd.apache.org/docs/2.4/mod/mod_authn_dbd.html</a>
<a href="https://httpd.apache.org/docs/2.4/misc/password_encryptions.html" title="https://httpd.apache.org/docs/2.4/misc/password_encryptions.html">https://httpd.apache.org/docs/2.4/misc/password_encryptions.html</a>
<a href="https://www.postgresql.org/docs/9.6/static/pgcrypto.html" title="https://www.postgresql.org/docs/9.6/static/pgcrypto.html">https://www.postgresql.org/docs/9.6/static/pgcrypto.html</a> (F.25.2)

It looks like my choices are:

1: Do the crypto in PostgreSQL. For example, I could insert
new password hashes:

INSERT INTO passwords (username, pwhash)
VALUES ('phil', crypt('passw0rd', gen_salt('bf',8)));

And check them:

SELECT * FROM passwords WHERE username='phil'
AND pwhash = crypt('passw0rd', pwhash);

The trouble is that this returns a pass/fail response, but at the
Apache end I don't see a way to make mod_authn_dbd (or anything else)
use that; that module wants to receive a password hash that it will
check itself. So:

2. Do the crypto in Apache. I could simply:

SELECT pwhash FROM passwords WHERE username='phil';

But getting the passwords into PostgreSQL in the first place is
not so easy. pgcrypto's blowfish implementation doesn't seem to
be quite the same as what Apache uses ($2a$ vs $2y$, whatever that
means...), so it looks like I would probably need to invoke the
htpasswd utility somewhere between my e.g. create_user.cgi /
change_password.cgi scripts and the SQL. So:

3. Use SHA1. This is described as "probably the most useful format
for DBD authentication" in the Apache docs, since the Apache and
PostgreSQL implementations are the same:

INSERT INTO passwords (username, pwhash)
VALUES ('phil', '{SHA}'||encode(digest('passw0rd','sha1'),'base64'));

SELECT pwhash FROM passwords WHERE username='phil';

But the disadvantage is that this is relatively insecure; there is no
salt and the SHA1 algorithm is quick enough that it could be brute-forced.
I could of course add a salt before hashing, but I don't think there is
a way to make mod_authn_dbd compare against the same salted password.

I find this all a bit surprising. Isn't this a common scenario? Am
I missing something? What do other people do?

Many thanks for any advice.

Regards, Phil.