0

Recently I setup Forward Data Wrapper. Here is what I do:

CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '192.168.125.1', port '5432', dbname 'template1 ');  
IMPORT FOREIGN SCHEMA public LIMIT TO (tbl_test) FROM SERVER foreign_server INTO public;

Create a user on the destination / foreign server : CREATE USER fdwuser WITH PASSWORD 'secret' And then do the necessary settings like GRANT. The FDW works.

But then I want to do more. What if I assemble all the users that want to access FDW into a GROUP. Then I do the following:

  • Create group role : demorole1

  • create user : fdwuser3

  • grant demorole1 to fdwuser3

  • create user mapping for group role "demorole1" :

    CREATE USER MAPPING FOR demorole1 SERVER foreign_server OPTIONS (user 'fdwuser', password 'secret');

  • select * from tbl_test; -- failed.

  • grant demorole1 access to tbl_test, schema and foreign server : GRANT ALL ON TABLE tbl_test TO demorole1; GRANT USAGE ON FOREIGN SERVER foreign_server TO demorole1; GRANT USAGE ON SCHEMA public TO fdwuser3;

  • Test : select * from tbl_test; --failed. user mapping not found for user fdwuser3

  • CREATE USER MAPPING FOR demorole1 SERVER foreign_server OPTIONS (user 'fdwuser', password 'secret');

  • Test again : select * from tbl_test; -- still failed. user mapping not found for "fdwuser3"

  • Do user mapping : CREATE USER MAPPING FOR fdwuser3 SERVER foreign_server OPTIONS (user 'fdwuser', password 'secret');

  • select * from tbl_test; --WORKS

Question : How can I create a group role so that the users on the local server can be set as it's member so I don't have to do Granting and Creating user mapping every time there is user wants to access FDW table ?

Thanks

1 Answer 1

1

That is not supported. You can either create a user mapping for the user itself or for PUBLIC, that is, for everybody. These are your choices. A user mapping created for a role will not be used for a member of that role. That is working as designed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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