PHP小码农

Appearance

模式（Schema）管理 ​

模式（Schema）是 PostgreSQL 的第二级命名空间，也是它与 MySQL 差异最大的地方之一。本节讲清三件事：模式怎么创建与销毁、search_path 到底如何解析表名、以及用模式做多租户隔离时该不该选这条路。

前置知识

阅读本节前，建议先了解：数据库的创建与管理

基础概念 ​

PostgreSQL 的对象层级比 MySQL 多一层：

text
实例（Instance）
└── 数据库（Database）              -- 物理隔离，不能跨库查询
    └── 模式（Schema）              -- 逻辑分组，可跨模式 JOIN
        └── 对象（Table / View / Index / Sequence / Function / Type ...）

关键在于模式的定位：

维度说明
是命名空间吗是。sales.orders 与 public.orders 可以是两张完全不同的表
是物理隔离吗不是。同一个库内的所有模式共用同一套表空间与 WAL
是权限边界吗部分。可以按模式授权，但 USAGE 一给出去就失去了隔离意义
能跨模式查询吗能。SELECT * FROM sales.orders JOIN public.users ... 完全合法
能跨模式建外键吗能，写全限定名即可

三个必须掌握的概念：

  • 命名空间（Namespace）：模式让同名对象共存，这是它存在的根本理由。
  • 搜索路径（Search Path, search_path）：一个有序的模式列表。写未限定表名时，PostgreSQL 按列表顺序查找，第一个命中的生效。默认值是 "$user", public。
  • "$user" 特殊项：它不是一个模式名，而是「与当前登录角色同名的模式」。current_user 是 alice 时，"$user" 就代表 alice 模式。这一项是很多「表建错地方」事故的根源。

两类系统模式需要知道：

text
pg_catalog           -- 系统目录，存放 pg_class、pg_attribute 等元数据表。始终隐式在搜索路径最前面
information_schema   -- SQL 标准定义的信息模式，只读视图
pg_toast             -- 大字段行外存储
public               -- 每个新库默认自带的模式，从 PostgreSQL 15 起 CREATE 权限已默认收回

语法与用法 ​

text
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] [ schema_element ... ]
CREATE SCHEMA AUTHORIZATION role_specification [ schema_element ... ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION role_specification ]
CREATE SCHEMA schema_name [ AUTHORIZATION role_specification ] CREATE TABLE ... CREATE VIEW ...

ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }

DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

-- 搜索路径
SET search_path TO schema1, schema2, ...;
SHOW search_path;
操作命令psql 快捷方式
创建CREATE SCHEMA—
查看列表\dn / 查 pg_namespace\dn+ 带属主与权限
查看某模式下对象\dt schema.*\dt app.*
改名ALTER SCHEMA ... RENAME TO—
改属主ALTER SCHEMA ... OWNER TO—
删除DROP SCHEMA—
切换SET search_path—

最小示例：

sql
-- 创建模式并指定属主（AUTHORIZATION 后跟角色名）
CREATE SCHEMA app AUTHORIZATION postgres;

-- 幂等写法
CREATE SCHEMA IF NOT EXISTS audit;

-- 一条语句里同时建模式与表（schema_element 语法）
CREATE SCHEMA staging
  CREATE TABLE import_log (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    file_name  text        NOT NULL,
    imported_at timestamptz NOT NULL DEFAULT now()
  );
text
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA

详细说明 ​

创建模式：三种写法与一个陷阱 ​

sql
-- 写法一：指定属主
CREATE SCHEMA sales AUTHORIZATION app_owner;

-- 写法二：省略模式名，用当前角色名作为模式名
-- 当前角色是 alice，则创建出名为 alice 的模式
CREATE SCHEMA AUTHORIZATION alice;

-- 写法三：带 schema_element，一次创建多个对象
CREATE SCHEMA finance
  CREATE TABLE invoice (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY)
  CREATE VIEW v_invoice AS SELECT * FROM invoice;

陷阱：CREATE SCHEMA AUTHORIZATION 不带模式名时，模式名就是角色名，而角色名可能与 "$user" 冲突，产生意料之外的解析结果：

sql
-- 当前连接角色是 app_user
CREATE SCHEMA AUTHORIZATION app_user;   -- 建出了名为 app_user 的模式

SHOW search_path;
SELECT current_schema();
text
   search_path
-----------------
 "$user", public
(1 row)

 current_schema
----------------
 app_user
(1 row)

此后所有未限定表名默认落到 app_user 模式，public 被无视了。这不是 bug，而是 "$user" 的设计行为。

search_path 的解析规则 ​

解析过程分两步，顺序不能颠倒：

text
步骤 1：把 search_path 中的 "$user" 展开
        "$user", public  →  实际角色是 alice  →  alice, public（若 alice 模式存在）
                                            →  public（若 alice 模式不存在，跳过该项）

步骤 2：按顺序查找第一个包含该对象的模式，命中即停
        找到就返回；全部没找到则报错 relation "xxx" does not exist

pg_catalog 始终隐式排在搜索路径最前面，除非你显式写出它：

sql
-- 即使不写 pg_catalog，下面的查询也能命中系统表
SELECT count(*) FROM pg_class;

-- 查看实际生效的完整搜索路径（显式版）
SHOW search_path;
text
   search_path
-----------------
 "$user", public
(1 row)
sql
-- 用 current_schemas(true) 看到包含隐式模式在内的真实列表
SELECT current_schemas(true) AS effective_path;
text
    effective_path
---------------------
 {pg_catalog,"$user",public}
(1 row)

解析规则中最容易踩的坑：同名对象只认第一个。

sql
-- 准备：在两个模式里建同名表
CREATE SCHEMA a;
CREATE SCHEMA b;
CREATE TABLE a.items (id int, src text);
CREATE TABLE b.items (id int, src text);
INSERT INTO a.items VALUES (1, 'from a');
INSERT INTO b.items VALUES (1, 'from b');
sql
-- 依赖搜索路径
SET search_path TO a, public;
SELECT * FROM items;      -- 命中 a.items
text
 id |  src
----+--------
  1 | from a
(1 row)
sql
SET search_path TO b, public;
SELECT * FROM items;      -- 命中 b.items
text
 id |  src
----+--------
  1 | from b
(1 row)
sql
-- 全限定名不受搜索路径影响，永远确定
SELECT * FROM a.items;
text
 id |  src
----+--------
  1 | from a
(1 row)

一个隐蔽的特殊情况：函数解析与表解析规则不同。函数会在搜索路径中查找所有匹配签名的候选，若发现多个同签名函数分布在多个模式中，且无法唯一确定，会直接报错：

sql
-- 错误！函数不存在于搜索路径中（表名解析失败也会如此）
SET search_path TO a, public;
SELECT * FROM items_typo;
text
ERROR:  relation "items_typo" does not exist
LINE 1: SELECT * FROM items_typo;
                      ^

修改搜索路径的三种作用域与优先级：

作用域语句生效范围优先级
会话级SET search_path TO ...当前连接中
事务级SET LOCAL search_path TO ...当前事务最高
角色级ALTER ROLE alice SET search_path TO ...该角色的所有连接低
数据库级ALTER DATABASE demo SET search_path TO ...该库的所有连接最低
sql
-- 会话级：断开即失效
SET search_path TO app, public;

-- 事务级：COMMIT / ROLLBACK 后恢复
BEGIN;
SET LOCAL search_path TO staging, public;
SELECT * FROM items;      -- 走 staging
COMMIT;
SELECT * FROM items;      -- 恢复原搜索路径

-- 角色级与数据库级：持久化，写入 pg_db_role_setting
ALTER ROLE  app_user SET search_path TO app, public;
ALTER DATABASE demo    SET search_path TO app, public;
sql
-- 查看某角色/数据库上固化的参数
SELECT
  coalesce(d.datname, '(all databases)')      AS database,
  coalesce(r.rolname, '(all roles)')          AS role,
  s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_authid   r ON r.oid = s.setrole
WHERE s.setconfig::text LIKE '%search_path%';
text
    database     |   role   |                     setconfig
-----------------+----------+----------------------------------------------------
 demo            | (all roles) | {search_path=app, public, timezone=Asia/Shanghai}
 (all databases) | app_user | {search_path=app, public}
(2 rows)

模式的权限：USAGE 与 CREATE 是两回事 ​

这是模式权限最容易被误解的地方，两者作用完全不同：

权限允许做什么不允许做什么
USAGE看到模式内的对象名、按名字访问已有对象（需对象本身也有权限）不能创建新对象
CREATE在该模式内创建表、视图、函数等新对象不能访问别人的对象
sql
-- 先撤销 public 模式的默认创建权限（PostgreSQL 15 起已是默认行为）
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 只给读权限：能查，但不能建
GRANT USAGE ON SCHEMA app TO readonly_role;

-- 给读写：能查也能建
GRANT USAGE, CREATE ON SCHEMA app TO app_user;

给权限前的查看方式：

sql
-- psql 元命令：ACL 列的格式为 grantee=privileges/grantor
\dn+
text
                              List of schemas
  Name  |  Owner   |           Access privileges            |      Description
--------+----------+----------------------------------------+------------------------
 app    | postgres | postgres=UC/postgres                  +| 应用主模式
        |          | app_user=UC/postgres                  +|
        |          | readonly_role=U/postgres               |
 audit  | postgres |                                        |
 public | pg_database_owner | pg_database_owner=UC/pg_database_owner+|
        |          | =U/pg_database_owner                   |
(3 rows)

ACL 缩写含义对照：

缩写权限适用对象类型
UUSAGE模式、序列、类型、语言
CCREATE模式、表空间、数据库
rSELECT表、序列
aINSERT表
wUPDATE表
dDELETE表
DTRUNCATE表
xREFERENCES表
tTRIGGER表
sql
-- 程序化查看模式权限
SELECT
  n.nspname                    AS schema_name,
  pg_get_userbyid(n.nspowner)  AS owner,
  n.nspacl                     AS acl
FROM pg_namespace n
WHERE n.nspname NOT LIKE 'pg\_%'
  AND n.nspname <> 'information_schema'
ORDER BY n.nspname;

跨模式访问与全限定名 ​

访问别的模式的对象有两条路：

sql
-- 方式一：全限定名（推荐用于 DDL 与跨域引用）
SELECT * FROM sales.orders WHERE amount > 100;

-- 方式二：把对方模式放进搜索路径（适合应用代码的 DML）
SET search_path TO app, core, public;
SELECT * FROM orders JOIN tenants ON tenants.id = orders.tenant_id;

全限定名在以下几处必须使用：

场景原因
CREATE TABLE 等 DDL搜索路径可能被连接参数改变，避免建错地方
外键引用REFERENCES core.tenants (id) 让依赖关系显式
函数与触发器内函数有自己的 search_path 快照，容易与调用方不一致
迁移脚本需要可复现，不能依赖运行环境
权限语句GRANT ... ON SCHEMA app 必须明确指出模式
sql
-- 推荐的跨模式建表写法：全部写全名
CREATE TABLE biz.orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  bigint      NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  product_id bigint      NOT NULL REFERENCES biz.products (id),
  qty        integer     NOT NULL CHECK (qty > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

跨模式 JOIN 与同模式无异：

sql
SELECT
  o.id,
  t.name  AS tenant_name,
  p.name  AS product_name,
  o.qty
FROM biz.orders o
JOIN core.tenants  t ON t.id = o.tenant_id
JOIN biz.products  p ON p.id = o.product_id
ORDER BY o.id;
text
 id | tenant_name | product_name | qty
----+-------------+--------------+-----
  1 | 蓝海科技     | 机械键盘      |   2
  2 | 蓝海科技     | 无线鼠标      |  10
  3 | 星辰教育     | 显示器支架    |   1
(3 rows)

用模式做多租户：方案与取舍 ​

多租户（Multi-Tenant）有三种主流方案，模式隔离是中间那一档：

方案隔离强度租户数量上限跨租户统计迁移复杂度典型场景
共享表 + tenant_id 列弱（靠 RLS 或代码）无上限容易低SaaS，租户多、数据量小
一租户一模式中几十~几千需 UNION中租户少而重、需独立备份
一租户一数据库强几十很难高强合规、需独立连接
一租户一实例最强很少不可能最高金融、大型企业专属

模式隔离的落地方式：

sql
-- 每个租户一个模式，结构完全相同
CREATE SCHEMA tenant_1001;
CREATE SCHEMA tenant_1002;

CREATE TABLE tenant_1001.users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
);

-- 用 LIKE 把结构复制到另一个租户，避免手写两遍
CREATE TABLE tenant_1002.users (LIKE tenant_1001.users INCLUDING ALL);
sql
-- 应用侧把租户 ID 映射到 search_path
SET search_path TO tenant_1001, public;
SELECT * FROM users;      -- 只会命中该租户的数据
text
 id |        email
----+----------------------
  1 | alice@tenant1001.com
(1 row)

模式隔离的真实代价（务必评估后再选）：

问题表现缓解方式
对象数量爆炸1000 租户 × 20 表 = 20000 张表，pg_class 变大，VACUUM 与元数据查询变慢限制租户数，或改用 tenant_id 方案
连接池易踩坑search_path 是会话级状态，连接归还池中未重置就会串租户连接池设 reset 语句，或用 SET LOCAL + 事务
统计查询繁琐需要拼 UNION ALL 或逐个模式查用 \gexec 或动态 SQL 生成
迁移要逐模式执行DDL 变更必须在每个模式上跑一遍写循环脚本，或用 event trigger
"$user" 引发串模式若租户模式名恰好等于登录角色名，会优先命中不要用角色名做模式名
无法整体约束跨租户唯一性（如邮箱全局唯一）需要额外的共享表在 public 放全局索引表

连接池串租户是多租户方案里最危险的问题，下面演示成因：

sql
-- 错误的做法：会话级 SET，连接归还池后可能残留
SET search_path TO tenant_1001, public;
-- ... 业务查询 ...
-- 连接归还连接池，search_path 仍是 tenant_1001

-- 下一个请求属于 tenant_1002，但从池中拿到这条连接
SELECT * FROM users;      -- 错误！读到了 tenant_1001 的数据
sql
-- 正确做法一：用事务级 SET LOCAL，事务结束自动恢复
BEGIN;
SET LOCAL search_path TO tenant_1001, public;
SELECT * FROM users;
COMMIT;                    -- search_path 自动还原

-- 正确做法二：每次连接初始化时显式复写
SET search_path TO tenant_1002, public;
text
-- 连接池（PgBouncer / HikariCP）配置建议
server_reset_query = DISCARD ALL

DISCARD ALL 会重置包括 search_path 在内的所有会话状态，是连接池的安全底线。

实战示例 ​

场景：一个 SaaS 系统采用「按业务域分模式 + public 收紧权限」的组织方式，并完成权限初始化。

第 1 步：规划模式

模式归属说明
core平台团队租户、用户、角色
biz业务团队商品、订单、支付
report数据团队报表视图、物化视图
stagingETL导入中间表，可随时清空
public—不放置业务对象，仅作兜底

第 2 步：创建模式并设定属主

sql
CREATE SCHEMA IF NOT EXISTS core    AUTHORIZATION app_owner;
CREATE SCHEMA IF NOT EXISTS biz     AUTHORIZATION app_owner;
CREATE SCHEMA IF NOT EXISTS report  AUTHORIZATION bi_owner;
CREATE SCHEMA IF NOT EXISTS staging AUTHORIZATION etl_user;
text
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA
CREATE SCHEMA

第 3 步：建表（全部使用全限定名）

sql
CREATE TABLE core.tenants (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name       text        NOT NULL,
  plan       text        NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'ent')),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE core.users (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  bigint      NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  email      text        NOT NULL,
  status     text        NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'disabled')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email)
);

CREATE TABLE biz.products (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint        NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  sku       text          NOT NULL,
  name      text          NOT NULL,
  price     numeric(10, 2) NOT NULL CHECK (price >= 0),
  UNIQUE (tenant_id, sku)
);

CREATE TABLE biz.orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  bigint        NOT NULL REFERENCES core.tenants (id) ON DELETE CASCADE,
  user_id    bigint        NOT NULL REFERENCES core.users (id),
  product_id bigint        NOT NULL REFERENCES biz.products (id),
  qty        integer       NOT NULL CHECK (qty > 0),
  amount     numeric(10, 2) NOT NULL CHECK (amount >= 0),
  created_at timestamptz   NOT NULL DEFAULT now()
);

第 4 步：写入演示数据

sql
INSERT INTO core.tenants (name, plan) VALUES
  ('蓝海科技', 'pro'),
  ('星辰教育', 'free');

INSERT INTO core.users (tenant_id, email) VALUES
  (1, 'alice@lanhai.com'),
  (1, 'bob@lanhai.com'),
  (2, 'carol@xingchen.com');

INSERT INTO biz.products (tenant_id, sku, name, price) VALUES
  (1, 'KB-001', '机械键盘',   349.00),
  (1, 'MS-002', '无线鼠标',   129.00),
  (2, 'ST-003', '显示器支架', 259.00);

INSERT INTO biz.orders (tenant_id, user_id, product_id, qty, amount) VALUES
  (1, 1, 1,  2, 698.00),
  (1, 1, 2, 10, 1290.00),
  (2, 3, 3,  1, 259.00);
text
INSERT 0 2
INSERT 0 3
INSERT 0 3
INSERT 0 3

第 5 步：创建报表视图（跨模式）

sql
CREATE VIEW report.v_tenant_sales AS
SELECT
  t.id                                  AS tenant_id,
  t.name                                AS tenant_name,
  count(o.id)                           AS order_count,
  coalesce(sum(o.amount), 0)            AS total_amount
FROM core.tenants t
LEFT JOIN biz.orders o ON o.tenant_id = t.id
GROUP BY t.id, t.name;
sql
SELECT * FROM report.v_tenant_sales ORDER BY total_amount DESC;
text
 tenant_id | tenant_name | order_count | total_amount
-----------+-------------+-------------+--------------
         1 | 蓝海科技     |           2 |      1988.00
         2 | 星辰教育     |           1 |       259.00
(2 rows)

第 6 步：初始化权限（先收紧，再按需放开）

sql
-- 6.1 收紧 public 模式：任何人都不能在这里建对象
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- 6.2 业务账号：core/biz 可读写，report 只读
GRANT USAGE, CREATE ON SCHEMA core, biz TO app_user;
GRANT USAGE          ON SCHEMA report   TO app_user;

-- 6.3 已有对象的权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA core, biz TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA core, biz TO app_user;

-- 6.4 未来对象的默认权限（关键，否则每次建表都要补 GRANT）
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA core, biz
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA core, biz
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- 6.5 只读账号
GRANT USAGE ON SCHEMA core, biz, report TO readonly_role;
GRANT SELECT ON ALL TABLES IN SCHEMA core, biz, report TO readonly_role;

第 7 步：固化数据库级搜索路径

sql
-- 让该库的所有新连接默认拥有正确的解析顺序
ALTER DATABASE demo SET search_path TO core, biz, public;

-- 验证（需重连后生效）
SHOW search_path;
SELECT current_schema();
text
    search_path
--------------------
 core, biz, public
(1 row)

 current_schema
----------------
 core
(1 row)

第 8 步：核对权限结果

sql
SELECT
  n.nspname                   AS schema_name,
  pg_get_userbyid(n.nspowner) AS owner,
  unnest(n.nspacl)            AS acl_entry
FROM pg_namespace n
WHERE n.nspname IN ('core', 'biz', 'report', 'public')
ORDER BY n.nspname;
text
 schema_name |  owner   |         acl_entry
-------------+----------+----------------------------
 biz         | app_owner| app_owner=UC/app_owner
 biz         | app_owner| app_user=UC/app_owner
 biz         | app_owner| readonly_role=U/app_owner
 core        | app_owner| app_owner=UC/app_owner
 core        | app_owner| app_user=UC/app_owner
 core        | app_owner| readonly_role=U/app_owner
 public      | pg_database_owner | pg_database_owner=UC/pg_database_owner
 report      | bi_owner | bi_owner=UC/bi_owner
 report      | bi_owner | app_user=U/bi_owner
 report      | bi_owner | readonly_role=U/bi_owner
(10 rows)

注意 public 模式只剩属主一条 ACL，PUBLIC 的 =UC/... 条目已消失——收紧成功。

第 9 步：清理演示对象

sql
DROP SCHEMA report CASCADE;
DROP SCHEMA staging CASCADE;
DROP SCHEMA core CASCADE;      -- 会连带删除依赖它的 biz 表
DROP SCHEMA biz  CASCADE;
text
DROP SCHEMA
DROP SCHEMA
NOTICE:  drop cascades to table core.tenants
NOTICE:  drop cascades to table core.users
DROP SCHEMA
NOTICE:  drop cascades to table biz.products
NOTICE:  drop cascades to table biz.orders
DROP SCHEMA

注意事项 ​

  • 删除模式前务必确认里面有什么。 DROP SCHEMA 非空时会报错，但 CASCADE 会静默删光一切——包括外模式依赖它的对象（上面的 NOTICE 就是级联提示）。
sql
-- 危险！public 模式里可能有全部业务表
-- DROP SCHEMA public CASCADE;

-- 安全做法：先查看
SELECT c.relname, c.relkind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'v', 'm')
ORDER BY c.relname;
  • DROP SCHEMA 需要属主或超级用户权限。 即使你有 USAGE 权限也不行。
sql
-- 错误！must be owner of schema app
DROP SCHEMA app;
  • public 模式从 PostgreSQL 15 起不再默认给 PUBLIC 授予 CREATE 权限。 依赖旧行为的迁移脚本需要显式补一条 GRANT CREATE ON SCHEMA public TO PUBLIC;，但强烈不建议这么做。
sql
-- PostgreSQL 15+：这已是默认状态
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 若确实需要旧行为（不推荐）
-- GRANT CREATE ON SCHEMA public TO PUBLIC;

  • search_path 是会话级状态，连接池下极易串数据。 多租户场景应使用 SET LOCAL（事务级）或给连接池配置 DISCARD ALL。

  • 不要把不受信任的用户放到搜索路径前面。 官方文档明确警告：如果 search_path 中包含普通用户可写模式的模式，攻击者可以创建同名函数来劫持系统函数调用（称为 search_path 注入）。安全函数与触发器应显式设置：

sql
-- 安全做法：函数内固定搜索路径，把 pg_catalog 显式放到最前
CREATE OR REPLACE FUNCTION public.safe_check(p text)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
BEGIN
  RETURN length(p) > 0;
END;
$$;
  • information_schema 是只读的，不要往里写东西。 它是 SQL 标准兼容层，里面全是视图。
sql
-- 错误！permission denied for schema information_schema
CREATE TABLE information_schema.my_t (...);

  • 模式名长度上限 63 字节，超出静默截断。

  • pg_temp 是隐式的临时模式。 每个会话有自己的临时模式，它在搜索路径中的位置可以显式指定；若未指定，默认排在最后（PostgreSQL 有特殊处理，避免临时表意外遮蔽正式表）。

  • 同名对象在不同模式共存时，权限独立。 给 USAGE 只意味着「能看见名字」，访问具体对象还需要该对象上的 SELECT/INSERT 等权限。

sql
-- 有 USAGE 无 SELECT：仍然读不了
GRANT USAGE ON SCHEMA core TO readonly_role;
SELECT * FROM core.tenants;
text
ERROR:  permission denied for table tenants
  • ALTER SCHEMA ... RENAME 不会自动更新视图、函数体里的字符串引用。 若视图定义里硬编码了模式名，必须重建视图。
sql
ALTER SCHEMA biz RENAME TO business;
-- 视图定义中的 'biz.products' 字符串不会变，需要重建

最佳实践 ​

  • 一个应用一组模式，按业务域划分（core / biz / report / staging），不要把表全堆在 public。
  • public 模式只保留扩展与全局对象，并撤销 PUBLIC 的 CREATE 权限。
sql
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_user;
  • 所有 DDL 使用全限定名，不依赖搜索路径：
sql
-- 不推荐
CREATE TABLE orders (id bigint);

-- 推荐
CREATE TABLE IF NOT EXISTS biz.orders (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY);
  • ALTER DEFAULT PRIVILEGES 与 GRANT 成对出现，否则新建对象会成为权限黑洞。
sql
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA biz
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
  • 给函数显式设置 search_path，尤其是 SECURITY DEFINER 函数：
sql
SET search_path = pg_catalog, pg_temp
  • 多租户优先考虑「共享表 + tenant_id + 行级安全（RLS）」，只在租户数少（几十个以内）、且确实需要独立备份/恢复时才用模式隔离。模式隔离是一种「用元数据换隔离」的折中，对象数量会随租户数线性膨胀。
sql
-- 共享表方案的租户隔离示例
ALTER TABLE biz.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_orders_tenant ON biz.orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

  • 连接池配置 DISCARD ALL 或在应用框架的「获取连接」钩子里重置会话状态，避免跨请求污染。

  • 用模式承载「灰度发布」：app_v1 / app_v2 两套结构并存，通过切换 search_path 实现秒级回滚。

sql
-- 发布新版本：先建 app_v2，造好数据，再切搜索路径
SET search_path TO app_v2, core, public;
-- 出问题立即切回
SET search_path TO app_v1, core, public;
  • 定期审计模式与对象归属，发现被误建的 public 对象：
sql
SELECT
  n.nspname AS schema_name,
  c.relname AS object_name,
  c.relkind AS kind
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relkind IN ('r', 'v', 'm', 'S')
ORDER BY c.relname;
  • 迁移脚本里用 CREATE SCHEMA IF NOT EXISTS 保持幂等，可重复执行。

下一节

继续学习：创建表（CREATE TABLE）

参考链接 ​