Sample Header Ad - 728x90

Grouping user into Role to access FDW table

0 votes
2 answers
694 views
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
Asked by padjee (337 rep)
Sep 18, 2022, 02:09 AM
Last activity: Nov 24, 2022, 06:55 AM