PostgreSQL 10.0 preview 主动防御 - 可配置是否允许执行不带where条件的updatedelete
背景
你是否曾经被不带where 条件的SQL误伤过呢?
比如
update tbl set amount=amount-100 where id=?;
缺少where条件,就变成了
update tbl set amount=amount-100;
正常情况下,这样的SQL不应该在业务逻辑中出现。通常出现在SQL注入,又或者误操作中。
如果你真的不小心执行了,那么全表的数据都会被删除或者更新,最快的恢复手段是flash back query,PostgreSQL中,可以使用xlog,生成UNDO,比如将xlog_level设置为logical,同时表的match必须设置为记录FULL OLD VALUE。
那么就有方法从xlog中生成UNDO,flash back该表。
flashback query属于被动防御的话,数据库有没有主动防御措施呢?
主动防御
PostgreSQL提供了一个机制,允许你设置参数
+bool allow_empty_deletes = true;
+bool allow_empty_updates = true;
从而允许是否能执行不带where 条件的update或delete.
这个参数可以设置为全局、会话级、用户级、库级、或者事务级别。
设置后,你就能控制是否允许执行不带条件的update,delete了。
扩展
其实不带where条件的update, delete还不够全面。比如where 1=1或者where true,都需要防范。
还有我们甚至可以设置百分比(比如百分之多少的记录被UPDATE,DELETE时,或者超过多少记录被DML后,回退整个事务)
通过PostgreSQL提供的钩子可以完成以上功能。
这个patch的讨论,详见邮件组,本文末尾URL。
PostgreSQL社区的作风非常严谨,一个patch可能在邮件组中讨论几个月甚至几年,根据大家的意见反复的修正,patch合并到master已经非常成熟,所以PostgreSQL的稳定性也是远近闻名的。
参考
https://commitfest.postgresql.org/12/948/
https://www.postgresql.org/message-id/flat/20160721045746.GA25043@fetter.org#20160721045746.GA25043@fetter.org
https://www.postgresql.org/message-id/attachment/45216/training_wheels_001.patch