Cannot access foreign table using Postgres FDW

Question

I had a foreign table set up in Postgres 10. The role "role1" has been granted usage on the foreign server (fs) that was set up using the postgres superuser.

I imported the table using the import schema command:

IMPORT FOREIGN SCHEMA f_schema LIMIT TO (my_fdw_table) FROM fs INTO ls;

That worked fine.

However, when I try to query the table I get the following error:

SELECT * FROM my_fdw_table LIMIT 1;
ERROR:  permission denied for view my_fdw_table
CONTEXT:  remote SQL command: ...

My understanding is that FDW should treat views and tables the same.

score 2 · Accepted Answer · answered Feb 08 '19 at 07:23

2

It looks like the remote user that you used in the user mapping for your local user and the foreign server does not have the required permissions on the table (or the schema that contains it).

answered Feb 08 '19 at 07:23

Laurenz Albe

209,280
17
206
263

score 0 · Answer 2 · answered Sep 25 '20 at 01:31

User "role1" should create user mapping for itself like:

CREATE USER MAPPING FOR role1 SERVER fs OPTIONS (USER 'role1', PASSWORD 'password1');
IMPORT FOREIGN SCHEMA f_schema LIMIT TO (my_fdw_table) FROM SERVER fs INTO ls;

Also, if "role1" is not an owner of the database, it should get access from its owner:

GRANT USAGE ON SCHEMA ls TO role1;

Assuming ls is local schema.

Cannot access foreign table using Postgres FDW

2 Answers2