行业知识
PostgreSQL（七）权限管理
目录
一、PG的权限说明
二、权限管理层次
三、对象权限概述及权限列表
1、权限类别概述
2、对象权限列表与含义
四、权限管理（语法）
1、授权
2、回收权限
3、易主
五、小实验
1、实验内容
2、实验过程
3、总结
一、PG的权限说明
1、每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限；
对象所有者可以自己回收、重授权自己的权限，用于防止误操作；
2、在数据库中，所有的权限都和角色挂钩；
3、对超级用户 postgres不做权限检查，其它用户走ACL（Access Control list）；
4、对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL；
二、权限管理层次
1、实例权限：控制部分用户、主机是否允许登录、访问数据库（白名单&黑名单）；
2、schema：PG的一个用户可以有多个schema，但一个schema只能属于一个用户；
3、object：表、视图、索引等对象的增删改查的权限
三、对象权限概述及权限列表
1、权限类别概述
表级对象权限控制；
列级别权限控制；
序列权限控制；
类型域的权限控制（域简单来说就是自定义的带约束的数据类型）；
FDW权限控制；
FS权限控制；
函数权限控制；
\ h grant显示所有可设置的访问权限；
2、对象权限列表与含义
rolename = xxxx --privileges granted to a role = XXXX --privileges granted to PUBLIC r --SELECT ("read")(允许从指定表,视图或序列的任何列或列出的特定列进行 SELECT。允许用 COPY TO。允许在UPDATE或 DELETE中引用现有列值。 对于序列,此权限还允许使用currval函数。对于大对象,此权限允许读取对象) W --UPDATE ("write")(允许将新行INSERT到指定的表中。如果列出了特定列,则只能在INSERT命令中为这些列分配(因此其他列将接收默认值)。也允许 COPY FROM。) a --INSERT ("append") (允许更新指定表的任何列或列出的特定列,依赖SELECT权限。) d --DELETE (允许删除指定表中的行,依赖SELECT权限) D --TRUNCATE(允许在指定的表上截断数据) X --REFERENCES(允许创建引用指定表或表的指定列的外键约束) t --TRIGGER(允许在指定的表上创建触发器) X --EXECUTEU (允许使用指定的函数或过程以及在函数) U --USAGE(对于schema,允许访问指定模式中包含的对象;对于sequence,允许使用currval和nextval函数。对于类型和域,允许在创建表、函数和其他模式对象时使用类型或域) C --CREATE(对于数据库,允许在数据库中创建新的schema、table、 index) c --CONNECT(允许用户连接到指定的数据库。在连接启动时检查此权限) T --TEMPORARY(TEMP,允许在使用指定数据库时创建临时表)arwddxt --ALL PRIVILEGES (for tables,varies for other objects * --grant option for preceding privilege(一次授予所有可用权限) /yyyy --role that granted this privilege 四、权限管理（语法）
1、授权
详细语法可在PG中执行 \h grant 查看；
简易语法与sql示例如下：
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,…] | ALL [ PRIVILEGES ] }ON DATABASE $database_name [,…]TO $role_specification [,…] [ WITH GRANT OPTION ]e.g.://授权用户wqdb在数据库wqdb上的一切create权限：GRANT CREATE ON DATABASE wqdb TO wqdb;//授权访问模式的权限给用户GRANT USAGE ON SCHEMA $sch_name TO $role_name;//授权指定模式下对象的访问权限给用户GRANT SELECT ON $sch_name.$tab_name TO $role_name; 2、回收权限
详细语法可在PG中执行 \h revoke 查看；
简易语法与sql示例如下：
//回收单个权限REVOKE SELECT ON $tab_name FROM $role_name//回收多个/所有权限REVOKE SELECT,UPDATE,INSERT ON $tab_name FROM $role_name;REVOKE ALL ON $tab_name FROM $role_name;//回收某模式下所有表的查询权限REVOKE SELECT ON ALL TABLES IN SCHEMA $schema_name FROM $role_name;//收列权限REVOKE SELECT(col1),UPDATE(col1) ON $tab_name FROM $role_name;//回收所有权限REVOKE ALL ON $tab_name FROM public;
3、易主
（1）转移单个表的属主（超级用户、属主可操作，原属主必须是被授权用户的成员）
ALTER TABLE $tab_name OWNER TO $new_owner_name;
（2）转移当前数据库表的属主（超级用户可操作，如果属主是postgres则不允许，因为其中包含数据字典表，只能用单表易主的方式修改）
REASSIGN OWNED BY $old_role_name TO $new_role_name;
五、小实验
1、实验内容
执行SQL--GRANT select ON all tables IN SCHEMA $sch_name TO $role_name;
授权某模式下所有表的查询权限给某个用户。
授权成功后再新建表，此用户能否拥有查看后建的表数据的权限？
答案：不能。
2、实验过程
//1、打开窗口1，超级用户登录，创建实验用户su - postgrespsqlpostgres=# select user; user ---------- postgres(1 行记录)postgres=# create user u1 password 'u1';CREATE ROLEpostgres=# grant create on database wqdb to u1;GRANTpostgres=# create user u2 password 'u2';CREATE ROLEpostgres=# \du 角色列表 角色名称 | 属性 | 成员属于 -------------+--------------------------------------------+------------ dbcreate | 建立 DB, 无法登录 | {} pgloader_pg | | {} postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {} u1 | | {} u2 | | {}//2、打开窗口2，u1用户登录，创建实验模式u1test与实验表apsql -U u1 -d wqdbwqdb=> create schema u1test;CREATE SCHEMAwqdb=> create table u1test.a(id int);CREATE TABLEwqdb=> insert into u1test.a values (1);INSERT 0 1wqdb=> select * from u1test.a; id ---- 1(1 行记录)//3、打开窗口3，u2用户登录，查询实验表psql -U u2 -d wqdbwqdb=> set search_path="$user", public,u1test;SETwqdb=> \d 关联列表 架构模式 | 名称 | 类型 | 拥有者 ----------+------+--------+-------- u1test | a | 数据表 | u1(1 行记录)wqdb=> select * from u1test.a; id ---- 1(1 行记录)//4、回到窗口2，新建表bwqdb=> create table u1test.b(id int);CREATE TABLEwqdb=> insert into u1test.b values (2);INSERT 0 1wqdb=> select * from u1test.b; id ---- 2(1 行记录)//5、回到窗口3，查看表bwqdb=> \d 关联列表 架构模式 | 名称 | 类型 | 拥有者 ----------+------+--------+-------- u1test | a | 数据表 | u1 u1test | b | 数据表 | u1(2 行记录)wqdb=> select * from u1test.b;错误: 对表 b 权限不够 3、总结
从最后的查询结果来看，授权后再新建的表，使用\d可以看到表存在，但无法查看表数据。
（pg可以实现：授权查看schema下所有表的权限后，也能查看后建的表数据。
具体实现方法等待更新中）
