核心系统用PG了，抠脑壳的权限，搞晕了！

登录后复制

作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、
高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理


公众号：IT邦德

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

文章目录

• 前言

• 1.PG权限架构

• 1.1 层层通关
• 1.2 行级访问
• 1.3 列级访问

• 1.4 各类型对象的权限列表
• 2.PG权限管理

• 2.1 数据库权限
• 2.2 schema权限
• 2.3 表的权限
• 2.4 表空间的权限

• 3.PG的三权分立
• 4.总结

前言

分享一些技能给大家，别让PG的权限再把大家绕进去了！

大家好，最近也是由于系统上线，PG的权限确实绕啊！对于我这个DBA老兵，花了2周的时间对权限进行了梳理，利用这次机会，分享一些技能给大家，别让PG的权限再把大家绕进去了！

1.PG权限架构

1.1 层层通关

1.允许多个用户使用同一数据库而不相互干扰,数据库严格分开的，不允许跨库访问

2.schema可以将数据库对象组织成易于管理的逻辑组，用户根据其对schema的权限，可以访问所连接数据库的schema中的对象

3.表空间是一个目录，仅是起到了物理隔离的作用，可以跨数据库，其管理功能依赖于文件系统。

4.ALL代表所有访问权限，PUBLIC代表所有用户

5.数据库在创建后，会自动创建名为public的schema，这个schema的all权限已经赋予给public角色

6.拥有者(Owner)对相应的数据库对象拥有完全的控制权

如果你想访问一个表，你就锝按照下面的流程层层通关才可以

1.2 行级访问

行级访问控制特性将数据库访问控制精确到数据表行级别，使数据库达到行级访问控制的能力。
不同用户执行相同的SQL查询操作，读取到的结果是不同的。
用户可以在数据表创建行访问控制(Row Level Security)策略

登录后复制

--创建表test，包含不同用户数据信息
postgres=# CREATE TABLE test
(id int, role varchar(100), data varchar(100));

--打开行访问控制策略开关
postgres=# ALTER TABLE test ENABLE ROW LEVEL; 

--创建行访问控制策略，当前用户只能查看用户自身的数据
postgres=# CREATE ROW LEVEL SECURITY POLICY test_rls 
ON test USING(role = CURRENT_USER);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.

1.3 列级访问

在一些业务场景中，某些列存储敏感信息，需要对用户不可见，但其他列的数据又需要用户能够查看或操作，此时就需要针对数据表的特定列做访问控制，实现针对用户的列级别的访问控制。

登录后复制

例：授予用户jack对customer_t1表的
c_customer_sk和c_first_name列的查询权限
GRANT SELECT (customer_sk, first_name) ON customer TO jack;
postgres=# \c - jack
postgres=# select customer_sk, first_name from customer;
 customer_sk | first_name
-------------+--------------
         2345| Grace
(1 row)
postgres=# select * from customer ;
ERROR:  permission denied for table customer

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

1.4 各类型对象的权限列表

2.PG权限管理

2.1 数据库权限

登录后复制

1. CREATE ON DATABASE:  
允许在数据库里创建新的模式
2. CONNECT ON DATABASE:   
允许用户连接到指定的数据库。
3. ALTER ON DATABASE:  
允许用户修改数据库中指定对象的属性，但不包括修改对象的所有者和修改对象所在的模式。
4. DROP ON DATABASE:  
允许用户删除数据库中指定的对象
5. ALL [ PRIVILEGES ] ON DATABASE: 
一次性给指定用户/角色赋予所有可赋予的权限
只有系统管理员有权执行GRANT ALL PRIVILEGES。

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

2.2 schema权限

登录后复制

1. 用户如果要创建模式需要有CREATE ON DATABASE权限
postgres=# grant create on database db_name to jack;
2. 模式属主表示模式的拥有者，拥有模式的所有权限
postgres=# alter schema myschema owner to jack
3. 用户只能访问属于自己的schema中的数据库对象，即使用的权限
postgres=# GRANT USAGE ON schema myschema TO jack;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

2.3 表的权限

登录后复制

1. 创建表需要有CREATE ON SCHEMA的权限
postgres=# GRANT USAGE,CREATE ON schema myschema TO jack;
2. 修改表、查看表首先需要有USAGE的权限
postgres=# GRANT USAGE ON schema myschema TO jack;
postgres=# GRANT SELECT,INSERT,UPDATE,DELETE 
ON ALL TABLES IN SCHEMA myschema TO jack;
3. 需要针对数据表的特定列做访问控制，实现针对用户的列级别的访问控制。

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

2.4 表空间的权限

表空间是一个目录，可以存在多个，里面存储的是它所包含的数据库的各种物理文件。由于表空间是一个目 录，仅是起到了物理隔离的作用，其管理功能依赖于文件系统。

PG自带了两个表空间,即默认表空间pg_default共享表空间pg_global

登录后复制

1. 表空间属主，表示表空间的拥有者
postgres=# CREATE TABLESPACE mytbs 
RELATIVE LOCATION 'tablespace/mytbs';
postgres=# alter tablespace mytbs owner to jack;
2. 在表空间中创建对象,需要以下权限
postgres=# GRANT CREATE ON TABLESPACE mytbs TO jack;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

3.PG的三权分立

默认情况下拥有Superuser属性的系统管理员，具备系统最高权限。但在实际业务管理中，为了避免系统管理员拥有过度集中的权利带来高风险，可以设置三权分立。

三权分立的基本思想是将系统中关键操作的权限分配给不同的角色，
来避免单个用户或角色能够完全控制整个系统，
这通常包括以下三个角色：

登录后复制

1.数据库管理员(DBA)：
负责数据库运维管理任务、如创建用户、
表空间、schema管理等。
2.安全管理员(SA)：
负责数据库安全策略的制定和实施，
包括用户权限分配和审计等。
3.应用管理员(AA)：
负责数据库应用的开发和维护，
包括表结构设计、查询优化和数据加载等。

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

4.总结

PG的权限对于初学者确实很绕，但也是由于PG的权限的规范管理，使得PG的安全级别控制的非常的规范，后期详细的PG权限分享请大家多看我的直播！