Postgresql 权限也能搞死你 之 小菜的一天 (2)

PostgreSQL的权限问题估计没有多少人会关注, 小菜经过上次的教训后,又找了一份工作,今天又是第一天上班,不过小菜到底有没有吸收了上次的教训. follow him

第一天上班，小菜被分配主管数据库的权限，小菜挺高兴，因为至少不会容易出错误导致大故障而造成上次的事情。

开发的小胡找到小菜，说哎，我的那个测试库需要一个权限，你可别给我太大，能查个数，改个数就行，因为现在的程序是上个离职的给我的，并且这个账号也不是我一个人用。小菜答应没有问题。

一会小胡就过来了，问怎么几个表都没有了，问小菜权限你怎么付的，小菜一脸无辜，我就给你了owner的权限，小胡气得脸都青了。“我问问你” owner 到底都有什么权限，你就瞎给。该有的都有了，你就是最大的权限呗，小菜答到

小胡一会就找到“老鸟“ 评理， 老鸟安抚了小胡，后问小菜，你知道owner 到底都有什么权限？小菜还是嘴硬，该有的都有了，都是最大的权限。

老鸟问小菜，你知道owner 属于一个小型的 superuser 吗？

我来演示给你看

select rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb from pg_roles;

从上图看 小胡是没有创建和SUPER的角色的，下面小胡在dvdrental 中创建了表并且可以自己对这个库中object 进行各种的权限赋予和revoke 操作。

你觉得你给他owner 这个权限合适吗？这个账号还是多个人使用，即使小胡做了一些权限的设置，其他人在知道密码的情况下，还是可以将这些设置都取消掉。

那你说他到底什么意思，表丢了还怪我了，小菜还是愤愤不平的说，那你怎么给他这个权限。

老鸟还是耐心的回答，

1 你应该只给 insert ,update, select 的权限

2 给与权限宁可少，别多，可以一点点的调

你看这样操作合适不合适,通过另一个管理员账户操作

revoke all on schema public from xiaohu;

Drop user xiaohu;

先把小胡的账户删掉，避免一些可能无法控制的问题

然后我们可以建立role 而不是 直接将权限赋予用户，这样会有很多好处。

1 我们建立一个只读 role

2 我们建立一个读写的 role

create role readonly;

alter role readonly set default_transaction_read_only=on;

grant usage on schema public to readonly;

grant select on all tables in schema public to readonly;

grant readonly to xiaohu;

alter default privileges in schema public grant select on sequences to readonly;

REVOKE ALL ON schema public FROM public;

在赋予后，目前小胡的账户，只能查看

然后我们在照方抓药，创建write 的role

create role write;

grant usage on schema public to write;

grant update,insert on all tables in schema public to write;

grant write to xiaohu;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO write;

在做完上面的操作后，目前的权限已经能满足小胡的需求了。

小菜找到小胡交差，上午总算是度过了，没想到下午又出事了。小菜被开发小胡又告到老鸟这里，小胡气愤的说，你看看让他给我建个表，我连查询和插入都不行，这都怎么回事，小菜一脸无辜的，我不就在你指定的地方建立表，上午你都有权限了，咋还不行，是不是你客户端有问题。

老鸟不大高兴的问小菜，我上午让你去研究一下权限，就想让你自己能解决这个问题，小菜不高兴的说，其实我在看高可用，权限这事没啥技术含量。

老鸟没搭理他，直接敲了几条命令，然后告诉小胡，回去试试

到底老鸟做了什么，就三条命令

ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA public GRANT ALL privileges ON sequences TO write;

ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA public GRANT update,insert ON TABLES TO write;

ALTER DEFAULT PRIVILEGES FOR USER postgres IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

具体的意思是，由于小胡的权限不能建表，所以只能让小菜给建表，而建表的账户和小胡的必然不一样，这就导致建完的表根本没有 read 和 write 的role 在新表上，所以上面的三条命令是让以后小菜用postgres 账户建表时带上write 和 readonly 账户

下午需要上线一个业务，老鸟对小菜交代，晚上的脚本已经写好，你就知晓一下脚本，有问题给我打电话。

到了晚上上线的时候，开发总监找过来和运维总监说了几句，就走了，运维总监过来问老鸟，说你们怎么回事，权限这么简单的事情也搞错，看看哪里有问题，别耽误上线，听说上午新来的和开发弄的不大愉快，你盯着点，看看行不行。

老鸟过去问了小菜，在赋予权限后试过没有，小菜说，我怎么试，我要用生产的用户名和密码来登录，我还有记录，回来在做错点什么又到我头上。

老鸟说，你怎么回事，这是理由，在说就算你不用，或不知道对方的账号密码登录，你也可以在你账号下，来进行测试呀

利用 reset role 和 set role 不就可以在你登录权限的基础上可以测试任何你建立的role

另外还可以通过下面的语句来看你当前的账号和你使用权限的账号是否不一样。

SELECT SESSION_USER, CURRENT_USER;

小菜说知道了，太啰嗦了，我正研究 POSTGRESQL 原理呢

转天，早上小菜早上起床要上班的时候，接到了电话，原来是单位HR 打来的，说由于一些工作的原因，感觉小菜不大适合，决定解除和小菜的劳动关系。

小菜愤怒的到了公司，心想一定是老鸟给自己上的眼药，准备讨个公道，站在电梯最后面，上班的人群乌央乌央的就挤进来。

在电梯里面就听见运维同事议论，说昨天来的DBA 太不靠谱，昨天的系统更新不大顺利都是因为他。

主要是升级一般都是DBA来和运维的一起来跟，其实只是让小菜执行一条命令就可以了，其他的都是运维的活，小菜就想一条命令也要我等到晚上12点，我就告诉运维帮我一下就行了。小菜将当天需要升级的数据库脚本放到了root 目录下，然后告诉运维执行就行了。

下图就是运维执行完的后果。

本周三更新 谁说postgresql 没有靠谱的高可用（3）