0

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?

1 Answer 1

0

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;
3
  • as i mention i used that before but i still have error, how can i fix? Feb 28, 2023 at 7:57
  • No, you didn't mention that at all. You mentioned that you granted the privilege to user postgres, which is different. Feb 28, 2023 at 8:01
  • Thanks alot my problem fixed! Feb 28, 2023 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.