一、引言

“每看一次PostgreSQL数据库的权限体系，就感觉自己搞懂了，对于PostgreSQL的权限体系控制，我简直强的可怕！但每次实践的时候却发现PostgreSQL的权限的不受控又一次把我按在地上摩擦！”

二、 背景

电科金仓数据库(原“人大金仓数据库”)，是典型的PostgreSQL系数据库，其权限体系和使用方式和PostgreSQL数据库极为相似，所以我以电科金仓数据库为例，通过创建一个只读用户的方式，来帮助理解pg系权限的相关内容，总结一个标准授权流程。

三、数据库权限基本概念

从方便对PostgreSQL数据库权限体系理解的层面划分，我们可以把PostgreSQL数据库的数据库对象分为以下几类：
1）用户；

2）数据库；

3）SCHEMA；

4）OBJECT对象(表、索引、函数、序列等)；

当我们想要对某个对象进行权限控制的时候，我们则必须按层级进行授权，即

1.先获取数据库的连接、使用权限(UC)；

2.获取schema的连接使用权限(UC)；

3.获取具体对象的具体权限(增删改查)；

四、案例-创建数据库用户及只读用户

这里通过一个标准案例来帮助我们对PostgreSQL授权过程进行了解。

场景及目标：

1.创建一个wxdb的数据库；

2.为wxdb数据库创建一个wxtest的schema；

3.创建个wx用户，该用户为wxdb和wxtest的所属用户；

4.创建一个readonly用户，该用户能够对wxdb的wx用户的wxtest的schema中所有表只读权限。同时，通过wx在test的schema下新建的表被继承只读权限。

01. 创建wxdb数据库

--连接数据库
ksql test system;
--创建wxdb数据库
create database wxdb;

02. 回收public对wxdb的相关权限

--该动作主要是为了保证wxdb的安全，避免其他用户可以通过public的schema连接wxdb
revoke all on DATABASE wxdb FROM PUBLIC ;
(revoke all on SCHEMA public FROM PUBLIC;)

03. 创建wxtest的schema

--使用system用户连接wxdb
\c wxdb
--创建wxtest的schema
create schema wxtest;

04. 创建wx的用户并允许wx连接wxdb,并将wxtest的连接和使用schema权限赋予wx

--创建wx用户
create user wx password 'Wx_123456#';
--为wx赋予wxdb数据库的连接权限
grant connect on database wxdb TO wx;
--查看数据库信息及权限
\l+
 wxdb      | system | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | system=CTc/system   +| 14 MB   | sys_default | 

           |        |          |             |             | wx=c/system         +|         |             | 

           |        |          |             |             | readonly=c/system    |         |             | 
 
--赋予wxtest的所有权限给用户wx
grant all on SCHEMA wxtest TO wx;
--查看wxtest的权限信息
\dn+
 wxtest           | system | system=UC/system +| 

                  |        | wx=UC/system     +| 

05.创建readonly用户

create user readonly password 'Readonly_123456#';

06.为readonly用户赋予wxdb的连接权限

grant usage on database wxdb to readonly;
--查看wxtest的权限信息
 \dn+
 wxtest           | system | system=UC/system +| 

                  |        | wx=UC/system     +| 

                  |        | readonly=U/system | 

07.通过wx在wxtest下创建test表：

\c wx wxdb
--设置schema
set search_path=wxtest;
--创建test表
create table test(id number);

08.在readonly用户中查询该表

\c wxdb readonly
set search_path=wxtest;
--查看表
\d
             关联列表

 架构模式 | 名称 |  类型  | 拥有者 

----------+------+--------+--------
 wxtest   | test | 数据表 | wx

--查询该表
select count(*) from test;
ERROR:  permission denied for table test

可以发现，此时没有test表的查询权限。

09.为readonly赋予所有表的查询权限：

\c wxdb wx
--给readonly用户赋予wxtest下表的选择权限
grant SELECT ON all tables in schema wxtest TO readonly;

10.再次对readonly用户中查询该表

\c wxdb readonly
set search_path=wxtest;
\d
             关联列表

 架构模式 | 名称 |  类型  | 拥有者 
----------+------+--------+--------
 wxtest   | test | 数据表 | wx 

select count(*) from test;
 count 

-------

     1

(1 行记录)

此时，可以看到，我们已经可以查询test表了。

11.我们再在wxdb中通过wx创建一张新表

\c wxdb wx
create table test1(id number);
--查看wxdb下的表
\d
              关联列表

 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx

12.在readonly用户中查询该表

\c wxdb readonly
您现在以用户名"readonly"连接到数据库"wxdb"。
set search_path=wxtest;
--查看wxdb下的表
\d
              关联列表

 架构模式 | 名称  |  类型  | 拥有者 

----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
(2 行记录)
wxdb=> select count(*) from test1;
ERROR:  permission denied for table test1

此时，可以发现，我们新创建的test1没有继承读取权限。如果我们想让readonly查看test1表，则需要再给readonly赋予一次select all tables权限（或者单独这张表的权限）。

\c wxdb wx
set search_path=wxtest;
--赋予权限
grant select ON all TABLES IN SCHEMA wxtest to readonly;
(grant select on TABLE wxtest.test1 TO readonly;)
--切换用户
\c wxdb readonly
wxdb=> set search_path=wxtest;
SET
wxdb=> \d
              关联列表
 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
(2 行记录)

wxdb=> select count(*) from test1;
 count 
-------
     0

(1 行记录)

为了方便整体的授权，我们希望在新创建表后，readonly用户也可以有读取的权限。因此，可以通过alter default privileges的方式来设置全局的权限给指定用户。那么，我们模拟一下授权操作（这里我们分别用管理员和数据库所属所属用户的两种方式来）。

方式一：管理员用户赋权

--通过system管理员连接数据库
ksql wxdb system
--进行授权
alter default privileges in schema wxtest  grant select on tables to readonly;
--切换所属用户
\c wxdb wx
--创建表
create table test2(id number);
--查看表
wxdb=> \d
              关联列表

 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
 wxtest   | test2 | 数据表 | wx

--切换用户
\c wxdb readonly
set search_path=wxtest;
wxdb=> \d
              关联列表
 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
 wxtest   | test2 | 数据表 | wx
(3 行记录)

--查询表
wxdb=> select * from test2;
ERROR:  permission denied for table test2

此时可以发现，我们依然没有新建表的读取权限，也就是说alter default privilege的授权没有生效！

那么我们试一下方式二：

--通过wx用户连接wxdb数据库
\c wxdb wx
set search_path=wxtest;
wxdb=> \d
              关联列表
 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
 wxtest   | test2 | 数据表 | wx
(3 行记录)
--进行授权
alter default privileges in schema wxtest  grant select on tables to readonly;
--创建表
create table test3(id number);
--查看表
wxdb=> \d
              关联列表
 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
 wxtest   | test2 | 数据表 | wx
 wxtest   | test3 | 数据表 | wx
 
--切换用户
\c wxdb readonly
set search_path=wxtest;
wxdb=> \d
              关联列表
 架构模式 | 名称  |  类型  | 拥有者 
----------+-------+--------+--------
 wxtest   | test  | 数据表 | wx
 wxtest   | test1 | 数据表 | wx
 wxtest   | test2 | 数据表 | wx
 wxtest   | test3 | 数据表 | wx
(4 行记录)

--查询表
select * from test3;
 id 
----
(0 行记录)

此时，发现新建表test3可以正常查询。

也就是说，当我们需要对数据库中的对象实现权限继承的时候，我们必须要通过该库及相应的schema的所有权限用户赋权才能生效，而直接用system这样的管理权限则无法生效。

四、 归纳总结

通过上述的案例，我们可以对金仓数据库(PostgreSQL系数据库)的授权操作做出以下流程归纳，作为后续的标准权限分配流程，从而达到最小化权限控制：