作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验，
Oracle、PostgreSQL ACE
博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、
高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理
公众号：IT邦德
文章目录
- 前言
- 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.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 customer1.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。
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;
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. 需要针对数据表的特定列做访问控制，实现针对用户的列级别的访问控制。
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;
3.PG的三权分立
默认情况下拥有Superuser属性的系统管理员，具备系统最高权限。但在实际业务管理中，为了避免系统管理员拥有过度集中的权利带来高风险，可以设置三权分立。
三权分立的基本思想是将系统中关键操作的权限分配给不同的角色，
来避免单个用户或角色能够完全控制整个系统，
这通常包括以下三个角色：
1.数据库管理员（DBA）：
负责数据库运维管理任务、如创建用户、
表空间、schema管理等。
2.安全管理员（SA）：
负责数据库安全策略的制定和实施，
包括用户权限分配和审计等。
3.应用管理员（AA）：
负责数据库应用的开发和维护，
包括表结构设计、查询优化和数据加载等。
4.总结
PG的权限对于初学者确实很绕，但也是由于PG的权限的规范管理，使得PG的安全级别控制的非常的规范，后期详细的PG权限分享请大家多看我的直播！
