設計運用WG  2014年度活動報告－企業が求める堅牢なデータベースを実現するために－～ディザスタ・リカバリとセキュリティ～PostgreSQL  エンタープライズ・コンソーシアム
WG3(設計運用WG)

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

アジェンダn WG3について¨ WG3の位置づけ、活動の概要¨ 2014年度の活動テーマ、実施体制

n 可用性テーマの成果紹介¨ 災害対策とディザスタ・リカバリ（DR）¨ DRを実現するシステム構成¨ 運用技術検証n セキュリティテーマの成果紹介¨ PCI  DSSのDB関連項目要件調査¨ PostgreSQLの適用技法n エンディング¨ 2014年活動を振り返って
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

2

WG3の位置づけ、活動概要
n 技術部会で設定された課題に具体的に取り組む

実働部隊の一つで主に設計・運用面に関する課題をテーマとする。  
¨ ミッションクリティカル性の高いエンタープライズ領域へのPostgreSQL適用に向けて、安定稼動のための技術ノウ
ハウを提供
ＰＧＥＣｏｎｓにおける課題領域

性能

性能評価手法、性能向上手法、チューニングなど

可用性

高可用クラスタ、BCP

保守性

保守サポート、トレーサビリティ

運用性

バックアップ運用、監視運用

セキュリティ

監査

互換性

データ、スキーマ、SQL、ストアドプロシージャの互換性

接続性

他ソフトウェアとの連携

昨年度のテーマは高可用クラスタ、バックアップ、監視Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

3

WG3の位置づけ、活動概要
n 技術部会で設定された課題に具体的に取り組む

実働部隊の一つで主に設計・運用面に関する課題をテーマとする。  
¨ ミッションクリティカル性の高いエンタープライズ領域へのPostgreSQL適用に向けて、安定稼動のための技術ノウ
ハウを提供
ＰＧＥＣｏｎｓにおける課題領域

性能

性能評価手法、性能向上手法、チューニングなど

可用性

高可用クラスタ、BCP

保守性

保守サポート、トレーサビリティ

運用性

バックアップ運用、監視運用

セキュリティ

監査

互換性

データ、スキーマ、SQL、ストアドプロシージャの互換性

接続性

他ソフトウェアとの連携

今年度はBCP、セキュリティに着目
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

4

WG3  2014年度活動テーマn 可用性  （昨年度からの継続テーマ）¨ 昨年度：  PostgreSQLで可用性を担保するシステム構成（HA・レプリケーション）の特徴を整理し、実機検証まで実施¨ 今年度：  災害対策を想定し、検討範囲をDRサイト構成に発展n セキュリティ  （今年度からの新設テーマ）¨ 利用できる機能の整理と実用レベルのバランス調査¨ 他DBMS向けに記述されたDBSCによる文書を参考に、  PCIDSS準拠のためのPostgreSQLにおける対処策をまとめる。
¨ ※DBSC:  DataBase  Security  Consortium

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

5

WG3  2014年度活動内容と活動体制【可用性】【セキュリティ】

活動内容n DRサイトをPostgreSQLで構築する場
合の構成と特徴を机上調査で整理

活動内容n PostgreSQL版  『データベースセキュリ
ティガイドライン』を作成

¨
¨
¨

n

遠隔地バックアップ
ストレージレベルでのデータ同期
PostgreSQLストリーミング・レプリケー
ション(SR)

SRを使ったDRサイトの実機検証
DRサイトの初期構築、障害時の運用
手順を整備¨ Amazon  Web  Service環境で遠隔拠点間のレプリケーションを検証¨

活動体制    （企業名アイウエオ順、敬称略）
n TIS株式会社n 日本電信電話株式会社n 株式会社日立ソリューションズ

DBSCによる  『データベースセキュリティガイドライン  第2.0版』の「製品別機能対応表」  をチェック¨ PCI  DSSv2.0での要求項目に対し、
PostgreSQLでの実現手法を整理¨ PostgreSQLの標準機能で実現できない箇所は、外部ソフトの利用やスクリ
プト記述などを使った対策方法を例示¨

活動体制    （企業名アイウエオ順、敬称略）
n 株式会社アシストn SRA  OSS,  Inc.  日本支社n NTTソフトウェア株式会社n サイオステクノロジー株式会社n 大日本印刷株式会社n 日本電気株式会社

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

6可用性テーマの成果紹介

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

7

災害対策とディザスタリカバリ
	n ITシステムの停止が与える経済／社会的影響は大¨ システム停止による利益の損失を最小限に抑える対策が必要  ⇒  災害対策n ディザスタリカバリ  （Disaster  Recovery：DR）¨ 災害等で被害を受けたシステムを復旧・修復すること。

また、そのための備えとなる機器やシステム、体制n DRのサービスレベルは高ければよいわけではない。
¨ システム規模や特性に応じて相応のコストがかかる。
¨ いつくるかわからない災害にどの程度投資して備えるか？

ITシステムのサービス継続レベルを検討しておくことが  DR  のポイント
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

8

DRを検討する指標
主要な３つの「目標」
・復旧時間目標      ：  RTO  （Recovery  Time  Objective）
・復旧時点目標      ：  RPO  （Recovery  Point  Objective）  ・復旧レベル目標  ：  RLO  （Recovery  Level  Objective）    RPO：復旧時点目標
どの時点のデータに戻るのか

RTO：復旧時間目標

RLO：復旧レベル目標

災害発生から
どの位でサービス
再開できるかサービス
レベル
週  日  時  分  秒

機能や性能の低下をどの位許容
して復旧するか
サービス
レベル

故障・災害
発生

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

⇒時間の経過9

DRを実現するシステム構成
構成

データ保全

構成パターン
復旧
目標

RTO
目標時間
RPO
復旧ポイント

サービス継続

構成1

構成2

構成3

構成4

フル
バックアップ
＆
データ
保管のみ

差分
バックアップ
＆
データ
保管のみ

H/W（
ストレージ）
で
レプリケー
ション

S/W
（DRBD）で
レプリケー
ション

シングル

シングル

ActiveStandby

ActiveStandby

ActiveStandby

ActiveStandby

長い

長い

長い

中

長い

長い

短い（方式
次第）

短い

長い（バック 中（バックアップ時点） アップ時点）

構成5

構成6

構成7

構成8

構成9

マスタ⇒遠
隔地DB  
部分レプリ
ケーション

マスタ⇒ローカル
スタンバイ⇒遠隔地DB  
カスケードレプリ
ケーション

ActiveActive

ActiveActive

Active-Active

短い

短い

短い

短い

短い

短い

フルバック 差分バック マスタ⇒遠
アップ＆
アップ＆
隔地DB  
事前リストア 事前リストア 非同期レプ
リケーション長い（バック 中（バックアップ時点） アップ時点）

RLO
復旧レベル

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

低

（方式
次第）

コスト（初期・構築・運用費用等）

低

低

高

中

低

低

中

中

中

運用性

楽

楽

楽

中

楽

楽

中

中

難

構築期間

短い

短い

短い

長い

短い

短い

長い

長い

長い

DRサイト活用

×

×

×

×

△

△

○

○

○

－

－

－

－

－

－

○

－

○

実機検証対象Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

10

DRを実現するシステム構成
構成

データ保全

構成パターン
復旧
目標

RTO
目標時間
RPO
復旧ポイント

サービス継続

構成1

構成2

構成3

構成4

フル
バックアップ
＆
データ
保管のみ

差分
バックアップ
＆
データ
保管のみ

H/W（
ストレージ）
で
レプリケー
ション

S/W
（DRBD）で
レプリケー
ション

シングル

シングル

ActiveStandby

ActiveStandby

ActiveStandby

ActiveStandby

長い

長い

長い

中

長い

長い

短い（方式
次第）

短い

長い（バック 中（バックアップ時点） アップ時点）

構成5

構成6

構成7

構成8

構成9

マスタ⇒遠
隔地DB  
部分レプリ
ケーション

マスタ⇒ローカル
スタンバイ⇒遠隔地DB  
カスケードレプリ
ケーション

ActiveActive

ActiveActive

Active-Active

短い

短い

短い

短い

短い

短い

フルバック 差分バック マスタ⇒遠
アップ＆
アップ＆
隔地DB  
事前リストア 事前リストア 非同期レプ
リケーション長い（バック 中（バックアップ時点） アップ時点）

RLO
復旧レベル

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

低

（方式
次第）

コスト（初期・構築・運用費用等）

低

低

高

中

低

低

中

中

中

運用性

楽

楽

楽

中

楽

楽

中

中

難

構築期間

短い

短い

短い

長い

短い

短い

長い

長い

長い

DRサイト活用

×

×

×

×

△

△

○

○

○

－

－

－

－

－

－

○

－

○

実機検証対象Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

11

DRを実現するシステム構成n

遠隔地バックアップでデータ保全を実現するパターン¨ 復旧にはバックアップのリストア、サービス起動が必要  （RTO）¨ 復旧後のデータ鮮度はバックアップ取得頻度に依存 （RPO ）DRサイト側のPostgreSQLは
コールドスタンバイ

一番ベーシックな
定期フルバックアップ

PostgreSQL	PostgreSQL	

差分バックアップを加えて頻度を強化

PostgreSQL	PostgreSQL	DISK	

フルバックアップ	

DISK	
リストア	

バック
DISK	
 アップ	

バック
アップ	

メインサイト	

DRサイト	

構成１：フルバックアップ＆データ保管のみ

DISK	

フルバッ
クアップ	

フルバッ
クアップ	

差分バッ
クアップ	

差分バッ
クアップ	

差分バッ
クアップ	

差分バッ
クアップ	

メインサイト	

リストア	

DRサイト	

構成２：差分バックアップ＆データ保管のみ

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

12

DRを実現するシステム構成
構成

データ保全

構成パターン
復旧
目標

RTO
目標時間
RPO
復旧ポイント

サービス継続

構成1

構成2

構成3

構成4

フル
バックアップ
＆
データ
保管のみ

差分
バックアップ
＆
データ
保管のみ

H/W（
ストレージ）
で
レプリケー
ション

S/W
（DRBD）で
レプリケー
ション

シングル

シングル

ActiveStandby

ActiveStandby

ActiveStandby

ActiveStandby

長い

長い

長い

中

長い

長い

短い（方式
次第）

短い

長い（バック 中（バックアップ時点） アップ時点）

構成5

構成6

構成7

構成8

構成9

マスタ⇒遠
隔地DB  
部分レプリ
ケーション

マスタ⇒ローカル
スタンバイ⇒遠隔地DB  
カスケードレプリ
ケーション

ActiveActive

ActiveActive

Active-Active

短い

短い

短い

短い

短い

短い

フルバック 差分バック マスタ⇒遠
アップ＆
アップ＆
隔地DB  
事前リストア 事前リストア 非同期レプ
リケーション長い（バック 中（バックアップ時点） アップ時点）

RLO
復旧レベル

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

低

（方式
次第）

コスト（初期・構築・運用費用等）

低

低

高

中

低

低

中

中

中

運用性

楽

楽

楽

中

楽

楽

中

中

難

構築期間

短い

短い

短い

長い

短い

短い

長い

長い

長い

DRサイト活用

×

×

×

×

△

△

○

○

○

－

－

－

－

－

－

○

－

○

実機検証対象Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

13

DRを実現するシステム構成n

H/W,S/Wのデータ同期でデータ保全を実現するパターン¨ 復旧にはバックアップのリストア、サービス起動が必要  （RTO）¨ リアルタイムなデータ同期により復旧後のデータ鮮度は向上 （RPO ）高機能なストレージによる
特定時点のスナップショット
or  レプリケーション

PostgreSQL	PostgreSQL	

DRサイト側のPostgreSQLは
コールドスタンバイ

ソフトウェア機能による
特定パーティションのレプリケーション

PostgreSQL	

DRBD	
スト
レージ	
メインサイト	

スト
レージ	

DISK	

DRサイト	

メインサイト	

構成３：H/Wでレプリケーション

PostgreSQL	

DRBD	

DISK	
DRサイト	

構成４：S/Wでレプリケーション

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

14

DRを実現するシステム構成
構成

データ保全

構成パターン
復旧
目標

RTO
目標時間
RPO
復旧ポイント

サービス継続

構成1

構成2

構成3

構成4

フル
バックアップ
＆
データ
保管のみ

差分
バックアップ
＆
データ
保管のみ

H/W（
ストレージ）
で
レプリケー
ション

S/W
（DRBD）で
レプリケー
ション

シングル

シングル

ActiveStandby

ActiveStandby

ActiveStandby

ActiveStandby

長い

長い

長い

中

長い

長い

短い（方式
次第）

短い

長い（バック 中（バックアップ時点） アップ時点）

構成5

構成6

構成7

構成8

構成9

マスタ⇒遠
隔地DB  
部分レプリ
ケーション

マスタ⇒ローカル
スタンバイ⇒遠隔地DB  
カスケードレプリ
ケーション

ActiveActive

ActiveActive

Active-Active

短い

短い

短い

短い

短い

短い

フルバック 差分バック マスタ⇒遠
アップ＆
アップ＆
隔地DB  
事前リストア 事前リストア 非同期レプ
リケーション長い（バック 中（バックアップ時点） アップ時点）

RLO
復旧レベル

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

低

（方式
次第）

コスト（初期・構築・運用費用等）

低

低

高

中

低

低

中

中

中

運用性

楽

楽

楽

中

楽

楽

中

中

難

構築期間

短い

短い

短い

長い

短い

短い

長い

長い

長い

DRサイト活用

×

×

×

×

△

△

○

○

○

－

－

－

－

－

－

○

－

○

実機検証対象Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

15

DRを実現するシステム構成nPostgreSQLのSRによりサービス継続を実現するパターン¨ 平常時からDRサイトのPostgreSQLサービスは起動  （RTO）¨ リアルタイムなデータ同期により復旧後のデータ鮮度も向上 （RPO ）¨ 平常時にDRサイトを参照用途で活用することも可能
マルチスタンバイ構成のストリーミングレプリケーション◎後半で検証！
Postgre	
SQL	

マスタ	
DB	

Postgre	
SQL	
スタンバイ	
DB１	

ストリーミングレプリ
ケーション	

メインサイト	

DRサイト側のPostgreSQLは
ホットスタンバイ

カスケード構成のストリーミングレプリケーション◎後半で検証！

Postgre	
SQL	Postgre	
SQL	Postgre	
SQL	Postgre	
SQL	

スタン
バイ	
DB２	

マスタ	
DB	

スタンバ
イ	
DB１	

スタン
バイ	
DB２	

ストリーミングレプリ
ケーション	

DRサイト	

構成７：マスタ⇒遠隔地DB
            非同期レプリケーション

メインサイト	

DRサイト	

構成９：マスタ⇒ローカルスタンバイ⇒遠隔地DBカスケードレプリケーション

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

16

DRを実現するシステム構成nPostgreSQLのSRによりサービス継続を実現するパターン¨ 平常時からDRサイトのPostgreSQLサービスは起動  （RTO）¨ リアルタイムなデータ同期により復旧後のデータ鮮度も向上 （RPO ）¨ 平常時にDRサイトを参照用途で活用することも可能
マルチスタンバイ構成のストリーミングレプリケーション◎後半で検証！
Postgre	
SQL	

マスタ	
DB	

Postgre	
SQL	
スタンバイ	
DB１	

ストリーミングレプリ
ケーション	

メインサイト	

DRサイト側のPostgreSQLは
ホットスタンバイ

カスケード構成のストリーミングレプリケーション◎後半で検証！

Postgre	
SQL	Postgre	
SQL	Postgre	
SQL	Postgre	
SQL	

スタン
バイ	
DB２	

マスタ	
DB	

スタンバ
イ	
DB１	

スタン
バイ	
DB２	

ストリーミングレプリ
ケーション	

DRサイト	

メインサイト	

DRサイト	

構成９：マスタ⇒遠隔地DB
構成７：マスタ⇒遠隔地DB
ストリーミングレプリケーションの運用性は実際どう？
  カスケードレプリケーション            非同期レプリケーション
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

17

運用技術検証〜構築手順と復旧手順〜

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

18

DRを実現するシステム構成
検証対象の選定
構成

データ保全

構成パターン
復旧
目標

RTO
目標時間
RPO
復旧ポイント

サービス継続

構成1

構成2

構成3

構成4

フル
バックアップ
＆
データ
保管のみ

差分
バックアップ
＆
データ
保管のみ

H/W（
ストレージ）
で
レプリケー
ション

S/W
（DRBD）で
レプリケー
ション

シングル

シングル

ActiveStandby

ActiveStandby

ActiveStandby

ActiveStandby

ActiveActive

ActiveActive

Active-Active

長い

長い

長い

中

長い

長い

短い

短い

短い

短い（方式
次第）

短い

短い

短い

短い

長い（バック 中（バックアップ時点） アップ時点）

構成5

構成6

構成7

フルバック 差分バック マスタ⇒遠アップ＆ 事 アップ＆ 事 隔地DB  非
前リストア
前リストア
同期レプリ
ケーション

長い（バック 中（バックアップ時点） アップ時点）

構成8

構成9

マスタ⇒遠 マスタ⇒ローカル
隔地DB  部 スタンバイ⇒遠分レプリケー 隔地DB  カスケー
ション
ドレプリケーション

RLO
復旧レベル

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

（方式
次第）

低

（方式
次第）

コスト（初期・構築・運用費用等）

低

低

高

中

低

低

中

中

中

運用性

楽

楽

楽

中

楽

楽

中

中

難

構築期間

短い

短い

短い

長い

短い

短い

長い

長い

長い

DRサイト活用

×

×

×

×

△

△

○

○

○

－

－

－

－

－

－

○

－

○

実機検証対象Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

19

n 目的¨ PostgreSQLでDRサイトを構築する手順と運用手順を確認
n 観点¨ ストリーミングレプリケーションによるデータ同期でDRサイトを構築¨ 各レプリケーション構成毎にDRサイト運用の違いを考察n 環境¨ 検証構成のメインサイト・DRサイトは仮想環境で構築

図.マルチスタンバイ構成

図.カスケード構成

実際にSRによるデータ同期するDRサイトを構築してどんな課題があるか確認してみよう！
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

20

検証内容1（構築手順）
START	

PostgreSQL インストール	
パラメータ設定	

データベース作成	
パラメータ設定	

ノード複製	

ノード複製	
レプリケーション稼働	

各リリースの実装機能	
Ver. 9.0 ・ストリーミング・レプリケーションが採用
Ver. 9.1 ・同期レプリケーション機能実装
Ver. 9.2 ・synchronous_commit による4種類の同期モードが可能
・カスケードレプリケーションが可能
Ver. 9.3 ・タイムラインIDの追従	
・高速フェイルオーバ機能	
Ver. 9.4 ・レプリケーションスロットの導入	・ロジカルデコーディングのサポート	
wal_level = hot_standby	
max_wal_senders = 4	hot_standby = on		※その他の設定パラメータは2014年度成果物ドキュメントをご確認ください	

pg_basebackup 	

レプリケーション稼働チェック	
pg_xlog_location_diff	pg_stat_replication	
障害発生時の運用	

障害検知方法選定	
障害時フェイルオーバ挙動	

END	

構築手順は成果物としてドキュメント化しました
ご参考ください。

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

21

検証内容2（マスタDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション
クライアント	

スタンバイDB1	
マスタDB	

スタンバイDB2	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	

マスタDB	

スタンバイDB1	

メインサイト	

スタンバイDB2	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

22

検証内容2（マスタDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション②	
 クライアント	③	

①  promoteコマンドでマスタ昇格②  クライアントの更新先を変更③  recovery.confを書き換えて再起動

新マスタ	
旧マスタDB	

スタンバイDB2	

①	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	

マスタDB	

スタンバイDB1	

メインサイト	

スタンバイDB2	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

23

検証内容2（マスタDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション②	
 クライアント	③	

①  promoteコマンドでマスタ昇格②  クライアントの更新先を変更③  recovery.confを書き換えて再起動

新マスタ	
旧マスタDB	

スタンバイDB2	

①	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	

マスタDB	

スタンバイDB1	

メインサイト	

スタンバイDB2	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

24

検証内容2（マスタDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション②	
 クライアント	③	

①  promoteコマンドでマスタ昇格②  クライアントの更新先を変更③  recovery.confを書き換えて再起動

新マスタ	
旧マスタDB	

スタンバイDB2	

①	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション②	
 クライアント	①  promoteコマンドでマスタ昇格②  クライアントの更新先を変更※  DRサイト側は操作不要

旧マスタDB	

新マスタ	

スタンバイDB2	

①	
メインサイト	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

25

検証内容2（メインサイトスタンバイDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション
クライアント	

スタンバイDB1	
マスタDB	

スタンバイDB2	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	

マスタDB	

スタンバイDB1	

メインサイト	

スタンバイDB2	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

26

検証内容2（メインサイトスタンバイDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション
スタンバイDBを急いで復旧しなくても、大きな影響なし。

クライアント	

スタンバイDB1	
マスタDB	

スタンバイDB2	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	

スタンバイDB1	
マスタDB	
スタンバイDB2	

メインサイト	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

27

検証内容2（メインサイトスタンバイDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション
スタンバイDBを急いで復旧しなくても、大きな影響なし。

クライアント	

スタンバイDB1	
マスタDB	

スタンバイDB2	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	

スタンバイDB1	
マスタDB	
スタンバイDB2	

メインサイト	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

28

検証内容2（メインサイトスタンバイDB障害からの復旧）
構成７：マスタ⇒遠隔地DB  非同期レプリケーション
スタンバイDBを急いで復旧しなくても、大きな影響なし。

クライアント	

スタンバイDB1	
マスタDB	

スタンバイDB2	

DRサイト	

メインサイト	

構成９：マスタ⇒メインサイト側スタンバイ⇒遠隔地DBカスケードレプリケーション
クライアント	①  recovery.confを書き換えて再起動
スタンバイDB1	

①	

マスタDB	
スタンバイDB2	

メインサイト	

DRサイト	
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

29検証から得られた知見

簡単に構築できる。

n SRでデータ同期するDRサイトはn 構成パターン、障害箇所により復旧手順は異なるので、

  環境に合わせたリカバリ手順を整備しておくこと。

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

30

運用技術検証〜遠隔地レプリケーションの実態〜

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

31

検証の目的、観点、環境n 目的¨ PostgreSQLのストリーミングレプリケーションでDRを実現する場合に発生する運用上の課題を調査する。

n 観点¨ 遠隔地レプリケーションによるレプリケーションの遅延の実態¨ レプリケーション方式特有のリスクと対策方法n 環境¨ メインサイト(東京)-DRサイト(シンガポール)間でレプリケーション環境を構築

SR(⾮非同期)
マスタサーバ

PostgreSQL

スタンバイサーバ

SR(同期)EBS
Data

Availability  Zone

Availability  Zone

構成７：マスタ⇒遠隔地DB
非同期レプリケーション

PostgreSQL

PostgreSQL

EBS
Data

スタンバイサーバ

EBS
Data

Availability  Zone

DRサイト：region(シンガポール)

メインサイト：region(東京)A W S 	 cloudCopyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

32

検証ケース(1)レプリケーション遅延の実態
n 検証方法p 500万件のテーブルを全件更新する更新負荷を掛け続けて計測p マスタDBと各スタンバイのレプリケーション遅延量を定期的に取得
n 検証結果p DRサイトのレプリケーション遅延が拡大し、レプリケーションが停止した。
A:  DRサイトSR停止前  ← →  B:  DRサイトSR停止後※スタンバイのサーバログにWALが受け取れなくなった旨のエラーが発生	
could not receive data from WAL stream: ERROR: requested WAL segment
00000001000000070000004E has already been removed	
統計情報(pg_stat_replication)	
※ A: DRサイトSR停止前	
timetz| client_addr | sync_state	----------------+-------------+------------	
13:59:55.232788+09 | 54.92.66.190 | sync	
13:59:55.232788+09 | 54.251.130.204 | async	
※ B: DRサイトSR停止後	
timetz| client_addr | sync_state	----------------+-------------+------------	
14:03:28.110256+09| 54.92.66.190 | sync	

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

33

検証ケース(2)レプリケーション停止への対策
n 検証方法→  DRサイトSR停止p recovery.confのrestore_commandにマスタDBのアーカイブファイルを直接コピーするコマンドを設定して稼働させる。

※アーカイブログからリストアする	
メッセージを出力	
restored log file “000000010000000	
70000004E” from archive	

restore_command =	
‘scp (マスタのIP):/usr/local/pgsql/arc/%f %p'	n 検証結果p レプリケーションが停止してから、→  DRサイトSR再開

約30分後にレプリケーションが再開¨ 別途アーカイブログを転送しておくことで、さらに再開が早まることを確認

レプリケーション停止のリスクを小さくするには、アーカイブログを随時スタンバイに転送するとよい。
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

34検証から得られた知見
n wal_keep_segmentsパラメータの有効性¨ 0→32  に設定すると、レプリケーションは停止しなかった。

wal_keep_segments  を大きく設定すれば、レプリケーション停止のリスクは小さくなる。
注意する点
DISK容量  （マスタサーバのWALファイルが残り続けるため）
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

35新機能：レプリケーションスロット
9.4から追加された新機能
n マスタはスタンバイのレプリケーション状況を保持する。
n¨ スタンバイが未適用のWALをマスタは消さない。
¨ スタンバイが参照しているデータの削除を防ぐ。→コンフリクトの防止
postgres=# SELECT * FROM pg_replication_slots;
slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn--------------+--------+------------+---------+------------+---------+-------+------------------+------------a_slot || physical |||t| 1890 || 0/D001218

マスタ	

WAL	

まだWAL消さないで	スタンバイ	

承知	

有効な場面
Ø スタンバイが複数存在する等適切な  wal_keep_segments  の見積が困難Ø wal_keep_segments  の値が適切でなかった場合の保険注意する点Ø DISK容量(マスタがWALを保持し続ける可能性があるため)
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

36検証から得られた知見
n

スタンバイのレプリケーションがマスタからどれ位遅れているかを知るのは意外と難しい。
pg_stat_replication  ビューの項目
項目	

マニュアルの説明	

sent_location	

接続上に送信された最後のトランザクションログ位置	

write_location	

スタンバイによってディスクに書き出された最後のトランザクションログ位置	

flush_location	

スタンバイによってディスクに吐き出された最後のトランザクションログ位置	

replay_location	
 スタンバイのデータベースに再生された最後のトランザクションログ位置	

n「sent_location  ≠  マスタのトランザクションログ位置」  に注意！
¨

マスタのトランザクションログ位置は別途関数で取得しなければならない。
postgres=# select client_addr, pg_xlog_location_diff(master,replay_location) as replaydiff from(select pg_current_xlog_insert_location() master)as m,pg_stat_replication;")

各スタンバイのアドレス	

n

client_addr | replaydiff--------------------+----------------54.92.66.190 | 1463080
54.251.130.204 | 315867024

未適用WAL量(単位はbyte)	

カスケード構成でマスタに直結しないスタンバイのトランザクシ
ョンログ位置を知るのはもっと困難
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

37

可用性テーマのまとめn PostgreSQLでDRサイトを構築する構成パターンとそれぞれの特徴を整理した。
n ストリーミングレプリケーションでDRサイトを構築する
構成に対して実機検証を行った。
¨ DRサイトの構築手順と障害復旧手順を確認¨ 遠隔地間レプリケーションの挙動を確認

n 導入は簡単。運用にはいくつか注意点があった。
¨ 構成パターン、障害箇所に合わせたリカバリ手順の整備¨ 遅延拡大によるレプリケーション停止のリスクn 実運用のノウハウは今年度の検証で得られたものを含めて、今後も整備していく必要がある。
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

38

セキュリティテーマの成果紹介

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

39

調査の目的・背景
n

背景
¨

n

過去の調査、検証でPostgreSQLはエンタープライズでも使える機能、性能を有することがわかっており、機能への要求が高度化している。その1つがセキュリティ。

課題
PostgreSQLが一般的なセキュリティ要件にどこまで対応しているかを検証・周知している資料が少ない。
¨ PostgreSQLの機能で解決できないセキュリティ要件への対応方法が不明⇒強固なセキュリティ要件を求める案件にPostgreSQLを適用できるかがわからない。
¨

n

目的
¨

PostgreSQLが強固なセキュリティ要件に対応できるかを検証する。

強固なセキュリティ要件の１つとして「PCI-DSS」がある。
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

40

PCI  DSSとはn

PCI  DSS(Payment  Card  Industry  Data  Security  Standards)とは¨n

クレジットカード会員データを安全に取り扱う事を目的として策定された、クレジットカード
業界のセキュリティ基準

経緯• 従来、各国際カードブランドが独自でリスク管理プログラムを運用。加盟店が各基準に対応する負荷が高い。
• ネット決済のグローバル化によりカード情報漏洩のリスク増加。
AmericanExpress	

Discover	

JCB	

Master
Card	

VISA	

PCI SSC(Payment Card Industry Security Standards Council)	

PCI DSS(Payment Card Industry Data Security Standards)	

• 基準の統一化による加盟店の負荷軽減• セキュリティ基準の遵守による加盟店の企業価値向上n

遵守【導入を必要とする想定企業】
イシュアー、アクワイアラー、サービス・プロバイダー、加盟店

加盟店

認定メリット1.
2.
3.

カード情報漏洩のリスク低減
基準に準拠による信頼性により企業価値向上
万が一情報流出事件が発生した場合でもペナルティが免責されるケースがある。

カードビジネスにおける必須条件
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

41

PCI  DSS要件nPCI  DSSでは、安全なネットワークの構築、データの保護、プログラムの脆
弱性、アクセス制御手法、ネットワークの監視及びテスト、セキュリティポリ
シーの整備といった分野が12の要件に分類され、それがさらに詳細な20
参考：PCI Security Standards Council：https://ja.pcisecuritystandards.org/minisite/en/
0項目にブレイクダウンされている。
pci-dss-supporting-docs-v20.php	
安全なネットワーク
構築・維持

情報セキュリティ・
ポリシーの整備カード会員データの保護

PCIDSS
12要件
詳細200項目

定期的なネットワークの監視及びテスト

脆弱性を管理するプログラムの整理
強固なアクセス
制御手法の導入

これらの項目に対応する必要がある。
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

42

調査方法
n

手順
PCI  DSS
調査n

• PCI  DSS要件でデータベースが対応する必要がある項目を抽出※DataBase  Security  Consortiumの成果物を引用

PostgreSQL
対応検討• 抽出した項目に対して、PostgreSQLでの対応可能か確認• 検証が必要となる箇所の抽出

検証作業• 検証作業が必要となる箇所について検証作業を実施

DataBase  Security  Consortium(DBSC)が作成したPCI  DSSのセキュリティガイドラインについて¨¨DBSCが過去にPCI  DSSデータベースセキュリティガイドラインを作成して公開している。
このガイドラインをPostgreSQLに適用することとした。

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

43

PostgreSQLの適用技法

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

44適用技法紹介n

PCI  DSSでDBが対応すべき分野
アクセス制御
p
p
p
p
p
p

アカウントポリシー機能の実現
不正アクセスのチェック
定期的なセッション情報の分析
不正アクセスの動的遮断
アクセス時間外の接続検知
長時間アイドル中の接続を自動的に遮断

暗号化
p
p

格納データの暗号化
ファイルシステムの透過的暗号化

ログ監査
p

出力したログの保全

次ページ以降で具体的適用技法を紹介
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

45

アクセス制御

暗号化

ログ監査

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

46

アカウントポリシー機能の実現n

アカウントポリシーとは？
データベースユーザアカウントに付加する様々な条件¨ 以下を強制する仕組みが要求されている「最初の接続時にパスワード変更、また、少なくとも90日ごとパスワード変更」「パスワードは数字、英文字の両方を含む７文字以上で、前回、前々回と同じものはダメ」  「パスワード認証を６回失敗したら、少なくとも30分はロックアウト」 など。
¨nPostgreSQLでは？
CREATE  ROLE  のオプションでは不足¨ 外部認証機能を使うことで対応¨ LDAP,  GSSAPI,  SSPI,  RADIUS,  PAM  が利用可能¨ OpenLDAP  +  LDAP外部認証  を使った実現例  （右図）  の構築手順を解説¨

データベース
接続

データベースサーバ
O penL D AP
dc=comPostgreS Q L
認証で利用

dc=example
ou=dbusers

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

47

不正アクセスのチェックn

不正なアクセスを試みられていないか¨n例） 短時間に一定回数以上のパスワード認証の失敗は、パスワード攻撃の可能性を疑う。

PostgreSQLでは？
接続失敗の証跡はサーバログに出力できる。
¨ ただし検索にはgrepでは限界があるためコーディングが必要  （でもコーディングは面倒..）¨n

サーバログをPostgreSQLのテーブルにロードして対処 SELECT文で自在に検索！
CSVLOG形式のログをCOPY  FROMコマンドでロード¨ 条件や集計を自在に指定¨検索例）パスワード認証エラーが１分間に100回発生していなか?
postgres_logテーブルについては「2014年度  WG3活動報告  セキュリティ編」参照

SELECT to_char(log_time,'YYYY-MM-DD HH24:MI') AS log_time,
count(*) AS cnt
FROM
postgres_log
WHERE message LIKE '%password authentication failed%'
GROUP BY to_char(log_time,'YYYY-MM-DD HH24:MI')HAVING count(*) >= 100;
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

48

定期的なセッション情報の分析nn

セッションを定期的に分析して不正な動きがないか確認
例1） ログイン失敗回数が多い接続試行
単なるパスワードミス？ 不正アクセスの試行？
¨ エラー原因や接続元ホストや接続先ユーザ、データベース などの傾向を確認¨検索例） 認証エラーの種類別の集計

SELECT message,
count(*) AS countFROM
postgres_log
WHERE sql_state_code IN ('28000','28P01')
GROUP BY message;
主な認証エラー
メッセージ

エラー原因

SQL
STATE

FATAL: no pg_hba.conf entry for host "[local]", user "xxxxx", database"xxxxx", SSL off	

pg_hba.confに該当設定が無い28000	FATAL: pg_hba.conf rejects connection for host "[local]", user "xxxxx",
database "xxxxx", SSL off	

接続拒否28000	FATAL: Peer authentication failed for user "xxxxx"	

Peer認証エラー

28000	FATAL: password authentication failed for user "xxxxx" 	

パスワードミス またはパスワードの有効期限切れ

28P01	

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

49定期的なセッション情報の分析n例2） 長時間に渡りログインしているセッション¨

なぜ長時間？ なにか不正してない？
検索例） ３０分以上接続していたセッション

SELECT log_time,
user_name,database_name,
～中略～
FROM
postgres_log
WHERE message LIKE 'disconnection:%'AND
substr(message,30,7) > '0:30:00';

接続元ホスト名、接続先ユーザ名、接続先データベース名、アプリケーション名などから不審なセッションかどうかを確認¨ 不審な点がある場合は、別途上記セッションの詳細を検索¨

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

50

不正アクセスの動的遮断n

PostgreSQLには、自動で不正アクセスを動的切断する機能はなく、手動で行う、個別に工夫したスクリプトが必要

n

実現手段
管理者がサーバログを見ることで不正アクセスをしているユーザを調べる。
¨ pg_stat_activityビューで、ユーザのプロセスIDを特定する。
¨ pg_terminate_backend()関数を利用
¨

n

適用例
¨

不正アクセスしているユーザが「user1」と分かっている場合に手動で動的遮断をする。
SELECT pg_terminate_backend(pid),FROM
pg_stat_activity
WHERE username='user1';

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

51

アクセス時間外の接続検知n

PostgreSQLの標準機能には、時間帯や曜日でアクセス制限をかけることができないので、作りこみが必要

n

実現手段
pg_hba.confの元となるファイルを時間帯・曜日別に複数用意する。
¨ cronで所定のpg_hba.confに上書きし、pg_ctrl  reloadで反映¨ 時間外の場合はpg_hba.confによってクライアント認証が失敗するので、クライアント認証エラーメッセージから、どのユーザによるものか検知する。
¨n

適用例：アクセス時間を下記表のように定義した場合
金曜  20:00

ユーザ名

曜日・時間帯user1

月曜08:00～金曜20:00、
土曜08:00～18:00

user2
postgres(管理者)

月曜08:00～金曜20:00、
日曜08:00～18:00
常時アクセス可能

postgres, user1, user2	

土曜  08:00

月曜  08:00
postgres	

postgres	cronでpg_hba.conf
を書き換え

postgres, user1	

日曜  08:00

土曜  18:00

日曜  18:00
postgres, user2	

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

postgres	52

長時間アイドル中の接続を自動的に遮断するn

OSのネットワーク機能や、ネットワーク機器の機能では？
¨n「長時間の応答待ち」 と 「アイドル中  （＝何もしてない）  」 の区別ができない。

PostgreSQLでは？
アイドル状態を自動的に遮断  （＝接続されているのを切断）  する機能は無い。
¨ 指定した接続を終了させる機能はある ・・・ pg_terminate_backend()  関数¨ 「接続はアイドル状態か？」  「現在の状態になってからの経過時間」  を知る手段はある。
・・・ pg_stat_ativity  システムビュー¨n

定期SQL実行で代替実現¨

以下を  cron  で定期実行

SELECT pg_terminate_backend(pid),
pid, datname, usename, application_name, client_addr,
client_hostname, client_port, query,backend_start, xact_start, query_start, state_change
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() AND state LIKE 'idle%'
AND state_change < current_timestamp - '15 min'::interval;
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

遮断した接続の情報を取得遮断する条件53アクセス制御

暗号化

ログ監査

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

54

格納データの暗号化n

やりたいこと¨n

PostgreSQLでは？
¨

n

データ暗号化機能/ツールを利用し、格納データを暗号化する。

contribモジュールの「pgcrypto」を利用して、暗号化/復号が可能

pgcryptoでできること/できないこと
できること
・共有鍵方式による暗号化/復号
・公開鍵方式による暗号化/復号¨ できないこと
・鍵の管理→鍵をどのように管理して運用するかは十分に検討しておくこと。
・透過的な暗号化/復号→次ページの「ファイルシステムの透過的暗号化」などを参考に。
¨

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

55

ファイルシステムの透過的暗号化n

やりたいことデータ暗号化機能/ツールを利用し、格納データを暗号化する。
¨ また、可能な限りSQLの書き換えは行いたくない。
¨n

PostgreSQLでは？
PostgreSQL本体の機能だけでは、透過的暗号化を行うことはできない。
¨ OSの機能や、商用製品を利用する必要がある。
¨n

OSによる代替案
OSに付属するdm-crypt暗号化機能で、ファイルシステムレベルで暗号化/復号する。
→ディスクの抜き取りや仮想イメージデータの奪取などのデータ盗み出しに対するデータ保  護が可能になる。
¨ 注意点→データへのアクセスについては、通常のファイルシステムを利用する場合と違いはない。
  ただし、マシンの再起動時にパスフレーズを入力するなどの適切な手順を踏む必要が
  でてくることに注意が必要である。
¨

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

56

アクセス制御

暗号化

ログ監査

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

57出力したログの保全n

やりたいこと¨n

監査証跡ファイルを不正な変更(改ざん)から保護する。

PostgreSQLでは？
リアルタイムに監査証跡ファイル(サーバログ)の改ざんを防止する仕組みはない。
¨ 改ざん防止のためには、別途OSやツールと組み合わせる必要がある。
¨

n

代替手段
防止はできないまでも、下記の点を考慮した運用を検討する改ざんされにくくする→サーバログへのアクセス権限を、log_file_modeパラメータで適切に設定する。
¨ 改ざんを検出する→万が一、改ざんが発生してもすぐに検知できる仕組みを用意する。例えば、OS付属の  auditdでサーバログへのアクセスを監視するなどがある。
¨ 改ざんされていないことを保証する→OpenSSLなどでサーバログに対する電子署名を生成し、サーバログの信ぴょう性を担  保する。
¨Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

58

セキュリティテーマまとめ
PostgreSQL• OpenLdap• etc...

外部周辺ツール• pg_hba.conf• contribモジュール• etc...

OS機能

•
•
•
•

auditdcron
dm_cryptetc...

PCI  DSS要件をクリアするためには、PostgreSQL単体の機能のみ
ではなく外部周辺ツールやOS機能を組み合わせて対応することがポイントになる。

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

59エンディング

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

60

2014年度の活動成果n 「2014年度WG3活動報告書」として、

PGEConsのサイトにドキュメントを公開しています。
¨ 可用性編：  63ページ、セキュリティ編：  83ページ

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

61

2014年度活動を振り返ってn 参加企業の皆様からいただいたコメント¨ 1つの企業のみでは取り組みづらい技術的な課題に対し

PGEConsを通して効率的、効果的に調査ができました。
¨ 普段は競合他社である方々から様々な視点で議論したりレビューを受けたりするのは新鮮で有意義でした。
¨ 普段とは異なるメンバと議論することで、技術面、ビジネ

ス面ともに通常の業務では得られない知識が得られたと思います。

Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

62

PGEConsで一緒に活動しませんか？
Copyright  ©  PostgreSQL  Enterprise  Consortium,  All  Rights  Reserved.

63