吃透 PostgreSQL 权限管理 —— 从用户创建到显式授权全攻略

原文：https://cloud.tencent.com/developer/article/2705868

摘要

深入拆解 PostgreSQL 用户权限体系，手把手教你正确授权数据库 / Schema，彻底解决使用中常见的权限不足问题（如 42501 报错）。

一、核心认知：PostgreSQL 用户与权限的底层逻辑

1.1 先搞懂：用户（Role）到底是什么？

PostgreSQL 里”用户”和”角色”本质是同一个东西（ROLE），区别仅在于是否有登录权限：

• ✨ 超级用户：默认 postgres，拥有所有权限，能创建 / 删除用户、数据库，执行任意操作；
• 🔍 普通用户：如 admin，无任何默认权限，所有操作都需要授权；
• 📌 关键属性：数据库、表、模式等对象都有”所有者”，所有者默认拥有该对象的全部权限（优先级高于授权）。

1.2 核心：权限是分层的，无法”向下覆盖”

PostgreSQL 采用「最小粒度、分层独立控制」模型，每一层权限只管自己的范围，类比成”进大楼”更好理解：

1.3 新手必区分：易混淆的权限关键词

• ❌ DELETE：删表数据（行级操作），属于数据权限；
• ❌ DROP：删表本身（结构操作），属于管理权限；
• ✅ USAGE：模式 / 序列的基础访问权（没有它，连表都看不到）；
• 🚨 ALL PRIVILEGES：看似包含所有权限，但部分场景（如 DROP）可能不生效。

二、实操：数据库与 Schema 的正确授权流程

2.1 前置：创建用户和数据库（postgres 超级用户执行）

-- 1. 创建带登录权限的普通用户（替换密码）
CREATE ROLE admin WITH LOGIN PASSWORD 'your_secure_password';

-- 2. 创建目标数据库（指定编码避免中文乱码）
CREATE DATABASE datareport
  OWNER postgres
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  LC_CTYPE 'zh_CN.UTF-8';

ERROR:  new collation (zh_CN.UTF-8) is incompatible with the collation of the template database (C)
HINT:  Use the same collation as in the template database, or use template0 as template.

CREATE DATABASE datareport
  OWNER postgres
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  LC_CTYPE 'zh_CN.UTF-8'
  TEMPLATE template0;

执行前先确认两件事

(1). OS 侧 locale 是否真的存在（这是个常见的第二步报错）

locale -a | grep -i zh_CN
# 期望看到 zh_CN.utf8

若没有：

• RHEL 8/9 系：dnf install glibc-langpack-zh
• 老发行版：localedef -i zh_CN -f UTF-8 zh_CN.UTF-8

否则即使改用 template0，也会报 ERROR: invalid locale name: "zh_CN.UTF-8"。

(2). 集群现有状态

SELECT datname, datcollate, datctype, datlocprovider,
       pg_encoding_to_char(encoding) AS enc
FROM pg_database ORDER BY datname;

SELECT collname, collprovider FROM pg_collation
WHERE collname LIKE 'zh%' OR collname LIKE 'zh_CN%';

第二条用来确认 pg_collation 里是否有系统 locale 的条目。如果集群是 --locale=C 初始化的，系统 locale 可能没被导入，后面查询里写 COLLATE "zh_CN.UTF-8" 仍然找不到 —— 补一句 SELECT pg_import_system_collations('pg_catalog'); 即可。

更推荐的做法：PG 15+ 用 ICU

libc 的 zh_CN.UTF-8 对中文和英文混排的排序效果一般（拼音序不稳定，且各平台 glibc 版本差异会导致同一份数据在不同机器上排序结果不一致）。如果版本够，建议这样建库：

-- 1. 创建带登录权限的普通用户（替换密码）
CREATE ROLE admin WITH LOGIN PASSWORD 'Pgsql_2026';

-- 2. 创建目标数据库（指定编码避免中文乱码）
CREATE DATABASE datareport
  OWNER postgres
  ENCODING 'UTF8'
  LOCALE_PROVIDER icu
  ICU_LOCALE 'zh-CN'
  LC_COLLATE 'C'
  LC_CTYPE 'C'
  TEMPLATE template0;

ICU 是内嵌的，不依赖 OS 安装 langpack，跨平台一致性好。PG 16 起还支持 ICU_RULES 自定义排序规则。列级也可以先用 COLLATE "zh-Hans-x-icu"。

几个容易出错的地方

• template1 的 collation 改不了，所以凡是需要中文排序的库，建库语句里必须显式带上 TEMPLATE template0，不要指望模板库以后被修正。
• LC_CTYPE 影响的不只是排序，还包括 upper()、lower()、~ 正则的字符类、pg_trgm 的分词切分。设成 C 时，中文全角的 ～、ＡＢＣ 这类字符的大小写转换会失效。
• ENCODING 最好显式写，不要依赖 template1 的默认编码；从 template0 建库时，如果编码和 LC_* 不匹配（比如 SQL_ASCII），会静默出问题。
• 已有数据要迁移的话，不要 ALTER DATABASE 去改 collation（PG 不支持），而是用 pg_dump | psql 重建；排序结果会变化，凡是依赖 ORDER BY 结果的索引需要重建（REINDEX DATABASE datareport），否则索引里的物理顺序和新的比较函数不一致，可能返回错误结果。
• 建完之后验证：SELECT datcollate, datctype FROM pg_database WHERE datname='datareport'; 以及连进去跑 SHOW lc_collate;。

2.2 分层授权（核心步骤，一步都不能少）

步骤 1：授予数据库级权限（基础连接权）

-- 授予数据库连接、创建临时表权限
GRANT ALL PRIVILEGES ON DATABASE datareport TO admin;

💡 作用：允许 admin 连接数据库；局限：无法访问库内的模式和表。

步骤 2：切换到目标数据库（必做！）

-- 所有模式/表授权必须在这个上下文执行，否则白做
\c datareport

-- 建模式
CREATE SCHEMA IF NOT EXISTS datareport AUTHORIZATION admin;

步骤 3：授予 Schema 级权限（访问模式权）

-- 授予模式的使用、创建、删除权限(drop权限非法)
GRANT USAGE, CREATE, DROP ON SCHEMA datareport TO admin;

-- 授予模式的使用、创建权限
GRANT USAGE, CREATE ON SCHEMA datareport TO admin;

💡 作用：能看到模式下的表；局限：仍无法操作表内数据。

步骤 4：授予表级全量权限（核心！解决查数据 / 删表问题）

-- 显式授予所有操作权限（重点包含 DROP）
GRANT SELECT, INSERT, UPDATE, DELETE, DROP, ALTER
  ON ALL TABLES IN SCHEMA datareport TO admin;

步骤 5：授予序列权限（自增字段必备）

-- 避免插入自增字段时报错
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA datareport TO admin;

步骤 6：设置未来对象默认权限（优化项）

-- 未来新建表自动获权，不用重复授权
ALTER DEFAULT PRIVILEGES IN SCHEMA datareport
  GRANT SELECT, INSERT, UPDATE, DELETE, DROP ON TABLES TO admin;

-- 未来新建序列自动获权
ALTER DEFAULT PRIVILEGES IN SCHEMA datareport
  GRANT ALL PRIVILEGES ON SEQUENCES TO admin;

2.3 兜底方案：修改对象所有者（授权失效时用）

如果授权后仍报权限不足，大概率是表的所有者还是 postgres：

-- 方式1：修改单张表所有者（替换表名）
ALTER TABLE datareport.目标表名 OWNER TO admin;

-- 方式2：批量修改所有表所有者（推荐）
DO $$
DECLARE
  tbl RECORD;
BEGIN
  FOR tbl IN
    SELECT table_name FROM information_schema.tables
    WHERE table_schema = 'datareport' AND table_type = 'BASE TABLE'
  LOOP
    EXECUTE 'ALTER TABLE datareport.' || quote_ident(tbl.table_name) || ' OWNER TO admin;';
  END LOOP;
END $$;

2.4 验证授权是否生效（必做！）

-- 1. 切换到 admin 用户
SET ROLE admin;

-- 2. 验证连接数据库
\c datareport

-- 3. 验证查询数据（替换表名）
SELECT * FROM datareport.目标表名 LIMIT 1;

-- 4. 验证删除表（谨慎！先备份）
-- DROP TABLE IF EXISTS datareport.目标表名;

-- 5. 恢复原用户
RESET ROLE;

2.5 通俗理解权限分层

• 只做前 3 步 = 有大楼门禁卡 + 电梯权限，但没有房间钥匙 → 能进大楼、进楼层，却打不开存放数据的”房间（表）”；
• 做完第 4 步 = 拿到房间钥匙 → 能正常查数据、操作表；
• 加做第 5 步 = 拿到房间里的工具 → 能正常使用自增字段。

实际例子：假设 datareport 模式下有 user_info 表：

• 仅前 3 步：admin 能看到表，但执行 SELECT * FROM user_info 报 42501；
• 做完第 4 步：查询正常；
• 表有自增主键：仅第 4 步会报序列权限不足，必须加做第 5 步。

三、进阶：显式授予权限，精准管控不踩坑

3.1 什么是显式授权？

不依赖 ALL PRIVILEGES 通配，精准指定需要的权限（如只授 SELECT、只授 DROP），核心适用场景：

1. 生产环境精细化管控（如只读用户只给 SELECT）；
2. 解决 DROP 等权限通配授权失效问题；
3. 排查权限问题（定位具体缺什么权限）。

3.2 核心示例

示例 1：只读用户授权（最常用）

\c datareport

-- 仅授 Schema 使用权限
GRANT USAGE ON SCHEMA datareport TO read_only_user;

-- 仅授查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA datareport TO read_only_user;

-- 未来新建表自动只读
ALTER DEFAULT PRIVILEGES IN SCHEMA datareport
  GRANT SELECT ON TABLES TO read_only_user;

示例 2：解决删除表 42501 报错

\c datareport

-- 显式授 DROP 权限
GRANT DROP ON ALL TABLES IN SCHEMA datareport TO admin;

-- 未来新建表自动有 DROP 权限
ALTER DEFAULT PRIVILEGES IN SCHEMA datareport
  GRANT DROP ON TABLES TO admin;

示例 3：自增字段序列授权

-- 仅授必要权限，不赋多余权限
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA datareport TO admin;

3.3 显式授权 vs 通配授权

3.4 注意事项

1. 📍 上下文生效：所有授权必须在目标数据库（\c 库名）执行；
2. 🔤 大小写敏感：表 / 模式名用双引号创建的（如 "DataReport"），授权时要保持一致；
3. ✅ 权限校验：快速查权限是否生效

-- 查 DROP 权限
SELECT has_table_privilege('admin', 'datareport.目标表名', 'drop') AS has_drop_priv;

-- 查 SELECT 权限
SELECT has_table_privilege('admin', 'datareport.目标表名', 'select') AS has_select_priv;

四、核心总结

1. 🪜 权限分层是基础：必须按「数据库 → Schema → 表 / 序列」逐层授权，上层权限覆盖不了下层；
2. 🎯 显式授权更可靠：DROP 等结构操作权限，显式授权比通配更稳；
3. 🏆 所有者是兜底：授权失效时，优先改表 / 模式的所有者为目标用户，这是解决 42501 最彻底的办法。

📝 生产环境完整授权脚本（直接复用）

-- 0) 建角色
CREATE ROLE admin WITH LOGIN PASSWORD 'your_secure_password';

-- 1) 建库（显式 owner 建议给 admin，避免后面两套 owner 打架）
CREATE DATABASE datareport
  OWNER admin
  ENCODING 'UTF8'
  TEMPLATE template0;

\c datareport
\set ON_ERROR_STOP on

-- 2) 建 schema
CREATE SCHEMA IF NOT EXISTS datareport AUTHORIZATION admin;

-- 3) 库级权限
GRANT CONNECT, CREATE, TEMPORARY ON DATABASE datareport TO admin;

-- 4) schema 级权限
GRANT USAGE, CREATE ON SCHEMA datareport TO admin;
-- 若还要在 public 里建对象（PG15+ 必须显式给）
GRANT USAGE, CREATE ON SCHEMA public TO admin;

-- 5) 既有对象（去掉非法的 DROP）
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
  ON ALL TABLES IN SCHEMA datareport TO admin;
GRANT USAGE, SELECT, UPDATE
  ON ALL SEQUENCES IN SCHEMA datareport TO admin;

-- 6) 未来对象：按“谁建表”选一条，别两条都当保险
-- 6a) 若对象由 postgres（或其他角色）创建，授权给 admin：
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA datareport
  GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO admin;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA datareport
  GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO admin;

-- 6b) 若对象由 admin 自己创建：什么都不用做，owner 天然拥有全部权限

-- 7) 批量改所有者（用 pg_class 而非 information_schema，不受权限过滤影响）
DO $$
DECLARE
  r RECORD;
BEGIN
  FOR r IN
    SELECT n.nspname, c.relname, c.relkind
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'datareport'
      AND c.relkind IN ('r','p','S')          -- 普通表 / 分区父表 / 序列
  LOOP
    IF r.relkind = 'S' THEN
      EXECUTE format('ALTER SEQUENCE %I.%I OWNER TO admin;', r.nspname, r.relname);
    ELSE
      EXECUTE format('ALTER TABLE %I.%I OWNER TO admin;', r.nspname, r.relname);
    END IF;
  END LOOP;
END $$;

🌟 小贴士：PostgreSQL 权限管理的核心是”最小权限原则”，生产环境尽量只授必要权限，既安全又能减少权限冲突问题。

## 跑完怎么验证（不要只看 "没报错"）

```
-- schema 是否存在、owner 是谁
SELECT nspname, pg_get_userbyid(nspowner) AS owner
FROM pg_namespace WHERE nspname = 'datareport';

-- admin 实际拿到的权限（逐表核对比 GRANT 语句本身更可信）
SELECT table_schema, table_name, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE grantee = 'admin' AND table_schema = 'datareport'
ORDER BY table_name, privilege_type;

-- 默认权限是否真的挂上了
SELECT pg_get_userbyid(defaclrole) AS for_role, defaclnamespace::regnamespace AS in_schema, defaclobjtype, defaclacl
FROM pg_default_acl;
```

一句话总结：**`CREATE DATABASE` 不产生同名 schema；`GRANT ON SCHEMA` 必须在 schema 落地之后，且 `\set ON_ERROR_STOP on` 否则你永远不知道它其实没生效。**

本文由 ByteHouse 原创发布，转载请注明出处。