1、前言介绍

PostgreSQL中的用户权限，一旦配合上schema，role，database各级别的对象，很容易就把用户弄得晕乎乎的，很多人反映，设置完以后，用户的权限跟自己想像的完全不一样。看来使用起来，还是有很多需要注意的事项。

2、public schema与public role

PostgreSQL默认情况下就有一个public schema和PUBLIC的内置的role。要知道这个PUBLIC的role好像可以代表所有的role。也就是说所有的user都可以在public schema下边干很多越权的事，从安全角度来讲，并不被提倡。

2.1、极简方式使用

为了说明问题，我们先来看看一个极简单的单user, 单schema，它适用于大多数中小型系统：

create database sampledb;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE sampledb FROM PUBLIC;

create user sampleuser with encrypted password 'test123';
grant all privileges on database sampledb to sampleuser;

\c sampledb sampleuser
-- create schema the same name with the main user
create schema sampleuser;

这个主体思想就是，public的schema及role全干掉，针对目标库。只有owner: sampleuser或者超级用户才可以访问当前的schema下的内容。比较干净。因为默认搜索路径是：

sampledb=> show search_path;
   search_path
-----------------
 "$user", public
(1 row)

$user即schema，直接就定位到当前user创建的对象。

2.2、发现一些问题

当我们尝试加上另一个role，并赋予只读的权限的时候，极易出现问题。这里给一个例子：注意，第13行，我们把它注释掉了于是问题出来了，发现基本上没法用。尽管后边看起来也好像把DEFAULT PRIVILEGES in schema的相关权限都grant了。不工作。

 1create database sampledb;
 2\c sampledb
 3REVOKE CREATE ON SCHEMA public FROM PUBLIC;
 4REVOKE ALL ON DATABASE sampledb FROM PUBLIC;
 5
 6create user sampleuser with encrypted password 'test123';
 7grant all privileges on database sampledb to sampleuser;
 8
 9-- create readonly user 
10create user sampleru with encrypted password 'test123';
11GRANT CONNECT ON DATABASE sampledb TO sampleuser,sampleru;
12
13-- \c sampledb sampleuser
14-- create schema the same name with the main user
15create schema sampleuser;
16
17--grant  usage
18grant USAGE on schema sampleuser  to sampleru ;
19
20--select permission 
21grant SELECT on all tables in schema sampleuser  to sampleru;
22
23--grant select for future table.
24ALTER DEFAULT PRIVILEGES IN schema sampleuser  grant select on tables to sampleru;

下边的表说的很仔细：(下边的图表摘自引用的原文)

十分清晰，修改某个具体用户后续创建的数据库对象的默认权限，如果不指定 for 子句，则会更改当前用户创建的对象的默认权限。如果默认不指定FOR ROLE，那意味着，这个grant的default全部都是由当前的user/role: postgres创建的对象传过去的。所以，后边的行为全是乱的。我们本来希望是由用户sampleuser创建的对象，只能被另外的只读用户去读。

加上第13行，那个grant行为就对了。让那个default schema权限是在role：sampleuser下边进行ALTER。

2.3、再回到AWS推荐的那套权限模板

 1\c postgres
 2CREATE DATABASE mydatabase;
 3
 4\c mydatabase
 5
 6CREATE SCHEMA myschema;
 7-- Revoke privileges from 'public' role
 8REVOKE CREATE ON SCHEMA public FROM PUBLIC;   
 9---回收所有人在public模式下的创建权限，为了防止后面还有人继续创表
10REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;  
11---回收所有人在数据库下面的所有权限
12
13-- Read-only role
14CREATE ROLE readonly;        
15---新建只读用户readonly
16GRANT CONNECT ON DATABASE mydatabase TO readonly; 
17---允许只读用户readonly连接数据库
18GRANT USAGE ON SCHEMA myschema TO readonly;   
19---允许只读用户readonly使用模式myschema
20GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO readonly; 
21---赋予所有myschema模式下的表的查询权限
22ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO readonly;  
23---赋予所有myschema模式下未来新建的表的只读权限
24
25-- Read/write role
26CREATE ROLE readwrite;        
27---新建读写用户readwrite
28GRANT CONNECT ON DATABASE mydatabase TO readwrite; 
29---允许读写用户readwrite连接数据库
30GRANT USAGE, CREATE ON SCHEMA myschema TO readwrite;
31---允许读写用户readwrite使用模式myschema，并且允许在myschema下面创建对象
32GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myschema TO readwrite;
33---赋予所有myschema模式下的表的增删改查权限
34ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
35---赋予所有myschema模式下未来新建的表的增删改查权限
36GRANT USAGE ON ALL SEQUENCES IN SCHEMA myschema TO readwrite;
37---赋予所有myschema模式下的序列的所有权限
38ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT USAGE ON SEQUENCES TO readwrite;
39---赋予所有myschema模式下未来新建的序列的所有权限
40
41-- Users creation
42CREATE USER reporting_user1 WITH PASSWORD 'test123';
43---创建可以读所有表的用户reporting_user1
44CREATE USER reporting_user2 WITH PASSWORD 'test123';
45---创建可以读所有表的用户reporting_user2
46CREATE USER app_user1 WITH PASSWORD 'test123';
47---创建可以读写所有表的用户app_user1
48CREATE USER app_user2 WITH PASSWORD 'test123';
49---创建可以读写所有表的用户app_user2
50
51-- Grant privileges to users
52GRANT readonly TO reporting_user1;
53---赋予只读角色给只读用户reporting_user1
54GRANT readonly TO reporting_user2;
55---赋予只读角色给只读用户reporting_user2
56GRANT readwrite TO app_user1;
57---赋予读写角色给读写用户app_user1
58GRANT readwrite TO app_user2;
59---赋予读写角色给读写用户app_user2

第1到6行，我把database和schema的创建给补上了。这样执行完以后，

[19:10:49-postgres@centos1:/var/lib/pgsql/script]$ psql mydatabase app_user1
psql (14.7)
Type "help" for help.

mydatabase=> create table myschema.t(id int);
CREATE TABLE
mydatabase=> \c - app_user2
You are now connected to database "mydatabase" as user "app_user2".
mydatabase=> select * from myschema.t;
ERROR:  permission denied for table t

你会发现新建的表，基本不好使。作为补充，我们要为各个role作一次default privileges的变更。补上下边几句：

\c mydatabase app_user1
alter default privileges for role app_user1 grant select,update,delete,insert on tables to readwrite;
alter default privileges for role app_user1 grant usage on sequences to readwrite;

alter default privileges for role app_user1 grant select on tables to readonly;

\c mydatabase app_user2
alter default privileges for role app_user2 grant select,update,delete,insert on tables to readwrite;
alter default privileges for role app_user2 grant usage on sequences to readwrite;

alter default privileges for role app_user2 grant select on tables to readonly;

后边再建的表，权限就全都使用上了。这是因为"for role" 威力，否则就还是基于role: postgres进行更改，作用不到上边去。

2.4、清除所有相关用户和table

如果要清除，不妨走些险招，直接drop schema cascade，那么相关的表什么的全都干掉，再删用户什么的，轻而易举，但是要注意实际的环境是不是需要这么做。引文里头提到的那个：https://github.com/cybertec-postgresql/drop_role_helper 插件应该是一个好帮手。

3、进一步思考

我认为，之所以，出现这么多容易让人混乱的使用在里边，就是schema和user在层级和概念上，非常容易把人带入一个灰色地带。user/role是一个全局的东西，而schema就挂在database下边。但是呢，A建的schema和B建的schema，在赋权方面，换一个role去建，再去改动基于schema的权限，就发现容易出乱子了。

如果比较理想的话， pg虽然不能跨库访问，但是能不能共享基于某一个schema赋予的权限呢？那样估计就能简化不少工作了。

说到底，两种权限的实现模式，我认为都可以用。极简模式，就是用户即schema，再加用户，我单独赋权就可以。AWS模式，别忘了最后为"FOR ROLE"去补一刀就行。缺了那一刀，还真的不灵。

END

参考：

1) https://mp.weixin.qq.com/s/bsFGLcx-xd9t8ufhTP-pXg (再唠唠晕乎的权限体系)

2) https://mp.weixin.qq.com/s/jQP36rXZb4sgA71AaIJ-Sw (又被权限搞晕了？拿捏)
3)https://www.cockroachlabs.com/docs/stable/alter-default-privileges.html#required-privileges

4) Managing PostgreSQL users and roles

https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

ITPub博客

PostgreSQL权限的使用，小心再被拿捏

1、前言介绍

最新文章