使用postgres超级用户创建FDW映射只读远程用户仍可新建外部表问题

问题原因 #

创建外部表未触发权限校验是因为PostgreSQL权限校验分两个阶段，你预期的远端权限校验不会在本地创建对象阶段执行：

• 本地创建外部表属于本地数据库对象操作，只要本地用户拥有本地目标schema的CREATE权限+对应FOREIGN SERVER的USAGE权限即可完成创建。你使用的本地postgres是超级用户，天然拥有所有权限，所以创建操作必然成功，这个阶段不会主动和远端数据库交互，不会校验映射用户的远端权限。
• 远端用户的权限只有在你对外部表执行实际操作（SELECT/INSERT/UPDATE/DELETE等）时才会校验：postgres_fdw会通过用户映射的凭据连接远端数据库，这时候如果只读用户没有对应权限才会返回错误，你可以尝试查询新建的外部表验证这个逻辑。

可选配置调整 #

如果你需要在创建外部表阶段就触发权限校验，可做以下配置：

1. 开启SERVER的远端校验开关
   开启后创建外部表、导入外部schema时都会主动连接远端校验对象存在性、映射用户权限，不符合要求会直接在创建阶段报错：

ALTER SERVER testfdw OPTIONS (ADD validate 'true');

如果是新建SERVER可以直接在CREATE SERVER语句中添加该参数。

2. 限制本地用户的SERVER使用权限
   默认创建的SERVER会给所有PUBLIC用户开放USAGE权限，你可以回收公共权限，仅授权给指定的本地用户：

-- 回收默认公共权限
REVOKE ALL ON SERVER testfdw FROM PUBLIC;
-- 仅给指定本地用户授权使用该SERVER创建外部表
GRANT USAGE ON SERVER testfdw TO 指定本地用户名;

注意该权限限制对超级用户不生效，超级用户会绕过所有权限校验，建议日常操作不要使用超级用户账号。

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