PostgreSQL / 系统课时

实例、数据库与模式

实例、数据库与模式

理解 PostgreSQL 的管理边界,关键不是背名词,而是知道“配置、连接、权限和对象到底属于哪一层”。本节把实例、数据库、模式、角色和对象放到同一张结构图里,并用一组 SQL 直接验证它们的作用范围。

学习目标

  • 说清实例、数据库、模式、角色和对象之间的层级与共享关系
  • 用 SQL 创建数据库、模式、角色和表,并验证对象归属
  • 根据隔离强度判断应拆数据库还是只拆模式

先把五个边界放在一起

运维语境中的 PostgreSQL 实例,可以理解为一组正在运行的服务器进程,它们管理一个数据库集群和对应的数据目录,并通过端口或 Unix Socket 对外提供连接。一个主机可以运行多个实例,只要它们使用不同的数据目录和监听端点。

数据库集群内部可以有多个数据库。一次普通客户端连接只能进入其中一个数据库;连接建立后,不能像切换模式那样直接访问另一个数据库中的表。跨数据库访问需要额外机制,例如 postgres_fdw 或 dblink。

每个数据库内部再包含多个模式。模式是对象的命名空间,表、视图、序列、函数和类型等对象通常位于某个模式中。同名对象可以存在于不同模式,例如 sales.orders 与 archive.orders。

角色不属于某个单独数据库,而是在整个数据库集群范围内共享。角色可以登录、成为其他角色的成员、拥有数据库对象并获得权限。因此,把两个业务拆进不同数据库,并不会得到两套互不相干的角色命名空间。

PostgreSQL 实例、数据库、模式、角色与对象的管理边界

用 SQL 验证对象属于哪一层

下面示例需要使用具备创建角色和数据库权限的管理账号执行。它创建一个集群级角色、一个数据库、一个模式和一张表,再查询当前数据库、表所在模式及对象所有者。

sql
CREATE ROLE lab_owner NOLOGIN;
CREATE DATABASE labdb OWNER lab_owner;

\connect labdb

CREATE SCHEMA app AUTHORIZATION lab_owner;

SET ROLE lab_owner;

CREATE TABLE app.orders (
    order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric(10, 2) NOT NULL
);

RESET ROLE;

SELECT current_database() AS database_name;

SELECT n.nspname AS schema_name,
       c.relname AS object_name,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE n.nspname = 'app'
  AND c.relname = 'orders';

SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolname = 'lab_owner';

预期输出

text
CREATE ROLE
CREATE DATABASE
You are now connected to database "labdb" as user "<USER>".
CREATE SCHEMA
SET
CREATE TABLE
RESET
 database_name
---------------
 labdb
(1 row)

 schema_name | object_name |   owner
-------------+-------------+-----------
 app         | orders      | lab_owner
(1 row)

  rolname   | rolcanlogin
------------+-------------
 lab_owner  | f
(1 row)

<USER> 取决于实验环境中实际使用的管理账号。这里最重要的现象是:orders 位于 labdb 的 app 模式中,而 lab_owner 是集群级角色;NOLOGIN 只表示它不能直接建立登录会话,并不妨碍它拥有对象。

名字解析和权限是两回事

模式首先解决“名字放在哪里”的问题。使用 app.orders 是显式限定对象;省略模式名时,PostgreSQL 会根据 search_path 逐个查找可见模式。生产代码通常应避免依赖含糊的搜索路径,特别是执行高权限函数或存在不受信任用户可创建对象的模式时。

权限则决定“谁能做什么”。访问某模式中的表,通常既要有模式上的 USAGE,又要有表上的相应对象权限;在别人的模式里创建对象还需要模式上的 CREATE。模式所有者、对象所有者和当前登录角色可以是不同角色,不要把“对象在哪个模式”误解成“对象归哪个用户”。

数据库和模式怎么选

如果两个系统需要更强的连接级隔离、独立的数据库所有者或更明确的管理边界,可以放到不同数据库。如果它们属于同一应用,需要频繁关联查询、共享事务或复用同一套连接,通常更适合放在同一数据库的不同模式中。

需要同时记住一个边界:数据库之间对普通 SQL 连接是隔离的,但同一实例中的数据库仍共享角色、部分集群级对象以及服务器资源。模式提供的是数据库内部的命名空间和权限边界,不是独立实例,也不是独立数据库。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

实例、数据库与模式

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通