Grouping user into Role to access FDW table

Question

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

Answer 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.

Answer 2

you have to change of role of group before; set rote demorole1; select * from tbl_test;