3 ms·

When it comes to security and SQL in general I think stored procedures are underappreciated. Bring back the DBA roll and make procedures for everything. Then yo

by Puts 5y ago

When it comes to security and SQL in general I think stored procedures are underappreciated. Bring back the DBA roll and make procedures for everything. Then you don't even have to build that API-layer. SQL is your API. And for things like password hashes, there doesn't even have to be a user that can read the hash-column, let a stored procedure compare them for you and there's no risk of leaking password hashes (unless the whole server gets compromised of course).

giraffe_lady 5y ago
I think there's a little momentum in that direction. I've noticed one of the "cool" startup stacks right now is typescript, serverless functions, postgres, and some places do a lot with sql functions, stored procedures, and pg/pl languages. Overall I agree with you but the process/tooling is really the issue. Not inherently, it's just less developed and less standardized than for other kinds of code. DBAs aren't necessarily a silver bullet here either. They have a different, somewhat overlapping focus and while they have tools for managing changes to SQL-as-code, they're usually not built around the assumption that devs are shipping diffs to SQL as part of their normal day-to-day work. Not insurmountable complexities by any means but unless you are or work closely with an expert in your chosen DB, it might be difficult to accurately estimate what you're getting into. --- This probably is overall more secure but not in a magical way. There's nothing really special about a user here, something will eventually have to have a role that can read that column. For example a classic trap that has caused plenty of security breaches is needing to write a function as `security definer` to account for RLS policies but not remembering to exclude user-writeable schemas from search_path. No users necessary for that leak.

3 ms·

When it comes to security and SQL in general I think stored procedures are underappreciated. Bring back the DBA roll and make procedures for everything. Then yo

by Puts 5y ago

giraffe_lady 5y ago
I think there's a little momentum in that direction. I've noticed one of the "cool" startup stacks right now is typescript, serverless functions, postgres, and some places do a lot with sql functions, stored procedures, and pg/pl languages. Overall I agree with you but the process/tooling is really the issue. Not inherently, it's just less developed and less standardized than for other kinds of code. DBAs aren't necessarily a silver bullet here either. They have a different, somewhat overlapping focus and while they have tools for managing changes to SQL-as-code, they're usually not built around the assumption that devs are shipping diffs to SQL as part of their normal day-to-day work. Not insurmountable complexities by any means but unless you are or work closely with an expert in your chosen DB, it might be difficult to accurately estimate what you're getting into. --- This probably is overall more secure but not in a magical way. There's nothing really special about a user here, something will eventually have to have a role that can read that column. For example a classic trap that has caused plenty of security breaches is needing to write a function as `security definer` to account for RLS policies but not remembering to exclude user-writeable schemas from search_path. No users necessary for that leak.