PostgreSQL 表空间、数据库、模式、角色和用户之间的关系梳理

作者介绍：老苏，10余年DBA工作运维经验，擅长Oracle、MySQL、PG数据库运维（如安装迁移，性能优化、故障应急处理等）
公众号：老苏畅谈运维
欢迎关注本人公众号，更多精彩与您分享。

一、模式、数据库、用户三者之间关系

模式是数据库领域的一个基本概念，有些数据库把用户和模式合二为一了，而PG中有清晰的模式定义，相对更加灵活自由。用户更加方便。

那么什么是模式呢？模式是数据库中的一个概念，可以将其理解为一个命名空间或目录，不同的模式下可以有相同名称的表、函数等对象而不会产生冲突。提出模式的概念是为了便于管理，只要有权限，各个模式的对象可以互相调用。如果没有模式的概念，那么不同用户或者开发者在创建各自数据库对象命名时可能会发生冲突以及相互干扰等问题，有了模式，就可以避免这些问题。总结起来模式有如下好处：
允许多个用户使用同一个数据库且用户之间不会互相干扰。
把数据库对象放在不同的模式下组织成逻辑组，使数据库对象更加便于管理
第三方应用可以放在不同的模式中，这样就不会和其他对象的名字产生冲突了。

一个数据库包含一个或者多个模式，模式中又包含了表、函数以及操作符等数据库对象。其中的层级关系是：数据库-模式-对象。需要注意的是，虽然能创建多个数据库实例，但不能同时访问不同数据库中的对象，
当需要访问另一个数据库中的表或其他对象时，需要重新连接到这个数据库，而模式却没有此限制，一个用户在连接到一个数据库后，就可以同时访问这个数据库中多个模式的对象。
所以，在使用MySQL或Oracle的用户迁移到Postgres时，需要注意其中的不同之处。

---- 当前用户、当前模式、当前数据库

postgres=> \c postgres
您现在已经连接到数据库 "postgres",用户 "hr".
postgres=>
postgres=> select current_user,current_schema,current_database();
 current_user | current_schema | current_database
--------------+----------------+------------------
 hr           | public         | postgres
(1 行记录)
postgres=> \c hrdb
您现在已经连接到数据库 "hrdb",用户 "hr".
hrdb=> select current_user,current_schema,current_database();
 current_user | current_schema | current_database
--------------+----------------+------------------
 hr           | hr             | hrdb
(1 行记录)

模式搜索路径

hrdb=> show search_path;
   search_path
-----------------
 "$user", public
(1 行记录)

二、表空间介绍

不同的数据库表空间有不同的定义。在postgres中，表空间允许在文件系统中定义数据库对象存储的位置，实质上就是指定一个目录。

应用场景：
存储磁盘没有空间时，可以使用表空间把数据存在其它地方；
利用表空间对数据库进行性能优化。常用来将频繁使用的数据表或者索引放在高性能的硬盘上，而较少使用的放在普通硬盘上。

自带两个表空间：pg_default,pg_global
pg_default 用来存储系统目录对象，用户表、用户表index、临时表、临时表index、内部临时表的默认空间。
pg_global 用来存储系统字典表
pg_tblspc 存储手动创建表空间的软连接信息。初始是空的，当手动增加表空间时，该目录下会自动生成一个软连接，指向表空间设定的路径。

一个表空间可以让多个数据库使用，而一个数据库可以使用多个表空间，属于“多对多”的关系。
Oracle中一个表空间只属于一个数据库，而一个数据库可以使用多个表空间，属于“一对多”的关系。

三、数据库

在postgre数据库中，数据库（database）可以认为是物理层面数据集合。
当使用不同的文字编码/排序规则等管理数据对象时，不同的文字编码规则/排序规则要创建独立的的数据库。Postgre关于文字编码/排序规则等行为的默认规则是以Database为单位的。
当需要对一组数据库表(或函数等)的并发数进行单独控制时，需要对这些表(或函数等)放入一个新建的Database中，postgre对最大并发访问会话进行单独控制的最小单元是Database。
当需要对一组数据库表(或函数等数据库对象)的访问进行严格隔离时（不仅仅是通过SQL层面的priviledge来控制），在postgre中，对于Access的控制，除了SQL级别的权限控制之外，可以在pg_hba.conf配置文件中进行会话级别的认证控制。
会话级别的高精度访问控制可做到：
【1】会话来源（IP地址）；【2】会话所使用的数据库用户；【3】会话连接目标的Database名

四、模式（Schema）

在postgre数据库中，模式（schema）可以认为是逻辑层面数据集合，不同与数据库（database）物理层面的数据集合。Schema是数据库内部用于组织管理数据库对象的逻辑集合，
schema下是各种应用程序会接触到的对象，比如：表、索引、数据类型、函数、操作符等。

schema是逻辑别构，将数据库进行逻辑划分。同一数据库下可以有多个schema，不同数据库下的schema互不相关。

应用场景：

当多个用户想使用同一个数据库，而这些用户之间又不能相互干扰。
当存在多个数据对象时，可以按业务或某种逻辑把对象组织成逻辑组。易管理和维护。
当集成第三方应用时，可以将第三方应用放在不同的模式中，可避免与其它已有数据对象在名字中的冲突。
总结：一般使用，一个用户对应一个schema,该用户的schema名等于用户名，并作为该用户缺省的schema模式权限：
一个新建的postgre数据包含一个叫public的预定义模式。
\dn+ 列出当前数据库的所有的模式和相关的权限。

  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         | 

第一列：当前数据库中模式的名称
第二列：当前数据库中每一个模式的所有者，默认为创建这个模式的角色。
第三列：模式的访问权限。
权限信息的格式为（左到右）：权限接受者、权限、权限授予者。图中第二行第三列的权限信息可以解读为：postgres(权限接受者)=UC(权限)/postgres(权限授予者)。
对模式来说只有两种可以被授予的权限：USAGE和CREATE,对应权限信息中的的U和C。
USAGE权限允许角色查询数据库对象，例如包含在模式中的表和视图。
CREATE权限允许角色在模式中创建数据库对象。
有多个相关权限信息时，列出权限信息时会使用加号来分隔，而且我们注意到public模式的权限中，第二行里接受权限者为空字符串，这代表权限会授予给所有的用户。
新创建的用户默认对所有public模式都有UC权限。上图中我执行了public模式的create权限回收操作：revoke create on schema public from public。所以上图中二行三列“+”号后只有“U”。

五、角色与用户的关系

在PostgreSQL中，存在两个容易混淆的概念：角色/用户。之所以说这两个概念容易混淆，是因为对于PostgreSQL来说，这是完全相同的两个对象。唯一的区别是在创建的时候：
1.我用下面的psql创建了角色kanon：
CREATE ROLE kanon PASSWORD ‘kanon’;
接着我使用新创建的角色kanon登录，PostgreSQL给出拒绝信息：
FATAL： role ‘kanon’ is not permitted to log in.
说明该角色没有登录权限，系统拒绝其登录。

2.我又使用下面的psql创建了用户kanon2:
CREATE USER kanon PASSWORD ‘kanon2’;
接着我使用kanon2登录，登录成功。
难道这两者有区别吗？查看文档，又这么一段说明：“CREATE USER is the same as CREATE ROLE except that it implies LOGIN.”----CREATE USER除了默认具有LOGIN权限之外，其他与CREATE ROLE是完全相同的。
为了验证这句话，修改kanon的权限，增加LOGIN权限：ALTER ROLE kanon LOGIN;再次用kanon登录，成功！
那么，事情就明了了：CREATE ROLE kanon PASSWORD ‘kanon’ LOGIN 等同于CREATE USER kanon PASSWORD ‘kanon’.
这就是ROLE/USER的区别。

六、数据库与模式的关系

看文档了解到：模式（schema）是对数据库（database）逻辑分割。
在数据库创建的同时，就已经默认为数据库创建了一个模式–public，这也是该数据库的默认模式。所有为此数据库创建的对象（表、函数、试图、索引、序列等）都是常见在这个模式中的。
实验如下：
1.创建一个数据库dbtt----CREATE DATABASE dbtt;
2.用kanon角色登录到dbtt数据库,查看dbtt数据库中的所有模式：/dn; 显示结果是只有public一个模式。
3.创建一张测试表----CREATE TABLE test(id integer not null);
4.查看当前数据库的列表： /d; 显示结果是表test属于模式public.也就是test表被默认创建在了public模式中。
5.创建一个新模式kanon，对应于登录用户kanon：CREATE SCHEMA kanon OWNER kanon；
6.再次创建一张test表，这次这张表要指明模式----CREATE TABLE kanon.test (id integer not null);
7.查看当前数据库的列表： /d; 显示结果是表test属于模式kanon.也就是这个test表被创建在了kanon模式中。
得出结论是：数据库是被模式(schema)来切分的，一个数据库至少有一个模式，所有数据库内部的对象(object)是被创建于模式的。
用户登录到系统，连接到一个数据库后，是通过该数据库的search_path来寻找schema的搜索顺序,可以通过命令 SHOW search_path; 具体的顺序，也可以通过SET search_path TO 'schema_name’来修改顺序。

官方建议是这样的：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"，
这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

七、表空间与数据库的关系

数据库创建语句CREATE DATABASE dbname 默认的数据库所有者是当前创建数据库的角色，默认的表空间是系统的默认表空间–pg_default。
为什么是这样的呢？因为在PostgreSQL中，数据的创建是通过克隆数据库模板来实现的，这与SQL SERVER是同样的机制。
由于CREATE DATABASE dbname并没有指明数据库模板，所以系统将默认克隆template1数据库，得到新的数据库dbname。（By default, the new database will be created by cloning the standard system database template1）.

而template1数据库的默认表空间是pg_default，这个表空间是在数据库初始化时创建的，所以所有template1中的对象将被同步克隆到新的数据库中。
相对完整的语法应该是这样的：CREATE DATABASE dbname OWNER kanon TEMPLATE template1 TABLESPACE tablespacename;

下面我们来做个实验验证一下：

1.连接到template1数据库，创建一个表作为标记：CREATE TABLE tbl_flag(id integer not null);向表中插入数据INSERT INTO tbl_flag VALUES (1);
 2.创建一个表空间:CREATE TABLESPACE tskanon OWNER kanon LOCATION '/tmp/data/tskanon';在此之前应该确保目录/tmp/data/tskanon存在，并且目录为空。
 3.创建一个数据库，指明该数据库的表空间是刚刚创建的tskanon：CREATE DATABASE dbkanon TEMPLATE template1 OWNERE kanon TABLESPACE tskanon;
 4.查看系统中所有数据库的信息：/l；可以发现，dbkanon数据库的表空间是tskanon,拥有者是kanon;
 5.连接到dbkanon数据库，查看所有表结构:/d;可以发现，在刚创建的数据库中居然有了一个表tbl_flag,查看该表数据，输出结果一行一列，其值为1，说明，该数据库的确是从template1克隆而来。

仔细分析后，不难得出结论：在PostgreSQL中，表空间是一个目录，里面存储的是它所包含的数据库的各种物理文件。

八、总结一下这张关系网

表空间是一个存储区域，在一个表空间中可以存储多个数据库，尽管PostgreSQL不建议这么做，但我们这么做完全可行。
一个数据库并不知直接存储表结构等对象的，而是在数据库中逻辑创建了至少一个模式，在模式中创建了表等对象，将不同的模式指派该不同的角色，可以实现权限分离，又可以通过授权，实现模式间对象的共享，并且，还有一个特点就是：public模式可以存储大家都需要访问的对象。
这样，我们的网就形成了。可是，既然一个表在创建的时候可以指定表空间，那么，是否可以给一个表指定它所在的数据库表空间之外的表空间呢？
答案是肯定的!这么做完全可以：那这不是违背了表属于模式，而模式属于数据库，数据库最终存在于指定表空间这个网的模型了吗？！
是的，看上去这确实是不合常理的，但这么做又是有它的道理的，而且现实中，我们往往需要这么做：将表的数据存在一个较慢的磁盘上的表空间，而将表的索引存在于一个快速的磁盘上的表空间。
但我们再查看表所属的模式还是没变的，它依然属于指定的模式。所以这并不违反常理。实际上，PostgreSQL并没有限制一张表必须属于某个特定的表空间，我们之所以会这么认为，是因为在关系递进时，偷换了一个概念：模式是逻辑存在的，它不受表空间的限制。