## 5.9.模式

[5.9.1. 创建模式](ddl-schemas.html#DDL-SCHEMAS-CREATE)

[5.9.2. 公共模式](ddl-schemas.html#DDL-SCHEMAS-PUBLIC)

[5.9.3. 模式搜索路径](ddl-schemas.html#DDL-SCHEMAS-PATH)

[5.9.4. 模式和特权](ddl-schemas.html#DDL-SCHEMAS-PRIV)

[5.9.5. 系统目录模式](ddl-schemas.html#DDL-SCHEMAS-CATALOG)

[5.9.6. 使用模式](ddl-schemas.html#DDL-SCHEMAS-PATTERNS)

[5.9.7. 便携性](ddl-schemas.html#DDL-SCHEMAS-PORTABILITY)

[](<>)

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

### 笔记

集群的用户不一定拥有访问集群中每个数据库的权限。共享角色名称意味着不能有不同的角色命名，`乔`在同一集群中的两个数据库中；但系统可以配置为允许`乔`只能访问部分数据库。

数据库包含一个或多个命名的*模式*，其中依次包含表。模式还包含其他类型的命名对象，包括数据类型、函数和运算符。相同的对象名可以在不同的模式中使用，而不会产生冲突；例如，两者都有`方案1`和`我的模式`可以包含名为`空白表`。与数据库不同，模式不是严格分开的：用户可以访问他们连接到的数据库中任何模式中的对象，如果他们有权限这样做的话。

有几个原因可以解释为什么要使用模式：

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

-   将数据库对象组织到逻辑组中，使其更易于管理。

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

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

### 5.9.1.创建模式

[](<>)

要创建模式，请使用[创建模式](sql-createschema.html)命令为模式指定一个您选择的名称。例如：

```
CREATE SCHEMA myschema;
```

[](<>)[](<>)

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

```
schema.table
```

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

实际上，更一般的语法

```
database.schema.table
```

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

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

```
CREATE TABLE myschema.mytable (
 ...
);
```

[](<>)

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

```
DROP SCHEMA myschema;
```

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

```
DROP SCHEMA myschema CASCADE;
```

看见[第5.14节](ddl-depend.html)了解这背后的一般机制。

通常，您会希望创建一个由其他人拥有的模式（因为这是将用户的活动限制为定义良好的名称空间的方法之一）。其语法如下：

```
CREATE SCHEMA schema_name AUTHORIZATION user_name;
```

甚至可以省略模式名，在这种情况下，模式名将与用户名相同。看见[第5.9.6节](ddl-schemas.html#DDL-SCHEMAS-PATTERNS)这是如何有用的。

以`pg_`是为系统目的保留的，用户无法创建。

### 5.9.2.公共模式

[](<>)

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

```
CREATE TABLE products ( ... );
```

以及：

```
CREATE TABLE public.products ( ... );
```

### 5.9.3.模式搜索路径

[](<>)[](<>)[](<>)

限定名称编写起来很乏味，而且通常最好不要将特定的模式名称连接到应用程序中。因此，表格通常由*非限定名称*，它只包含表名。系统确定哪个表是按照*搜索路径*，这是要查看的模式列表。搜索路径中的第一个匹配表被视为所需的表。如果搜索路径中没有匹配项，则会报告错误，即使数据库中的其他架构中存在匹配的表名。

在不同的模式中创建相似的命名对象的能力使编写每次都精确引用相同对象的查询变得复杂。它还为用户提供了改变其他用户查询行为的可能性，无论是恶意的还是意外的。由于查询中普遍存在非限定名称，以及它们在PostgreSQL内部结构中的使用，向`搜索路径`有效地信任所有拥有`创造`该模式的特权。运行普通查询时，能够在搜索路径的架构中创建对象的恶意用户可以控制并执行任意SQL函数，就像您执行了它们一样。

[](<>)

搜索路径中命名的第一个模式称为当前模式。除了是第一个搜索的模式外，如果`创建表格`命令未指定架构名称。

[](<>)

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

```
SHOW search_path;
```

在默认设置中，返回：

```
 search_path
### 5.9.4. Schemas and Privileges

[]()

 By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the `USAGE` privilege on the schema. To allow users to make use of the objects in the schema, additional privileges might need to be granted, as appropriate for the object.

 A user can also be allowed to create objects in someone else's schema. To allow that, the `CREATE` privilege on the schema needs to be granted. Note that by default, everyone has `CREATE` and `USAGE` privileges on the schema `public`. This allows all users that are able to connect to a given database to create objects in its `public` schema. Some [usage patterns](ddl-schemas.html#DDL-SCHEMAS-PATTERNS) call for revoking that privilege:
```

从public中撤销创建公共模式；

```
 (The first “public” is the schema, the second “public” means “every user”. In the first sense it is an identifier, in the second sense it is a key word, hence the different capitalization; recall the guidelines from [Section 4.1.1](sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS).)

### 5.9.5. The System Catalog Schema

[]()

 In addition to `public` and user-created schemas, each database contains a `pg_catalog` schema, which contains the system tables and all the built-in data types, functions, and operators. `pg_catalog` is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched *before* searching the path's schemas. This ensures that built-in names will always be findable. However, you can explicitly place `pg_catalog` at the end of your search path if you prefer to have user-defined names override built-in names.

 Since system table names begin with `pg_`, it is best to avoid such names to ensure that you won't suffer a conflict if some future version defines a system table named the same as your table. (With the default search path, an unqualified reference to your table name would then be resolved as the system table instead.) System tables will continue to follow the convention of having names beginning with `pg_`, so that they will not conflict with unqualified user-table names so long as users avoid the `pg_` prefix.

### 5.9.6. Usage Patterns

 Schemas can be used to organize your data in many ways. A *secure schema usage pattern* prevents untrusted users from changing the behavior of other users' queries. When a database does not use a secure schema usage pattern, users wishing to securely query that database would take protective action at the beginning of each session. Specifically, they would begin each session by setting `search_path` to the empty string or otherwise removing non-superuser-writable schemas from `search_path`. There are a few usage patterns easily supported by the default configuration:

* Constrain ordinary users to user-private schemas. To implement this, issue `REVOKE CREATE ON SCHEMA public FROM PUBLIC`, and create a schema for each user with the same name as that user. Recall that the default search path starts with `$user`, which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default. After adopting this pattern in a database where untrusted users had already logged in, consider auditing the public schema for objects named like objects in schema `pg_catalog`. This pattern is a secure schema usage pattern unless an untrusted user is the database owner or holds the `CREATEROLE` privilege, in which case no secure schema usage pattern exists.

* Remove the public schema from the default search path, by modifying [`postgresql.conf`](config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE) or by issuing `ALTER ROLE ALL SET search_path = "$user"`. Everyone retains the ability to create objects in the public schema, but only qualified names will choose those objects. While qualified table references are fine, calls to functions in the public schema [will be unsafe or unreliable](typeconv-func.html). If you create functions or extensions in the public schema, use the first pattern instead. Otherwise, like the first pattern, this is secure unless an untrusted user is the database owner or holds the `CREATEROLE` privilege.

* Keep the default. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users.

 For any pattern, to install shared applications (tables to be used by everyone, additional functions provided by third parties, etc.), put them into separate schemas. Remember to grant appropriate privileges to allow the other users to access them. Users can then refer to these additional objects by qualifying the names with a schema name, or they can put the additional schemas into their search path, as they choose.

### 5.9.7. Portability

 In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. Therefore, many users consider qualified names to really consist of `*`user_name`*.*`table_name`*`. This is how PostgreSQL will effectively behave if you create a per-user schema for every user.

 Also, there is no concept of a `public` schema in the SQL standard. For maximum conformance to the standard, you should not use the `public` schema.

 Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.
```