Is it a good idea to let a database do a password check?

Question

Setup

A server running a PostgreSQL database and an Apache with several php applications. Access to db is over the loopback interface. The db contains a table with the username and SHA-512-CRYPT password hash ($6$salt$hash).

Common solution

Applications read password from user, hash it and do a string comparison on the database. If strings match, user is authenticated.

My solution

The database has a stored function(or prepared statement?) check_password(user, password) which php applications query with SQL. So they send the user and password in clear and interpret the db's answer (e.g. rows returned). This has the advantage that I don't have to make sure that all the applications support the same secure hash algorithm and I can encapsulate the authentication in a single place in the db. The same could be done for password changes update_password(user, old_password, new_password).

Are there any drawbacks to this solution?

EDIT: Added clarification on stored password format.

Answer 1

The "common solution" you mentioned has one glaring and obvious flaw: it can't use salt. If your hash has a salt (it should), then you need to fetch the salt before performing the hash operation. And if you're fetching the salt, you should just fetch the hash result at the same time. In fact, the two are typically stored in the same string.

Ideally, you should fetch the hash from the database and perform the computation on it in your application. Your bit about not wanting to worry about your applications supporting your hash functions is odd. Applications typically lead, not lag, databases in technology adoption.

Your solution of putting the password checking routine in a stored procedure is a bit crazy from a scalability point of view; password hashing if done right is extremely processor and memory intensive. Dumping that kind of load onto your database is ludicrous.

Stored procedures push computation from the web server to the database server, as do complex SQL queries. While that's OK in theory, in practice your database tends to be the least scalable part of your whole operation. Web servers tend to be (should be if you do things right) completely stateless, so running ten webservers takes no more work than running two. Databases are not so simple, and scaling up your database is one of the most difficult and expensive propositions most small websites face. This is immensely more difficult if you use any sort of database-side business logic. If you want the thing to be scalable, your application server should contain logic and no state, and your database should contain state and no logic.

Answer 2

Assuming that your stored procedure uses something like bcrypt with a secure salt and many rounds, I see no problem with what you are implementing. Your basically treating the database as an identity provider in a federated identity system where the applications are PHP applications.

That said, I see no reason why you would do this. PHP has builtin functions for password management. These are well tested and, in the event of a problem being discovered, will be patched quickly. That is unlikely to be the case for your custom stored procedure. When it comes to security, it is (nearly) always better to go with the road more traveled. So stick with the PHP primitives and be confident that they got it right.

Answer 3

Is the stored procedure doing salting, and multiple rounds of secure hashing? If you don't implement this right, it could effect DB performance and your security. There's a LOT of libraries out there in pretty much every web language to handle secure authentication.

Using a stored procedure is a good idea when authenticating the user or interacting with any accounts table. That way even a SQLi couldn't modify the table beyond using the functions you created.

EDIT: Looks like someone did this over on stack overflow https://stackoverflow.com/questions/12736576/how-reimplement-bcryptpasswordhasher-of-django-with-postgres-9-1