9 ms·
It's hard to make things totally impossible, but hopefully a good post-mortem will identify the systemic issues that led to it happening, fix them, and not thro
by llamataboot 7y ago
It's hard to make things totally impossible, but hopefully a good post-mortem will identify the systemic issues that led to it happening, fix them, and not throw anyone under the bus.
- londons_explore 7y ago"I'll just ask engineering to fix your wonky account" "Ah yeah - it got in a bad state somehow, let me fix it manually" UPDATE permissions SET allow = 1 WHERE user=671156 AND permission=16 AND org=101 OR 102; Classic SQL blunder...
- londons_explore 7y agoFor anyone reading this who has login access to a production SQL database...: * Change your account to readonly. Make a new admin account, and put its credentials somewhere hard to get (and audited!). * Make a directory in git for 'one off sql statements'. Make them all go through code review and have an automated system run them on merge/deploy. * Enforce style rules with a linter/test, like "UPDATE must have a LIMIT" * Anything the above process is too burdensome to do should have an API or admin interface built for the purpose. * Aim to eventually get rid of your readonly account. A leaked customer data dump could kill the company and shouldn't be available to any malware on your machine. You aren't as secure as you think you are.
- megous 7y agoYou can also do changes in the transaction and check that it did what you expected before committing.
- PowerfulWizard 7y agoThat is smart, I would add "limit 2" to a single row update, so if it returns 2 rows updated I know it was wrong.
- SOLAR_FIELDS 7y agoFair warning about this: if your transaction isolation level in your database is set to serialized hanging out in mid transaction while you verify your changes will block other calls to the table(s)! I always recommend that you are wrapping calls in transactions if you are touching prod data. I only left the WHERE clause of my update query off once in my life before I started doing that.
- megous 7y agoIf you fear this, you can put it into script like this: BEGIN; UPDATE... SELECT for verification ROLLBACK; And then just change ROLLBACK to COMMIT, after you're sattisfied. There's no need to do it interactively.
- jjeaff 7y agoI agree with everything you said except "a customer database leak could kill a company". Do we have any example of any company going under due to a data breach? Unfortunately, there seem to be a lot of examples of enormous breaches that did essentially nothing. (like Experian)
- londons_explore 7y agoThat Italian malware vendor closed down after all it's internal documents were leaked. As did the law firm behind the Panama papers
- usmannk 7y agoHacking Team is still alive and kicking.
- jjeaff 7y agoWell yes, but I would attribute the first to simply doing illegal things and getting caught. The data breach just helped them get caught faster. The second, yes, although privacy was literally their number one product and I wouldn't categorize a leak quite the same as a breach.
- succleaf 7y agoCode Spaces https://www.theregister.co.uk/2014/06/18/code_spaces_destroyed/ https://www.theregister.co.uk/2014/06/18/code_spaces_destroy... MyBizHomepage - once valued at $100m in 2008 https://www.nytimes.com/2012/08/23/business/smallbusiness/struggling-to-recover-from-a-cyberattack.html https://www.nytimes.com/2012/08/23/business/smallbusiness/st... but that fact I could find them at all by searching for https://duckduckgo.com/?q=company+folded+after+breach https://duckduckgo.com/?q=company+folded+after+breach is evidence that it is rare
- throwaway287391 7y ago> * Enforce style rules with a linter/test, like "UPDATE must have a LIMIT" As someone who has never had access to a DB with any serious number of users, can you explain this one further? What if you really do want to update every row? Do you just do LIMIT INT_MAX or the like, and just force people to write that so that they always know they're updating the entire table? Or are you saying you should only ever use UPDATE on a known finite (and small?) number of rows?
- groceryheist 7y agoI think it might just be like a psychological thing to try to prevent mistakes. Like a dialogue box asking "Sure?" before you delete your harddrive.
- londons_explore 7y agoUpdating every row is usually a bad idea anyway. That means every row needs to be rewritten, which involves reading and writing all data in the table. That will take locks which mean old data needs to be kept (in case of a transaction abort), doubling disk usage. Since half of rows are now dead, all other queries to the table will take double the disk access time. Basically, if your table is over a few gigabytes, updating every row in one query on a production instance is a really bad plan.
- CydeWeys 7y agoSo how do you make the change then? Do SQL dialects provide built-in tools to do an UPDATE to a large table in a saner way, or do you end up having to write an application to do so?
- joatmon-snoo 7y agoIt's the notion of a staggered rollout. You should never initiate a global change without first validating it on some small, controlled-impact subset.
- isatty 7y agoYou forgot one thing, if you’re ever executing one off statements make sure they’re in a transaction if it’s an insert or update. BEGIN; Statement COMMIT; or ROLLBACK etc
- gknoy 7y agoI'm sorry for being a SQL noob. Can you elaborate on what is wrong with this, and what would be the way to write it without causing the scale of problem described in the OP?
- brianwu02 7y agothe last OR statement "OR 102" will evaluate true. It should be UPDATE permissions SET allow = 1 WHERE user=671156 AND permission=16 AND org=101 OR org=102;
- fazzone 7y agoThe problem is that the condition is parsed as (user=671156 AND permission=16 AND org=101) OR 102 The right way is to use something like org IN (101, 102)
- 7y ago
- snaky 7y ago> In addition to the SQL-standard privilege system available through GRANT, tables can have row security policies that restrict, on a per-user basis, which rows can be returned by normal queries or inserted, updated, or deleted by data modification commands. This feature is also known as Row-Level Security https://www.postgresql.org/docs/11/ddl-rowsecurity.html https://www.postgresql.org/docs/11/ddl-rowsecurity.html