5.8. Schemas

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

Note

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

数据库包含一个或多个名为* schemas *的文件，而这些文件又包含表。模式还包含其他种类的命名对象，包括数据类型，函数和运算符。相同的对象名称可以在不同的模式中使用，而不会发生冲突。例如schema1和myschema都可以包含名为mytable的表。与数据库不同，架构不是严格分开的：用户可以访问与其连接的数据库中任何架构中的对象(如果有权限)。

人们可能要使用模式的原因有几个：

  • 允许许多用户使用一个数据库而不会互相干扰。

  • 将数据库对象组织成逻辑组以使其更易于 Management。

  • 第三方应用程序可以放在单独的架构中，这样它们就不会与其他对象的名称冲突。

架构类似于 os 级别的目录，不同之处在于架构不能嵌套。

5 .8.1. 创建模式

要创建模式，请使用CREATE SCHEMA命令。给模式一个您选择的名称。例如：

CREATE SCHEMA myschema;

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

schema.table

这适用于需要表名的任何地方，包括表修改命令和以下章节中讨论的数据访问命令。 (为简便起见，我们仅讨论表，但是相同的思想也适用于其他类型的命名对象，例如类型和函数.)

实际上，更通用的语法

database.schema.table

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

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

CREATE TABLE myschema.mytable (
 ...
);

要删除模式为空(已删除其中的所有对象)的模式，请使用：

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

有关此背后的一般机制的说明，请参见Section 5.13。

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

CREATE SCHEMA schema_name AUTHORIZATION user_name;

您甚至可以省略架构名称，在这种情况下，架构名称将与用户名相同。有关如何使用的信息，请参见Section 5.8.6。

以pg_开头的模式名称仅供系统使用，不能由用户创建。

5 .8.2. 公共架构

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

CREATE TABLE products ( ... );

and:

CREATE TABLE public.products ( ... );

5 .8.3. 模式搜索路径

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

在不同模式中创建名称相同的对象的能力使编写每次都引用完全相同的对象的查询变得复杂。它还为用户打开了恶意或意外更改其他用户查询行为的可能性。由于查询中普遍存在不合格名称，并且在 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;

这样一来，如果没有明确的限定条件，我们将无法再访问公共架构。除了默认存在之外，公共模式没有什么特别的。也可以将其删除。

另请参见Section 9.25，以了解其他操作模式搜索路径的方法。

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

OPERATOR(schema.operator)

这是避免语法歧义所必需的。一个例子是：

SELECT 3 OPERATOR(pg_catalog.+) 4;

实际上，人们通常依赖于运算符的搜索路径，以免写出那么丑陋的东西。

5 .8.4. 架构和特权

默认情况下，用户无法访问他们不拥有的架构中的任何对象。为此，架构的所有者必须授予该架构的USAGE特权。为了允许用户使用架构中的对象，可能需要授予其他特权，以适合该对象。

还可以允许用户在其他人的架构中创建对象。为此，需要授予该模式的CREATE特权。请注意，默认情况下，每个人都对public模式具有CREATE和USAGE特权。这允许所有能够连接到给定数据库的用户在其public模式中创建对象。一些usage patterns要求撤销该特权：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(第一个“ public”是架构，第二个“ public”表示“每个用户”.从第一个意义上讲，它是一个标识符，在第二个意义上，它是一个关键字，因此大小写不同；请回顾Section 4.1.1中的准则)

5 .8.5. 系统目录架构

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

由于系统表名称以pg_开头，因此最好避免使用此类名称，以确保将来某个版本定义的系统表名称与表相同时不会发生冲突。 (使用默认搜索路径，然后会将对表名的不合格引用解析为系统表.)系统表将 continue 遵循以pg_开头的名称的约定，以便它们不会与不合格的用户冲突。表名称，只要用户避免使用pg_前缀即可。

5 .8.6. 使用模式

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

  • 将普通用户限制为用户专用模式。要实现此目的，请发出REVOKE CREATE ON SCHEMA public FROM PUBLIC，并为每个与该用户同名的用户创建一个架构。回想一下，默认搜索路径以$user开头，它解析为用户名。因此，如果每个用户都有一个单独的架构，则默认情况下他们将访问自己的架构。在已经有不受信任的用户登录的数据库中采用这种模式之后，请考虑审核公共模式中名为pg_catalog的对象(如对象)。除非不受信任的用户是数据库所有者或拥有CREATEROLE特权，否则此模式是安全模式使用模式，在这种情况下，不存在安全模式使用模式。

  • 通过修改postgresql.conf或发出ALTER ROLE ALL SET search_path = "$user"，从默认搜索路径中删除公共架构。每个人都保留在公共架构中创建对象的能力，但是只有合格的名称才能选择这些对象。虽然合格的表引用很好，但是可以调用公共模式将是不安全或不可靠的中的函数。如果您在公共模式中创建函数或扩展，请改用第一个模式。否则，像第一种模式一样，这是安全的，除非不受信任的用户是数据库所有者或拥有CREATEROLE特权。

  • 保留默认值。所有用户都隐式访问公共架构。这模拟了架构根本不可用的情况，从而使从非模式感知的世界平稳过渡。但是，这绝不是安全的模式。仅当数据库有一个用户或几个相互信任的用户时，才可接受。

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

5.8.7. Portability

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

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

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

Name PostgreSQL 中文文档
Version
Language
Badge
Last Updated 2021-07-07T13:04:41