如何使用postgres_fdw在外部表上设置权限？

Question

源数据存储在数据库keys的public模式下的keys表中（参考pg文档: https://www.postgresql.org/docs/current/postgres-fdw.html)。

create table keys (
    id varchar not null,
    keyname varchar not null,
    created timestamp default current_timestamp not null,
    modified timestamp default current_timestamp not null
);

参考用户/模式/数据库是vids/public/vids。

1. 建立服务器连接

CREATE SERVER keys
        FOREIGN DATA WRAPPER postgres_fdw
        OPTIONS (host '1.2.3.4', port '5432', dbname 'keys');

创建用户映射

CREATE USER MAPPING FOR vids
        SERVER keys
        OPTIONS (user 'keys', password 'keys');

3. 创建表格映射

create foreign table keys (
    id varchar not null,
    keyname varchar not null,
    created timestamp default current_timestamp not null,
    modified timestamp default current_timestamp not null
) server keys options (schema_name 'public', table_name 'keys');

尝试在连接到vids数据库时以vids身份访问外部表：

vids=> select * from keys;
ERROR:  permission denied for foreign table keys

我不明白，既然用户keys是外部数据库中keys表的所有者，那么这里应该做什么？

- WestCoastProjects

---

什么是"引用用户"? - jjanes

@jjanes 我在问题中明确说明了：用户/模式/数据库是_vids_/public/_vids_。 - WestCoastProjects

在这个过程的每一步中，仍然不清楚哪个用户登录到了哪个服务器上的哪个数据库。此外，您为clip创建了一个用户映射，但似乎在其他地方并没有使用该用户。 - jjanes

错误消息表明问题出在本地。外部表的本地表示不属于vids，而且vids没有权限访问它。因此，它从来没有足够的进展来确定keys是否可以访问外部的keys。 - jjanes

@jjanes 谢谢，我会检查本地表示的权限。clip 是一个打字错误，已经更正为 vids。 - WestCoastProjects

@jjanes 是的，将“所有”权限授予本地“用户映射”的“外部表”解决了权限问题。连接仍然无法正常工作，但这是我的责任。请随意[/鼓励]提供答案。 - WestCoastProjects

Answer 1

来自@jjanes的评论：

错误信息表明问题在本地端。外部表的本地表示由vids拥有，而vids没有权限访问它。因此它从来没有足够的机会去判断keys是否可以访问外部的keys

所以我需要更正的步骤是：

 grant all on table keys to clips;

- WestCoastProjects