5.9. 架构#

5.9.1. 创建架构
5.9.2. 公共架构
5.9.3. 架构搜索路径
5.9.4. 架构和权限
5.9.5. 系统目录架构
5.9.6. 使用模式
5.9.7. 可移植性

PostgreSQL数据库集群包含一个或多个已命名的数据库。角色和其他一些对象类型在整个集群中共享。客户端连接到服务器只能访问单个数据库中的数据，即连接请求中指定的数据。

注意

集群用户不一定有权访问集群中的每个数据库。角色名称共享意味着在同一集群中的两个数据库中不能有不同的角色名称，例如joe；但可以将系统配置为仅允许joe访问某些数据库。

数据库包含一个或多个已命名的模式，模式又包含表。模式还包含其他类型的已命名对象，包括数据类型、函数和运算符。相同对象名称可在不同模式中使用而不会发生冲突；例如，schema1和myschema都可以包含名为mytable的表。与数据库不同，模式没有严格分离：如果用户有权限，他们可以访问所连接数据库中任何模式中的对象。

人们可能希望使用模式的原因有以下几个

允许许多用户使用一个数据库，而不会互相干扰。
将数据库对象组织成逻辑组，以便更易于管理。
可以将第三方应用程序放入单独的模式中，以便它们不会与其他对象的名称冲突。

模式类似于操作系统级别的目录，只是模式不能嵌套。

5.9.1. 创建模式#

要创建模式，请使用CREATE SCHEMA命令。根据您的选择为模式命名。例如

CREATE SCHEMA myschema;

要在模式中创建或访问对象，请编写一个限定名称，该名称由模式名称和表名称组成，并用点分隔

schema.table

这适用于任何需要表名称的地方，包括表修改命令和后续章节中讨论的数据访问命令。（为了简洁，我们只讨论表，但相同的想法适用于其他类型的已命名对象，例如类型和函数。）

实际上，更通用的语法

database.schema.table

也可以使用，但目前这只是为了形式上符合 SQL 标准。如果您编写数据库名称，它必须与您连接的数据库相同。

因此，要在新模式中创建表，请使用

CREATE TABLE myschema.mytable (
 ...
);

要删除空模式（其中的所有对象都已删除），请使用

DROP SCHEMA myschema;

要删除包含所有包含对象的模式，请使用

DROP SCHEMA myschema CASCADE;

请参阅第 5.14 节以了解其背后的通用机制的说明。

通常，您会希望创建由其他人拥有的模式（因为这是将用户的活动限制在明确定义的命名空间的一种方法）。语法如下

CREATE SCHEMA schema_name AUTHORIZATION user_name;

您甚至可以省略模式名称，在这种情况下，模式名称将与用户名相同。请参阅第 5.9.6 节以了解这如何有用。

以pg_开头的模式名称是为系统目的保留的，用户无法创建。

5.9.2. 公共模式#

在前面的部分中，我们创建了表，而没有指定任何模式名称。默认情况下，此类表（和其他对象）将自动放入名为“public”的模式中。每个新数据库都包含这样的模式。因此，以下内容是等效的

CREATE TABLE products ( ... );

和

CREATE TABLE public.products ( ... );

5.9.3. 模式搜索路径#

限定名称很难编写，而且通常最好不要将特定模式名称连接到应用程序中。因此，通常通过非限定名称来引用表，其中仅包含表名。系统通过遵循搜索路径（要查找的模式列表）来确定要使用的表。搜索路径中第一个匹配的表将被视为所需的表。如果在搜索路径中没有匹配项，则会报告错误，即使在数据库的其他模式中存在匹配的表名也是如此。

在不同模式中创建同名对象的能力使编写每次都引用完全相同的对象的查询变得复杂。它还为用户提供了恶意或意外地更改其他用户查询行为的可能性。由于在查询中普遍使用非限定名称并在PostgreSQL内部使用它们，因此将模式添加到search_path实际上信任所有在该模式上拥有CREATE权限的用户。当您运行普通查询时，能够在搜索路径模式中创建对象的恶意用户可以控制并执行任意 SQL 函数，就像您执行它们一样。

搜索路径中命名的第一个模式称为当前模式。除了作为第一个被搜索的模式之外，它也是在CREATE TABLE命令未指定模式名称时将创建新表中的模式。

要显示当前搜索路径，请使用以下命令

SHOW search_path;

在默认设置中，此命令返回

search_path
--------------
 "$user", public

第一个元素指定要搜索与当前用户同名的模式。如果不存在此类模式，则忽略该条目。第二个元素引用我们已经看到的公共模式。

搜索路径中存在的第一个模式是创建新对象的默认位置。这就是默认情况下在公共模式中创建对象的原因。当在没有任何模式限定（表修改、数据修改或查询命令）的其他上下文中引用对象时，将遍历搜索路径，直到找到匹配对象。因此，在默认配置中，任何不合格的访问都只能再次引用公共模式。

要将我们的新模式放入路径中，我们使用

SET search_path TO myschema,public;

（我们在此处省略$user，因为我们不需要它。）然后，我们可以在没有模式限定的情况下访问表

DROP TABLE mytable;

此外，由于myschema是路径中的第一个元素，因此默认情况下新对象将在其中创建。

我们还可以编写

SET search_path TO myschema;

然后，我们不再能够在没有明确限定的情况下访问公共模式。公共模式没有特殊之处，只是它默认存在。它也可以删除。

另请参阅第 9.26 节，了解操作模式搜索路径的其他方法。

搜索路径对数据类型名称、函数名称和运算符名称的工作方式与对表名称的工作方式相同。数据类型和函数名称可以与表名称完全相同的方式进行限定。如果您需要在表达式中编写限定的运算符名称，则有一个特殊规定：您必须编写

OPERATOR(schema.operator)

需要这样做以避免语法歧义。一个示例是

SELECT 3 OPERATOR(pg_catalog.+) 4;

在实践中，人们通常依赖搜索路径来查找运算符，以免编写出如此难看的代码。

5.9.4. 模式和权限#

默认情况下，用户无法访问他们不拥有的模式中的任何对象。要允许这样做，模式的所有者必须授予模式上的USAGE权限。默认情况下，每个人都对模式public拥有该权限。为了允许用户使用模式中的对象，可能需要授予其他权限，具体取决于对象。

还可以允许用户在其他人的模式中创建对象。要允许这样做，需要授予模式上的CREATE权限。在从PostgreSQL14 或更早版本升级的数据库中，每个人都对模式public拥有该权限。一些使用模式要求撤销该权限

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

（第一个“public”是模式，第二个“public”表示“每个用户”。在第一个意义上它是一个标识符，在第二个意义上它是一个关键字，因此大小写不同；请回想第 4.1.1 节中的指南。）

5.9.5. 系统目录模式#

除了public和用户创建的模式外，每个数据库都包含一个pg_catalog模式，其中包含系统表以及所有内置数据类型、函数和运算符。pg_catalog始终有效地成为搜索路径的一部分。如果在路径中未显式命名它，那么在搜索路径的模式之前，它会被隐式搜索before。这确保了始终可以找到内置名称。但是，如果您希望让用户定义的名称覆盖内置名称，则可以将pg_catalog显式放在搜索路径的末尾。

由于系统表名称以pg_开头，因此最好避免使用此类名称，以确保在未来版本定义与您的表同名的系统表时，您不会遇到冲突。（使用默认搜索路径，对表名称的不限定引用将被解析为系统表。）系统表将继续遵循名称以pg_开头的惯例，这样只要用户避免使用pg_前缀，它们就不会与不限定的用户表名称冲突。

5.9.6. 使用模式#

模式可以用于以多种方式组织您的数据。安全模式使用模式可防止不受信任的用户更改其他用户查询的行为。当数据库不使用安全模式使用模式时，希望安全查询该数据库的用户将在每个会话开始时采取保护措施。具体来说，他们将通过将search_path设置为空字符串或从search_path中删除非超级用户可写的模式，来开始每个会话。默认配置轻松支持一些使用模式

将普通用户限制为用户私有模式。要实现此模式，首先确保没有模式具有公共 CREATE 权限。然后，对于需要创建非临时对象的每个用户，创建一个与该用户同名的模式，例如 CREATE SCHEMA alice AUTHORIZATION alice。（请回想默认搜索路径以 $user 开头，它解析为用户名。因此，如果每个用户都有一个单独的模式，则他们默认访问自己的模式。）此模式是一种安全模式使用模式，除非不受信任的用户是数据库所有者或已获得相关角色的 ADMIN OPTION，在这种情况下，不存在安全模式使用模式。
在 PostgreSQL 15 及更高版本中，默认配置支持此使用模式。在早期版本中或使用已从早期版本升级的数据库时，您需要从 public 架构中移除 public CREATE 权限（发布 REVOKE CREATE ON SCHEMA public FROM PUBLIC）。然后考虑对 public 架构进行审计，以查找与架构 pg_catalog 中的对象同名的对象。
通过修改 postgresql.conf 或发布 ALTER ROLE ALL SET search_path = "$user"，从默认搜索路径中移除 public 架构。然后，授予在 public 架构中创建的权限。只有限定名称才会选择 public 架构对象。虽然限定表引用很好，但对 public 架构中函数的调用将不安全或不可靠。如果您在 public 架构中创建函数或扩展，请使用第一个模式。否则，与第一个模式一样，除非不受信任的用户是数据库所有者或已在相关角色上授予 ADMIN OPTION，否则这是安全的。
保留默认搜索路径，并授予在 public 架构中创建的权限。所有用户都隐式访问 public 架构。这模拟了根本不可用架构的情况，从而从非架构感知世界中平稳过渡。但是，这绝不是一种安全的模式。仅当数据库具有单个用户或几个相互信任的用户时，才可接受。在从 PostgreSQL 14 或更早版本升级的数据库中，这是默认设置。

对于任何模式，要安装共享应用程序（每个人都使用的表、第三方提供的其他函数等），请将它们放入单独的架构中。请记住授予适当的权限，以允许其他用户访问它们。然后，用户可以通过使用架构名称限定名称来引用这些其他对象，或者他们可以选择将其他架构放入其搜索路径中。

5.9.7. 可移植性#

在 SQL 标准中，不存在同一架构中的对象由不同用户拥有的概念。此外，某些实现不允许您创建与所有者名称不同的架构。事实上，在仅实现标准中指定的架构基本支持的数据库系统中，架构和用户这两个概念几乎是等效的。因此，许多用户认为限定名称实际上由*user_name*.*table_name*组成。如果您为每个用户创建每个用户架构，PostgreSQL将有效地表现为这样。

此外，SQL 标准中没有public架构的概念。为了最大程度地符合标准，您不应使用public架构。

当然，一些 SQL 数据库系统可能根本不实现架构，或通过允许（可能有限的）跨数据库访问来提供命名空间支持。如果您需要使用这些系统，那么不使用架构将实现最大的可移植性。

5.9. 架构# ​

5.9.1. 创建模式# ​

5.9.2. 公共模式# ​

5.9.3. 模式搜索路径# ​

5.9.4. 模式和权限# ​

5.9.5. 系统目录模式# ​

5.9.6. 使用模式# ​

5.9.7. 可移植性# ​