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
Last activity: Nov 24, 2022, 06:55 AM