How to give Read_write users access to foreign tables imported through foreign data wrapper?

Question

I had a question regarding the extension postgres_fdw. I am trying to use postgres_fdw to import foreign tables from DB A to DB B. These databases are on the same host.

In Database B, I have a set of users that have read_write access for each schemas. I want them also have access to these foreign tables and any subsequent views and tables that are created from these tables.

My issue is: All the postgres_fdw commands, I've had to run as superuser. I've run the following:

CREATE SERVER DB_A_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'host_name', dbname 'Database_A');
CREATE USER MAPPING FOR read_write_user
  SERVER DB_A_server
  OPTIONS (user 'readonly_DB_A', password 'readonly_DB_A');
GRANT USAGE ON FOREIGN SERVER  DB_A_server to read_write_user;
SET ROLE read_write_user;

I'm unable to run these commands as the read_write_user for Databaase B. My end goal is to have: read_write users import foreign tables as they please.

IMPORT FOREIGN SCHEMA completed LIMIT TO (Database_A_table1, Database_A_table2 ) FROM DB_A_server INTO public

What would I have to do in order to give the read_write role access to these foreign tables?

Can I do something like: GRANT USAGE ON FOREIGN DATA WRAPPER postgres_fdw TO ; https://learn.microsoft.com/en-us/answers/questions/741419/create-foreign-data-wrapper-without-superuser-priv.html Thanks!

Answer 1

Yes, you can run

GRANT USAGE ON FOREIGN DATA WRAPPER postgresql_fdw TO read_write_user;

and then run all yourcommands as read_write_user.