[pushpop]

Another thing to bare in mind, for those who are rolling their own, is how you caress those passwords from the DB.

The common approach is a simple DB SQL select. But that then means if your web server gets exploited an attacker can dump the entire password database.

The safer option is to write a stored procedures to return or modify that table and set permissions on that table so even your web app creds can’t directly query the password table. Then your web service only has access to check a single password, rather than downloading every hash on the DB.

If you can also offload the encryption/decryption and hashing then that is another step forward too.

[pushpop]

Anyone care to elaborate how this post is wrong?

Hint: it isn’t. But I’ve been penalised for it all the same

[colinclerk]

I'll bite. It doesn't seem obviously wrong to me. I would also love to know if I'm misunderstanding something.

I've never seen it done this way, but I think postgres pgcrypto could support this.

If I had to guess, I haven't seen it done this way because authentication frameworks are not normally in a position to lock down access to the database in this way (e.g. it couldn't create a password table that the web app credentials can't see, because it's integrated into the web app and uses the web app credentials to create the password table). The way they typically behave is: - When updating password, run bcrypt in the web server and INSERT - When testing password, SELECT the bcrypt hash down to the web server, and test on the web server.

Have you used this stored procedure strategy in production? I'm particularly interested if it's caused any challenges with resource usage in the database server?

The top answer in this stack overflow question makes the argument that you should bcrypt in the web server to lessen the time it's unhashed: > Use php bcrypt if you can, it'll lessen the time that the password remains unhashed. https://stackoverflow.com/questions/2647158/how-can-i-hash-p...

I'm not sure I agree with this argument, unless perhaps the database is hosted by a separate vendor (which would mean another party is receiving the unhashed password). Also note: the strategy proposed in that answer doesn't have the benefit of a stored procedure preventing a SELECT all, so maybe the less time argument makes sense in that case.

Perhaps there's a valid discussion around - is this going overboard? Is preparing for a leak of web app database credentials an attack vector we really need to prepare for? If we do, are password hashes the critical data we need to be securing in this manner? When hashes leak I've been asked to change my password as precautionary measure, but hashing algos are such that this event shouldn't be catastrophic. Unlike a credit card number leak, for example, which would cause a bigger headache.

[pushpop]

I learned this technique when I worked on the blue team of a cyber security firm.

It’s not about sending your passwords in clear text to your RDBMS. You’d still use bcrypt like you normally would but instead of querying the password table directly you’d have that prewritten as a stored procedure. That way you can have different permissions for the password table.

Some frameworks and/or other managed solutions might already be doing this but it’s worth mentioning anyway since people are talking about hand rolling their own auth.

I don’t know why I added the part about offloading encryption though. That doesn’t make sense. I guess that will teach me talking security before my first coffee of the day

[colinclerk]

I can't really use bcrypt like I normally would, since bcrypt normally* concatenates the cost and salt into a single string with the hash that I store in my database: https://en.wikipedia.org/wiki/Bcrypt

I believe I can split out the cost and salt. That would let me:

1. SELECT the user's salt and cost from a table that is accessible with my web app credentials.

2. Run bcrypt on the user-provided password with the selected salt and cost within my webapp.

3. Ask my stored procedure if my resulting hash matches the hash in the database, even though I cannot SELECT the hash directly with my web app credentials.

Am I understanding this correctly? I imagine it would have been easier pre-bcrypt, when generating salts was less abstracted away from the developer.

I can't think of a reason this wouldn't work, and it adds a layer of security if implemented properly. But I say that hesitantly, as I would all things crypto, particularly since I wouldn't consider it common practice and I'm not sure if I'm overlooking something. I'm pretty sure I'd need to hack on bcrypt-ruby more than comfortable to actually implement.

* I say normally because that's what the wiki says and how bcrypt-ruby behaves, but I haven't done wider research.