Postgres 14更新FDW角色时遇用户映射错误求助

核心问题分析 #

你遇到的错误user mapping not found for role1，结合role2的用户映射umoptions为NULL的现象，最可能的原因是用户映射创建时的服务器名称拼写错误，导致映射未关联到正确的FDW服务器；其次是用户映射未正确配置远程用户和密码参数。

具体排查与修复步骤 #

1. 检查服务器名称拼写 #

原SQL中的CREATE USER MAPPING语句存在拼写错误：SERVER <serevr_name>（多了一个字母r），与CREATE SERVER中的<server_name>不匹配。这会导致用户映射被创建到一个不存在的服务器上，而非你实际创建的FDW服务器。

验证映射关联的服务器

执行以下查询确认role2的映射是否关联到正确的服务器：

SELECT srvname, usename, umoptions 
FROM pg_user_mappings 
WHERE usename = 'role2';

如果srvname显示为<serevr_name>（带多余r），说明存在拼写错误。

修复拼写错误并重建映射

• 删除错误的用户映射：

DROP USER MAPPING IF EXISTS FOR role2 SERVER <serevr_name>;

• 重新创建正确的用户映射：

CREATE USER MAPPING IF NOT EXISTS
    FOR role2
    SERVER <server_name>
    OPTIONS (USER 'role2', PASSWORD '<password>');

2. 强制更新用户映射参数 #

如果CREATE USER MAPPING IF NOT EXISTS未覆盖已有映射（比如之前创建过无参数的映射），使用ALTER USER MAPPING直接更新参数：

ALTER USER MAPPING FOR role2 SERVER <server_name>
    OPTIONS (SET USER 'role2', SET PASSWORD '<password>');

3. 验证映射配置 #

再次执行查询确认umoptions已包含正确的用户和密码：

SELECT umoptions 
FROM pg_user_mappings 
WHERE srvname = '<server_name>' AND usename = 'role2';

结果应类似{user=role2, password=<password>}，而非NULL。

4. 确认权限配置 #

虽然role2已被授予rds_superuser，仍可补充必要权限确保访问正常：

-- 授予FDW服务器使用权限
GRANT USAGE ON FOREIGN SERVER <server_name> TO role2;
-- 授予外部模式下所有表的查询权限
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO role2;

5. 远程服务器权限检查 #

确保外部PostgreSQL服务器上的role2拥有<f_schema>下对应表的SELECT权限：

-- 在远程服务器执行
GRANT SELECT ON ALL TABLES IN SCHEMA <f_schema> TO role2;

测试验证 #

完成上述步骤后，以role2身份重新执行FDW查询，确认错误消失。

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