permission denied for view my_view in postgres_fdw

Question

I have 2 databases db1 & db2, I created postgres_fdw into db1 & db 2 with:

CREATE EXTENSION postgres_fdw;

I configured server and user mapping into db2 :

CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 
'my_host', port '5432', dbname 'db1');
CREATE USER MAPPING FOR postgres SERVER remote_server OPTIONS (user 'my_user', 
password 'my_pass');

and Import to my db2 :

IMPORT FOREIGN SCHEMA public LIMIT TO (my_view) FROM SERVER remote_server 
INTO public;

All configuration is correct and my view imported succesfully to foreign table but i get :

*ERROR: permission denied for view my_view

by the way i tried :

GRANT SELECT ON my_view TO postgres;

but i still get same error how can i fix that?

Answer 1

The connection to db1 is made with the user you specify in the user mapping, which is my_user. So you need to grant that privilege to that user:

GRANT SELECT ON my_view TO my_user;

• Mohammadreza Ataei: as i mention i used that before but i still have error, how can i fix?
• Laurenz Albe: No, you didn't mention that at all. You mentioned that you granted the privilege to user postgres, which is different.
• Mohammadreza Ataei: Thanks alot my problem fixed!