在同一個 PostgreSQL Instance（或稱作一個 PostgreSQL Cluster）裡面，不同的 Database 間要交換資料，這看起來是個小小問題，但其實只能走連線交換資料的方式，沒有想像中的直覺。
PostgreSQL 提供以下兩種方式進行：
1. 用 Postgres_FDW 存取外部表
2. 用 DBLink 連到其他資料庫
其中的 postgres_fdw 在最近發展功能越來越豐富，因此一般建議使用 Postgres_FDW 去進行不同 Database 之間，資料的交換。
以下簡單示範在 EDB 公司的 PostgreSQL 9.5 企業版中使用 postgres_fdw 進行存取。
以下即操作步驟
先確認 edb 裡面的範例表
bash-4.1$ psql -U enterprisedb -d edb
Password:
psql.bin (9.5.3.8)
Type "help" for help.
edb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------+----------+--------------
public | dept | table | enterprisedb
public | emp | table | enterprisedb
public | jobhist | table | enterprisedb
public | next_empno | sequence | enterprisedb
public | salesemp | view | enterprisedb
登入啟用套件，然後設置一個存放外部表的 Schema
edb=# \c postgres You are now connected to database "postgres" as user "enterprisedb". postgres=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION postgres=# CREATE SCHEMA edb_sample_tabs; CREATE SCHEMA
設置存取資訊：分成資料庫連線資訊，以及帳號密碼資訊。注意一點，在此 CREATE SERVER 中連線資訊不指定 host IP，便會走本地的 Unix Socket，這會比走網路協定要快的多。
postgres=# CREATE SERVER local_edb
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'edb');
CREATE SERVER
postgres=# CREATE USER MAPPING FOR CURRENT_USER
SERVER local_edb
OPTIONS (user 'enterprisedb', password 'password');
CREATE USER MAPPING
最後，就可以匯入表格資訊
postgres=# IMPORT FOREIGN SCHEMA public
FROM SERVER local_edb
INTO edb_sample_tabs;
ImMPORT FOREIGN SCHEMA
postgres=# SET search_path TO edb_sample_tabs;
SET
postgres=# \d
List of relations
Schema | Name | Type | Owner
-----------------+----------+---------------+--------------
edb_sample_tabs | dept | foreign table | enterprisedb
edb_sample_tabs | emp | foreign table | enterprisedb
edb_sample_tabs | jobhist | foreign table | enterprisedb
edb_sample_tabs | salesemp | foreign table | enterprisedb
上面使用的 Foreign Schema 是 PostgreSQL 9.5 版才引入的功能，這功能讓外部表的匯入更為簡易，不用輸入完整表格 DDL 也能夠進行，還能指定/排除特定表格作匯入，是一個不錯的功能。
僅匯入 emp 表格：
postgres=# IMPORT FOREIGN SCHEMA public
LIMIT TO (emp)
FROM SERVER local_edb
INTO edb_sample_tabs;
排除 emp 表格之外的都匯入
postgres=# IMPORT FOREIGN SCHEMA public
EXCLUDE (emp)
FROM SERVER local_edb
INTO edb_sample_tabs;
換作是匯入外部表的語法，則需要指名 DDL；不過好處是，能夠自選要提供的欄位，不用讓「全部」欄位都出現在外部表裡面
postgres=# CREATE FOREIGN TABLE scm.foreign_emp(
empno numeric,
ename varchar(10)
) SERVER local_edb
OPTIONS (schema_name 'public', table_name 'emp');
以上所作的簡單示範，是直接用 Super User 進行；若是要給予一般帳戶存取表格，只需要處理相關外部表的 GRANT 即可。而要給予建立外部表的能力，則需要賦予帳戶 FOREIGN DATA WRAPPER 的 USAGE 權限才行。至於啟用任何一種 Foreign Data Wrapper 套件（例如，CREATE EXTENSION postgres_fdw）則需要先以 Super User 建立才行。範例請參考下述示範。
接著，以下示範的是，允許一個 Non-SuperUser 的帳號（在此為一個 Database Owner）在某個資料庫可以建立 Foreign Server、外部表；然後允許其他普通帳戶可以存取該表格。
prod=# \c You are now connected to database "prod" as user "enterprisedb". prod=# CREATE EXTENSION postgres_fdw; CREATE EXTENSION prod=# grant all on FOREIGN DATA WRAPPER postgres_fdw TO prod; GRANT
prod=# -- 離開 Super User，換成 database owner prod=# \c prod prod Password for user prod: You are now connected to database "prod" as user "prod". prod=> -- 先建立一個 Non-Public Schema prod=> CREATE SCHEMA scm; prod=> prod=> -- 建立 prod 這個帳號專屬的外部表 prod=> -- 注意的是，USER MAPPING 指定的對象要指派給需要用的帳號 prod=> -- 在此設定開放給大眾帳號存取 prod=> CREATE SERVER local_edb FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'edb'); CREATE SERVER prod=> CREATE USER MAPPING FOR PUBLIC SERVER local_edb OPTIONS (user 'enterprisedb', password 'edb'); CREATE USER MAPPING prod=> IMPORT FOREIGN SCHEMA public LIMIT TO (emp) FROM SERVER local_edb INTO scm; IMPORT FOREIGN SCHEMA prod=> select * from scm.emp limit 1; empno | ename | job | mgr | hiredate | sal | comm | deptno -------+-------+-------+------+--------------------+--------+------+-------- 7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20 (1 row) prod=> prod=> prod=> -- 接著，授予某個普通帳號 normaluser 存取外部表的權限 prod=> -- 要賦予的權限，只要使該帳號能存取表格就夠了，用法與一般表一樣 prod=> grant usage on SCHEMA scm to dever1; GRANT prod=> grant SELECT on scm.emp to dever1; GRANT
prod=> -- 換成普通帳號 normaluser 登入
prod=> \c prod normaluser
Password for user normaluser:
You are now connected to database "prod" as user "normaluser".
prod=> select * from scm.emp limit 1;
empno | ename | job | mgr | hiredate | sal | comm | deptno
-------+-------+-------+------+--------------------+--------+------+--------
7369 | SMITH | CLERK | 7902 | 17-DEC-80 00:00:00 | 800.00 | | 20
(1 row)
prod=>
prod=> -- 值得提的一點是，連線資訊正常帳號看不到
prod=> \deu+
List of user mappings
Server | User name | FDW Options
-----------+-----------+-------------
local_edb | public |
(1 row)
prod=>
prod=> -- 然而，如果 prod 帳號授予 normaluser 相關權限，normaluser 就會得知密碼了
prod=# \c prod prod
Password for user prod:
You are now connected to database "prod" as user "prod".
prod=> grant usage on FOREIGN SERVER local_edb TO dever1;
GRANT
prod=> \c prod normaluser
Password for user normaluser:
You are now connected to database "prod" as user "normaluser".
prod=> \deu+
List of user mappings
Server | User name | FDW Options
-----------+-----------+-----------------------------------------
local_edb | public | ("user" 'enterprisedb', password 'edb')
(1 row)
最後小結
- 在一個 Database 啟用 Foreign Data Wrapper：
- 需要由 Super User 啟用
- 允許一個帳戶（通常是 Database Owner）能建立 FDW，並且其他帳戶看不到他存儲的帳號密碼資訊：
- 由 Super User 允許該帳戶（Database Owner）使用 Foreign Data Wrapper 套件的使用權限
- 建立外部表的連線資訊、登入帳密，還有外部表
- 登入帳密設置（CREATE USER MAPPING）要指定會用到的帳號，請參考 PostgreSQL: Documentation : CREATE USER MAPPING
- 規劃上，可以透過 GROUP ROLE 作權限配置
- 允許一個普通帳號存取某個不屬於他的外部表：
- 把外部表當作普通表格，由外部表的建立者賦予普通帳號相關的表葛存取權限
- 連線資訊，對於一般帳號，是看不到的
- 要是賦予了某帳號對 Foreign Server 有 Usage 權限，這個帳號就會不小心看到帳密了 ...
最後提醒：在 PostgreSQL 裡面，Database 是各自物件獨立的邏輯個體，Schema 類似一種 Name Space 的想法。所以就算是待在同一個 Instance 裡面的不同 Database，並不能像不同 Schema 一樣，「直接」的相互存取。
參考資料：
A look at Foreign Data Wrappers Craig Kerstiens還有更多，可以直接搜尋，或參考之前發布的文章
沒有留言:
張貼留言