Foreign tables - oracle_fdw

Edwin UY <edwin.uy@xxxxxxxxx> · Sun, 5 May 2024 15:07:38 +1200

Hi,

I followed this link for using oracle_fdw to access Oracle from PostgreSQL.
Oracle is ON-PREM and PostgreSQL is Aurora-PostgreSQL-RDS-Version 15.

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.foreign-data-wrappers.html#postgresql-oracle-fdw

As administrator:

CREATE EXTENSION oracle_fdw;
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
GRANT USAGE ON FOREIGN SERVER oradb TO user1;

As user1

CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
SELECT * FROM mytab;
The SELECT gives ERROR:  permission denied for schema public
I have to do GRANT ALL ON SCHEMA public TO user1, this resolves the permission denied error.

Ideally, I don't really want each user to be doing the create user mapping and create foreign table.

At the moment, I am planning to do an Oracle to Aurora-PostgreSQL migration
I have 2 schema, schema1 and schema2. 
schema1 will contain new tables.
schema2 has some tables that I will either be using ora2pg or DMS to migrate data from Oracle to PostgreSQL. The data on schema1 will be populated with data from schema2 depending on some logic condition of the tables on schema2.
So the developer will be having some API to run on schema1 do some logic of some on the tables on schema2 and insert the values into the new tables on schema1.
After this, I need oracle_fdw to access some tables in the Oracle Database and then update some column of some table/s on schema1.

And then there will be several users, user1 to user5 for example.
From 
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Extensions.foreign-data-wrappers.html#postgresql-oracle-fdw, it appears user1 to user5 need to create user mapping and create foreign tables.

Is it possible that as schema owner of either schema1 or schema2, i.e. administrator, I create the user mapping and the foreign tables and then grant access to user1 to user5 and schema2/schema1?
I can't find any reference/doc/example on whether this is possible or not.

I also don't have the password of each user. Can I use set role user1 to user5 to do the CREATE USER MAPPING and CREATE FOREIGN TABLE.

In summary, I am wanting to do the CREATE USER MAPPING and CREATE FOREIGN TABLE once only in either SCHEMA1 and SCHEMA2 and grant any role/user to be able to access these foreign tables, mainly just a select.

Any guidance will be much appreciated. Please advise. Thanks in advance.

Regards,
Ed