查看 Foreign data wrapper 相關資訊

使用 Foreign Data Wrapper，如果想要找出相關資訊怎麼辦？

Foreign Data Wrapper 分三部份：Foreign Serbver、User Mapping 以及 Foreign Tables。這些有關資訊都被紀錄到 Information Schema 裡面了

• 列舉已經設置的 User Mapping

edb=# \deu+
                 List of user mappings
 Server |  User name   |          FDW Options           
--------+--------------+--------------------------------
 orabg5 | enterprisedb | (username 'hr', password 'hr')
 to_ora | enterprisedb | ("user" 'hr', password 'hr')
(2 rows)

edb=# select * from pg_user_mapping;                                 
 umuser | umserver |         umoptions         
--------+----------+---------------------------
     10 |    16757 | {user=hr,password=hr}
     10 |    16768 | {username=hr,password=hr}
(2 rows)

edb=# -- 上面用 Super User，把密碼也列出來了
edb=# \c edb aaa
Password for user aaa:
You are now connected to database "edb" as user "aaa".
edb=> select * from pg_user_mapping;
ERROR:  permission denied for relation pg_user_mapping
edb=> \deu+
        List of user mappings
 Server |  User name   | FDW Options
--------+--------------+-------------
 orabg5 | enterprisedb |
 to_ora | enterprisedb |
(2 rows)
edb=>

上面可以看到，如果當初在設置遠端資料庫的帳號時，也多存密碼的話，使用資料庫的 Super User 會看到相關密碼，而其他沒有權限的帳戶則不會看到。

• 列舉 Foreign Servers

edb=# select srvname, srvoptions from pg_foreign_server;             
 srvname |                                                                             srvoptions                                                                             
---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 to_ora  | {dbserver=//10.140.0.18/xe}
 to_ora2 | {dbserver=//localhost/xe}
 orabg5  | {drivername=oracle.jdbc.driver.OracleDriver,url=jdbc:oracle:thin:@10.140.0.18:1521:xe,querytimeout=15,jarfile=/usr/lib/jvm/jre/lib/ext/ojdbc7.jar,maxheapsize=600}
(3 rows)

列舉 Foreign Table

edb=# select * from information_schema.foreign_tables;
 foreign_table_catalog | foreign_table_schema | foreign_table_name | foreign_server_catalog | foreign_server_name
-----------------------+----------------------+--------------------+------------------------+---------------------
 edb                   | enterprisedb         | tst2ftw            | edb                    | to_ora
 edb                   | enterprisedb         | testjfdw           | edb                    | orabg5
(2 rows)

當然，使用者對應（User Mapping）、外部伺服器（Foreign Servers）和外部表（Foreign Table）等物件，也能分別用 psql 裡面的 \deu、\des、\det 等 meta-command 查詢出來。

參考：

oracle10g - Show linked servers in PostgreSQL - Stack Overflow

information_schema with PostgreSQL · Cogs and Levers