PostgreSQL-schema

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

使用模式有几个原因：

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

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

一、创建schema

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

CREATE SCHEMA myschema;

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

schema.table

如果模式为空（其中的所有对象都已被删除），要删除它，请使用：

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

通常，您会想要创建一个由其他人拥有的模式（因为这是将用户的活动限制在明确定义的名称空间的方法之一）。其语法是：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

以 pg_ 开头的模式名称是为系统保留的，不能由用户创建。

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

CREATE TABLE products ( ... );

CREATE TABLE public.products ( ... );

三、schema搜索路径
限定名称编写起来很乏味，而且通常最好不要将特定的模式名称连接到应用程序中。因此，表通常由不限定的名称来引用，这些名称仅由表名组成。系统通过遵循搜索路径来确定哪个表是指要查找的模式列表。搜索路径中的第一个匹配表被认为是想要的表。如果搜索路径没有匹配，会报错，即使匹配的表名存在于数据库的其他模式中。

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

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

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

SHOW search_path;

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

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

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

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

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

SET search_path TO myschema,public;

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

OPERATOR(schema.operator)

例如：

SELECT 3 OPERATOR(pg_catalog.+) 4;

四、schema和权限
默认情况下，用户无法访问他们不拥有的schema中的任何对象。为此，模式的所有者必须授予模式的 USAGE 权限。为了允许用户使用模式中的对象，可能需要授予额外的权限，以适合对象。

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

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

（第一个“public”是模式，第二个“public”是指“每个用户”。在第一个意义上它是一个标识符，在第二个意义上它是一个关键词，因此不同的大小写；)

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

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

六、使用建议

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

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

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