从小白到专家 PG技术大讲堂 - Part 8：PG对象权限管理

北京CUUG 2023-02-16

250

Part 8：PG对象权限管理

内容1	PostgreSQL 对象权限概述
内容2	PostgreSQL 对象权限授权
内容3	PostgreSQL 查看对象权限
内容4	PostgreSQL 对象权限回收

8.1、对象权限概述

对象权限概述

Ø 每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限

Ø 在数据库中所有的权限都和角色挂钩

Ø 对超级用户postgres不做权限检查，其它用户走ACL(Access ControL List)

Ø 对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走ACL

对象级别的权限

Ø 表级对象权限控制

Ø 列级别权限控制

Ø 序列权限控制

Ø 类型域的权限控制(域简单来说就是自定义的带约束的数据类型)

Ø FDW权限控制

Ø FS权限控制

Ø 函数权限控制

Ø \h GRANT显示所有可设置的访问权限

对象权限列表

rolename=xxxx -- privileges granted to a role
  =xxxx -- privileges granted to PUBLIC  
    r -- SELECT ("read")
    w -- UPDATE ("write") 
    a -- INSERT ("append") 
    d -- DELETE
    D -- TRUNCATE
    x -- REFERENCES 
    t -- TRIGGER 
    X -- EXECUTE
    U -- USAGE 
    C -- CREATE 
    c -- CONNECT
    T -- TEMPORARY
    arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)  
    * -- grant option for preceding privilege
    /yyyy --role that granted this privilege

对象权限含义

Ø SELECT：允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。也允许使用COPY TO。在 UPDATE或DELETE中引用现有列值也需要此权限。对于序列，此权限还允许使用currval函数。对于大对象，此权限允许读取对象。

Ø INSERT：允许将新行INSERT到指定的表中。如果列出了特定列，则只能在INSERT命令中为这些列分配（因此其他列将接收默认值）。也允许COPY FROM。

Ø UPDATE：允许更新指定表的任何列或列出的特定列，需要SELECT权限。

Ø DELETE：允许删除指定表中的行，需要SELECT权限。

Ø TRUNCATE：允许在指定的表上截断数据。

Ø REFERENCES：允许创建引用指定表或表的指定列的外键约束。

Ø TRIGGER：允许在指定的表上创建触发器。

Ø CREATE：对于数据库，允许在数据库中创建新的schema、table、index。

Ø CONNECT：允许用户连接到指定的数据库。在连接启动时检查此权限。

Ø TEMPORARY、TEMP：允许在使用指定数据库时创建临时表。

Ø EXECUTE：允许使用指定的函数或过程以及在函数。

Ø USAGE：对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。

Ø ALL PRIVILEGES：一次授予所有可用权限。

8.2、对象权限授权

对象权限授权

Ø 每种类型的对象权限都不一样，详细可参考：

https://www.postgresql.org/docs/9.6/static/sql-grant.html

Ø 基本语法参考（表对象）：

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }  [, ...] | ALL [ PRIVILEGES ] }
  ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] }
  TO role_specification [, ...] [ WITH GRANT OPTION ]

授权示例

Ø 授权单个权限给用户

GRANT SELECT ON tab_name TO role_name;

Ø 授权多个/所有权限给用户

GRANT SELECT, UPDATE, INSERT ON tab_name TO role_name; 
GRANT ALL ON tab_name TO role_name;

Ø 授权某模式下所有表的查询权限给用户

GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO role_name;

Ø 授权列权限给用户

GRANT SELECT (col1), UPDATE (col1) ON tab_name TO role_name;

Ø 授权所有权限给所有用户

GRANT ALL ON tab_name TO public;

8.3、查看对象权限

查看对象权限

Ø 查看对象权限数据字典表

 information_schema.table_privileges

Ø 显示对象的访问权限列表

 \z或\dp [tablename]

查看对象权限示例

Ø 查看对象权限数据字典表

select grantor,grantee,privilege_type,is_grantable
  from information_schema.table_privileges
  where table_name='t1';

查看对象权限示例

Ø 显示对象的访问权限列表

    \z或\dp [tablename]

8.4、对象权限回收

回收示例

Ø 回收单个权限

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;

回收示例（特例）

Ø 任何用户对public模式都有all的权限，为了安全可以禁止用户对public 模式的create权限。

REVOKE CREATE ON SCHEMA public FROM public;

Ø 属主可以取消自己在指定表上的某些权限

REVOKE UPDAE ON tab_name FROM role_name;
REVOKE ALL ON tab_name FROM role_name;

Ø 属主可以授权自己在指定表上的某些权限

GRANT ALL ON tab_name TO role_name;

以上就是Part 8 - PG对象权限管理的内容，欢迎进群一起探讨交流

QQ交流群：752027153

微信交流群：联系客服拉你进微信PG交流群

钉钉交流群：35822460，本周五晚上8点会在钉钉群专门讲解

从小白到专家 PG技术大讲堂 - Part 8：PG对象权限管理

评论