本文

FDW（外部データラッパ）のアセスメント
1. 概要
今回のアセスメントの前提として、以下のような状況を設定しています。
・社内システムの現状・勤怠管理、人事給与等、業務ごとにシステムが存在し、主管部署が運用している。
・業務システムの利用者を個別に社員情報として保有している。
・社員情報の変更は、変更の発生時点で各業務システムの管理者が独自に行っている。
・業務システムごとに、様々な種類のDBMSを採用している。
・社内システムの課題
・業務システムごとに、社員情報の変更実施のタイムラグが発生する。
例）・他部門から人事部への異動後、直ぐには人事給与システムを利用できない。
・退職者の利用権限が残ったまま、利用できる状態にある。
・社員情報のメンテナンスの運用負荷が高い。
例）・所属異動が月3回ある。
・解決策
・社員情報のメンテナンスを人事部で一元管理する。
・実現方法
・PostrgreSQLの外部データラッパというデータ連携機能を利用し、各業務システムの社員情報をPostgreSQL自体のテーブルとして扱うことで、参照、更新等のメンテナンスを行う。

1.1. 検証環境
業務システム用DBサーバを５つと一元管理用のPostgreSQLサーバを１つ用意します。

クライアント

データ連携
福利厚生システム

データ連携
データ連携
データ連携

データ連携

社員ポータルサイト

人事給与システム
会計システム

勤怠管理システム

ページ 1

本文

１．２．検証データ
各社員は、所属部署やその役職により、以下の表に示した要領で、システムへのログイン可否や利用権限をあたえられていることとします。
・検証データ
部名
経理部
100

ユーザ
ＩＤ

ユーザ名

－

52030

経理 太郎

経理課
10010

55040

課名

社員ポータルサイト
PostgreSQL

福利厚生システム
Firebird
Login

利用権限

利用権限

Login

利用権限

Login

利用権限

部長

○

一般

○

管理者

○

一般

○

一般

経理 一郎

課長

○

一般

○

管理者

○

一般

○

一般

58020

経理 二郎

主任

○

一般

○

一般

○

一般

○

一般

61050

経理 三郎

社員

○

一般

○

一般

○

一般

○

一般

53150

経理 一男

課長

○

一般

○

管理者

○

一般

○

一般

71080

経理 二男

主任

○

一般

○

一般

○

一般

○

一般

79010

経理 三男

社員

○

一般

○

一般

○

一般

○

一般

－

51030

人事 太郎

部長

○

管理者

○

一般

○

管理者

○

一般

○

一般

人事給与課
20010

52100

人事 一郎

課長

○

管理者

○

一般

○

管理者

○

一般

○

一般

55090

人事 二郎

主任

○

管理者

○

管理者

○

一般

○

一般

58060

人事 三郎

社員

○

一般

○

管理者

○

一般

○

一般

62010

人事 一男

課長

○

管理者

○

一般

○

管理者

○

一般

○

管理者

65030

人事 二男

主任

○

管理者

○

一般

○

管理者

○

一般

○

管理者

76020

人事 三男

社員

×

一般

○

管理者

○

一般

○

管理者

－

52071

営業 太郎

部長

○

一般

○

一般

営業1課
30010

61011

営業 一郎

課長

○

一般

○

一般

62051

営業 二郎

主任

○

一般

○

一般

67071

営業 三郎

社員

○

一般

○

一般

72011

営業 一男

課長

○

一般

○

一般

73021

営業 二男

主任

○

一般

○

一般

74031

営業 三男

社員

○

一般

○

一般

－

67022

開発 太郎

部長

○

一般

○

一般

開発1課
40010

73092

開発 一郎

課長

○

一般

○

一般

75082

開発 二郎

主任

○

一般

○

一般

77142

開発 三郎

社員

○

一般

○

一般

75222

開発 一男

課長

○

一般

○

一般

76892

開発 二男

主任

○

一般

○

一般

74322

開発 三男

社員

○

一般

○

一般

－

58023

情報 太郎

部長

○

システム管理者

○

管理者

システム課
50010

63043

情報 一郎

課長

○

システム管理者

○

管理者

67183

情報 二郎

社員

○

システム管理者

○

管理者

開発2課
40020

情報
システム部
500

勤怠管理システム
Oracle

Login

営業2課
30020

開発部
400

会計システム
MySQL

利用権限

福利厚生課
20020

営業部
300

人事給与システム
Oracle
Login

財務課
10020

人事部
200

役職

ページ 2

本文

１．４．検証シナリオ
検証データの状態から、以下のような異動が発生したこととします。
①人事太郎が休職②営業三男を経理部財務課に異動③人事三郎が主任に昇格④人事二郎が営業部に異動⑤開発次郎が退職・検証シナリオ想定結果
部名
経理部
100

課名

－
経理課
10010

財務課
10020

ユーザ
ＩＤ
52030

経理 太郎

役職

部長

勤怠管理システム
Oracle

社員ポータルサイト
Oracle

福利厚生システム
Firebird
Login

Login

利用権限

Login

利用権限

Login

利用権限

Login

利用権限

○

一般

○

管理者

○

一般

○

一般

課長

○

一般

○

管理者

○

一般

○

一般

58020

経理 二郎

主任

○

一般

○

一般

○

一般

○

一般

61050

経理 三郎

社員
○
一般
検証シナリオ②
所属変更
53150 経理 一男
課長○
一般
人事給与システムと会計システムにユーザ追加
71080 経理 二男
主任
○
一般

○

一般

○

一般

○

一般

○

管理者

○

一般

○

一般

○

一般

○

一般

○

一般

営業 三男

社員

○

一般

○

一般

○

一般

○

一般

経理 三男

社員

○

一般

○

一般

○

一般

○

一般

人事 太郎

部長

×

管理者

×

一般

×

管理者

×

一般

×

一般

人事給与課
20010

52100

人事 一郎

課長

○

管理者

○

一般

○

管理者

○

一般

○

一般

58060

人事 三郎

主任

○

管理者

○

管理者

○

一般

○

一般

人事 一男
課長
○
検証シナリオ③
人事給与システムに管理者権限を付与
65030 人事 二男
主任
○

管理者

○

一般

○

管理者

○

一般

○

管理者

管理者

○

一般

○

管理者

○

一般

○

管理者

76020

人事 三男

社員

一般

○

管理者

○

一般

○

管理者

－

52071

営業 太郎

部長

○

一般

○

一般

営業1課
30010

61011

営業 一郎

課長

○

一般

○

一般

55090

人事 二郎

主任

○

一般

○

一般

○

一般

営業 二郎検証シナリオ④
主任
所属変更
67071
営業 三郎
社員
人事給与システム、福利厚生システム登録
勤怠管理システムの利用権限を一般ユーザに
72011 営業 一男
課長

○

一般

○

一般

○

一般

○

一般

○

一般

○

一般

62010

×

○

管理者

73021

営業 二男

主任

○

一般

○

一般

－

67022

開発 太郎

部長

○

一般

○

一般

開発1課
40010

73092

開発 一郎

課長

○

一般

○

一般

75082

開発 二郎

主任

○

一般

○

一般

77142 検証シナリオ⑤
開発 三郎
レコード削除75222 開発 一男

社員

○

一般

○

一般

課長

○

一般

○

一般

76892

開発 二男

主任

○

一般

○

一般

74322

開発 三男

社員

○

一般

○

一般

－

58023

情報 太郎

部長

○

システム管理者

○

管理者

システム課
50010

63043

情報 一郎

課長

○

システム管理者

○

管理者

67183

情報 二郎

社員

○

システム管理者

○

管理者

開発2課
40020

ページ 3

利用権限
システム

利用権限

51030

営業2課
30020

情報
システム部
500

会計システム
MySQL

経理 一郎

62051

開発部
400

人事給与システム
Oracle

－

福利厚生課
20020

営業部
300

ユーザ名

55040

74031
検証シナリオ①
すべてのシステムをログイン不可に
79010
人事部
200

・所属部署と業務システムの利用権限対応表

経理部

人事部

営業部

開発部

情報
システム部

人事給与システム・一般（必須）・一般（必須） ・管理者
・不可（主任以上）・不可・不可会計システム・一般（必須）・管理者
・一般（任意） ・不可（課長以上）

・不可・不可

勤怠管理システム・一般（必須） ・管理者
（必須）

社員ポータルサイト

・一般（必須） ・一般（必須） ・一般（必須） ・一般（必須） ・管理者
（必須）

福利厚生システム・不可・一般（必須） ・一般（必須）・一般（必須）・管理者
・不可（福利厚生）

・不可

・システム
管理者
（必須）

・不可

本文

2. 検証作業概要に示した内容で検証作業を行います。

2.1. 外部データラッパの導入検証
今回導入検証用に入手した以下のFDWの導入から外部テーブルの参照までを検証しました。
※FDWと同名のシートに詳細を記載してあります。
種類
RDBMS

非RDBMS

対象

FDW

ORACLE

Oracle_fdw

MySQL

MySQL_fdw

PostgreSQL

postgres_fdw

Firebird

firebird_fdw

SQLiteSQLite_fdw

LDAP

LDAP_fdw(python)

CSV

file_fdw

FDWの入手先：PostgreSQL wiki： https://wiki.postgresql.org/wiki/Foreign_data_wrappers を参照してください。

2.2. 検証シナリオの実施
検証シナリオに従い実施して、1.4の表「検証シナリオ想定結果」の通りになることを検証しました。
※別シート「検証シナリオ①～⑤」及び「検証結果」に実施結果を記載してあります。

2.3. Oracle_fdwの基本動作検証
FDWの基本動作検証の一環で、Oracle_fdwに限定して実施した結果を紹介します。
※別シート「Oracle_fdw動作検証」に詳細を記載してあります。
①検証環境（Oracle）のテーブル定義とデータ
カラム物理名カラム論理名制約

user_iduser_namepassword

user_groupuser_id

ユーザID

主キー

1 David_Humepass_hume

thought

user_name

ユーザ名-

2 Immanuel_Kant

pass_kantphilosophy

password

パスワード-

3 Gilles_Deleuze

pass_gilles

philosophy

user_group組織-

4 Hitoshi_Nagai

pass_hitoshi

philosophy

5 Kitaro_Nishidapass_kitaro

philosophy②検証内容
・CRUDの検証
PostgreSQL上の外部テーブルへのCRUD操作を行い、PostgreSQL及びOracle上で操作の結果を確認しました。
・トランザクション管理の検証
PostgreSQL上への更新処理の確定（COMMIT）、更新処理の破棄（ROLLBACK）操作を行い、 PostgreSQL及びOracle上で操作の結果を確認しました。

ページ 4

本文
・同時実行制御の検証
oracle_fdwを経由した複数のトランザクションによる、同一データの同時更新時のロック制御を検証しました。
結果として、以下の図のようになることを確認しました。
・先にロックしたトランザクションAをCOMMITした場合

トランザクションA

トランザクションB

トランザクションC

BEGIN

UPDATE（排他ロック開始）

BEGIN
UPDATE（ロック解除待ちとなる）

UPDATE（ロック解除待ちとなる）
COMMIT（排他ロック終了）

トランザクションD

BEGIN

BEGIN
UPDATE（排他ロック開始）

・先にロックしたトランザクションCをROLLBACKした場合

例外発生
による処理終了

ROLLBACK（排他ロック終了）（排他ロック開始）
COMMIT（排他ロック終了）※上記結果は、「oracle_fdw経由のトランザクション分離レベルはすべてSerializableになる」という仕様によるものです。
・ロック解除待ち回避の検証
同時実行制御検証のトランザクションBのように、トランザクションを受け付けた上で、最後に異常終了してしまう挙動は、業務用アプリケーションとしては致命的です。
これを避けるために、SELECT句による明示的ロック（SELECT ~ FOR UPDATE NOWAIT）を行い、トランザクションBの実行が受け付けられない（リソース・ビジーエラー）ようにする方法があります。
そこで、同時実効制御検証のトランザクションB及びトランザクションDにおいて、UPDATEの前に明示的ロックを行いましたが、上記結果と同様にロック解除待ち状態となりました。
・同時実行制御検証結果の考察
複数トランザクションを用いる場合、ロック解除待ちをした上で、直列化失敗によるエラーが発生し、処理が失敗してしまう可能性があります。
同時実行制御検証のトランザクションBのような事態を回避する為に、以下の点に留意する必要があると思われます。
①:同一データを複数トランザクションで同時に更新しないようにする。
②:Oracle定義例外 ORA-08177: can't serialize access for this transaction が発生した場合、例外が発生したトランザクションをROLLBACKし、再処理する。
以上

ページ 5

Oracle_fdw
No

概要

対象

ユーザ

コマンド
# cd /usr/local/src

1 Oracleクライアントの入手

PostgreSQLサーバ

確認 （確認コマンド等）# ls /usr/local/src
Oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm
oracle-instantclient11.2-devel-11.2.0.1.0-1.x86_64.rpm
oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.rpm（参考） 通常の入手方法
1.OTN（Oralce Technorogy Network）にユーザー登録する
2.ダウンロードサイトからrpmファイルをローカルPCにダウンロードする
3.rpmファイルをPostgreSQLサーバへアップロードする

root※ダウンロードサイト（2016/2/16現在）
http://www.oracle.com/technetwork/topics/linuxx86-64soft092277.html※クライアント接続ライブラリ（sqlplus）は接続確認用であり、FDWに必須ではない。

2 Oracleクライアント基本パッケージのインストール

PostgreSQLサーバ

3 OracleクライアントSDKパッケー
PostgreSQLサーバ
ジのインストール4 OracleクライアントSQL*Plusパ
PostgreSQLサーバ
ッケージのインストール

5 インストール済みOracleクライ
アントパッケージの確認

6 Oracle用環境変数の設定

PostgreSQLサーバ

PostgreSQLサーバ# rpm -ivh oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm# rpm -ivh oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64.rpm
準備しています...
#################################[100%]
更新中 / インストール中...
1:oracle-instantclient11.2-basic11################################# [100%]# rpm -ivh oracle-instantclient11.2-devel-11.2.0.1.0-1.x86_64.rpm# rpm -ivh oracle-instantclient11.2-devel-11.2.0.1.0-1.x86_64.rpm
準備しています...
#################################[100%]
更新中 / インストール中...
1:oracle-instantclient11.2-devel11################################# [100%]# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.rpm# rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64.rpm
準備しています...
#################################[100%]
更新中 / インストール中...
1:oracle-instantclient11.2-sqlplus################################# [100%]# pm -qa | grep -i oracle# rpm -qa | grep -i oracle
oracle-instantclient11.2-devel-11.2.0.1.0-1.x86_64
oracle-instantclient11.2-sqlplus-11.2.0.1.0-1.x86_64
oracle-instantclient11.2-basic-11.2.0.1.0-1.x86_64# export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:$LD_LIBRARY_PATH# echo $LD_LIBRARY_PATH/usr/lib/oracle/11.2/client64/lib:# export PATH=/usr/lib/oracle/11.2/client64/bin:$PATH# echo $PATH/usr/lib/oracle/11.2/client64/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/pgsql-9.4/bin

root

root

root

root

root

ページ 6

Oracle_fdw# sqlplus [ユーザ]/[パスワード]@[サーバ名]/[データベース名]# sqlplus system/manager@133.227.221.136:1521/xe
SQL*Plus: Release 11.2.0.1.0 Production on Wed Feb 17 00:26:47
2016
Copyright (c) 1982, 2009, Oracle. All rights reserved.

7 Oracleサーバへの接続

PostgreSQLサーバ

root
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit
Production
SQL>$ vi ~postgres/.bash_profile(以下の値を追加)$ source .bash_profile$ echo $LD_LIBRARY_PATH/usr/lib/oracle/11.2/client64/lib:

export LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib:$LD_LIBRARY_PATH
8 環境設定(~postgres/.bash_profile)

PostgreSQLサーバ

postgres$ pg_ctl stop -m fast

$ ps -ef | grep postgresRoot4645 2319 0 00:32 pts/0 00:00:00 su - postgres
postgres 4646 4645 0 00:32 pts/0 00:00:00 -bash
postgres 4889
1 0 00:40 pts/0 00:00:00 /usr/pgsql9.4/bin/postgres
postgres 4890 4889 0 00:40 ?
00:00:00 postgres: logger
process
postgres 4892 4889 0 00:40 ?
00:00:00 postgres: checkpointerprocess
postgres 4893 4889 0 00:40 ?
00:00:00 postgres: writer
process
postgres 4894 4889 0 00:40 ?
00:00:00 postgres: wal writer
process
postgres 4895 4889 0 00:40 ?
00:00:00 postgres: autovacuum
launcher process
postgres 4896 4889 0 00:40 ?
00:00:00 postgres: statscollector process
postgres 4926 4646 0 00:43 pts/0 00:00:00 ps -ef
postgres 4927 4646 0 00:43 pts/0 00:00:00 grep --color=auto
postgres$ pg_ctl start -w

9 PostgreSQLの再起動

PostgreSQLサーバ

postgresページ 7

Oracle_fdw

10 Oracle_fdwの入手

11 コンパイルの実行

PostgreSQLサーバ

PostgreSQLサーバ# cd /usr/local/src/# git clone https://github.com/laurenz/oracle_fdw.git# cd oracle_fdw$ ls -l /usr/local/src/oracle_fdw
合計 372-rw-r--r--. 1 root root 10079 2月 17 00:47 CHANGELOG-rw-r--r--. 1 root root 1009 2月 17 00:47 LICENSE-rw-r--r--. 1 root root 1751 2月 17 00:47 Makefile
lrwxrwxrwx. 1 root root 17 2月 17 00:47 README ->README.oracle_fdw-rw-r--r--. 1 root root 31878 2月 17 00:47 README.oracle_fdw-rw-r--r--. 1 root root 156 2月 17 00:47 TODO
drwxr-xr-x. 2 root root 72 2月 17 00:47 expected-rw-r--r--. 1 root root 231 2月 17 00:47 oracle_fdw--1.0--1.1.sql-rw-r--r--. 1 root root 1003 2月 17 00:47 oracle_fdw--1.1.sql-rw-r--r--. 1 root root 156006 2月 17 00:47 oracle_fdw.c-rw-r--r--. 1 root root 133 2月 17 00:47 oracle_fdw.control-rw-r--r--. 1 root root 7959 2月 17 00:47 oracle_fdw.h-rw-r--r--. 1 root root 44463 2月 17 00:47 oracle_gis.c-rw-r--r--. 1 root root 96260 2月 17 00:47 oracle_utils.c# export PATH=${PATH}:/usr/pgsql-9.4/bin# make USE_PGXS=1

エラーが表示されないこと.

# make USE_PGXS=1 install

エラーが表示されないこと.

root

root

# ls /usr/pgsql-9.4/lib/oracle_fdw.so/usr/pgsql-9.4/lib/oracle_fdw.so

12 インストールの実行

PostgreSQLサーバ

root$ psql <DB名>$ psql testdb

CREATE EXTENSION oracle_fdw;

ｔestdb=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION
testdb=> \dx

PostgreSQL エクステンション
13 の作成PostgreSQLサーバ

インストール済みの拡張の一覧
名前 | バージョン | スキーマ |説明------------+------------+-----------+---------------------------------------oracle_fdw | 1.1| public| foreign data wrapper for Oracle
accessplpgsql | 1.0| pg_catalog | PL/pgSQL procedural language(2 行)

postgresページ 8

Oracle_fdw
CREATE SERVER oracle_server
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (dbserver '//[サーバ名]/[データベース名]');

外部サーバの作
14 PostgreSQL
成PostgreSQLサーバ

testdb=> \des外部サーバー一覧
名前| 所有者 | 外部データラッパー---------------+----------+-------------------oracle_server | postgres | oracle_fdw(1 行)

postgres

GRANT USAGE ON FOREIGN SERVER oracle_server TO [ユーザ名];
外部サーバの利
15 PostgreSQL
用権限

PostgreSQLサーバ

16 PostgreSQLユーザーとの関連
PostgreSQLサーバ
付け

17 PostgreSQL 外部表の作成

18 PostgreSQL 外部表の検索

PostgreSQLサーバ

PostgreSQLサーバ

postgrespostgrespostgrestestdb=# CREATE SERVER oracle_server
testdb-# FOREIGN DATA WRAPPER oracle_fdw
testdb-# OPTIONS (dbserver '//133.227.221.136/XE');
CREATE SERVER

testdb=# GRANT USAGE ON FOREIGN SERVER oracle_server TO
jip;
GRANT
testdb=# \c - jip
データベース "testdb" にユーザ"jip"として接続しました。

CREATE USER MAPPING FOR [ユーザ名]
SERVER oracle_server
OPTIONS (user '<ORACLEユーザ>', password '<ORACLEパスワード>');testdb=# CREATE USER MAPPING FOR jip
testdb-# SERVER oracle_server
testdb-# OPTIONS (user 'scott', password 'tiger');
CREATE USER MAPPINGCREATE FOREIGN TABLE foreign_oracle_user_tbl(
no int,id text,
pass text,level int)
SERVER oracle_server
OPTIONS (schema '<スキーマ所有者>', table '<テーブル名>');testdb=> CREATE FOREIGN TABLE foreign_oracle_user_tbl
testdb-> (testdb(>no int,testdb(>id text,
testdb(>pass text,
testdb(>level int
testdb(> )testdb-> SERVER oracle_server
testdb-> OPTIONS (schema 'JIP', table 'USER_TBL');CREATE FOREIGN TABLE

select * from foreign_oracle_user_tbl;

testdb=> select * from foreign_oracle_user_tbl;
no | id | pass | level----+---------+---------+------1 | O_user1 | O_pass1 | 3
1 | O_user2 | O_pass1 | 2
1 | O_user3 | O_pass1 | 2
1 | O_user4 | O_pass1 | 1
1 | O_user5 | O_pass1 | 1(5 行)

postgresページ 9

MySQL_fdw
No

概要

対象

ユーザ

コマンド
# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQLshared-5.6.28-1.el7.x86_64.rpm

確認 （確認コマンド等）# ls /usr/local/src/MySQL*/usr/local/src/MySQL-client-5.6.28-1.el7.x86_64.rpm/usr/local/src/MySQL-devel-5.6.28-1.el7.x86_64.rpm/usr/local/src/MySQL-shared-5.6.28-1.el7.x86_64.rpm# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQLdevel-5.6.28-1.el7.x86_64.rpm※クライアント接続ライブラリ（MySQL-client）は接続確認用であり、FDWに必須ではない。

# cd /usr/local/src

1 MySQLライブラリの入手

PostgreSQLサーバ

root# wget http://ftp.jaist.ac.jp/pub/mysql/Downloads/MySQL-5.6/MySQLclient-5.6.28-1.el7.x86_64.rpm

2 MySQL-shredライブラリのイン
PostgreSQLサーバ
ストール

3 MySQL-devel-ライブラリのイン
PostgreSQLサーバ
ストール

4 MySQL-client-ライブラリのイ
ンストール

PostgreSQLサーバ

5 インストール済みMySQLライブ
PostgreSQLサーバ
ラリの確認# rpm -Uvh MySQL-shared-5.6.28-1.el7.x86_64.rpm# rpm -Uvh MySQL-shared-5.6.28-1.el7.x86_64.rpm
警告: MySQL-shared-5.6.28-1.el7.x86_64.rpm: ヘッダー V3
DSA/SHA1 Signature、鍵 ID 5072e1f5: NOKEY
準備しています...
#################################[100%]
更新中 / インストール中...
1:MySQL-shared-5.6.28-1.el7################################# [100%]# rpm -Uvh MySQL-devel-5.6.28-1.el7.x86_64.rpm# rpm -Uvh MySQL-devel-5.6.28-1.el7.x86_64.rpm
警告: MySQL-devel-5.6.28-1.el7.x86_64.rpm: ヘッダー V3 DSA/SHA1
Signature、鍵 ID 5072e1f5: NOKEY
準備しています...
#################################[100%]
更新中 / インストール中...
1:MySQL-devel-5.6.28-1.el7################################# [100%]# rpm -Uvh MySQL-client-5.6.28-1.el7.x86_64.rpm# rpm -Uvh MySQL-client-5.6.28-1.el7.x86_64.rpm
警告: MySQL-client-5.6.28-1.el7.x86_64.rpm: ヘッダー V3
DSA/SHA1 Signature、鍵 ID 5072e1f5: NOKEY
準備しています...
#################################[100%]
更新中 / インストール中...
1:MySQL-client-5.6.28-1.el7################################# [100%]# rpm -qa | grep -i mysql# rpm -qa | grep -i mysql
MySQL-devel-5.6.28-1.el7.x86_64
MySQL-client-5.6.28-1.el7.x86_64
MySQL-shared-5.6.28-1.el7.x86_64

root

root

root

root

ページ 10MySQL_fdw# mysql -u [ユーザ] -h [サーバ名] -p[パスワード] [データベース名]# mysql -u pguser -h 133.227.221.136 -ppguser mysqldb
Warning: Using a password on the command line interface can be
insecure.
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.28 MySQL Community Server (GPL)6 MySQLサーバへの接続

PostgreSQLサーバ

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights
reserved.

root

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.
Mysql>

7 MySQL_fdwの入手

8 コンパイルの実行

9 インストールの実行

PostgreSQLサーバ

PostgreSQLサーバ

PostgreSQLサーバ# cd /usr/local/src/# git clone https://github.com/EnterpriseDB/mysql_fdw.git# cd mysql_fdw# ls /usr/local/src/mysql_fdw
CONTRIBUTING.md README.md
deparse.omysql_fdw.control mysql_init.sh option.c
LICENSEconnection.c expected
mysql_fdw.h
mysql_query.c option.o
META.jsonconnection.o mysql_fdw--1.0.sql mysql_fdw.omysql_query.h sql
Makefile
deparse.c
mysql_fdw.c
mysql_fdw.so
mysql_query.o[root@gp-platform03 mysql_fdw]## export PATH=${PATH}:/usr/pgsql-9.4/bin# make USE_PGXS=1

エラーが表示されないこと.

# make USE_PGXS=1 install

エラーが表示されないこと.

root

root

# ls /usr/pgsql-9.4/lib/mysql_fdw.so/usr/pgsql-9.4/lib/mysql_fdw.soroot

ページ 11MySQL_fdw
CREATE EXTENSION mysql_fdw;$ psql testdb
testdb=# CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
testdb=# \dx

エクステンション
10 PostgreSQLの作成

PostgreSQLサーバ

インストール済みの拡張の一覧
名前 | バージョン | スキーマ |説明------------+------------+-----------+-------------------------------------------------mysql_fdw | 1.0| public| Foreign data wrapper for queryinga MySQL server
oracle_fdw | 1.1| public| foreign data wrapper for Oracle
accessplpgsql | 1.0| pg_catalog | PL/pgSQL procedural language(3 行)

postgres

CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '[サーバ名]', port '[ポート番号');

外部サーバの作
11 PostgreSQL
成PostgreSQLサーバ

testdb=# CREATE SERVER mysql_server
testdb-# FOREIGN DATA WRAPPER mysql_fdw
testdb-# OPTIONS (host '133.227.221.136', port '3306');
CREATE SERVER
testdb=# \des外部サーバー一覧
名前| 所有者 | 外部データラッパー---------------+----------+-------------------mysql_server | postgres | mysql_fdw
oracle_server | postgres | oracle_fdw(2 行)

postgres

GRANT USAGE ON FOREIGN SERVER mysql_server TO [ユーザ名];

testdb=# GRANT USAGE ON FOREIGN SERVER mysql_server TO
jip;
GRANT
testdb=# \c - jip
データベース "testdb" にユーザ"jip"として接続しました。

外部サーバの利
12 PostgreSQL
用権限

PostgreSQLサーバ

postgres

CREATE USER MAPPING FOR [ユーザ名]
SERVER mysql_server
OPTIONS (username ''<MySQLユーザ>', password '<MySQLパスワード>');PostgreSQLユーザーの関連付 PostgreSQLサーバ
13 け

postgresページ 12

testdb=> CREATE USER MAPPING FOR jip
testdb-> SERVER mysql_server
testdb-> OPTIONS (username 'pguser', password 'pguser');
CREATE USER MAPPINGMySQL_fdw

14 PostgreSQL 外部表の作成

15 PostgreSQL 外部表の検索

PostgreSQLサーバ

PostgreSQLサーバ

postgres

CREATE FOREIGN TABLE foreign_mysql_user_tbl(
no int,id text,
pass text,level int)
SERVER mysql_server
OPTIONS (dbname '<データベース名>', table_name ''<テーブル名>');testdb=> CREATE FOREIGN TABLE foreign_mysql_user_tbl
testdb-> (testdb(>no int,testdb(>id text,
testdb(>pass text,
testdb(>level int
testdb(> )testdb-> SERVER mysql_server
testdb-> OPTIONS (dbname 'mysqldb', table_name 'user_tbl');
CREATE FOREIGN TABLE

select * from foreign_mysql_user_tbl;

testdb=> select * from foreign_mysql_user_tbl;
INFO: Successfully connected to MySQL database mysqldb atserver 133.227.221.136 via TCP/IP with cipher <none> (server
version: 5.6.28, protocol version: 10)
no | id | pass | level----+---------+---------+------1 | M_user1 | M_pass1 |3
1 | M_user2 | M_pass1 |2
1 | M_user3 | M_pass1 |2
1 | M_user4 | M_pass1 |1
1 | M_user5 | M_pass1 |1(5 行)

postgresページ 13

postgres_fdw
No

概要

対象

ユーザ

コマンド
CREATE EXTENSION postgres_fdw;

確認 （確認コマンド等）$ psql testdb
testdb=# CREATE EXTENSION postgres_fdw;
CREATE EXTENSION
testdb=# \dx
名前エクステンショ PostgreSQLサーバ
1 PostgreSQL
ンの作成

インストール済みの拡張の一覧| バージョン | スキーマ |説明--------------+------------+-----------+-----------------------------------------------------------------file_fdw| 1.0| public| foreign-data wrapper for flat file acc
essfirebird_fdw | 0.2.5| public| foreign data wrapper for Firebird
multicorn | 1.3.1| public| Multicorn Python bindings for Postgres
9.2.* Foreign Data Wrapper
mysql_fdw | 1.0| public| Foreign data wrapper for querying a My
SQL server
oracle_fdw | 1.1| public| foreign data wrapper for Oracle accessplpgsql| 1.0| pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0| public| foreign-data wrapper for remote PostgreSQL servers
sqlite_fdw | 0.0.1| public| SQLite Foreign Data Wrapper(8 行)

postgres

CREATE SERVER postgres_server
testdb=# CREATE SERVER postgres_server
FOREIGN DATA WRAPPER postgres_fdw
testdb-# FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host '[サーバ名]' , port '[ポート番号]' , dbname '[サーバ名]'); testdb-# OPTIONS(host'133.227.221.136',port'5432',dbname'testdb');
CREATE SERVER

外部サーバの PostgreSQLサーバ
2 PostgreSQL
作成

testdb=# \des外部サーバー一覧
名前| 所有者 | 外部データラッパー-----------------+----------+-------------------csv_server| postgres | file_fdw
firebird_server | postgres | firebird
ldap_server| postgres | multicorn
mysql_server | postgres | mysql_fdw
oracle_server | postgres | oracle_fdw
postgres_server | postgres | postgres_fdw
sqlite_server | postgres | sqlite_fdw(7 行)

postgres

GRANT USAGE ON FOREIGN SERVER postgres_server TO [ユーザ名]; testdb=# GRANT USAGE ON FOREIGN SERVER postgres_server TO jip;
GRANT
PostgreSQL 外部サーバの3 利用権限PostgreSQLサーバ

PostgreSQLユーザーの関
4 連付け

PostgreSQLサーバ

postgrespostgrestestdb=# \c - jip
データベース "testdb" にユーザ"jip"として接続しました。
CREATE USER MAPPING FOR [ユーザ名]
SERVER postgres_server
OPTIONS (user '<Postgresユーザ>', password '<Postgresパスワード>');ページ 14

testdb=> CREATE USER MAPPING FOR jip
SERVER postgres_server
OPTIONS(user'postgres',password'postgres');
CREATE USER MAPPING

postgres_fdw

5 PostgreSQL 外部表の作成 PostgreSQLサーバ

postgres

CREATE FOREIGN TABLE postgres_user_tbl
testdb=> CREATE FOREIGN TABLE postgres_user_tbl(
testdb-> (uidnumber
int,
testdb(> uidnumber int,
uid
text,
testdb(> uid text,
cn
text,
testdb(> cn text,
gidnumber
int,
testdb(> gidnumber int,
Usernumber text,
testdb(> usernumber text,
loginlevel
int,
testdb(> loginlevel text,
uselevelint
testdb(> uselevel text)
testdb(> )SERVER postgres_server
testdb-> SERVER postgres_server
OPTIONS (schema_name'<スキーマ所有者>',table_name'<テーブル名>'); testdb-> OPTIONS(schema_name'public',table_name'portal_tbl');
CREATE FOREIGN TABLE

select * from postgres_user_tbl;

6 PostgreSQL 外部表の検索 PostgreSQLサーバ

postgrestestdb=> select * from postgres_user_tbl;
uidnumber |uid|
cn| gidnumber | usernumber | loginlev
el | uselevel-----------+----------------+----------------+-----------+-----------+-----------+---------52030 | taro.keiri| Taro Keiri|
100 | pass|1|055040 | ichiro.keiri | Ichiro Keiri | 10010 | pass
|1
|0
(中略）
58020 | jiro.keiri| Jiro Keiri| 10010 | pass
|1|055090 | jiro.jinji| Jiro Jinji| 30010 | pass|1|075082 | jiro.kaihatu | Jiro Kaihatu | 40010 | pass|1
|0
(31 行)

ページ 15

firebird_fdw
No

概要

対象

1 firebird-libfbclientのインストー
PostgreSQLサーバ
ル

ユーザ

コマンド
# yum -y install firebird-libfbclientroot# yum -y install firebird-devel

2 firebird--develのインストール

PostgreSQLサーバ

root

3 libfqの入手

PostgreSQLサーバ

root

4 libfqのコンパイル＆インストー
ル

5 環境設定(~postgres/.bash_profile)

PostgreSQLサーバ

PostgreSQLサーバ

root

postgres# cd /usr/local/src/# git clone https://github.com/ibarwick/libfq.git# cd libfq./configure
makemake install$ vi ~postgres/.bash_profile(以下の値を追加)# rpm -qa | grep -i firebird
firebird-libfbclient-2.5.5.26952.0-2.el7.x86_64
firebird-devel-2.5.5.26952.0-2.el7.x86_64
firebird-libfbembed-2.5.5.26952.0-2.el7.x86_64# ls /usr/local/lib
libfq.a libfq.la libfq.so libfq.so.0 libfq.so.0.1.4 libyajl.so libyajl.so.2
libyajl.so.2.0.1 libyajl_s.a$ source .bash_profile$ echo $LD_LIBRARY_PATH/usr/local/lib:

export LD_LIBRARY_PATH=/usr/local/lib:$LD_LIBRARY_PATH$ pg_ctl stop -m fast

$ ps -ef | grep postgresRoot4645 2319 0 00:32 pts/0 00:00:00 su - postgres
postgres 4646 4645 0 00:32 pts/0 00:00:00 -bash
postgres 4889
1 0 00:40 pts/0 00:00:00 /usr/pgsql9.4/bin/postgres
postgres 4890 4889 0 00:40 ?
00:00:00 postgres: logger
process
postgres 4892 4889 0 00:40 ?
00:00:00 postgres: checkpointerprocess
postgres 4893 4889 0 00:40 ?
00:00:00 postgres: writer
process
postgres 4894 4889 0 00:40 ?
00:00:00 postgres: wal writer
process
postgres 4895 4889 0 00:40 ?
00:00:00 postgres: autovacuum
launcher process
postgres 4896 4889 0 00:40 ?
00:00:00 postgres: statscollector process
postgres 4926 4646 0 00:43 pts/0 00:00:00 ps -ef
postgres 4927 4646 0 00:43 pts/0 00:00:00 grep --color=auto
postgres$ pg_ctl start -w

6 PostgreSQLの再起動

確認 （確認コマンド等）

PostgreSQLサーバ

postgres

7 Oracle_fdwの入手

PostgreSQLサーバ

root# cd /usr/local/src/# git clone https://github.com/ibarwick/firebird_fdw.git# cd firebird_fdw

PostgreSQLサーバ

root# export PATH=${PATH}:/usr/pgsql-9.4/bin# make

エラーが表示されないこと.

8 コンパイルの実行# make install

エラーが表示されないこと.
# ls /usr/pgsql-9.4/lib/firebird_fdw.so/usr/pgsql-9.4/lib/firebird_fdw.so

9 インストールの実行

PostgreSQLサーバ

root

ページ 16# ls /usr/local/src/firebird_fdw
BUGS CHANGELOG License META.json Makefile README.md
firebird_fdw.control firebird_fdw.so packaging sql src testfirebird_fdw$ psql <DB名>$ psql testdb

CREATE EXTENSION firebird_fdw;

testdb=# CREATE EXTENSION firebird_fdw;
CREATE EXTENSION
Testdb=# \dx

エクステンション
10 PostgreSQLの作成

PostgreSQLサーバ

PostgreSQL 外部ラッパーの設
11 定
PostgreSQLサーバ

外部サーバの作
12 PostgreSQL
成PostgreSQLサーバ

13 PostgreSQLユーザーとの関連
PostgreSQLサーバ
付け

14 PostgreSQL 外部表の作成

PostgreSQLサーバ

インストール済みの拡張の一覧
名前| バージョン | スキーマ |説明--------------+------------+-----------+-----------------------------------------------------------------file_fdw| 1.0| public | foreign-data wrapper for flat fileaccessfirebird_fdw | 0.2.5| public| foreign data wrapper for Firebird
multicorn | 1.3.1| public | Multicorn Python bindings for
Postgres 9.2.* Foreign Data Wrapper
mysql_fdw | 1.0| public | Foreign data wrapper for queryinga MySQL server
oracle_fdw | 1.1| public| foreign data wrapper for Oracle
accessplpgsql| 1.0| pg_catalog | PL/pgSQL procedural language
postgres_fdw | 1.0| public| foreign-data wrapper for remote
PostgreSQL servers
sqlite_fdw | 0.0.1| public | SQLite Foreign Data Wrapper(8 行)

postgrespostgrespostgrespostgrespostgres

CREATE FOREIGN DATA WRAPPER firebird
HANDLER firebird_fdw_handler
VALIDATOR firebird_fdw_validator;

testdb=# CREATE FOREIGN DATA WRAPPER firebird
testdb-# HANDLER firebird_fdw_handler
testdb-# VALIDATOR firebird_fdw_validator;
CREATE FOREIGN DATA WRAPPER

CREATE SERVER firebird_server
FOREIGN DATA WRAPPER firebird
OPTIONS (address '[サーバ名]',
database '[データベース名]');

testdb=# CREATE SERVER firebird_server
testdb-# FOREIGN DATA WRAPPER firebird
testdb-# OPTIONS (testdb(#address '133.227.221.136',
testdb(#database '/data/firebird/firedb.fdb'
testdb(# );CREATE SERVER

CREATE USER MAPPING FOR [ユーザ名]
SERVER oracle_server
OPTIONS (username '<ユーザ>', password '<パスワード>');testdb=# \desCREATE USER MAPPING FOR CURRENT_USER SERVER
testdb=>firebird_server
外部サーバー一覧
testdb-#'jip', password 'jip');
名前 OPTIONS(username| 所有者 | 外部データラッパー
CREATE USER MAPPING---------------+----------+-------------------oracle_server | postgres | oracle_fdw(1 行)
testdb=# CREATE FOREIGN TABLE fb_test(
testdb(#usercd integer,
testdb(#usernm varchar(100),
testdb(#bumoncd integer,
testdb(#loginflg char(1),testdb(#levelno char(1)testdb(# )testdb-# SERVER firebird_server
testdb-# OPTIONS(
testdb(# table_name 'usertbl'
testdb(# );CREATE FOREIGN TABLE

CREATE FOREIGN TABLE fb_test(
usercd integer,
usernm varchar(100),
bumoncd integer,loginflg char(1),levelno char(1))
SERVER firebird_server
OPTIONS(
table_name '<テーブル名>');

ページ 17

firebird_fdw
select * from fb_test;

15 PostgreSQL 外部表の検索

PostgreSQLサーバ

testdb=# select * from fb_test;
usercd | usernm | bumoncd | loginflg | levelno--------+--------------+---------+----------+--------51030 | Taro Jinji | 200 | 1|052100 | Ichiro Jinji | 20010 | 1|055090 | jiro Jinji | 20010 | 1|058060 | saburo Jinji | 20010 | 1|062010 | kazuo Jinji | 20020 | 1|165030 | fusao Jinji | 20020 | 1|176020 | mituo Jinji | 20020 | 1
|1
(7 行)

postgresページ 18

SQLite_fdw
No

概要

対象

ユーザ

コマンド
# yum -y install sqlite-devel

1 SQLiteライブラリのインストール

PostgreSQLサーバ

2 インストール済みSQLiteライブ
PostgreSQLサーバ
ラリの確認

root

# yum list installed | grep sqlite-develroot

3 SQLite_fdwの入手

PostgreSQLサーバ

root# cd /usr/local/src/# git clone https://github.com/gleu/sqlite_fdw.git# cd sqlite_fdw

4 コンパイルの実行

PostgreSQLサーバ

root# export PATH=${PATH}:/usr/pgsql-9.4/bin# make# make install

5 インストールの実行

PostgreSQLサーバ

root
CREATE EXTENSION sqlite_fdw;

エクステンション
6 PostgreSQLの作成

PostgreSQLサーバ

postgres

確認 （確認コマンド等）# yum -y install sqlite-devel
読み込んだプラグイン:fastestmirror, langpacks
Loading mirror speeds from cached hostfile* base: centos.usonyx.net* epel: epel.mirror.srv.co.ge* extras: centos.usonyx.net* nux-dextop: li.nux.ro* updates: centos.usonyx.net
依存性の解決をしています--> トランザクションの確認を実行しています。
---> パッケージ sqlite-devel.x86_64 0:3.7.17-8.el7 を インストール--> 依存性の処理をしています: sqlite = 3.7.17-8.el7 のパッケージ:
sqlite-devel-3.7.17-8.el7.x86_64--> トランザクションの確認を実行しています。
---> パッケージ sqlite.x86_64 0:3.7.17-4.el7 を 更新---> パッケージ sqlite.x86_64 0:3.7.17-8.el7 を アップデート--> 依存性解決を終了しました。
依存性を解決しました# yum list installed | grep sqlite-devel
sqlite-devel.x86_64
3.7.17-8.el7@base================================================================================
アーキテクチャー#Packagels /usr/local/src/sqlite_fdw
容量License README.md docバージョン
sqlite_fdw.control リポジトリー
src=========================================================
Makefile
THANKS.md sql sqlite_fdw.so=======================
インストール中:
エラーが表示されないこと.
sqlite-devel
x86_643.7.17-8.el7
base
104 k
依存性関連での更新をします:sqlitex86_643.7.17-8.el7
base
393 k
エラーが表示されないこと.
トランザクションの要約=========================================================# ls /usr/pgsql-9.4/lib/sqlite_fdw.so=======================/usr/pgsql-9.4/lib/sqlite_fdw.so
インストール 1 パッケージ更新( 1 個の依存関係のパッケージ)$ psql testdb
合計容量: 498 k
testdb=#
CREATE EXTENSION
sqlite_fdw;
総ダウンロード容量:
104 k
CREATEEXTENSION
Downloading
packages:sqlite-devel-3.7.17-8.el7.x86_64.rpm| 104 kB 00:00
testdb=#\dx
Running transaction
check
Running transaction test インストール済みの拡張の一覧
名前 | バージョン| スキーマ |説明Transaction
test succeeded------------+------------+-----------Running transaction+-------------------------------------------------------更新します: sqlite-3.7.17-8.el7.x86_64
1/3----------インストール中: sqlite-devel-3.7.17-8.el7.x86_64
multicorn | 1.3.1| public| Multicorn Python bindings for
2/3
Postgres
Data Wrapper
整理中 9.2.* Foreign: sqlite-3.7.17-4.el7.x86_64
3/3
mysql_fdw
public| Foreign data wrapper for querying
検証中 | 1.0:| sqlite-3.7.17-8.el7.x86_64
1/3
a 検証中
MySQL server: sqlite-devel-3.7.17-8.el7.x86_64
oracle_fdw | 1.1| public| foreign data wrapper for Oracle
2/3
access検証中: sqlite-3.7.17-4.el7.x86_64
3/3
plpgsql | 1.0| pg_catalog | PL/pgSQL procedural language
sqlite_fdw | 0.0.1| public| SQLite Foreign Data Wrapper
インストール:(5sqlite-devel.x86_64
行)
0:3.7.17-8.el7
依存性を更新しました:
sqlite.x86_64 0:3.7.17-8.el7
完了しました!

ページ 19

SQLite_fdw
CREATE SERVER sqlite_server
FOREIGN DATA WRAPPER sqlite_fdw
OPTIONS (database [リモートファイル名]'');

PostgreSQL 外部サーバの作
7 成PostgreSQLサーバ

testdb=# CREATE SERVER sqlite_server
testdb-# FOREIGN DATA WRAPPER sqlite_fdw
testdb-# OPTIONS (database '/mnt/data/litedb');
CREATE SERVER
testdb=# \des外部サーバー一覧
名前| 所有者 | 外部データラッパー---------------+----------+-------------------ldap_server | postgres | multicorn
mysql_server | postgres | mysql_fdw
oracle_server | postgres | oracle_fdw
sqlite_server | postgres | sqlite_fdw(4 行)

postgres※NFSによるリモートサーバのファイル共有が設定済み
・/mnt/data

8 PostgreSQL 外部表の作成

9 PostgreSQL 外部表の検索

PostgreSQLサーバ

PostgreSQLサーバ

postgres

CREATE FOREIGN TABLE sqlite_user_tbl(
userid int,
name varchar(100),
bumon int,
login char(1),level char(1))
SERVER sqlite_server
OPTIONS (table 'usertbl');testdb=# CREATE FOREIGN TABLE sqlite_user_tbl
testdb-# (testdb(#userid int,
testdb(#name varchar(100),
testdb(#bumon int,
testdb(#login char(1),testdb(#level char(1)testdb(# )testdb-# SERVER sqlite_server
testdb-# OPTIONS (table 'usertbl');
CREATE FOREIGN TABLE

select * from sqlite_user_tbl;

testdb=# select * from sqlite_user_tbl;
WARNING: The sqlite3 database has not been analyzed.
HINT: Run ANALYZE on table "usertbl", database"/mnt/data/litedb".
userid | name | bumon | login | level--------+-----------+-------+-------+------51030 | 人事 太郎 | 200 | 1|052100 | 人事 一郎 | 20010 | 1|055090 | 人事 二郎 | 20010 | 1|058060 | 人事 三郎 | 20010 | 1|062010 | 人事 一男 | 20020 | 1|165030 | 人事 二男 | 20020 | 1|176020 | 人事 三男 | 20020 | 1|1(7 行

postgresページ 20LDAP_fdw(python)
No

概要

対象

ユーザ

コマンド
# yum -y install python-devel

1 python-develのインストール

PostgreSQLサーバ

確認 （確認コマンド等）※Python 2.7.5が事前にインストール済み# yum -y install python-devel
読み込んだプラグイン:fastestmirror, langpacks
Loading mirror speeds from cached hostfile* base: centos.usonyx.net* epel: epel.mirror.net.in* extras: ftp.iij.ad.jp* nux-dextop: li.nux.ro* updates: ftp.iij.ad.jp
依存性の解決をしています--> トランザクションの確認を実行しています。
---> パッケージ python-devel.x86_64 0:2.7.5-34.el7 を インストール--> 依存性の処理をしています: python(x86-64) = 2.7.5-34.el7 のパッケージ: pythondevel-2.7.5-34.el7.x86_64--> トランザクションの確認を実行しています。
---> パッケージ python.x86_64 0:2.7.5-16.el7 を 更新---> パッケージ python.x86_64 0:2.7.5-34.el7 を アップデート--> 依存性の処理をしています: python-libs(x86-64) = 2.7.5-34.el7 のパッケージ:
python-2.7.5-34.el7.x86_64--> トランザクションの確認を実行しています。
---> パッケージ python-libs.x86_64 0:2.7.5-16.el7 を 更新---> パッケージ python-libs.x86_64 0:2.7.5-34.el7 を アップデート--> 依存性解決を終了しました。

root

依存性を解決しました# yum -y install python-pip

2 python-pipのインストール

PostgreSQLサーバ

root# pip install pgxnclient

3 pgxnclientのインストール

PostgreSQLサーバ

root# pip install ldap3
ldap3 libraryのインスト PostgreSQLサーバ
4 Pythonールroot

ページ 21=======================================================================#=========
yum -y install python-pip
Package
アーキテクチャー langpacks
読み込んだプラグイン:fastestmirror,
リポジトリー 容量
Loading mirror speeds fromバージョン
cached hostfile=======================================================================* base: centos.usonyx.net=========* epel: epel.mirror.net.in
インストール中:* extras: ftp.iij.ad.jp
x86_642.7.5-34.el7
base
391 k
*python-devel
nux-dextop: li.nux.ro
依存性関連での更新をします:* updates: ftp.iij.ad.jp
pythonx86_642.7.5-34.el7
base
88 k
依存性の解決をしています
python-libs
x86_642.7.5-34.el7
base
5.6 M
-->
トランザクションの確認を実行しています。
---> パッケージ python-pip.noarch 0:7.1.0-1.el7 を インストール
トランザクションの要約-->
依存性解決を終了しました。
================================================================================
依存性を解決しました# pip install pgxnclient
インストール
1 パッケージ/usr/lib/python2.7/site更新( 2 個の依存関係のパッケージ)=======================================================================
packages/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90:=========
InsecurePlatformWarning: A true SSLContext object is not available. This prevents
合計容量:
M アーキテクチャー
Packageurllib3
from6.1
configuring
SSL appropriately and may cause certain SSL connections to
総ダウンロード容量:
391 kseeバージョン
リポジトリー 容量
fail. For more information,
Downloading
packages:=======================================================================
https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
python-devel-2.7.5-34.el7.x86_64.rpm| 391 kB 00:00=========
InsecurePlatformWarning
Runningtransaction
check7.1.0, however version 8.0.2 is available.
インストール中:You are using
pip version
transaction
test
python-pip
epelM#Running
pipshould
install
ldap3 noarchYouconsiderupgrading via7.1.0-1.el7
the 'pip install --upgrade
pip'1.5
command.
Transaction
testYou
are using
pipsucceededversion 7.1.0, however version 8.0.2 is available.
Collecting
pgxnclient
Runningtransaction
トランザクションの要約
Youshouldconsider upgrading via the 'pip install --upgrade pip' command.
/usr/lib/python2.7/site更新します: python-libs-2.7.5-34.el7.x86_64
1/5=======================================================================
Collecting
ldap3
packages/pip/_vendor/requests/packages/urllib3/util/ssl_.py:90:
更新します:python-2.7.5-34.el7.x86_64
2/5=========
Downloading
ldap3-1.0.4-py2.py3-none-any.whl(327kB)
InsecurePlatformWarning: A true SSLContext object is not available. This prevents
インストール中: python-devel-2.7.5-34.el7.x86_64
インストール
1 パッケージ
100%from|????????????????????????????????|
890kB/surllib3
configuring
SSL
appropriately and327kB
may causecertain SSL3/5
connections to
整理中:python-2.7.5-16.el7.x86_64
4/5Collecting
pyasn1>=0.1.8(from
ldap3)
fail. For more information, see
整理中: python-libs-2.7.5-16.el7.x86_64
5/5
総ダウンロード容量:
1.5
M
Downloading pyasn1-0.1.9-py2.py3-none-any.whl
https://urllib3.readthedocs.org/en/latest/security.html#insecureplatformwarning.
検証中:
python-devel-2.7.5-34.el7.x86_64
1/5
インストール容量:
6.6
M
Installing
collected
packages:pyasn1,
ldap3
InsecurePlatformWarning
検証中: ldap3-1.0.4
python-libs-2.7.5-34.el7.x86_64
2/5
Downloading
Successfully
installed
pyasn1-0.1.9
Downloadingpackages:
pgxnclient-1.2.1.tar.gz(87kB)
検証中: python-2.7.5-34.el7.x86_64
3/5
python-pip-7.1.0-1.el7.noarch.rpm| 1.5 MB 00:15
100% |????????????????????????????????|
90kB 2.7MB/s
検証中transaction: python-libs-2.7.5-16.el7.x86_64
4/5Runningcheck
Installing
collected packages:
pgxnclient
検証中:
python-2.7.5-16.el7.x86_64
5/5Runningtransaction
test
Running setup.py install for pgxnclient
Transaction
succeeded
Successfullytest
installed
pgxnclient-1.2.1
インストール:
Runningtransaction
python-devel.x86_64
警告:
RPMDB は yum 0:2.7.5-34.el7
以外で変更されました。
インストール中: python-pip-7.1.0-1.el7.noarch
1/1
依存性を更新しました:
検証中: python-pip-7.1.0-1.el7.noarch
1/1
python.x86_64 0:2.7.5-34.el7
python-libs.x86_64 0:2.7.5-34.el7
インストール:
完了しました!
python-pip.noarch 0:7.1.0-1.el7
完了しました!

LDAP_fdw(python)# export PATH=${PATH}:/usr/pgsql-9.4/bin# pgxn install multicorn
汎用FDW（multicorn）の PostgreSQLサーバ
5 Python
インストールroot$ psql <DB名>CREATE EXTENSION multicorn;

エクステンション
6 PostgreSQLの作成

PostgreSQLサーバ

postgres

CREATE SERVER ldap_server foreign data wrapper multicorn
options (wrapper 'multicorn.ldapfdw.LdapFdw');
外部サーバの作
7 PostgreSQL
成

8 PostgreSQL 外部表の作成

PostgreSQLサーバ

PostgreSQLサーバ

postgrespostgres

CREATE FOREIGN TABLE ldap_user_tbl (uidNumber character varying,uid character varying,cn character varying,userPassword character varying) server ldap_server options (uri 'ldap://<サーバ名>',
path '<検索ベース>',
scope '<検索階層>',
binddn '<バインドDN >',bindpwd '<バインドDNパスワード >',objectClass '検索属性');

ページ 22# pgxn install multicorn
INFO: best version: multicorn 1.3.1
INFO: saving /tmp/tmpU5HGqJ/multicorn-1.3.1.zip
INFO: unpacking: /tmp/tmpU5HGqJ/multicorn-1.3.1.zip
INFO: building extension
Python version is 2.7[ -d sql ] || mkdir sql[ -d src ] || mkdir src
touch directories.stamp
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement$-Wendif-labels
psql testdb -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing-fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,testdb=#
CREATE EXTENSION
multicorn;
D_FORTIFY_SOURCE=2-fexceptions-fstack-protector-strong --param=ssp-bufferCREATE
EXTENSION
size=4 -grecord-gcc-switches-m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I.
-I./ -I/usr/include/python2.7 -I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsqltestdb-#\dx
9.4/include/internal-D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -osrc/errors.o src/errors.c インストール済みの拡張の一覧| バージョン | スキーマ-Wpointer-arith
|
説明
gcc名前
-Wall -Wmissing-prototypes-Wdeclaration-after-statement------------+------------+------------Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing+-------------------------------------------------------------------fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,multicorn | 1.3.1| public| Multicorn
Python bindings for Postgres
9.2.* Foreign
D_FORTIFY_SOURCE=2-fexceptions-fstack-protector-strong--param=ssp-bufferData
size=4Wrapper-grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I.
mysql_fdw| 1.0| public -I. |-I./
Foreigndata wrapper for querying a MySQL
server-I./-I/usr/include/python2.7-I/usr/pgsql-9.4/include/server-I/usr/pgsqloracle_fdw | 1.1| public| foreigndata wrapper for Oracle
access
9.4/include/internal-D_GNU_SOURCE-I/usr/include/libxml2-I/usr/include-c -oplpgsql | 1.0src/python.c| pg_catalog | PL/pgSQL procedural language
src/python.o(4
gcc行)-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement-Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing-fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffertestdb=#
CREATE SERVER ldap_server
foreign data wrapper
multicorn options( -I.
size=4 -grecord-gcc-switches-m64 -mtune=generic-DLINUX_OOM_ADJ=0-fpic
testdb(#wrapper 'multicorn.ldapfdw.LdapFdw'-I./ -I/usr/include/python2.7-I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsqltestdb(#);
9.4/include/internal-D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o
CREATESERVER
src/query.osrc/query.c
testdb=#gcc -Wall \des-Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement
外部サーバー一覧-Wendif-labels-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing
名前 -fexcess-precision=standard| 所有者 | 外部データラッパー-fwrapv-O2 -g -pipe -Wall -Wp,---------------+----------+-------------------D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-bufferldap_server| postgres | multicorn
size=4-grecord-gcc-switches-m64 -mtune=generic -DLINUX_OOM_ADJ=0 -fpic -I.
mysql_server| postgres | mysql_fdw-I./-I/usr/include/python2.7-I. -I./ -I/usr/pgsql-9.4/include/server -I/usr/pgsqloracle_server | postgres| oracle_fdw -I/usr/include/libxml2 -I/usr/include -c -o
9.4/include/internal-D_GNU_SOURCE(3
行)
src/multicorn.osrc/multicorn.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement-Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing-fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,testdb=>
CREATE FOREIGNTABLE ldap_user_tbl(
D_FORTIFY_SOURCE=2-fexceptions-fstack-protector-strong--param=ssp-buffertestdb(>
uidNumber charactervarying,size=4 -grecord-gcc-switches-m64-mtune=generic -DLINUX_OOM_ADJ=0 -fpic
testdb(>uid charactersrc/errors.ovarying,-shared -o multicorn.so
src/python.o src/query.o src/multicorn.otestdb(>cn charactervarying,-L/usr/pgsql-9.4/lib-Wl,--as-needed-L/usr/lib64 -Wl,--as-needed -Wl,testdb(>
userPassword character varying -L/usr/lib64 -lpython2.7 -lpthread
rpath,'/usr/pgsql-9.4/lib',--enable-new-dtags
testdb(>server ldap_server options (-ldl -lutil) -lm
testdb(>uri 'ldap://133.227.221.136',.//preflight-check.sh
testdb(>path 'ou=Users,dc=example,dc=com',cp sql/multicorn.sql
sql/multicorn--1.3.1.sql
testdb(>scope'sub',
INFO: installing
extension
testdb(>binddn'cn=manager,dc=example,dc=com',
Python version
is 2.7
testdb(>bindpwd 'admin',.//preflight-check.sh
testdb(>objectClass'*'/usr/bin/mkdir-p '/usr/pgsql-9.4/lib'
testdb(>);/usr/bin/mkdir-p '/usr/pgsql-9.4/share/extension'
CREATEFOREIGN
TABLE/usr/bin/mkdir-p '/usr/pgsql-9.4/share/extension'/usr/bin/mkdir -p '/usr/share/doc/pgsql/extension'/usr/bin/install -c -m 755 multicorn.so '/usr/pgsql-9.4/lib/multicorn.so'
cp .//setup.py ./setup--1.3.1.py
sed -i -e "s/__VERSION__/1.3.1-dev/g" ./setup--1.3.1.py
python2.7 ./setup--1.3.1.py installrunning installrunning bdist_egg
running egg_info
creating python/multicorn.egg-info
writing python/multicorn.egg-info/PKG-INFO
writing top-level names to python/multicorn.egg-info/top_level.txt
writing dependency_links to python/multicorn.egg-info/dependency_links.txt
writing manifest file 'python/multicorn.egg-info/SOURCES.txt'
reading manifest file 'python/multicorn.egg-info/SOURCES.txt'
writing manifest file 'python/multicorn.egg-info/SOURCES.txt'
installing library code to build/bdist.linux-x86_64/egg
running install_lib
running build_py

LDAP_fdw(python)
select * from ldap_user_tbl;

9 PostgreSQL 外部表の検索

PostgreSQLサーバ

testdb=> select * from ldap_user_tbl;
uidnumber |uid|cn| userpassword-----------+-----------------+-----------------+-------------|||
1001| takahiko.takeda | Takahiko Takeda | takeda
1002| shinya.yokoyama | Shinya Yokoyama | yokoyama
1003| osamu.inoue| Osamu Inoue | inoue1004| naoki.ishikawa | Naoki Ishikawa | ishikawa
1005| kazuo.tamura | Kazuo Tamura | tamura(6 行)

postgresページ 23

file_fdw
No

概要

対象

ユーザ

コマンド
CREATE EXTENSION file_fdw;

確認 （確認コマンド等）$ psql testdb
testdb=# CREATE EXTENSION file_fdw;
CREATE EXTENSION
testdb=# \dx

PostgreSQL エクステンション
1 の作成

PostgreSQLサーバ

インストール済みの拡張の一覧
名前 | バージョン | スキーマ |説明------------+------------+-----------+-----------------------------------------------------------------file_fdw | 1.0| public | foreign-data wrapper for flat fileaccessmulticorn | 1.3.1| public| Multicorn Python bindings for
Postgres 9.2.* Foreign Data Wrapper
mysql_fdw | 1.0| public| Foreign data wrapper for queryinga MySQL server
oracle_fdw | 1.1| public| foreign data wrapper for Oracle
accessplpgsql | 1.0| pg_catalog | PL/pgSQL procedural language
sqlite_fdw | 0.0.1| public| SQLite Foreign Data Wrapper(6 行)

postgres

CREATE SERVER csv_server
FOREIGN DATA WRAPPER file_fdw;

外部サーバの作
2 PostgreSQL
成3 PostgreSQL 外部表の作成

PostgreSQLサーバ

PostgreSQLサーバ

testdb=# \des外部サーバー一覧
名前| 所有者 | 外部データラッパー---------------+----------+-------------------csv_server | postgres | file_fdw
ldap_server | postgres | multicorn
mysql_server | postgres | mysql_fdw
oracle_server | postgres | oracle_fdw
sqlite_server | postgres | sqlite_fdw(5 行)

postgrespostgrestestdb=# CREATE SERVER csv_server
testdb-# FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER

CREATE FOREIGN TABLE csv_user_tbl(
uidnumber
int,
uid
text,
cn
text,
gidnumber
int,
userpassword text,
loginlevel
int,
uselevel
int)
SERVER csv_server
OPTIONS ( filename '/mnt/data/user.csv', format 'csv' );

testdb=# CREATE FOREIGN TABLE csv_user_tbl
testdb-# (testdb(#uidnumber
int,
testdb(#uid
text,
testdb(#cn
text,
testdb(#gidnumber
int,
testdb(#userpassword text,
testdb(#loginlevel
int,
testdb(#uselevelint
testdb(# )testdb-# SERVER csv_server
testdb-# OPTIONS ( filename '/mnt/data/user.csv', format 'csv' );CREATE FOREIGN TABLE
NFSによるリモートサーバのファイル共有が設定済み
・/mnt/data

ページ 24

file_fdw
select * from csv_user_tbl;

4 PostgreSQL 外部表の検索

PostgreSQLサーバ

postgresページ 25

testdb=# select * from cvs_user_tbl;
uidnumber |uid|
cn| gidnumber | userpassword |loginlevel | uselevel-----------+----------------+----------------+----------+--------------+------------+---------52030 | taro.keiri| Taro Keiri|
100 | pass|1|055040 | ichiro.keiri | Ichiro Keiri | 10010 | pass|
1|058020 | jiro.keiri| Jiro Keiri| 10010 | pass|
1|061050 | saburo.keiri | Saburo Keiri | 10010 | pass|1|053150 | kazuo.keiri | Kazuo Keiri | 10020 | pass|
1|071080 | fusao.keiri | Fusao Keiri | 10020 | pass|
1|079010 | mituo.keiri | Mituo Keiri | 10020 | pass|1|051030 | taro.jinji| Taro Jinji |200 | pass|
1|0

検証シナリオ①◆シナリオ検証①：人事太郎が休職・業務要件：すべてのシステムのログイン許可を不可状態に変更する
実行する処理すべてのシステムのログインを不可状態に変更する

実行クエリ
testdb=# UPDATE mysql_user_tbl SET login = 0 WHERE userid = 51030;
UPDATE oracle_kintai_tbl SET loginkyoka = 0 WHERE uidnumber = 51030;
UPDATE 1
testdb=# UPDATE oracle_kintai_tbl SET loginkyoka = 0 WHERE uidnumber = 51030;
UPDATE 1
testdb=# UPDATE postgres_user_tbl SET loginlevel = 0 WHERE uidnumber = 51030;
UPDATE fb_user_tbl SET loginflg = 0 WHERE usercd = 51030;
UPDATE 1
testdb=# UPDATE oracle_user_tbl SET loginkyoka = 0 WHERE userid = 51030;
UPDATE 1◆業務前
ユーザ名
Taro Jinji

ユーザID

部署ID

51030

人事給与login200

人事給与利用権限1

会計login1

会計利用権限1

勤怠管理login0

勤怠管理利用権限 社員ポータルlogin 社員ポータル利用権限 福利厚生login
1

0

1

0

福利厚生利用権限
1

0◆業務完了後（赤字部分が変更箇所）
ユーザ名
Taro Jinji

ユーザID
51030

部署ID

人事給与login200

人事給与利用権限01

会計login

会計利用権限0

勤怠管理login0

ページ 26

勤怠管理利用権限 社員ポータルlogin 社員ポータル利用権限 福利厚生login
0

0

0

0

福利厚生利用権限
10

検証シナリオ②◆シナリオ検証②：営業三男が経理部財務課に異動
・業務要件：
人事給与システムに一般ユーザとして新規ユーザ登録
会計システムに一般ユーザとして新規ユーザ登録
勤怠管理システムの所属組織を経理部に変更
社員ポータルサイト上の所属組織を経理部に変更
実行する処理人事給与システムに一般ユーザとしてユーザ登録

会計システムに一般ユーザとしてユーザ登録

勤怠管理システムの所属組織を経理部に変更
社員ポータルサイト上の所属組織を経理部に変更

◆業務前
ユーザ名
Mituo Eigyo

ユーザID
部署ID
人事給与login
74031
30020

実行クエリ
testdb=# INSERT INTO oracle_user_tbl VALUES
testdb-# (testdb(# 74031,'営業 三男',10020,'1','0'
testdb(# );INSERT 0 1
testdb=# INSERT INTO mysql_user_tbl VALUES
testdb-# (testdb(# 74031,'営業 三男',10020,'1','0'
testdb(# );INSERT 0 1
testdb=# UPDATE oracle_kintai_tbl SET gidnumber=10020 WHERE uidnumber = 74031;
UPDATE 1
testdb=# UPDATE postgres_user_tbl SET gidnumber=10020 WHERE uidnumber = 74031;
UPDATE 1

人事給与利用権限 会計login◆業務完了後（赤字は変更した箇所）
ユーザ名
ユーザID
部署ID
人事給与login 人事給与利用権限 会計login
Mituo Eigyo
74031
10020
1
0

会計利用権限1

勤怠管理login

勤怠管理利用権限 社員ポータルlogin 社員ポータル利用権限 福利厚生login
10
10

福利厚生利用権限

会計利用権限 勤怠管理login 勤怠管理利用権限 社員ポータルlogin 社員ポータル利用権限 福利厚生login
0
1
0
1
0

福利厚生利用権限

ページ 27

検証シナリオ③◆シナリオ検証③：人事三郎が主任に昇格
・業務要件：人事給与システムの権限を管理者権限に変更
実行する処理実行クエリ

人事給与システムの権限を管理者権限に変更

testdb=# UPDATE oracle_user_tbl SET kengen = 1 WHERE userid = 58060;
UPDATE 1

◆業務前
ユーザ名

ユーザID

Saburo Jinji

部署ID
58060

20010

人事給与login

人事給与利用権限

10

人事給与login

人事給与利用権限

1

1

会計login

会計利用権限勤怠管理login

勤怠管理利用権限

1

社員ポータルlogin0

社員ポータル利用権限
1

0

福利厚生login

福利厚生利用権限

10

福利厚生login

福利厚生利用権限

1

0◆業務完了後（赤字は変更した箇所）
ユーザ名
Saburo Jinji

ユーザID

部署ID
58060

20010

会計login

会計利用権限勤怠管理login1

ページ 28

勤怠管理利用権限

社員ポータルlogin0

社員ポータル利用権限
10

検証シナリオ④◆シナリオ検証④：人事二郎が営業部に異動
・業務要件：
人事給与システムのユーザ登録を抹消する
福利厚生システムのユーザ登録を抹消する
勤怠管理システムの権限を一般ユーザ権限に変更
勤怠管理システム上の所属組織を営業部に変更
社員ポータルサイト上の所属組織を営業部に変更
実行する処理実行クエリ

人事給与システムのユーザ登録を抹消する

testdb=# DELETE FROM oracle_user_tbl WHERE userid = 55090;
DELETE 1

福利厚生システムのユーザ登録を抹消する

testdb=# DELETE FROM fb_user_tbl WHERE usercd = 55090;
DELETE 1

勤怠管理システムの権限を一般ユーザ権限に変更

testdb=# UPDATE oracle_kintai_tbl SET loginkyoka = 0,gidnumber = 30010
WHERE uidnumber = 55090;

勤怠管理システム上の所属組織を営業部に変更

UPDATE 1

社員ポータルサイト上の所属組織を営業部に変更

testdb=# UPDATE postgres_user_tbl SET gidnumber = 30010
WHERE uidnumber = 55090;
UPDATE 1

◆業務前
ユーザ名

ユーザID

Jiro Jinji

部署ID
55090

人事給与login

人事給与利用権限

1

1

人事給与login

人事給与利用権限

20010

会計login

会計利用権限勤怠管理login

勤怠管理利用権限0

社員ポータルlogin0

社員ポータル利用権限
1

福利厚生login

福利厚生利用権限

10

福利厚生login

福利厚生利用権限0◆業務完了後（赤字は変更した箇所）
ユーザ名
Jiro Jinji

ユーザID

部署ID
55090

30010 （削除）

会計login

会計利用権限勤怠管理login（削除）0

ページ 29勤怠管理利用権限

社員ポータルlogin0

社員ポータル利用権限
1

0 （削除）（削除）検証シナリオ⑤◆検証シナリオ⑤：開発二郎が退職・業務要件：
勤怠管理システムのユーザ登録を抹消する社員ポータルサイト上のユーザ登録を抹消する
実行する処理勤怠管理システムのユーザ登録を抹消する社員ポータルサイト上のユーザ登録を抹消する

◆業務前
ユーザ名
Jiro Kaihatu

ユーザID
部署ID
人事給与login
75082
40010

実行クエリ
testdb=# DELETE FROM oracle_kintai_tbl WHERE uidnumber = 75082;
DELETE 1
testdb=# DELETE FROM postgres_user_tbl WHERE uidnumber = 75082;
DELETE 1

人事給与利用権限

会計login

会計利用権限勤怠管理login◆業務完了後（赤字は変更した箇所）(レコード削除)

ページ 30

勤怠管理利用権限 社員ポータルlogin 社員ポータル利用権限 福利厚生login
10
10

福利厚生利用権限

検証結果◆シナリオで想定した業務の実行状態を比較したものです
以下の表は、全システムの外部表を結合し、その中からログイン許可情報、システム利用権限をそれぞれ取り出したものです。
ユーザ名

ユーザID

部署ID

人事給与login

人事給与利用権限

会計login

会計利用権限勤怠管理login

勤怠管理利用権限

社員ポータルlogin

社員ポータル利用権限

福利厚生login

Taro Keiri

52030

100

1

0

1

1

1

0

1

0

Taro Jinji

51030

200

1

1

1

0

1

0

1

0

Taro Eigyo

52071

300

1

0

1

0

Taro Kaihatu

67022

400

1

0

1

0

Taro System

58023

500

1

1

1

1

Ichiro Keiri

55040

10010

1

0

1

1

1

0

1

0

Saburo Keiri

61050

10010

1

0

1

0

1

0

1

0

Jiro Keiri

58020

10010

1

0

1

0

1

0

1

0

Mituo Keiri

79010

10020

1

0

1

0

1

0

1

0

Kazuo Keiri

53150

10020

1

0

1

1

1

0

1

0

Fusao Keiri

71080

10020

1

0

1

0

1

0

1

0

Ichiro Jinji

52100

20010

1

1

1

0

1

0

1

業務③で変更Saburo Jinji

58060

20010

1

0

1

0

業務④で変更Jiro Jinji

55090

20010

1

1

0

Kazuo Jinji

62010

20020

1

1

Mituo Jinji

76020

20020

0

0

Fusao Jinji

65030

20020

1

1

Ichiro Eigyo

61011

Jiro Eigyo

業務①で変更業務②で変更業務⑤で変更

福利厚生利用権限
1

0

0

1

0

1

0

1

0

0

1

0

1

0

1

0

1

0

1

1

1

0

1

0

1

1

1

0

1

0

1

1

30010

1

0

1

0

62051

30010

1

0

1

0

Saburo Eigyo

67071

30010

1

0

1

0

Mituo Eigyo

74031

30020

1

0

1

0

1
1

0
0

Kazuo Eigyo

72011

30020

1

0

1

0

Fusao Eigyo

73021

30020

1

0

1

0

Jiro Kaihatu

75082

40010

1

0

1

0

Ichiro Kaihatu

73092

40010

1

0

1

0

Saburo Kaihatu

77142

40010

1

0

1

0

Kazuo Kaihatu

75222

40020

1

0

1

0

Fusao Kaihatu

76892

40020

1

0

1

0

Mituo Kaihatu

74322

40020

1

0

1

0

Jiro System

67183

50010

1

1

1

1

Ichiro System

63043

50010

1

1

1

1

(31 行)

ページ 31

検証結果

ユーザ名

ユーザID

部署ID

人事給与login

人事給与利用権限

会計login

会計利用権限勤怠管理login

勤怠管理利用権限

社員ポータルlogin

社員ポータル利用権限

福利厚生login

Taro Keiri

52030

100

1

0

1

1

1

0

1

0

Taro Jinji

51030

200

0

1

0

0

0

0

0

0

Taro Eigyo

52071

300

1

0

1

0

Taro Kaihatu

67022

400

1

0

1

0

Taro System

58023

500

1

1

1

1

Ichiro Keiri

55040

10010

1

0

1

1

1

0

1

0

Saburo Keiri

61050

10010

1

0

1

0

1

0

1

0

Jiro Keiri

58020

10010

1

0

1

0

1

0

1

0

Mituo Keiri

79010

10020

1

0

1

0

1

0

1

0

Kazuo Keiri

53150

10020

1

0

1

1

1

0

1

0

Fusao Keiri

71080

10020

1

0

1

0

1

0

1

0

業務②で変更Mituo Eigyo

74031

10020

1

0

1

0

1

0

1

0

Ichiro Jinji

52100

20010

1

1

1

0

1

0

1

業務③で変更Saburo Jinji

58060

20010

1

1

1

0

1

Kazuo Jinji

62010

20020

1

1

1

0

1

0

Fusao Jinji

65030

20020

1

1

1

0

1

Mituo Jinji

76020

20020

0

0

Saburo Eigyo

67071

Jiro Eigyo

業務①で変更業務④で変更

業務⑤によってレコード一件減福利厚生利用権限
1

0

0

1

0

0

1

0

1

0

1

1

0

1

0

1

1

1

0

1

0

1

1

30010

1

0

1

0

62051

30010

1

0

1

0

Ichiro Eigyo

61011

30010

1

0

1

0

Jiro Jinji

55090

30010 （削除）

0

0

1

0 （削除）

Fusao Eigyo

73021

30020

1

0

1

0

Kazuo Eigyo

72011

30020

1

0

1

0

（削除）

Ichiro Kaihatu

73092

40010

1

0

1

0

Saburo Kaihatu

77142

40010

1

0

1

0

Kazuo Kaihatu

75222

40020

1

0

1

0

Fusao Kaihatu

76892

40020

1

0

1

0

Mituo Kaihatu

74322

40020

1

0

1

0

Jiro System

67183

50010

1

1

1

1

Ichiro System

63043

50010

1

1

1

1

(30 行)

ページ 32（削除）Oracle_fdw動作検証◆検証環境準備-

操作

コマンド・結果【Postgres】

コマンド・結果【Oracle】
SQL> CREATE TABLE JIP.USER_TABLE(
user_id NUMBER,
user_name VARCHAR2(20),Oracleに検証用のテーブルを作成-

password VARCHAR2(20),user_group VARCHAR2(20),PRIMARY KEY(user_id));
表が作成されました。

検証用テーブルにデータ挿入-

省略（本文参照）

testdb=> CREATE FOREIGN TABLE FDW_TEST_PSQL_ORACLE
testdb-> (testdb(> user_id INTEGER OPTIONS(KEY'YES'),
testdb(> user_name TEXT,

環境準備
外部テーブルの作成

testdb(> password TEXT,-

testdb(> user_group TEXT,
testdb(> )testdb-> SERVER oracle_server
testdb-> OPTIONS(SCHEMA'JIP',TABLE'USER_TABLE');CREATE FOREIGN TABLE
testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;
user_id | user_name| password | user_group---------+----------------+--------------+-----------検証用テーブルの一覧

1 | David_Hume

SQL> SELECT * FROM JIP.USER_TABLE;| pass_hume

2 | Immanuel_Kant | pass_kant| thought| philosophy

USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- -------------------1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy

2 Immanuel_Kant

pass_kantphilosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy(4 行)

ページ 33

Oracle_fdw動作検証◆基本動作（CRUD）の検証
検証内容

操作

コマンド・結果【Postgres】

コマンド・結果【Oracle】

testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;

SQL> SELECT * FROM JIP.USER_TABLE;

user_id | user_name| password | user_group---------+----------------+--------------+-----------更新前テーブルの確認

1 | David_Hume| pass_hume

2 | Immanuel_Kant | pass_kant| thought

USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- -------------------1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy| philosophy

2 Immanuel_Kant

pass_kantphilosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy(4 行)
testdb=> INSERT INTO FDW_TEST_PSQL_ORACLE VALUES
testdb-> (testdb(> 5,
uesr_id = 5 のレコードを新規登録する

testdb(> 'Kitaro_Nishida',-

testdb(> 'pass_kitaro',
testdb(> 'philosophy'

任意のレコードを更新する
任意のレコードを削除する
レコードを新規登録する

testdb(> );user_jd = 2 のレコードのuser_groupを、'philosophy' から 'thought'に更新する
user_jd = 4 のレコードを削除する

INSERT 0 1
testdb=> UPDATE FDW_TEST_PSQL_ORACLESET user_group = 'thought' WHERE user_id = 2;
UPDATE 1
testdb=> DELETE FROM FDW_TEST_PSQL_ORACLE
WHERE user_id = 4;
DELETE 1
testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;
user_id | user_name1 | David_Hume

SQL> SELECT * FROM JIP.USER_TABLE;| password | user_group---------+----------------+-------------+-----------更新後テーブルの確認-| pass_hume | thought

USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- --------------------

2 | Immanuel_Kant | pass_kant | thought

1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy

2 Immanuel_Kant

pass_kantthought

5 | Kitaro_Nishida | pass_kitaro | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

5 Kitaro_Nishidapass_kitaro

philosophy(4 行)

ページ 34

Oracle_fdw動作検証◆トランザクション特性検証（COMMIT機能)
検証内容

操作

コマンド・結果【Postgres】

コマンド・結果【Oracle】

testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;

SQL> SELECT * FROM JIP.USER_TABLE;

user_id | user_name | password | user_group---------+---------------+--------------+-----------更新前テーブルの確認

1 | David_Hume| pass_hume

2 | Immanuel_Kant | pass_kant| thought

USER_ID USER_NAME
1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy| philosophy

2 Immanuel_Kant

pass_kantphilosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy

testdb=> BEGIN;

-

BEGIN
testdb=> INSERT INTO FDW_TEST_PSQL_ORACLE VALUES(

外部テーブルに
user_id = 5のレコードを挿入

外部テーブルのuser_id = 2のレコードのuser_groupを'thought'に更新-

testdb(> 5,'Kitaro_Nishida','pass_kitaro','philosophy'
testdb(> );INSERT 0 1
testdb=> UPDATE FDW_TEST_PSQL_ORACLESET user_group = 'thought' WHERE user_id = 2;
UPDATE 1-

testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;

SQL> SELECT * FROM JIP.USER_TABLE;

user_id | user_name

COMMIT機能の確認| password | user_group---------+----------------+--------------+-----------1 | David_Hume
トランザクション内にのみ更新がされていることを確認

USER_GROUP---------- -------------------- -------------------- --------------------(4 行)
トランザクション開始

PASSWORD| pass_hume| thought

2 | Immanuel_Kant | pass_kant| thought

USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- -------------------1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy

2 Immanuel_Kant

pass_kantphilosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

5 | Kitaro_Nishida | pass_kitaro | philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy(5 行)
トランザクションのコミット

testdb=> COMMIT;-COMMIT
testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;
user_id | user_name| password | user_group---------+----------------+--------------+-----------1 | David_Hume
外部環境にコミット内容が反映されていることを確認

SQL> SELECT * FROM JIP.USER_TABLE;
USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- --------------------| pass_hume| thought

1 David_Humepass_hume

thought

2 | Immanuel_Kant | pass_kant| thought

2 Immanuel_Kant

pass_kantthought

3 | Gilles_Deleuze | pass_gilles | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy

5 | Kitaro_Nishida | pass_kitaro | philosophy

5 Kitaro_Nishidapass_kitaro

philosophy(5 行)

ページ 35

Oracle_fdw動作検証◆トランザクション特性検証(ROLLBACK機能）
検証内容

操作

コマンド・結果【Postgres】

更新前テーブルの確認

省略（COMMIT機能の確認部分参照）

トランザクション開始

コマンド・結果【Oracle】

testdb=> BEGIN;

-

BEGIN

外部テーブルに
user_id = 5のレコードを挿入

省略（COMMIT機能の確認部分参照）-

外部テーブルのuser_id = 2のレコードのuser_groupを'thought'に更新

省略（COMMIT機能の確認部分参照）-

testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;

SQL> SELECT * FROM JIP.USER_TABLE;

user_id | user_name| password | user_group---------+----------------+--------------+-----------1 | David_Hume
トランザクション内にのみ更新がされていることを確認
ROLLBACK機能の確認| pass_hume| thought

2 | Immanuel_Kant | pass_kant| thought

USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- -------------------1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy

2 Immanuel_Kant

pass_kantphilosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

5 | Kitaro_Nishida | pass_kitaro | philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy(5 行)
トランザクションのロールバック

testdb=> ROLLBACK;-ROLLBACK
testdb=> SELECT * FROM FDW_TEST_PSQL_ORACLE;

SQL> SELECT * FROM JIP.USER_TABLE;

user_id | user_name | password | user_group---------+---------------+--------------+-----------更新前の状態に戻ることを確認

1 | David_Hume| pass_hume

2 | Immanuel_Kant | pass_kant| thought| philosophy

USER_ID USER_NAMEPASSWORD

USER_GROUP---------- -------------------- -------------------- -------------------1 David_Humepass_hume

thought

3 | Gilles_Deleuze | pass_gilles | philosophy

2 Immanuel_Kant

pass_kantphilosophy

4 | Hitoshi_Nagai | pass_hitoshi | philosophy

3 Gilles_Deleuze

pass_gilles

philosophy

4 Hitoshi_Nagai

pass_hitoshi

philosophy(4 行)

ページ 36

Oracle_fdw動作検証
・検証パターン1 （別トランザクションによる同一データの更新、トランザクションAがCOMMITし、トランザクションBの処理が失敗するパターン）
検証内容
トランザクションAで
外部表の1つのレコードを
UPDATEする

操作

コマンド・結果【Postgres（トランザクションA）】【Postgres】
トランザクションA開始

testdb=> BEGIN;【Postgres】
user_jd = 2 のレコードのuser_groupを、'philosophy' から 'thought'に更新する

BEGIN
testdb=> UPDATE fdw_test_psql_oracle
SET user_group = 'thought' WHERE user_id = 2;
UPDATE 1【Postgres】
トランザクションB開始-

トランザクションBで
外部表の同一キーのレコードを【Postgres】
UPDATEする
user_jd = 2 のレコードのuser_groupを、'philosophy' から 'thought'に更新する
検証内容

操作

コマンド・結果【Postgres（トランザクションB）】
testdb=> BEGIN;
BEGIN
testdb=> UPDATE fdw_test_psql_oracle
SET user_group = 'thought' WHERE user_id = 2;
(ロック待ち状態となる)-

コマンド・結果【Oracle】
SQL>
SELECT a.SID sid,a.USERNAME username,a.SERIAL# serialno,a.PROGRAM program,
b.REQUEST request,b.TYPE type,b.BLOCK block,b.LMODE lmode
FROM V$SESSION a,V$LOCK b
WHERE a.SID = b.SID
AND b.TYPE IN ('TX','TM');

SID USERNAMESERIALNO---------- ------------------------------ ---------PROGRAMREQUEST TY

BLOCK------------------------------------------------ ---------- -- ---------LMODE

ロック情報を取得する【Oracle】
ロック情報を取得する-----------

34 SCOTT

31429
0 TX

1

6
38 SCOTT

24973
6 TX

0

0
38 SCOTT

24973
0 TM

0

3
34 SCOTT

31429
0 TM

0

3

検証内容

操作

トランザクションAをコミットすると 【Postgres】
エラーが発生する
トランザクションAのコミット

コマンド・結果【Postgres（トランザクションA）】(2セッションのうち1つが行排他ロックのリクエストを行っていること、
もう1つのセッションが別の行排他ロックの妨げとなっていることが確認できる)
コマンド・結果【Postgres（トランザクションB）】

testdb=> COMMIT;ERROR: error executing query: OCIStmtExecute failed to execute remote query

COMMITDETAIL: ORA-08177: can't serialize access for this transaction

ページ 37

Oracle_fdw動作検証
・検証パターン1 （別トランザクションによる同一データの更新、トランザクションAがROLLBACKし、トランザクションBの処理が即座に開始されるパターン）
検証内容

操作【Postgres】
外部表の1つのレコードを
user_jd = 2 のレコードのuser_groupを、
UPDATEする'philosophy' から 'thought'に更新する
外部表の同一キーのレコードを 【Postgres】
user_jd = 2 のレコードのuser_groupを、
UPDATEする'philosophy' から 'thought'に更新する
ロック情報を取得する【Oracle】
ロック情報を取得する

トランザクションAをロールバック 【Postgres】
するとトランザクションBが直ちに トランザクションAのロールバック
開始される

コマンド・結果【Postgres（トランザクションA）】

コマンド・結果【Postgres（トランザクションB）】

省略（トランザクションをCOMMITした場合と同様）-

省略（トランザクションをCOMMITした場合と同様）-

省略（トランザクションをCOMMITした場合と同様）-

testdb=> ROLLBACK;UPDATE 1

ROLLBACK

ページ 38

Oracle_fdw動作検証
・検証パターン2（別トランザクションによる別データの更新）
検証内容
トランザクションAで
外部表の1つのレコードを
UPDATEする

トランザクションBで
外部表のロックされていない
レコードをUPDATEする
検証内容

操作

コマンド・結果【Postgres（トランザクションA）】【Postgres】
トランザクションA開始

testdb=> BEGIN;【Postgres】
user_jd = 2 のレコードのuser_groupを、'philosophy' から 'thought'に更新する

BEGIN
testdb=> UPDATE fdw_test_psql_oracle
SET user_group = 'thought' WHERE user_id = 2;
UPDATE 1【Postgres】
トランザクションB開始-【Postgres】
user_jd = 5 のレコードのuser_groupを、'philosophy' から 'thought'に更新する

-

操作

-

コマンド・結果【Postgres（トランザクションB）】
testdb=> BEGIN;
BEGIN
testdb=> update fdw_test_psql_oracle set user_group = 'philosophy'
Where user_id = 5;
UPDATE 1
コマンド・結果【Oracle】
SQL>
SELECT a.SID sid,a.USERNAME username,a.SERIAL# serialno,a.PROGRAM program,
b.REQUEST request,b.TYPE type,b.BLOCK block,b.LMODE lmode
FROM V$SESSION a,V$LOCK b
WHERE a.SID = b.SID
AND b.TYPE IN ('TX','TM');

SID USERNAMESERIALNO---------- ------------------------------ ---------PROGRAMREQUEST TY

BLOCK------------------------------------------------ ---------- -- ---------LMODE

ロック情報を取得する【Oracle】
ロック情報を取得する-----------

34 SCOTT

31429
0 TX

0

6
38 SCOTT

24973
0 TX

0

6
38 SCOTT

24973
0 TM

0

3
34 SCOTT

31429
0 TM

0

3

(2セッションが表共有ロック及び行排他ロックを行っていることが確認できる)

ページ 39

Oracle_fdw動作検証
・検証パターン3（NOWAITオプションの動作確認）
検証内容
トランザクションAで
外部表の1つのレコードを
UPDATEする
検証内容

操作

コマンド・結果【Postgres（トランザクションA）】【Postgres】
トランザクションA開始

testdb=> BEGIN;【Postgres】
user_jd = 2 のレコードのuser_groupを、'philosophy' から 'thought'に更新する
操作

BEGIN
testdb=> UPDATE fdw_test_psql_oracle
SET user_group = 'thought' WHERE user_id = 2;
UPDATE 1-

コマンド・結果【Postgres（トランザクションB）】
コマンド・結果【Oracle】
SQL> SELECT * FROM JIP.USER_TABLE
WHERE user_id = 2 FOR UPDATE NOWAIT;

Oracle側でNOWAIT
オプションが機能するかどうか確認する【Oracle】
user_id = 2のレコードを
NOWAITオプションを付けてSELECT FOR UPDATEする

SELECT * FROM JIP.USER_TABLE-

WHERE user_id = 2 FOR UPDATE NOWAIT*
行1でエラーが発生しました。:
ORA-00054: リソース・ビジー。NOWAITが指定されているか、タイムアウトしました

検証内容
oracle_fdw経由でリソースビジーが返るかどうか
確認

操作【Postgres】
user_id = 2のレコードを
NOWAITオプションを付けてSELECT FOR UPDATEする

トランザクションAをコミットすると 【Postgres】
エラーが発生する
トランザクションAのコミット

コマンド・結果【Postgres（トランザクションA）】

コマンド・結果【Postgres（トランザクションB）】-

testdb=> SELECT * FROM fdw_test_psql_oracle FOR UPDATE NOWAIT;（ロック解除待ちとなる）

testdb=> COMMIT;ERROR: error executing query: OCIStmtExecute failed to execute remote query

COMMITDETAIL: ORA-08177: can't serialize access for this transaction

ページ 40