I have an impression that many developers actively avoid stored procedures, trigger or any 'complex' database features because they don't fit well into a typical CI/CD pipeline which deals only with application code.
It is quite possible to test stored procedures, it's just requires some additional work/infrastructure.
It's mostly a familiarity and education problem, as well as the fact MySQL didn't have good support for this. Developers are exposed to procedural programming languages at university, but seldom to databases, let alone stored procedures. Unit tests can be written in SQL or PL/PGSQL and will typically run much faster than a CI pipeline.
Re:Securing a Postgres Database
by doctor_eval on Friday April 02, 2021 @05:35PM
(#26676234)
When used for business logic they also execute about 20x faster than the same logic encoded in a client, and in far fewer LOC. Getting rid of all those round-trips has a huge effect!
Running a slow hash operation that should take a non-negligible amount of CPU on your single core per process database server isn’t a great idea.
That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
You can do the slow pbkdf2 hash on your webserver and use the result for your stored procedure to check the password. If the password column is only visible to the stored procedure you have used the best features of both systems.
No you can’t. To do the hash you need the per user salt before you start hashing. Which would require reading from the DB.
Then you can retrieve salt, do hash on server, and use the stored procedure to compare. More requests, but a tradeoff for security \o/
There is no reason you can‘t have a deterministic function which generates a hash for the user.
A simple sha256(lower(email)) is equally secure as a complete random salt, the only requirement on a salt is to be unique.
Re:Securing a Postgres Database
by cooperaustinj on Friday April 02, 2021 @05:38PM
(#26676263)
I agree with the password example. I don't agree with people updating queries willy-nilly. Updated queries should go through the same life-cycle of testing (etc) as code, meaning they can simply go out with continuous application deployments anyways.
People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.