PostgreSQL用户无权限查询jiaofayuan模式表的问题排查

环境与操作背景 #

• 系统：Debian 10
• PostgreSQL版本：16.2
• 服务器IP：192.168.208.221，本地PC IP：192.168.208.12，pgAdmin4部署在服务器上，pg_hba.conf已配置访问权限

以postgres用户登录psql执行了以下操作：

create database pgtest;
create schema jiaofayuan;
create user pguser with password 'Adminadmin';
grant all privileges on database pgtest,pgtest2 to pguser;
grant all privileges on all tables in schema public,jiaofayuan to pguser;

后续发现pguser可正常执行select * from public.cities;，但执行select * from jiaofayuan.city;时提示无权限。已切换到pgtest库重新执行授权：

postgres=# \c pgtest;
You are now connected to database "pgtest" as user "postgres".
pgtest=# grant all privileges on all tables in schema public,jiaofayuan to pguser;
GRANT;

权限查询结果显示pguser对jiaofayuan模式下的表拥有完整权限：

pgtest=# SELECT grantee, privilege_type FROM information_schema.table_privileges WHERE grantee = 'pguser' AND table_schema = 'jiaofayuan';
 grantee | privilege_type
---------+----------------
 pguser  | INSERT
 pguser  | SELECT
 pguser  | UPDATE
 pguser  | DELETE
 pguser  | TRUNCATE
 pguser  | REFERENCES
 pguser  | TRIGGER
(7 rows)

public模式下的表权限也正常：

pgtest=# SELECT grantee, privilege_type FROM information_schema.table_privileges WHERE grantee = 'pguser' AND table_schema = 'public';
 grantee | privilege_type
---------+----------------
 pguser  | INSERT
 pguser  | SELECT
 pguser  | UPDATE
 pguser  | DELETE
 pguser  | TRUNCATE
 pguser  | REFERENCES
 pguser  | TRIGGER
 pguser  | INSERT
 pguser  | SELECT
 pguser  | UPDATE
 pguser  | DELETE
 pguser  | TRUNCATE
 pguser  | REFERENCES
 pguser  | TRIGGER
(14 rows)

问题 #

为何pguser有权限访问public.cities，却无法访问jiaofayuan.city？是否存在配置遗漏？

核心原因：缺失模式的USAGE权限 #

虽然已为pguser授予了jiaofayuan模式下所有表的全权限，但未授予用户对jiaofayuan模式本身的USAGE（使用）权限。PostgreSQL的权限逻辑中，用户要访问模式内的对象，必须先拥有该模式的USAGE权限，否则即便表权限齐全也无法访问。

默认情况下，public模式会自动给所有用户授予USAGE权限，这也是pguser能正常访问public.cities的原因。

解决步骤 #

1. 切换到pgtest数据库（若当前未在该库中）：

\c pgtest;

2. 授予pguser对jiaofayuan模式的USAGE权限，若需要让用户在该模式下创建对象，可同时加上CREATE权限：

grant usage on schema jiaofayuan to pguser;
-- 如需创建对象权限，执行以下命令
grant create on schema jiaofayuan to pguser;

3. 验证：重新以pguser身份登录，执行select * from jiaofayuan.city;即可正常访问。

补充：默认权限配置（可选） #

如果后续需要在jiaofayuan模式下新建表时，自动给pguser授予权限，可设置模式的默认权限：

alter default privileges in schema jiaofayuan grant all privileges on tables to pguser;

此配置会让后续在该模式下创建的所有表自动继承给pguser的权限，无需每次手动授权。

内容的提问来源于stack exchange，提问作者Aragorn1026