Postgres能否将search_path设置为多个schema?
Postgres search_path多schema设置不生效问题排查方案
问题根因与对应解决方法
- 同名对象优先级覆盖
Postgres的search_path按从左到右的顺序匹配对象,只要在靠前的SCHEMA1找到你要查询的表/视图,就会停止向后匹配,不会返回SCHEMA2的同名对象数据。
解决方法:- 查询时明确指定schema前缀:
SELECT * FROM SCHEMA2.target_table; - 调整search_path的优先级顺序:
SET search_path TO SCHEMA2,SCHEMA1;
- 查询时明确指定schema前缀:
- 缺少SCHEMA2的访问权限
当前登录用户如果没有SCHEMA2的USAGE权限,或者没有对应表的SELECT权限,Postgres会默认当做对象不存在处理,不会主动抛出权限错误。
解决方法:用schema所有者或超级用户执行授权语句:
-- 授予schema使用权限 GRANT USAGE ON SCHEMA SCHEMA2 TO current_login_user; -- 授予schema下所有表的查询权限 GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA2 TO current_login_user; -- 配置后续新建表的默认查询权限 ALTER DEFAULT PRIVILEGES IN SCHEMA SCHEMA2 GRANT SELECT ON TABLES TO current_login_user;
- search_path作用范围问题
你在begin开启的事务内执行的set search_path默认仅在当前事务内生效,事务提交/回滚后设置就会失效,事务外执行的查询不会用到该配置。
解决方法:- 要当前会话全局生效,在事务外执行设置语句:
SET search_path TO SCHEMA1,SCHEMA2; - 要永久对指定用户生效:
ALTER ROLE your_user_name SET search_path TO SCHEMA1,SCHEMA2; - 要永久对指定数据库所有用户生效:
ALTER DATABASE your_db_name SET search_path TO SCHEMA1,SCHEMA2;
- 要当前会话全局生效,在事务外执行设置语句:
- schema名称大小写匹配问题
Postgres默认会把未加双引号的SQL标识符转为小写,如果你创建SCHEMA时用了双引号指定大写名称(CREATE SCHEMA "SCHEMA2";),设置search_path时不加双引号会默认匹配小写的schema2,导致找不到目标schema。
解决方法:设置时给大写schema名加双引号:SET search_path TO "SCHEMA1","SCHEMA2";
内容的提问来源于stack exchange,提问作者kasmawati
相关产品推荐
相关产品推荐

