Using the oracle_fdw extension to access foreign data in Aurora PostgreSQL

For easy and efficient access to Oracle databases for Aurora PostgreSQL, you can use the PostgreSQL oracle_fdw extension, which provides a foreign data wrapper. For a complete description of this extension, see the oracle_fdw documentation.

The oracle_fdw extension is supported on Amazon RDS for PostgreSQL versions 12.7, 13.3, and higher that use x86-based DB instance classes.

Turning on the oracle_fdw extension

To use the oracle_fdw extension, perform the following procedure.

To turn on the oracle_fdw extension

• Run the following command using an account that has rds_superuser permissions.

  CREATE EXTENSION oracle_fdw;

Example using a foreign server linked to an RDS for Oracle database

The following example demonstrates using a foreign server linked to an RDS for Oracle database.

To create a foreign server linked to an RDS for Oracle database

1. Note the following for the RDS for Oracle DB instance:

   • Endpoint

   • Port

   • Database name

2. Create a foreign server.

   test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name');
   CREATE SERVER

3. Grant usage to a user who doesn't have rds_superuser permissions, for example user1.

   test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1;
   GRANT

4. Connect as user1 and create a mapping to an Oracle user.

   test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword');
   CREATE USER MAPPING

5. Create a foreign table linked to an Oracle table.

   test=> create foreign table mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE');
   CREATE FOREIGN TABLE

6. Query the foreign table.

   test=> select * from mytab;
    a
   ---
    1
   (1 row)

If the query reports the following error, check your security group and access control list (ACL) to make sure that both instances can communicate.

ERROR: connection for foreign table "mytab" cannot be established
DETAIL: ORA-12170: TNS:Connect timeout occurred

Considerations for DB instances from non–Intel-based classes

The oracle_fdw foreign data wrapper relies on the Oracle client. The Oracle client isn't supported on Graviton2-based DB instances, such as the db.m6g instance class. Any calls to Graviton2-based DB instances result in the following error.

ERROR: oracle_fdw is not supported on this instance type, please refer to the documentation for more details

The following considerations apply to DB instances from non–Intel-based DB classes:

• Migrating from an x86-based DB instance to a Graviton2-based one succeeds, but produces the previous error message when oracle_fdw uses the Oracle client.

• Reverting to an x86-based instance makes oracle_fdw work again.

• If you have a read replica on x86 and the primary DB instance is on Graviton2, calls that require the Oracle client succeed on the read replica if they don't generate write activity. Any calls that don't require the Oracle client succeed on the primary. Therefore, you can successfully perform the steps in the previous example (except the query) on the primary DB instance and run the query on the read replica.

Working with encryption in transit

PostgreSQL-to-Oracle encryption in transit is based on a combination of client and server configuration parameters. For an example using Oracle 21c, see About the Values for Negotiating Encryption and Integrity in the Oracle documentation. The client used for oracle_fdw on RDS is configured with ACCEPTED, meaning that the encryption depends on the Oracle database server configuration.

pg_user_mapping and pg_user_mappings permissions

In the following table, you can find an illustration of user mapping permissions using the example roles. The rdssu1 and rdssu2 users have the rds_superuser role, and the user1 user doesn't.

Note

You can use the \du metacommand in psql to list existing roles.

test=> \du
                                                               List of roles
    Role name    |                         Attributes                         |                          Member of
-----------------+------------------------------------------------------------+-------------------------------------------------------------
 rdssu1          |                                                            | {rds_superuser}
 rdssu2          |                                                            | {rds_superuser}
 user1           |                                                            | {}

Users with the rds_superuser role can't query the pg_user_mapping table. The following example uses rdssu1,

test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> select * from pg_user_mapping;
ERROR: permission denied for table pg_user_mapping

On RDS for PostgreSQL, all users—even ones with the rds_superuser role—can see only their own umoptions values in the pg_user_mappings table. The following example demonstrates.

test=> SET SESSION AUTHORIZATION rdssu1;
SET
test=> select * from pg_user_mappings;
 umid  | srvid | srvname | umuser | usename    |            umoptions
-------+-------+---------+--------+------------+----------------------------------
 16414 | 16411 | oradb   |  16412 | user1      |
 16423 | 16411 | oradb   |  16421 | rdssu1     | {user=oracleuser,password=mypwd}
 16424 | 16411 | oradb   |  16422 | rdssu2     |
(3 rows)

test=> SET SESSION AUTHORIZATION rdssu2;
SET
test=> select * from pg_user_mappings;
 umid  | srvid | srvname | umuser | usename    |            umoptions
-------+-------+---------+--------+------------+----------------------------------
 16414 | 16411 | oradb   |  16412 | user1      |
 16423 | 16411 | oradb   |  16421 | rdssu1     |
 16424 | 16411 | oradb   |  16422 | rdssu2     | {user=oracleuser,password=mypwd}
(3 rows)

test=> SET SESSION AUTHORIZATION user1;
SET
test=> select * from pg_user_mappings;
 umid  | srvid | srvname | umuser | usename    |           umoptions
-------+-------+---------+--------+------------+--------------------------------
 16414 | 16411 | oradb   |  16412 | user1      | {user=oracleuser,password=mypwd}
 16423 | 16411 | oradb   |  16421 | rdssu1     |
 16424 | 16411 | oradb   |  16422 | rdssu2     |
(3 rows)

Because of differences in implementation of information_schema._pg_user_mappings and pg_catalog.pg_user_mappings, a manually created rds_superuser requires additional permissions to view passwords in pg_catalog.pg_user_mappings.

No additional permissions are required for a user with the rds_superuser role to view passwords in information_schema._pg_user_mappings.

Users who don't have the rds_superuser role can view passwords in pg_user_mappings only under the following conditions:

• The current user is the user being mapped and owns the server or holds the USAGE privilege on it.

• The current user is the server owner and the mapping is for PUBLIC.