越来越多的互联网企业在使用postgresql数据库，我们也不例外。
昨天开发请我建立了一个只读用户abc_tmp_test用户，并且将mkl_rw用户下的32个表授权给只读用户用。ok，请简单轻松的一个需求，很快就完成了。
但是今天开发来和我说，昨天授权的几个表中，有部分表还是没有权限去读取，让我帮忙看看。一开始，我以为是昨天遗漏了，先道了一个歉，再次进行了授权，授权完成之后，检查了32个表，都能被只读用户查询，于是放心的告诉开发，昨天的所有表都已经授权好了，我也检查过一次了。这次肯定不会漏了。
万万没想到，半小时后，开发来和我说，不行，还是有其中几个表没有权限。我之前的连接还没断开，再次跑了一遍之前的检查语句，确实没有权限了。卧槽？这是咋回事？数据库中有雷锋了？
我再次授权了一次，并且检查了information_schema.table_privileges，确认了再次授权后，是新增了32行记录。这次我没有先通知开发，说已经授权完成了，而是过了一会，我再次去查，变成了28行，又过了一会，变成了16行！
也就是我授权的32个表的select权限给只读用户，过一段时间之后，这32个表中的一些表的权限会慢慢消失！而且消失权限的表，也没有发现先授权的先消失，后授权的后消息的规律，但是可以发现最终剩下的，就是那16个表。我开始怀疑起人生了……
难道是pg中授权的表的数量有限？不能超过16个？也没查到相关的参数啊。
难道是那16个表有什么特殊设置？从建表语句中也没看到啊。
难道授权之后需要checkpoint刷盘？测试了checkpoint还是一样丢权限。
难道真的有雷锋出现啊。还说什么pg和oracle一样牛，一样稳定，连基本的授权都会丢。
正在逐个检查参数之际，同事通过检查log，发现了drop table的语句……
原来如此，这个案例，可以用下面的测试过程模拟出来了：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
-bash-4.2$ psql -d mybidbaa -U mkl_rw psql (9.6.2) Type "help" for help. mybidbaa=> --创建表bbbdba_test，并且授权给abc_tmp_test用户 mybidbaa=> create table bbbdba_test as select now(); SELECT 1 mybidbaa=> mybidbaa=> mybidbaa=> grant select on mkl_rw.bbbdba_test to abc_tmp_test; GRANT mybidbaa=> mybidbaa=> mybidbaa=> mybidbaa=> mybidbaa=> \q -bash-4.2$ psql -d mybidbaa -U abc_tmp_test psql (9.6.2) Type "help" for help. mybidbaa=> mybidbaa=> --用abc_tmp_test登录，可以查到bbbdba_test表。 mybidbaa=> select * from mkl_rw.bbbdba_test; now ------------------------------- 2017-11-16 16:08:14.123217+08 (1 row) mybidbaa=> \q -bash-4.2$ -bash-4.2$ -bash-4.2$ psql -d mybidbaa -U mkl_rw psql (9.6.2) Type "help" for help. mybidbaa=> --删除表bbbdba_test，然后重建表bbbdba_test。 mybidbaa=> drop table mkl_rw.bbbdba_test; DROP TABLE mybidbaa=> mybidbaa=> mybidbaa=> create table bbbdba_test as select now(); SELECT 1 mybidbaa=> \q -bash-4.2$ -bash-4.2$ -bash-4.2$ -bash-4.2$ psql -d mybidbaa -U abc_tmp_test psql (9.6.2) Type "help" for help. mybidbaa=> --可以看到，权限丢了！！ mybidbaa=> select * from mkl_rw.bbbdba_test; ERROR: permission denied for relation bbbdba_test mybidbaa=> \q -bash-4.2$ -bash-4.2$ -bash-4.2$ -bash-4.2$ |
是的，如果table被drop了之后，再次重建，此时原本授权给只读用户的权限，也会消失。
向开发确认，是否有drop之后重建表的操作，开发确认，有段程序确实会定期的逐个drop表后重建表！！
为什么要进行drop表之后重建表的操作？开发说是通过调用框架清理数据，框架就是这么干的。
ok，明白了目的是为了清理数据，而不涉及到表结构的修改，那么其实用truncate来清理就可以了。如下测试，权限不会丢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
-bash-4.2$ -bash-4.2$ -bash-4.2$ psql -d mybidbaa -U mkl_rw psql (9.6.2) Type "help" for help. mybidbaa=> grant select on mkl_rw.bbbdba_test to abc_tmp_test; GRANT mybidbaa=> truncate table mkl_rw.bbbdba_test; TRUNCATE TABLE mybidbaa=> \q -bash-4.2$ psql -d mybidbaa -U abc_tmp_test psql (9.6.2) Type "help" for help. mybidbaa=> select * from mkl_rw.bbbdba_test; now ----- (0 rows) mybidbaa=> \q -bash-4.2$ |
最终，开发修改了代码，再次授权那32张表之后，权限不再慢慢消失了。
这个故事中可以学到的教训有二：
1. 大千世界无奇不有，数据库中没有雷锋，而是有各种万万没想到的逻辑。
2. 幸亏我们在建库的时候，建库标准要求设置了log_statement=ddl, 才能在log中发现线索。（其实我们oracle和pg的建库标准，都设置了记录ddl）
2条评论
感谢分享
开发大爷们都有一手好逻辑，典型的只干自己那一摊儿。