postgresqlpostgresql-10database-permissionsforeign-data-wrapper

Cannot access foreign table using Postgres FDW

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.

Solution

  • 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).