【PostgreSQL】模式Schema

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

数据库包含一个或多个命名schema，而这些schema又包含表。schema还包含其他类型的命名对象，包括数据类型、函数和运算符。相同的对象名称可以在不同的架构中使用而不会发生冲突;例如，both 和myschema可以包含名为mytable的表。与数据库不同，架构不是严格分离的：如果用户具有访问权限，则可以访问他们所连接到的数据库中任何架构中的对象。

可能想要使用架构的原因有以下几个：

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

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

创建模式

要创建模式，请使用 CREATE SCHEMA 命令。为架构指定您选择的名称。例如：

CREATE SCHEMA myschema;

若要在架构中创建或访问对象，请编写一个由架构名称和表名称组成的限定名称，并用点分隔：

schema.table

这适用于需要表名的任何位置，包括表修改命令和以下章节中讨论的数据访问命令。（为简洁起见，我们只讨论表，但同样的想法也适用于其他类型的命名对象，例如类型和函数。

实际上，更通用的语法

database.schema.table

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

因此，若要在新架构中创建表，请使用：

CREATE TABLE myschema.mytable (
 ...
);

若要删除架构（如果架构为空）（其中的所有对象都已删除），请使用：

DROP SCHEMA myschema;

若要删除包含所有包含对象的架构，请使用：

DROP SCHEMA myschema CASCADE;

通常，您需要创建其他人拥有的架构（因为这是将用户的活动限制在定义明确的命名空间中的方法之一）。其语法为：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

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

以pg_开头的架构名称保留用于系统目的，用户无法创建。

公共模式

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

CREATE TABLE products ( ... );

和：

CREATE TABLE public.products ( ... );

模式搜索路径

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

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

搜索路径中命名的第一个架构称为当前架构。除了是第一个搜索的架构之外，它还是在命令未指定schema名称时将在其中创建新表CREATE TABLE的schema。

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

SHOW search_path;

在默认设置中，这将返回：

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

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

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

为了将我们的新架构放在路径中，我们使用：

SET search_path TO myschema,public;

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

DROP TABLE mytable;

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

我们也可以这样写：

SET search_path TO myschema;

这样，如果没有显式限定，我们就无法再访问公共架构。公共架构没有什么特别之处，只是它默认存在。它也可以被丢弃。

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

OPERATOR(schema.operator)

这是避免语法歧义所必需的。例如：

SELECT 3 OPERATOR(pg_catalog.+) 4;

在实践中，人们通常依赖于运算符的搜索路径，这样就不必写出如此丑陋的东西。

模式和权限

默认情况下，用户无法访问他们不拥有的架构中的任何对象。若要允许这样做，架构的所有者必须授予对架构的权限。默认情况下，每个人都对架构具有该权限。要允许用户使用架构中的对象，可能需要根据对象的需要授予其他权限 USAGE public。

还可以允许用户在其他人的架构中创建对象。若要允许这样做，需要授予对架构的权限。在从 PostgreSQL 14 或更早版本升级的数据库中，每个人都对架构具有该权限。某些使用模式要求撤消该权限：CREATE public

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

（第一个“public”是架构，第二个“public”是“每个用户”。在第一种意义上，它是一个标识符，在第二种意义上，它是一个关键词，因此大小写不同。

系统目录模式

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

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

使用模式

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

将普通用户限制为用户专用架构。若要实现此模式，请首先确保没有架构具有公共权限。然后，对于需要创建非临时对象的每个用户，创建与该用户同名的架构，例如CREATE SCHEMA alice AUTHORIZATION alice $user ADMIN OPTION 。（回想一下，默认搜索路径以开头，解析为用户名。因此，如果每个用户都有单独的架构，则默认情况下他们会访问自己的架构。此模式是一种安全模式使用模式，除非不受信任的用户是数据库所有者或已被授予相关角色，在这种情况下，不存在安全模式使用模式。

在 PostgreSQL 15 及更高版本中，默认配置支持此使用模式。在早期版本中，或者使用从先前版本升级的数据库时，需要从架构中删除公共权限（问题）。然后考虑审核架构中类似对象命名的对象的架构。

 REVOKE CREATE ON SCHEMA public FROM PUBLIC public pg_catalog

通过修改 postgresql.conf 或发出 .然后，授予在公共架构中创建的权限。只有限定的名称才会选择公共架构对象。虽然限定的表引用很好，但对公共架构中的函数的调用将是不安全或不可靠的。如果在公共架构中创建函数或扩展，请改用第一种模式。否则，与第一种模式一样，这是安全的，除非不受信任的用户是数据库所有者或已被授予相关角色。

ALTER ROLE ALL SET search_path = "$user"ADMIN OPTION

保留默认搜索路径，并授予在公共架构中创建的权限。所有用户都隐式访问公共架构。这模拟了架构完全不可用的情况，从而提供了从非架构感知世界的平滑过渡。但是，这从来都不是一个安全的模式。仅当数据库具有单个用户或几个相互信任的用户时，才可接受。在从 PostgreSQL 14 或更早版本升级的数据库中，这是默认设置。

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

可移植性

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

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

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