PostgreSQL的组织结构(3)-默认角色和schema

基础信息
OS版本：Red Hat Enterprise Linux Server release 7.9 (Maipo)
DB版本：16.2
pg软件目录：/home/pg16/soft
pg数据目录：/home/pg16/data
端口：5777

1 默认角色

PostgreSQL 自带一些内置的角色，这些角色拥有特定的系统权限，用于执行管理任务、权限控制等。下面是一些主要的 PostgreSQL 自带角色及其用途介绍：

  1. postgres:
  • 这是安装 PostgreSQL 时默认创建的超级用户角色。它拥有数据库中的所有权限，包括创建和删除数据库、管理用户角色、分配权限等。
  • 通常使用 postgres 用户进行数据库的初步设置和管理操作。
  1. pg_signal_backend:
  • 这个角色允许发送信号到同一数据库中的其他会话。例如，可以使用它来取消查询或终止会话。
  • 这个角色主要用于数据库维护和控制操作。
  1. pg_read_all_data:
  • 这个角色主要是在 PostgreSQL 15 版本中引入的，作为一种新的权限管理机制，允许用户读取数据库中所有表和视图的数据，而无需对每个表单独授予权限。
  1. pg_write_all_data:
  • 这个角色主要是在 PostgreSQL 15 版本中引入的，作为一种新的权限管理机制，允许写入所有表和视图（但不包括DDL操作如创建或删除表）的角色，而无需对每个表单独授予权限。
  1. pg_monitor:
  • 查看系统目录中隐藏信息的能力，例如，统计信息或系统表中的某些列。
  • 执行诊断查询，例如，查看执行计划或检查服务器的运行状态。
  • 访问数据库的各种统计视图，这对于分析和优化查询、监控系统性能等任务至关重要。
  1. pg_read_all_settings:
  • 访问所有的 PostgreSQL 配置设置，这包括通过 SHOW 命令、pg_settings 系统视图，或者其他方法访问的设置。
  • 读取包括一些敏感设置在内的配置，而无需被授予超级用户权限。
  1. pg_read_all_stats:
  • 访问 pg_stat 系列视图，这些视图提供了关于数据库操作的详细统计信息，比如每个表的读写频率、索引的使用状况、被执行查询的性能统计等。
  • 访问 pg_statio 系列视图，这些视图提供了关于数据库 I/O 操作的统计数据，比如对表、索引、Toast 表等的磁盘 I/O 统计信息。
  • 能够查看执行计划统计信息，这对于分析查询性能和进行优化是很有帮助的。
  1. pg_read_server_files:
  • 允许读取服务器上的文件。这对于执行某些数据库操作特别有用，比如使用 COPY FROM 命令或 pg_read_file 函数来读取服务器上的文件内容到 PostgreSQL 数据库中。
  • 使用这个角色可以帮助数据库管理员细粒度地控制对服务器文件的访问，从而避免将过高的权限（如超级用户权限）授予不需要这么高权限的用户。
  1. pg_write_server_files:
  • 允许持有该角色的用户写入服务器上的文件。这个角色为数据库操作提供了在服务器文件系统上执行写操作的能力，比如使用 COPY TO 命令将查询结果写入服务器上的文件中，或者通过其他数据库函数创建或修改服务器上的文件内容。
  1. pg_execute_server_program:
  • 允许用户在服务器上执行外部程序，这可以用于从数据库触发操作系统级别的脚本或程序。
  • 这个角色特别适用于那些需要从 PostgreSQL 数据库内部，通过 SQL 命令执行服务器上的外部程序或脚本的情况。
  1. pg_use_reserved_connections:
  • 允许授予权限的用户使用保留的连接。
  • 该角色特别适用于需要确保某些关键操作或关键用户在数据库高负载时仍能连接到数据库的场景。这可能包括自动化的维护脚本、关键任务执行或特定的监控服务等。
  1. pg_create_subscription:
  • 赋予用户创建复制订阅的能力。
  • 该角色特别适用于需要设置和管理逻辑复制的用户或应用。
  1. pg_checkpoint:
  • 允许用户执行CHECKPOINT命令。
  1. pg_stat_scan_tables:
  • 允许用户执行监视功能。但这些功能可能会占用表上的ACCESS SHARE锁，且会持续很长一段时间。
  1. pg_database_owner:
  • 表示当前数据库所属。

2 默认schema

PostgreSQL 默认安装时包含一些内建的 schema，最重要的是 public schema。以下是一些 PostgreSQL 自带的、较为重要的 schema 及其用途：

  1. public:
  • 这是 PostgreSQL 数据库中默认的 schema。如果你创建表或其他数据库对象而没有明确指定 schema，这些对象将默认被创建在 public schema 下。
  • 所有用户默认都有权限在 public schema 下创建、修改和删除对象。
  1. pg_catalog:
  • pg_catalog 是 PostgreSQL 存储数据库元数据的地方，比如信息模式（Information Schema）就在这里。它包含了所有内置的函数、数据类型以及系统表等。例如，当你在查询数据类型信息或者查看当前数据库中的所有表时，实际上这些信息都来自于 pg_catalog 中的系统表。
  • 这个 schema 对 PostgreSQL 的运行来说是必需的，而且它对所有用户都是可见的。
  1. information_schema:
  • information_schema 提供了关于数据库对象（如表、视图、列等）的信息的 SQL 标准视图。这些视图提供了一种标准化的方法来查询元数据，无论底层 RDBMS 是什么。
  • 虽然 information_schema 的数据是从 pg_catalog 派生出来的，但 information_schema 提供的信息是以一种更易理解和标准化的形式展现的。
  1. pg_toast:
  • TOAST（The Oversized-Attribute Storage Technique）是 PostgreSQL 处理大型数据行的机制。pg_toast schema 存储了特定 TOAST 数据的结构和信息。当一个表的某列数据超过了页面大小（默认是 8KB）时，PostgreSQL 会使用 TOAST 技术来存储这些大型字段。
  • 通常情况下，普通用户不需要直接与 pg_toast schema 交互。
  1. pg_temp:
  • pg_temp 是存放临时表的地方。这些临时表仅在当前会话中可见，并且在会话结束时被自动删除。在不同的会话中，即使使用相同的临时表名，实际上也是不同的表。
  • PostgreSQL 为每个会话动态创建一个临时 schema，来存放该会话的临时表。

PostgreSQL每个版本的角色和schema可能不一样，详情请查看对应版本的官方文档。

谨记：心存敬畏，行有所止。