Amazon RDS for PostgreSQL でサポートされている外部データラッパーを使用する - Amazon Relational Database Service

Amazon RDS for PostgreSQL でサポートされている外部データラッパーを使用する

外部データラッパー (FDW) は、外部データへのアクセスを提供する特定のタイプの拡張機能です。例えば、oracle_fdw 拡張機能を使用すると、 RDS for PostgreSQL DB クラスターが Oracle データベースと連動できるようになります。別の例では、PostgreSQL ネイティブの postgres_fdw 拡張機能を使用すると、RDS for PostgreSQL DB インスタンスの外部に置かれた PostgreSQL DB インスタンスに保存されているデータにアクセスできます。

以下で、PostgreSQL でサポートされている、いくつかの 外部データラッパーについての情報を確認できます。

SQL を使用した DB ログのアクセスのための log_fdw 拡張機能の使用

RDS for PostgreSQL は、SQL インターフェイスを通じてデータベースエンジンのログにアクセスする際に使用できる、log_fdw 拡張機能をサポートしています。log_fdw エクステンションは、データベースログ用の外部テーブルの作成を容易にする 2 つの関数を提供します。

  • list_postgres_log_files - データベースログディレクトリのファイルとファイルサイズ (バイト単位) を一覧表示します。

  • create_foreign_table_for_log_file(table_name text, server_name text, log_file_name text) - 現在のデータベースで指定されたファイルの外部テーブルを構築します。

log_fdw によって作成されたすべての関数は、rds_superuser によって所有されます。rds_superuser ロールのメンバーは、これらの関数へのアクセス権限を他のデータベースユーザーに付与することができます。

デフォルトでは、ログファイルは、log_destination パラメータで指定されたように、Amazon RDS によって stderr (標準エラー) 形式で生成されます。このパラメータには、stderrcsvlog (カンマ区切り値、CSV) の 2 つのオプションしかありません。パラメータに csvlog オプションを追加すると、Amazon RDS は stderrcsvlog 両方のログを生成します。これはインスタンスのストレージ容量に影響を与える可能性があるため、ログ処理に影響を与える他のパラメータに注意する必要があります。詳細については、「ログの送信先の設定」を参照してください。

csvlog ログを生成すること 1 つの利点は、log_fdw 拡張機能により、データが複数の列にきちんと分割された外部テーブルを構築できることです。これを行うには、インスタンスをカスタム DB パラメータグループに関連付けて、log_destination の設定を変更できるようにする必要があります。これを行う方法については、「RDS for PostgreSQL DB インスタンスでのパラメータの使用」を参照してください。

次の例では、log_destination パラメータに cvslog が含まれることを前提としています。

log_fdw 拡張を使用するには

  1. log_fdw 拡張を入手します。

    postgres=> CREATE EXTENSION log_fdw; CREATE EXTENSION
  2. 外部データラッパーとしてログサーバーを作成します。

    postgres=> CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw; CREATE SERVER
  3. ログファイルのリストからすべてを選択します。

    postgres=> SELECT * FROM list_postgres_log_files() ORDER BY 1;

    レスポンスの例を次に示します。

    file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2016-08-09-22.csv | 1111 postgresql.log.2016-08-09-23.csv | 1172 postgresql.log.2016-08-10-00.csv | 1744 postgresql.log.2016-08-10-01.csv | 1102 (4 rows)
  4. 選択したファイルの、1 つの 'log_entry' 列でテーブルを作成します.

    postgres=> SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2016-08-09-22.csv');

    レスポンスでは、テーブルが存在しているということ以外の詳細を返しません。

    ----------------------------------- (1 row)
  5. ログファイルのサンプルを選択します。次のコードは、ログの時間とエラーメッセージの説明を取得します。

    postgres=> SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;

    レスポンスの例を次に示します。

    log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2016 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2016 PDT | database system was interrupted; last known up at 2016-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2016 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2016 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2016 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2016 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2016 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows)

外部データへのアクセスのための postgres_fdw 拡張機能の使用

postgres_fdw 拡張を使用してリモートデータベースサーバーにあるテーブルのデータにアクセスできます。PostgreSQL DB インスタンスからリモート接続を設定すると、リードレプリカにもアクセスできます。

postgres_fdw を使用してリモートデータベースサーバーにアクセスするには

  1. postgres_fdw 拡張をインストールします。

    CREATE EXTENSION postgres_fdw;
  2. CREATE SERVER を使用して外部データサーバーを作成します。

    CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
  3. リモートサーバーで使用するロールを識別するためのユーザーマッピングを作成します。

    CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
  4. リモートサーバーのテーブルにマッピングするテーブルを作成します。

    CREATE FOREIGN TABLE foreign_table ( id integer NOT NULL, data text) SERVER foreign_server OPTIONS (schema_name 'some_schema', table_name 'some_table');

mysql_fdw 拡張機能による MySQL データベースの操作

RDS for PostgreSQL DB インスタンスから MySQL 互換データベースにアクセスするには、mysql_fdw 拡張機能をインストールしそれを使用します。この外部データラッパーを使用すると、RDS for MySQL、Aurora MySQL、MariaDB、その他の MySQL 互換データベースを操作できます。RDS for PostgreSQL から MySQL データベースへの接続は、クライアントとサーバーの設定に応じて、ベストエフォートベースで暗号化されます。ただし、必要に応じて暗号化を強制できます。詳細については、「拡張機能で転送中の暗号化を使用する」を参照してください。

mysql_fdw 拡張機能は、Amazon RDS for PostgreSQL のバージョン 14.2、13.6、およびそれ以降のリリースでサポートされています。MySQL 互換データベースインスタンス上のテーブルに対する RDS for PostgreSQL DB での選択、挿入、更新、および削除をサポートします。

mysql_fdw 拡張機能を使用するように RDS for PostgreSQL DB をセットアップする

RDS for PostgreSQL DB インスタンスでの mysql_fdw 拡張機能のセットアップには、DB インスタンスでの拡張機能のロードと、MySQL DB インスタンスへの接続ポイントの作成が関係しています。このタスクでは、MySQL DB インスタンスに関する次の詳細が必要です。

  • ホスト名またはエンドポイント。RDS for MySQL DB インスタンスの場合、コンソールを使用してエンドポイントを見つけることができます。[Connectivity & security] (接続とセキュリティ) タブを選択し、[Endpoint and port] (エンドポイントとポート) セクションを確認します。

  • ポート番号。MySQL のデフォルトポート番号は 3306 です。

  • データベースの名前 DB 識別子。

また、MySQL ポート 3306 のセキュリティグループまたはアクセスコントロールリスト (ACL) へのアクセスを提供する必要があります。RDS for PostgreSQL DB インスタンスと RDS for MySQL DB インスタンスの両方が、ポート 3306 にアクセスする必要があります。アクセスが正しく設定されていない場合、MySQL 互換テーブルに接続しようとすると、次のようなエラーメッセージが表示されます。

ERROR: failed to connect to MySQL: Can't connect to MySQL server on 'hostname.aws-region.rds.amazonaws.com:3306' (110)

次の手順では、ユーザーが (rds_superuser アカウントとして) 外部サーバーを作成します。次に、外部サーバーへのアクセスを特定のユーザーに付与します。その後、これらのユーザーは、MySQL DB インスタンスを操作するための適切な MySQL ユーザーアカウントへの独自のマッピングを作成します。

mysql_fdw を使用して MySQL データベースサーバーにアクセスするには

  1. rds_superuser ロールがあるアカウントを使用して PostgreSQL DB インスタンスを接続します。RDS for PostgreSQL DB インスタンスの作成時にデフォルトを受け入れた場合、ユーザー名は postgres であり、psql コマンドラインツールを使用して次のように接続できます。

    psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=postgres –-password
  2. 次のように mysql_fdw 拡張機能をインストールします。

    postgres=> CREATE EXTENSION mysql_fdw; CREATE EXTENSION

拡張機能が RDS for PostgreSQL DB インスタンスにインストールされたら、MySQL データベースへの接続を提供する外部サーバーをセットアップします。

外部サーバーを作成するには

RDS for PostgreSQL DB インスタンスで、以下のタスクを実行します。このステップは、rds_superuser特権 (postgres など) があるユーザーとして接続していることを前提としています。

  1. RDS for PostgreSQL DB インスタンスに外部サーバーを作成します。

    postgres=> CREATE SERVER mysql-db FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'db-name.111122223333.aws-region.rds.amazonaws.com', port '3306'); CREATE SERVER
  2. 適切なユーザーに外部サーバーへのアクセスを付与します。これらは、管理者以外のユーザー、つまり、rds_superuser ロールのないユーザーである必要があります。

    postgres=> GRANT USAGE ON FOREIGN SERVER mysql-db to user1; GRANT

PostgreSQL ユーザーは、外部サーバーを介して MySQL データベースへの独自の接続を作成し、管理します。

例: RDS for PostgreSQL から RDS for MySQL データベースを操作する

RDS for MySQL DB インスタンスに、単純なテーブルがあるとします。RDS for PostgreSQL ユーザーが、そのテーブルで (SELECT)、INSERTUPDATEDELETE の項目をクエリしたいと思っています。mysql_fdw 拡張機能は、前の手順で詳述されているように、RDS for PostgreSQL DB インスタンスで作成された、と仮定します。rds_superuser 権限のあるユーザーとして RDS for PostgreSQL DB インスタンスに接続した後、次の手順に進むことができます。

  1. RDS for PostgreSQL DB インスタンスで、外部サーバーを作成します。

    test=> CREATE SERVER mysqldb FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB.aws-region.rds.amazonaws.com', port '3306'); CREATE SERVER
  2. rds_superuser の許可を持たないユーザーに、(例えば user1 として) 使用を許可します。

    test=> GRANT USAGE ON FOREIGN SERVER mysqldb TO user1; GRANT
  3. user1 として接続し、MySQL ユーザーへのマッピングを作成します。

    test=> CREATE USER MAPPING FOR user1 SERVER mysqldb OPTIONS (username 'myuser', password 'mypassword'); CREATE USER MAPPING
  4. MySQL テーブルにリンクされた外部テーブルを作成します。

    test=> CREATE FOREIGN TABLE mytab (a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name ''); CREATE FOREIGN TABLE
  5. 外部テーブルに対して単純なクエリを実行します。

    test=> SELECT * FROM mytab; a | b ---+------- 1 | apple (1 row)
  6. MySQL テーブルでのデータの追加、変更、削除を行うことができます。例:

    test=> INSERT INTO mytab values (2, 'mango'); INSERT 0 1

    SELECT クエリをもう一度実行して、結果を確認します。

    test=> SELECT * FROM mytab ORDER BY 1; a | b ---+------- 1 | apple 2 | mango (2 rows)

拡張機能で転送中の暗号化を使用する

RDS for PostgreSQL から MySQL への接続は、デフォルトで転送中の暗号化 (TLS/SSL) を使用します。ただし、クライアントとサーバーの設定が異なる場合、接続は暗号化されていない状態に戻ります。RDS for MySQL ユーザアカウントの REQUIRE SSL オプションを指定して、すべての発信接続に対して暗号化を適用できます。この同じアプローチは MariaDB および Aurora MySQL ユーザーアカウントでも機能します。

REQUIRE SSL に構成された MySQL ユーザーアカウントの場合、安全な接続を確立できないと接続の試行は失敗します。

既存の MySQL データベースユーザーアカウントの暗号化を強制するには、ALTER USER コマンドを使用できます。次の表に示すとおり、構文は MySQL のバージョンによって異なります。詳細については、MySQL リファレンスマニュアルALTER USER を参照してください。

MySQL 5.7、MySQL 8 MySQL 5.6

ALTER USER 'user'@'%' REQUIRE SSL;

GRANT USAGE ON *.* to 'user'@'%' REQUIRE SSL;

mysql_fdw 拡張機能の詳細については、mysql_fdw ドキュメントをご覧ください。

oracle_fdw 拡張機能による Oracle データベースの操作

RDS for PostgreSQL DB インスタンス から Oracle データベースにアクセスするには、oracle_fdw 拡張機能をインストールして、使用します。この拡張機能は、Oracle データベース用の外部データラッパーです。この拡張機能の詳細については、oracle_fdw のドキュメントを参照してください。

oracle_fdw 拡張機能は、RDS for PostgreSQL のバージョン 12.7、13.3 以上のバージョンでサポートされています。

oracle_fdw 拡張機能の有効化

oracle_fdw 拡張機能を使用するには、以下の手順を実行します。

oracle_fdw 拡張機能を有効化するには

  • rds_superuser のアクセス許可を持つアカウントを使用して、次のコマンドを実行します。

    CREATE EXTENSION oracle_fdw;

例: Amazon RDS for Oracle Database にリンクされた外部サーバーの使用

以下は、Amazon RDS for Oracle のデータベースにリンクされた外部サーバーの使用例です。

RDS for Oracle データベースにリンクされた外部サーバーを作成するには

  1. RDS for Oracle DB インスタンスの以下の点を書き留めます。

    • エンドポイント

    • ポート

    • データベース名

  2. 外部サーバを作成します。

    test=> CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//endpoint:port/DB_name'); CREATE SERVER
  3. rds_superuser の権限を持たないユーザーに、(例えば user1 として) 使用を許可します。

    test=> GRANT USAGE ON FOREIGN SERVER oradb TO user1; GRANT
  4. user1 として接続し、Oracle ユーザーへのマッピングを作成します。

    test=> CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user 'oracleuser', password 'mypassword'); CREATE USER MAPPING
  5. Oracle テーブルにリンクされた外部テーブルを作成します。

    test=> CREATE FOREIGN TABLE mytab (a int) SERVER oradb OPTIONS (table 'MYTABLE'); CREATE FOREIGN TABLE
  6. 外部テーブルに対しクエリを実行します。

    test=> SELECT * FROM mytab; a --- 1 (1 row)

クエリで次のエラーが報告された場合は、セキュリティグループとアクセスコントロールリストをチェックして、両方のインスタンス間で通信が可能なことを確認します。

ERROR: connection for foreign table "mytab" cannot be established DETAIL: ORA-12170: TNS:Connect timeout occurred

転送時の暗号化の使用

PostgreSQL から Oracle への転送時における暗号化は、クライアントとサーバの設定パラメータの組み合わせに基づき構成されます。Oracle 21c の使用例については、Oracle ドキュメントの「About the Values for Negotiating Encryption and Integrity」を参照してください。Amazon RDS で oracle_fdw 用に使用されるクライアントは、ACCEPTED に設定されています。つまり、暗号化は Oracle データベースサーバーの設定に依存します。

データベースが RDS for Oracle 上にある場合の暗号化の設定については、「Oracle ネイティブネットワーク暗号化」を参照してください。

pg_user_mappings のビューおよび許可を理解する

PostgreSQL カタログ pg_user_mapping は、RDS for PostgreSQL ユーザーからのマッピングを外部データ (リモート) サーバー上のユーザーに保存します。カタログへのアクセスは制限されていますが、pg_user_mappings ビューをクリックすると、マッピングが表示されます。以下に、Oracle データベースの例で許可がどのように適用されるかを示す例がありますが、この情報は一般的に外部データラッパーに適用されます。

次の出力では、ロールとアクセス許可が、3 つの異なるサンプルユーザーにマップされていることが示されています。ここで、ユーザー rdssu1rdssu2rds_superuser ロールのメンバーであり、user1 はメンバーではありません。この例では、psql メタコマンド \du を使用して、既存のロールを一覧表示します。

test=> \du List of roles Role name | Attributes | Member of -----------------+------------------------------------------------------------+------------------------------------------------------------- rdssu1 | | {rds_superuser} rdssu2 | | {rds_superuser} user1 | | {}

すべてのユーザー (rds_superuser 権限を持っているユーザーを含む) は、pg_user_mappings テーブルで独自のユーザーマッピング (umoptions) を表示することが許可されています。次の例に示すように、rdssu1 がすべてのユーザーマッピングを取得しようとすると、rdssu1rds_superuser 権限があっても、次のエラーが発生します。

test=> SELECT * FROM pg_user_mapping; ERROR: permission denied for table pg_user_mapping

次に例をいくつか示します。

test=> SET SESSION AUTHORIZATION rdssu1; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | {user=oracleuser,password=mypwd} 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows) test=> SET SESSION AUTHORIZATION rdssu2; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+---------------------------------- 16414 | 16411 | oradb | 16412 | user1 | 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | {user=oracleuser,password=mypwd} (3 rows) test=> SET SESSION AUTHORIZATION user1; SET test=> SELECT * FROM pg_user_mappings; umid | srvid | srvname | umuser | usename | umoptions -------+-------+---------+--------+------------+-------------------------------- 16414 | 16411 | oradb | 16412 | user1 | {user=oracleuser,password=mypwd} 16423 | 16411 | oradb | 16421 | rdssu1 | 16424 | 16411 | oradb | 16422 | rdssu2 | (3 rows)

information_schema._pg_user_mappingspg_catalog.pg_user_mappings の間に実装上の違いがあるため、手動で作成された rds_superuserpg_catalog.pg_user_mappings 内のパスワードを表示する場合には、追加のアクセス許可が必要となります。

rds_superuserinformation_schema._pg_user_mappings 内のパスワードを表示する際には、追加のアクセス許可は必要ありません。

rds_superuser ロールを持たないユーザーの場合、以下の条件の下でのみ、pg_user_mappings 内のパスワードを表示できます。

  • 現在のユーザーはマップされているユーザーであり、サーバーの所有者であるか、そのサーバーに対する USAGE 権限を保持しています。

  • 現在のユーザーはサーバーの所有者であり、マッピングは PUBLIC となっています。

tds_fdw 拡張機能による SQL Server データベースの操作

PostgreSQL tds_fdw 拡張機能を使用して、Sybase や Microsoft SQL Server データベースなど、表形式データストリーム (TDS) プロトコルをサポートするデータベースにアクセスできます。この外部データラッパーを使用すると、RDS for PostgreSQL DB インスタンス を、Amazon RDS for Microsoft SQL Server を含む、TDS プロトコルを使用するデータベースに接続できます。詳細については、GitHub にある tds-fdw/tds_fdw に関するドキュメントを参照してください。

tds_fdw 拡張機能は、Amazon RDS for PostgreSQL のバージョン 14.2、13.6、およびそれ以降のリリースでサポートされています。

tds_fdw 拡張機能を使用するように RDS for PostgreSQL DB をセットアップする

次の手順では、tds_fdw をセットアップして、RDS for PostgreSQL DB インスタンスと使用する例を示します。tds_fdw を使用して SQL Server データベースに接続する前に、インスタンスの次の詳細を取得する必要があります。

  • ホスト名またはエンドポイント。RDS for SQL Server DB インスタンスの場合、コンソールを使用してエンドポイントを見つけることができます。[Connectivity & security] (接続とセキュリティ) タブを選択し、[Endpoint and port] (エンドポイントとポート) セクションを確認します。

  • ポート番号。Microsoft SQL Server のデフォルトポート番号は 1433 です。

  • データベースの名前 DB 識別子。

また、SQL Server ポート、1433 のセキュリティグループまたはアクセスコントロールリスト (ACL) でのアクセスを提供する必要があります。RDS for PostgreSQL DB インスタンスと RDS for SQL Server DB インスタンスの両方が、ポート 1433 にアクセスする必要があります。アクセスが正しく設定されていない場合、Microsoft SQL Server をクエリしようとすると、次のエラーメッセージが表示されます。

ERROR: DB-Library error: DB #: 20009, DB Msg: Unable to connect: Adaptive Server is unavailable or does not exist (mssql2019.aws-region.rds.amazonaws.com), OS #: 0, OS Msg: Success, Level: 9

tds_fdw を使用して SQL Server データベースに接続するには

  1. rds_superuser ロールがあるアカウントを使用して、PostgreSQL DB インスタンスに接続します。

    psql --host=your-DB-instance.aws-region.rds.amazonaws.com --port=5432 --username=test –-password
  2. tds_fdw 拡張機能をインストールします。

    test=> CREATE EXTENSION tds_fdw; CREATE EXTENSION

RDS for PostgreSQL DB インスタンスに拡張機能をインストールした後、外部サーバーをセットアップします。

外部サーバーを作成するには

rds_superuser 権限があるアカウントを使用する RDS for PostgreSQL DB インスタンスで次のタスクを実行します。

  1. RDS for PostgreSQL DB インスタンスで外部サーバーを作成します。

    test=> CREATE SERVER sqlserverdb FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019.aws-region.rds.amazonaws.com, port '1433', database 'tds_fdw_testing'); CREATE SERVER
  2. rds_superuser ロール権限を持たないユーザーに、(例えば user1 として) 許可を付与します。

    test=> GRANT USAGE ON FOREIGN SERVER sqlserverdb TO user1;
  3. user1 として接続し、SQL Server ユーザーへのマッピングを作成します。

    test=> CREATE USER MAPPING FOR user1 SERVER sqlserverdb OPTIONS (username 'sqlserveruser', password 'password'); CREATE USER MAPPING
  4. SQL Server テーブルにリンクされた外部テーブルを作成します。

    test=> CREATE FOREIGN TABLE mytab (a int) SERVER sqlserverdb OPTIONS (table 'MYTABLE'); CREATE FOREIGN TABLE
  5. 外部テーブルに対しクエリを実行します。

    test=> SELECT * FROM mytab; a --- 1 (1 row)

接続に転送中の暗号化を使用する

RDS for PostgreSQL から SQL Server への接続には、SQL Server のデータベース設定に応じて、転送中の暗号化 (TLS/SSL) を使用します。SQL Server が暗号化用に設定されていない場合、SQL Server データベースへの要求を行う RDS for PostgreSQL クライアントは、暗号化されていない状態に戻ります。

rds.force_ssl パラメータを設定して、RDS for SQL Server DB インスタンスへの接続に暗号化を強制できます。この方法については、「DB インスタンスへの接続に SSL を使用させる」を参照してください。RDS for SQL Server での SSL/TLS 設定の詳細については、「Microsoft SQL Server DB インスタンスでの SSL の使用」を参照してください。