PostgreSQL’s foreign data wrapper to Oracle: hide passwords using secure external password store 

This post was originally published on this site

Hi there, customer is worried about the credentials that are stored in PostgreSQL db
when using foreign data wrappers. In that case to Oracle, using oracle_fdw.
It is possible to query a stored password from data dictionary. Only privileged users can do it, but still kind of a concern.

Example:

postgres=# SELECT srvname, umoptions FROM pg_catalog.pg_user_mappings;
 srvname |          umoptions
---------+-----------------------------
 nireus  | {user=SCOTT,password=tiger}
(1 row)

We can hide the password, by using secure external password store. See this. Here are the necessary steps to integrate it into the usage together with PostgreSQL and oracle_fdw:

1. Create the secure external password store for the oracle client in a wallet. And add the secret credential. Note that this example uses EZconnect. mkstore command is in a regular Oracle client. If you want to use instantclient at postgres server this would basically work but then you should create the wallet elsewhere where you have a client, e.g. on an Oracle server and copy it over.

mkstore -wrl /path/to/wallet -create 
mkstore -wrl /path/to/wallet -createCredential '//zam36:1521/NIREUS_RW' scott 

The wallet gets a password, you need it whenever you adapt it or want to see secret contents.

2. create/adapt a clients sqlnet.ora and add

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /path/to/wallet)
     )
    )
SQLNET.WALLET_OVERRIDE = TRUE

make sure to have TNS_ADMIN set to the directory with this sqlnet.ora and potentially a tnsnames.ora in the environment of postgres server process.

3. We create fdw extension in postgres as documented in postgres.

CREATE EXTENSION oracle_fdw;

4. test whether it can be loaded

SELECT oracle_diag();

This posts some important infos about the oracle_fdw. in my case (sorry for the old postgres release):

oracle_fdw 2.1.0, PostgreSQL 10.15 (Ubuntu 10.15-0ubuntu0.18.04.1), Oracle client 19.3.0.0.0, ORACLE_HOME=/opt/oracle/product/instantclient_19_3, TNS_ADMIN=/var/lib/postgresql/zam41_wallet

5. create foreign server, make sure you use the same string as stored in the wallet aka. secure external password store

CREATE SERVER nireus FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//zam36:1521/NIREUS_RW' );
-- in case you want to remove it
-- DROP SERVER nireus;

You can check it with psql’s “des” or in the view pg_catalog.pg_foreign_server.

6. Now the important part where we do not provide the password. We just use 2 empty strings.

CREATE USER MAPPING FOR postgres SERVER nireus OPTIONS (user '', password '');
-- DROP USER MAPPING FOR postgres SERVER nireus;

7. Check the connection

CREATE FOREIGN TABLE oracle_query
 ( oracle_user  VARCHAR(256),
   os_user      VARCHAR(256),
   module       VARCHAR(256),
   server_host  VARCHAR(256)
)
SERVER nireus OPTIONS (table '(
SELECT
  sys_context(''userenv'',''current_user'') oracle_user,
  sys_context(''userenv'',''os_user'') os_user,
  sys_context(''userenv'',''module'') module,
  sys_context(''userenv'',''server_host'') server_host
FROM dual)');
-- DROP FOREIGN TABLE oracle_query;

postgres=# SELECT * FROM oracle_query;
 oracle_user | os_user  |  module  | server_host
-------------+----------+----------+-------------
 SCOTT       | postgres | postgres | zam36
(1 row)

Thus you can use secure external password store to hide Oracle password when using oracle_fdw.
q.e.d.
Have fun
Mathias