Amazon QuickSightからEC2上のPostgreSQLにアクセスする

2018.11.02

Amazon QuickSight Enterprise EditionではVPC内のデータにアクセスできます。

この機能を利用することで

  • Amazon RDS・Amazon Redshift
  • EC2 上に構築したデータベース

にパブリック IP アドレスが割り振られていないケースでも、 QuickSight のデータソースに利用できます。

Amazon Web Services ブログには、RDS PostgreSQL を例にした VPC 接続方法が解説されています。

Amazon QuickSightのプライベートVPC内のデータアクセスの設定方法について | Amazon Web Services

今回は、このバリエーションとして、EC2 上の PostgreSQL に対して接続してみます。

実質的な手順は AWS ブログと同じです。 元ブログを参考に、EC2 上のデータストアへの接続を試してみたら、ハマることなく無事疎通できたことの記録としてお読みください。

構成図も元ブログから大いにインスパイアされています。

イメージ図

QuickSight のデータソースとして Amazon Linux 2 上の PostgreSQL 9.2 を利用します。

ポイント

設定上のポイントは以下です。

  • Amazon QuickSight の VPC アクセスでは Elastic Network Interface(ENI) が作成される
  • QuickSight の VPC 通信用のセキュリティグループを新規に作成
  • QuickSight から PostgreSQL on EC2 への通信では、PostgreSQL on EC2 の セキュリティグループの inbound rule に QuickSight のセキュリティグループを指定
  • PostgreSQL on EC2 からの戻りの通信はステートレス。そのため、QuickSight のセキュリティグループの inbound rule に PostgreSQL on EC2 の セキュリティグループを指定

特に、最後のリターントラフィックがステートレスであることにご注意ください。

前提

  • Amazon QuickSight Enterprise Edition を契約している
  • QuickSight の管理者は以下の権限がある
  • quicksight:CreateVPCConnection
  • ec2:CreateNetworkInterface

事前準備:EC2 に PostgreSQL をインストール

Amazon Linux 2 に PostgreSQL 9.2 をインストールします。

インスタンスの用意

Amazon Linux2 で EC2 を起動します。 このインスタンスには、セキュリティグループ「PostgreSQL-access」をアタッチします。

Inbound Rule には以下のルールを追加します。

  • Protocol : TCP(PostgreSQL)
  • Port Range : 5432
  • Source : VPC の CIDR

root権限:PostgreSQL のインストール

PostgreSQL のインストール

$ sudo yum install -y postgresql-server

データの初期化

$ sudo -u postgres postgresql-setup initdb
Initializing database ... OK
$ sudo systemctl start postgresql.service
$

postgresユーザー:PostgreSQLのデータベース・テーブル作成

postgres ユーザーで

  • データベース : dbname
  • テーブル : foo

を作成します。

$ sudo su - postgres
$ psql
psql (9.2.24)
Type "help" for help.
postgres=#
postgres=# create database dbname;
CREATE DATABASE
postgres=# \connect dbname;
You are now connected to database "dbname" as user "postgres".
dbname=# create table foo(a int);
CREATE TABLE
dbname=# insert into foo values(1);
INSERT 0 1

postgresユーザー権限:VPC内からPostgreSQL にアクセスできるように設定変更

pg_hba.conf:パスワード認証のための変更

/var/lib/pgsql/data/pg_hba.conf

$ diff -u pg_hba.conf.orig pg_hba.conf
--- pg_hba.conf.orig	2018-10-28 18:22:38.326738552 +0000
+++ pg_hba.conf	2018-10-28 18:23:45.046444268 +0000
@@ -79,6 +79,7 @@
 # "local" is for Unix domain socket connections only
 local   all             all                                     peer
 # IPv4 local connections:
+host    all             all             172.31.0.0/16            md5
 host    all             all             127.0.0.1/32            ident
 # IPv6 local connections:
 host    all             all             ::1/128                 ident

172.31.0.0/16 は対象 VPC の CIDR です。

pg_hba.conf : localhost 以外からの接続のため

/var/lib/pgsql/data/postgresql.conf

$ diff -u postgresql.conf.orig postgresql.conf
--- postgresql.conf.orig	2018-10-28 18:22:45.006709089 +0000
+++ postgresql.conf	2018-10-28 18:25:06.362078497 +0000
@@ -56,6 +56,7 @@

 # - Connection Settings -

+listen_addresses = '*'		# what IP address(es) to listen on;
 #listen_addresses = 'localhost'		# what IP address(es) to listen on;
 					# comma-separated list of addresses;
 					# defaults to 'localhost'; use '*' for all

postgresユーザー権限:QuickSight 接続用ユーザーの作成

$ createuser -P -s -e username
Enter password for new role:
Enter it again:
CREATE ROLE username PASSWORD 'XXX' SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;

QuickSight 用サブネットからの接続確認

設定を反映するために、PostgreSQL をリスタートします。

$ sudo systemctl restart postgresql.service

QuickSight 用セキュリティグループを適用した EC2 から PostgreSQL に接続できることを確認します。

$ psql -h 172.31.4.110 -U username  dbname # 172.31.4.110 は PostgreSQL on EC2 のプライベートIPアドレス
Password for user username:
psql (9.2.24)
Type "help" for help.

dbname=# select * from foo;
 a
---
 1
(1 row)

dbname=#

以上で事前準備は完了です。

1.QuickSight用のセキュリティグループ作成

QuickSight の ENI にアタッチするセキュリティグループを作成します。

名前を「Amazon-QuickSight-access」とします。

2.QuickSight から PostgreSQL へのトラフィック

QuickSight からPostgreSQL on EC2 のセキュリティグループ(PostgreSQL-access)に QuickSight のセキュリティグループ(Amazon-QuickSight-access)経由のアクセスを許可します。

セキュリティグループ PostgreSQL-access の Inbound Rule に以下のルールを追加します。

  • Protocol : TCP
  • Port Range : PostgreSQL
  • Source : 「Amazon-QuickSight-access」 ※ QuickSight ENI 用 セキュリティグループ

3.QuickSight から PostgreSQL へのトラフィックの戻り

Amazon QuickSight ENI にアタッチされているセキュリティグループはステートフルではありません。つまり、送信先ホストからの戻りトラフィックは自動的に許可されません。

Amazon QuickSight Elastic Network Interface にアタッチされているセキュリティグループは、ほとんどのセキュリティグループとは動作が異なります。セキュリティグループは通常ステートフルです。つまり、送信接続が確立されると、送信先ホストからのリターントラフィックが自動的に許可されます。ただし、Amazon QuickSight ネットワークインターフェイスにアタッチされているセキュリティグループはステートフルではありません。つまり、送信先ホストからの戻りトラフィックは自動的に許可されません。この場合、ネットワークインターフェイスセキュリティグループに Egress ルールを追加しても機能しません。したがって、明示的に承認するために、受信ルールをセキュリティグループに追加する必要があります。

すべての受信リターンパケットの送信先ポート番号はランダムに割り当てられたポート番号に設定されているため、セキュリティグループの受信ルールはすべてのポート (0–65535) のトラフィックを許可する必要があります。Amazon QuickSight がどのインスタンスに接続できるかを制限しない場合は、すべてのポートでトラフィックを 0.0.0.0/0 にする受信ルールを使用してこのセキュリティグループを設定できます。特定のインスタンスにのみ接続するように Amazon QuickSight を制限する場合は、セキュリティグループ ID を指定できます (推奨)。または、受信セキュリティグループルールで許可するインスタンスのプライベート IP アドレスを指定することもできます。この場合、インバウンドセキュリティグループルールでは、すべてのポートでトラフィックを許可する必要があります。

https://docs.aws.amazon.com/quicksight/latest/user/working-with-aws-vpc.html

そのため、送信先ホストからの戻りトラフィックをこのセキュリティグループ(Amazon-QuickSight-access)のインバウンドルールで許可します。 すべての受信リターンパケットの送信先ポート番号はランダムに割り当てられたポート番号に設定されているため、セキュリティグループの受信ルールはすべてのポート (0–65535) のトラフィックを許可します。

データソースは PostgreSQL on EC2 にアタッチしたセキュリティグループです。

  • Protocol : TCP
  • Port Range : ALL
  • Source : 「PostgreSQL-access」 ※ PostgreSQL on EC2 用 セキュリティグループ

4.QuickSightアカウント設定メニューからVPC接続定義を作成

QuickSight 管理コンソールの「Manage QuickSight→Manage VPC connections」から VPC コネクションを追加します。

  • VPC connection name : 任意の名前
  • VPC ID : PostgreSQL on EC2 インスタンスのある VPC ID
  • subnet ID : QuickSight ENI 用 に利用するサブネット
  • Security group ID : 「Amazon-QuickSight-access」 ※ QuickSight ENI 用 セキュリティグループ

5.QuickSightのデータソースの定義

QuickSight 管理コンソールの「Manage Data set→New data set」からPostgreSQL を選択します。

データセットの設定画面で PostgreSQL の情報を記載します。

  • Data source name : 任意の名前
  • Connection type : VPC connections から先程作成した「VPC connection name」を選択
  • Database server : EC2 インスタンスの IP アドレス
  • Port : 5432
  • Database name : dbname ※ 環境にあわせる
  • Username : username ※ 環境にあわせる
  • Password : password ※ 環境にあわせる
  • Enable SSL : チェックボックスを無効 ※ 本番では環境にあわせる

上記を入力後、「Validate connection」をクリックして接続確認します。

無事接続に成功した場合は、「Create data source」 からデータソースを作成します。

スキーマ・テーブルが見えていれば、疎通に成功しています。

おめでとうございます。

最後に

Amazon QuickSight から AWS データストアへの接続方法を調べていて、「Amazon QuickSight から AWS データストアへの接続の許可」というドキュメントにたどり着くかもしれません。

読み進めるとわかりますが、このドキュメントはパブリックIPアドレスが割り振られた AWS 上のデータストアへの接続方法です。

プライベートVPC内のデータアクセスの詳細については、次のドキュメントを参照する必要があります。

AWS ドキュメント » Amazon QuickSight » ユーザーガイド » 管理 » AWS サービスでの作業 » Amazon VPC の操作

さらに、プライベートサブネットにある RDS PostgreSQL を例に、図入りで具体的に詳しく解説したものが、冒頭でも紹介した次の AWS ブログです。

Amazon QuickSightのプライベートVPC内のデータアクセスの設定方法について | Amazon Web Services

ブログどおりに設定を行なうことで、パブリックIPアドレスの無い

  • RDS
  • Redshift
  • EC2 上のデータストア
  • オンプレミスのデータストア

を QuickSight のデータセットに利用できます。

それでは。