PostgreSQL学习总结（6）—— PostgreSQL 模式（SCHEMA）详解-白红宇

PostgreSQL学习总结（6）—— PostgreSQL 模式（SCHEMA）详解

阅读量：794 次

发布时间：2023-03-03

本文共 1724 字，大约阅读时间需要 5 分钟。

PostgreSQL 模式（SCHEMA）概述与操作指南

PostgreSQL 模式（SCHEMA）是一种组织数据库对象的强大机制，它允许将数据库对象划分为逻辑组，方便管理和访问。以下将从基础到高级内容详细介绍 PostgreSQL 模式的创建、使用、权限管理以及与搜索路径的关系。

1. 模式的基本概念

PostgreSQL 模式可以被看作是一个表的集合。一个模式可以包含表、视图、索引、数据类型、函数以及操作符等。与数据库不同，模式允许同一名称在多个模式中重复使用，例如 schema1 和 myschema 都可以包含名为 mytable 的表。模式的作用类似于操作系统层次的目录，但其具有的特点是不可嵌套。

PostgreSQL 数据库集群可以包含多个命名数据库。数据库中的用户和用户组是共享的，但数据并不共享。每个客户端连接只能访问其声明的特定数据库。需要注意的是，集群中的用户并不一定拥有访问所有数据库的权限，系统可以配置为限制用户的访问范围。

2. 模式的主要作用

隔离用户：允许多个用户共享同一数据库而不互相干扰。

便于管理：通过将数据库对象组织为逻辑组，简化管理流程。

支持第三方应用：将第三方应用置于独立模式中，避免命名冲突。

模式与操作系统目录类似，但具有以下区别：

模式不能嵌套。

模式的命名空间是全局的，不会因为嵌套而产生冲突。

3. 模式的创建与访问

要创建一个新模式，可以使用以下命令：

CREATE SCHEMA myschema;

在访问模式中的对象时，需要使用模式名称修饰对象名称，格式为 schema.table。例如，访问 myschema 模式中的 mytable 表，可以直接使用：

SELECT * FROM myschema.mytable;

如果要在多个模式中引用同一表，可以通过搜索路径来实现。搜索路径由模式名称组成，系统会从前往后寻找第一个存在的表。

要查看当前搜索路径，可以执行：

SHOW search_path;

默认搜索路径通常为 $user,public，其中 $user 是当前登录用户的用户名，public 是公共模式。

4. 模式的权限管理

模式的权限管理至关重要，确保只有授权用户才能访问模式中的对象。默认情况下，用户只能访问他们在模式中拥有的对象。为了允许用户访问模式中的对象，需要在模式上赋予他们 USAGE 权限。

要创建一个受限的模式，可以使用以下命令：

CREATE SCHEMA myschema AUTHORIZATION username;

此外，公共模式默认允许所有用户在其上创建对象。为了防止未经授权的创建，可以撤销公共模式的 CREATE 权限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

5. 系统表模式

每个 PostgreSQL 数据库都包含一个 pg_catalog 模式，用于存储系统表和内置数据类型、函数、操作符。pg_catalog 总是搜索路径的一部分，且优先级高于用户定义的模式。

为了避免与系统表发生命名冲突，建议避免使用以 pg_ 开头的名称。系统表的优先级确保了内置名称的唯一性，但如果需要自定义名称，可以将 pg_catalog 放在搜索路径之后。

6. 模式的使用建议

单一用户模式：为每个用户创建独立模式，名称与用户相同。默认搜索路径从用户模式开始，确保用户只能访问自己的对象。

共享应用模式：将第三方应用或共享表存放在独立模式中，确保不会干扰其他用户。

灵活搜索路径：允许用户将多个模式添加至搜索路径中，从而灵活访问不同模式中的对象。

7. 移植性考虑

PostgreSQL 模式与 SQL 标准有一定差异。在遵循标准的前提下，尽量避免使用 public 模式。对于其他数据库系统，模式概念可能有所不同，或根本不存在 public 模式。因此，在进行数据库迁移时，应谨慎处理模式的使用和命名。

总结

通过合理使用 PostgreSQL 模式，可以有效地组织和管理数据库对象，提升管理效率和安全性。模式的创建、权限管理和搜索路径配置是数据库设计中的关键环节，需要仔细规划和优化。

转载地址：http://qsxfk.baihongyu.com/

你可能感兴趣的文章