PostgreSQL / 系统课时
创建与管理模式
创建与管理模式
schema 既能把对象按业务边界分组,也能通过命名空间与权限控制减少相互干扰。本篇用一组可执行 SQL 创建两个 schema、放入同名表并验证隔离效果,再说明 search_path、权限和删除操作中最容易踩到的边界。
学习目标
- 使用 SQL 创建、查看和删除 schema
- 验证不同 schema 可以安全保存同名对象
- 理解
USAGE、对象权限与search_path各自控制什么
创建独立命名空间
创建 schema 需要当前角色对数据库具备相应的 CREATE 权限。建议在练习数据库执行下面的示例;它创建 lesson_ns_app 与 lesson_ns_audit 两个 schema,并分别建立同名表 event_log。
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;预期输出
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。这只影响名称解析,不会移动对象。
SET search_path TO lesson_ns_app, public;
SELECT current_schema() AS current_schema;
SELECT message FROM event_log;预期输出
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 角色已经存在,并由有权授权当前表且负责创建后续表的对象所有者执行。
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;预期输出
GRANT
GRANT
ALTER DEFAULT PRIVILEGES默认权限只影响执行该语句的创建角色以后创建的对象。如果未来由其他角色建表,需要针对那个创建角色配置相应的默认权限;已经存在的表仍需单独授权。
安全删除和清理 schema
空 schema 可以用 DROP SCHEMA lesson_ns_app; 删除;只要其中仍有表、视图等依赖对象,PostgreSQL 就会拒绝。CASCADE 会连同依赖对象一起删除,因此必须先确认对象清单。
练习结束后,可以清理本篇创建的两个专用 schema。
DROP SCHEMA lesson_ns_app CASCADE;
DROP SCHEMA lesson_ns_audit CASCADE;预期输出
DROP SCHEMA
DROP SCHEMA客户端可能额外显示 NOTICE,提示被级联删除的对象,具体行数取决于对象数量。实际项目中,schema 名称应表达稳定的业务边界,并把对象所有权、USAGE、对象权限和默认权限一并设计,而不是只靠命名实现隔离。