[Task]: Create foreign data wrapper and test data access through it

[coilysiren]

Summary

CREATE EXTENSION oracle_fdw;

CREATE SERVER grants FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//HOST:PORT/DATABASE');

Fill in HOST, PORT, DATABASE

CREATE USER MAPPING FOR ROLE SERVER grants OPTIONS (user 'USER', password 'PASSWORD');
GRANT USAGE ON FOREIGN SERVER grants TO ROLE;
GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO ROLE;

Fill in ROLE, USER, PASSWORD

• perform for every relevant ROLE, eg
  • migrator
  • app
  • root* (doesn't need GRANTs)

CREATE FOREIGN TABLE api.foreign_topportunity (...TABLE SCHEMA...) SERVER grants OPTIONS (schema 'EGRANTSADMIN', table 'TOPPORTUNITY');
GRANT ALL ON api.foreign_topportunity to ROLE;

Fill in ...TABLE SCHEMA...

• perform for every relevant ROLE, eg
  • migrator
  • app

See also slack: https://betagrantsgov.slack.com/archives/C05UJJNKN3B/p1708019321304029

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

Acceptance criteria

• [x] FDW tested in dev
• [x] FDW tested in staging

[coilysiren]

Using the RDS query editor to run these commands now

[sumiat]

separate ticket for FDW tested in prod will be created

[coilysiren]

PROD ticket => https://github.com/HHS/simpler-grants-gov/issues/1281