postgresqlpostgresql-9.5oracle-fdw

Oracle foreign data wrapper

I have master-slave architecture. On the slave I have an Oracle database with two schemas, e.g. TEST1 and TEST2.

I have all objects (e.g. EMPLOYEES) stored in schema TEST1, and user TEST1 (or admin) has given read only privileges on TEST1.EMPLOYEES to TEST2, so when I use TEST1.EMPLOYEES in a query on the Oracle database I can access its data.

How can I implement the same using Oracle foreign data wrapper in postgres 9.5 because I have credentials for TEST2 and not TEST1? When I try to access the foreign table it give an error saying that TEST2.EMPLOYEES does not exist.

Solution

  • You can easily do that if you define the user mapping with the credentials of user TEST2 and the foreign table with the schema option, i.e.

    CREATE FOREIGN TABLE ... OPTIONS (schema 'TEST1', table 'EMPLOYEES');