作者
digoal
日期
2021-04-06
标签
PostgreSQL , pg_read_all_data , pg_write_all_data , 只读
背景
《PostgreSQL MySQL 兼容性之 - 读写用户的只读影子用户 - 默认读权限》
如果你希望给数据库创建一个只读账号, 可以读所有数据, 现在有一个角色可用: pg_read_all_data (SELECT rights on all tables, views and
sequences, and USAGE rights on all schemas)
如果你希望给数据库创建一个写账号, 可以写所有数据, 现在有一个角色可用: pg_write_all_data (INSERT, UPDATE and DELETE rights on all tables,
views and sequences.) . 注意, 如果使用了RLS行安全策略, 有些受RLS策略保护的数据可能依旧无法写入, 但是赋予了BYPASSRLS权限后, 就能随意写入了.
这两个角色大幅度减化了一些场景的权限操作.
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6c3ffd697e2242f5497ea4b40fffc8f6f922ff60
```
Add pg_read_all_data and pg_write_all_data roles
author Stephen Frost sfrost@snowman.net
Mon, 5 Apr 2021 17:42:52 +0000 (13:42 -0400)
committer Stephen Frost sfrost@snowman.net
Mon, 5 Apr 2021 17:42:52 +0000 (13:42 -0400)
commit 6c3ffd697e2242f5497ea4b40fffc8f6f922ff60
tree 7045f2fb4cbeb7fcbc9492ed8e21a99bf25b37cd tree
parent ad8b674922eb70dc5cd02951dd82fe2c4c37c80a commit | diff
Add pg_read_all_data and pg_write_all_data roles
A commonly requested use-case is to have a role who can run an
unfettered pg_dump without having to explicitly GRANT that user access
to all tables, schemas, et al, without that role being a superuser.
This address that by adding a "pg_read_all_data" role which implicitly
gives any member of this role SELECT rights on all tables, views and
sequences, and USAGE rights on all schemas.
As there may be cases where it's also useful to have a role who has
write access to all objects, pg_write_all_data is also introduced and
gives users implicit INSERT, UPDATE and DELETE rights on all tables,
views and sequences.
These roles can not be logged into directly but instead should be
GRANT'd to a role which is able to log in. As noted in the
documentation, if RLS is being used then an administrator may (or may
not) wish to set BYPASSRLS on the login role which these predefined
roles are GRANT'd to.
Reviewed-by: Georgios Kokolatos
Discussion: https://postgr.es/m/20200828003023.GU29590@tamriel.snowman.net
```
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
