PostgreSQL / 系统课时

创建与管理模式

创建与管理模式

schema 既能把对象按业务边界分组,也能通过命名空间与权限控制减少相互干扰。本篇用一组可执行 SQL 创建两个 schema、放入同名表并验证隔离效果,再说明 search_path、权限和删除操作中最容易踩到的边界。

学习目标

  • 使用 SQL 创建、查看和删除 schema
  • 验证不同 schema 可以安全保存同名对象
  • 理解 USAGE、对象权限与 search_path 各自控制什么

创建独立命名空间

创建 schema 需要当前角色对数据库具备相应的 CREATE 权限。建议在练习数据库执行下面的示例;它创建 lesson_ns_app 与 lesson_ns_audit 两个 schema,并分别建立同名表 event_log。

sql
CREATE SCHEMA lesson_ns_app;
CREATE SCHEMA lesson_ns_audit;

CREATE TABLE lesson_ns_app.event_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message text NOT NULL
);

CREATE TABLE lesson_ns_audit.event_log (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message text NOT NULL
);

INSERT INTO lesson_ns_app.event_log (message) VALUES ('app event');
INSERT INTO lesson_ns_audit.event_log (message) VALUES ('audit event');

SELECT 'lesson_ns_app' AS schema_name, message
FROM lesson_ns_app.event_log
UNION ALL
SELECT 'lesson_ns_audit' AS schema_name, message
FROM lesson_ns_audit.event_log
ORDER BY schema_name;

预期输出

text
CREATE SCHEMA
CREATE SCHEMA
CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
    schema_name    |   message
-------------------+-------------
 lesson_ns_app     | app event
 lesson_ns_audit   | audit event
(2 rows)

两个 event_log 互不冲突,因为它们的完整名称不同。如果示例名称已经存在,CREATE SCHEMA 会报重复对象错误;先使用后文清理语句,或换一组练习专用名称。

控制未限定名称的解析

如果希望当前会话把 lesson_ns_app 作为默认 schema,可以调整 search_path。这只影响名称解析,不会移动对象。

sql
SET search_path TO lesson_ns_app, public;

SELECT current_schema() AS current_schema;
SELECT message FROM event_log;

预期输出

text
SET
 current_schema
-----------------
 lesson_ns_app
(1 row)

  message
-----------
 app event
(1 row)

此时未限定的 event_log 会优先解析为 lesson_ns_app.event_log。在共享环境中,不要依赖不可控的会话默认值;迁移脚本、后台任务和权限敏感 SQL 更适合显式写出 schema 限定名。

把 schema 权限和对象权限分开

schema 上的 USAGE 允许角色通过该命名空间访问其中对象的名称,但不会自动授予表的 SELECT、INSERT 等权限;CREATE 则决定角色能否在该 schema 中创建新对象。实际授权时通常要同时考虑 schema 权限与对象权限。

下面是片段示例,依赖前提是 lesson_reader 角色已经存在,并由有权授权当前表且负责创建后续表的对象所有者执行。

sql
GRANT USAGE ON SCHEMA lesson_ns_app TO lesson_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA lesson_ns_app TO lesson_reader;

ALTER DEFAULT PRIVILEGES IN SCHEMA lesson_ns_app
GRANT SELECT ON TABLES TO lesson_reader;

预期输出

text
GRANT
GRANT
ALTER DEFAULT PRIVILEGES

默认权限只影响执行该语句的创建角色以后创建的对象。如果未来由其他角色建表,需要针对那个创建角色配置相应的默认权限;已经存在的表仍需单独授权。

安全删除和清理 schema

空 schema 可以用 DROP SCHEMA lesson_ns_app; 删除;只要其中仍有表、视图等依赖对象,PostgreSQL 就会拒绝。CASCADE 会连同依赖对象一起删除,因此必须先确认对象清单。

练习结束后,可以清理本篇创建的两个专用 schema。

sql
DROP SCHEMA lesson_ns_app CASCADE;
DROP SCHEMA lesson_ns_audit CASCADE;

预期输出

text
DROP SCHEMA
DROP SCHEMA

客户端可能额外显示 NOTICE,提示被级联删除的对象,具体行数取决于对象数量。实际项目中,schema 名称应表达稳定的业务边界,并把对象所有权、USAGE、对象权限和默认权限一并设计,而不是只靠命名实现隔离。

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

目录与阅读设置

阅读设置

17

记录一个想法

创建与管理模式

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

登录哪都通