PostgreSQL 11中使用dblink时如何撤销用户权限授权

问题原因 #

你遇到的情况大概率是以下两种原因之一：

1. 超级用户创建外部服务器/用户映射时，误将对象所有权转移给了普通用户；
2. 错误地给普通用户授予了DROP权限（尽管PostgreSQL默认不会为普通用户分配该权限）。

你之前执行的revoke grant option for all on foreign server my_server from not_my_role;无效，是因为ALL对于外部服务器仅指代USAGE权限，而GRANT OPTION控制的是用户能否将权限转授他人，和删除对象的权限无关。

正确操作步骤 #

1. 检查外部服务器的所有者与权限

首先确认目标外部服务器的所有者和已分配权限：

-- 查看外部服务器的所有者
SELECT srvname, rolname AS owner
FROM pg_foreign_server fs
JOIN pg_roles r ON fs.srvowner = r.oid
WHERE srvname = 'my_server';

-- 查看外部服务器的权限分配
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'my_server' AND table_type = 'FOREIGN SERVER';

2. 修正外部服务器权限/所有权

• 如果普通用户是服务器的所有者，将所有权转回超级用户：

  ALTER FOREIGN SERVER my_server OWNER TO 你的超级用户角色;
  

• 如果普通用户被显式授予了DROP权限，撤销该权限：

  REVOKE DROP ON FOREIGN SERVER my_server FROM not_my_role;
  

3. 检查并修正用户映射的所有权

用户映射的删除权限默认仅属于其创建者（所有者）或超级用户，若普通用户能删除映射，大概率是映射的所有者为该普通用户：

-- 查看用户映射的所有者
SELECT u.usename AS mapping_owner, fs.srvname
FROM pg_user_mapping um
JOIN pg_user u ON um.umuser = u.usesysid
JOIN pg_foreign_server fs ON um.umserver = fs.oid
WHERE fs.srvname = 'my_server';

若映射所有者为普通用户，将其转回超级用户：

ALTER USER MAPPING FOR 映射关联的用户名 SERVER my_server OWNER TO 你的超级用户角色;

4. 后续权限管控建议

创建外部服务器后，仅给需要使用dblink的用户授予必要的USAGE权限，避免过度授权：

GRANT USAGE ON FOREIGN SERVER my_server TO 需要访问的角色;

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