Amazon Aurora PostgreSQL でサポートされている外部データラッパーを使用する
外部データラッパー (FDW) は、外部データへのアクセスを提供する特定のタイプの拡張機能です。例えば、oracle_fdw
拡張機能を使用すると、Aurora PostgreSQL DB インスタンス が Oracle データベースと連動できるようになります。
以下で、PostgreSQL でサポートされている、いくつかの 外部データラッパーについての情報を確認できます。
トピック
SQL を使用した DB ログのアクセスのための log_fdw 拡張機能の使用
Aurora PostgreSQL DB クラスター は、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 Aurora によって stderr
(標準エラー) 形式で生成されます。このパラメータには、stderr
と csvlog
(カンマ区切り値、CSV) の 2 つのオプションしかありません。パラメータに csvlog
オプションを追加すると、Amazon Aurora は stderr
と csvlog
両方のログを生成します。これは DB クラスターのストレージ容量に影響を与える可能性があるため、ログ処理に影響を与える他のパラメータに注意する必要があります。詳細については、「ログの送信先の設定 (stderr、csvlog)」を参照してください。
csvlog
ログを生成すること 1 つの利点は、log_fdw
拡張機能により、データが複数の列にきちんと分割された外部テーブルを構築できることです。これを行うには、インスタンスをカスタム DB パラメータグループに関連付けて、log_destination
の設定を変更できるようにする必要があります。これを行う方法については、「「パラメータグループを使用する」 」を参照してください。
次の例では、log_destination
パラメータに cvslog
が含まれることを前提としています。
log_fdw 拡張を使用するには
-
log_fdw
拡張機能をインストールします。postgres=>
CREATE EXTENSION log_fdw;
CREATE EXTENSION
-
外部データラッパーとしてログサーバーを作成します。
postgres=>
CREATE SERVER log_server FOREIGN DATA WRAPPER log_fdw;
CREATE SERVER
-
ログファイルのリストからすべてを選択します。
postgres=>
SELECT * FROM list_postgres_log_files() ORDER BY 1;
レスポンスの例を次に示します。
file_name | file_size_bytes ------------------------------+----------------- postgresql.log.2023-08-09-22.csv | 1111 postgresql.log.2023-08-09-23.csv | 1172 postgresql.log.2023-08-10-00.csv | 1744 postgresql.log.2023-08-10-01.csv | 1102 (4 rows)
-
選択したファイルの、1 つの 'log_entry' 列でテーブルを作成します。
postgres=>
SELECT create_foreign_table_for_log_file('my_postgres_error_log', 'log_server', 'postgresql.log.2023-08-09-22.csv');
レスポンスでは、テーブルが存在しているということ以外の詳細を返しません。
----------------------------------- (1 row)
-
ログファイルのサンプルを選択します。次のコードは、ログの時間とエラーメッセージの説明を取得します。
postgres=>
SELECT log_time, message FROM my_postgres_error_log ORDER BY 1;
レスポンスの例を次に示します。
log_time | message ----------------------------------+--------------------------------------------------------------------------- Tue Aug 09 15:45:18.172 2023 PDT | ending log output to stderr Tue Aug 09 15:45:18.175 2023 PDT | database system was interrupted; last known up at 2023-08-09 22:43:34 UTC Tue Aug 09 15:45:18.223 2023 PDT | checkpoint record is at 0/90002E0 Tue Aug 09 15:45:18.223 2023 PDT | redo record is at 0/90002A8; shutdown FALSE Tue Aug 09 15:45:18.223 2023 PDT | next transaction ID: 0/1879; next OID: 24578 Tue Aug 09 15:45:18.223 2023 PDT | next MultiXactId: 1; next MultiXactOffset: 0 Tue Aug 09 15:45:18.223 2023 PDT | oldest unfrozen transaction ID: 1822, in database 1 (7 rows)
外部データへのアクセスのための postgres_fdw 拡張機能の使用
postgres_fdw
postgres_fdw を使用してリモートデータベースサーバーにアクセスするには
postgres_fdw 拡張をインストールします。
CREATE EXTENSION postgres_fdw;
CREATE SERVER を使用して外部データサーバーを作成します。
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'xxx.xx.xxx.xx', port '5432', dbname 'foreign_db');
リモートサーバーで使用するロールを識別するためのユーザーマッピングを作成します。
CREATE USER MAPPING FOR local_user SERVER foreign_server OPTIONS (user 'foreign_user', password 'password');
リモートサーバーのテーブルにマッピングするテーブルを作成します。
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 データベースの操作
Aurora PostgreSQL DB クラスター から MySQL 互換データベースにアクセスするには、mysql_fdw
拡張機能をインストールしそれを使用します。この外部データラッパーを使用すると、RDS for MySQL、Aurora MySQL、MariaDB、その他の MySQL 互換データベースを操作できます。Aurora PostgreSQL DB クラスター から MySQL データベースへの接続は、クライアントとサーバーの設定に応じて、ベストエフォートベースで暗号化されます。ただし、必要に応じて暗号化を強制できます。詳細については、「拡張機能で転送中の暗号化を使用する」を参照してください。
mysql_fdw
拡張機能は、Amazon Aurora PostgreSQL バージョン 15.4、14.9、13.12、12.16、 以降のリリースでサポートされています。MySQL 互換データベースインスタンス上のテーブルに対する RDS for PostgreSQL DB での選択、挿入、更新、および削除をサポートします。
トピック
mysql_fdw 拡張機能を使用するように Aurora PostgreSQL DB をセットアップする
Aurora PostgreSQL DB クラスター での mysql_fdw
拡張機能のセットアップには、DB クラスターでの拡張機能のロードと、MySQL DB インスタンスへの接続ポイントの作成が関係しています。このタスクでは、MySQL DB インスタンスに関する次の詳細が必要です。
ホスト名またはエンドポイント。Aurora MySQL DB クラスター の場合、コンソールを使用してエンドポイントを見つけることができます。[接続とセキュリティ] タブを選択し、[エンドポイントとポート] セクションを確認します。
ポート番号。MySQL のデフォルトポート番号は 3306 です。
データベースの名前 DB 識別子。
また、MySQL ポート 3306 のセキュリティグループまたはアクセスコントロールリスト (ACL) へのアクセスを提供する必要があります。Aurora PostgreSQL DB クラスターと Aurora 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 データベースサーバーにアクセスするには
rds_superuser
ロールがあるアカウントを使用して PostgreSQL DB インスタンスを接続します。Aurora PostgreSQL DB クラスター の作成時にデフォルトを受け入れた場合、ユーザー名はpostgres
であり、psql
コマンドラインツールを使用して次のように接続できます。psql --host=
your-DB-instance
.aws-region
.rds.amazonaws.com --port=5432 --username=postgres –-password次のように
mysql_fdw
拡張機能をインストールします。postgres=>
CREATE EXTENSION mysql_fdw;
CREATE EXTENSION
拡張機能が Aurora PostgreSQL DB クラスターにインストールされたら、MySQL データベースへの接続を提供する外部サーバーをセットアップします。
外部サーバーを作成するには
Aurora PostgreSQL DB クラスター でこれらのタスクを実行します。このステップは、rds_superuser
特権 (postgres
など) があるユーザーとして接続していることを前提としています。
Aurora 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
適切なユーザーに外部サーバーへのアクセスを付与します。これらは、管理者以外のユーザー、つまり、
rds_superuser
ロールのないユーザーである必要があります。postgres=>
GRANT USAGE ON FOREIGN SERVER
mysql-db
touser1
;GRANT
PostgreSQL ユーザーは、外部サーバーを介して MySQL データベースへの独自の接続を作成し、管理します。
例: Aurora PostgreSQL から Aurora MySQL データベースを操作する
Aurora PostgreSQL DB インスタンスにシンプルなテーブルがあると仮定します。Aurora PostgreSQL ユーザーが、そのテーブルで (SELECT
)、INSERT
、UPDATE
、DELETE
の項目をクエリしたいと思っています。mysql_fdw
拡張機能は、前の手順で詳述されているように、RDS for PostgreSQL DB インスタンスで作成された、と仮定します。rds_superuser
権限のあるユーザーとして RDS for PostgreSQL DB インスタンスに接続した後、次の手順に進むことができます。
Aurora PostgreSQL DB インスタンスで外部サーバーを作成します。
test=>
CREATE SERVER
mysqldb
FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host 'your-DB
.aws-region
.rds.amazonaws.com', port '3306');CREATE SERVER
rds_superuser
の許可を持たないユーザーに、(例えばuser1
として) 使用を許可します。test=>
GRANT USAGE ON FOREIGN SERVER mysqldb TO user1;
GRANT
user1
として接続し、MySQL ユーザーへのマッピングを作成します。test=>
CREATE USER MAPPING FOR
user1
SERVER mysqldb OPTIONS (username 'myuser
', password 'mypassword
');CREATE USER MAPPING
MySQL テーブルにリンクされた外部テーブルを作成します。
test=>
CREATE FOREIGN TABLE
mytab
(a int, b text) SERVER mysqldb OPTIONS (dbname 'test', table_name '');CREATE FOREIGN TABLE
外部テーブルに対して単純なクエリを実行します。
test=>
SELECT * FROM mytab;
a | b ---+------- 1 | apple (1 row)
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)
拡張機能で転送中の暗号化を使用する
Aurora 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.0 | MySQL 5.6 |
---|---|
|
|
mysql_fdw
拡張機能の詳細については、mysql_fdw
oracle_fdw 拡張機能による Oracle データベースの操作
Aurora PostgreSQL DB クラスター から Oracle データベースにアクセスするには、oracle_fdw
拡張機能をインストールして、使用します。この拡張機能は、Oracle データベース用の外部データラッパーです。この拡張機能の詳細については、oracle_fdw
oracle_fdw
拡張機能は、PostgreSQL 12.7 (Amazon Aurora release 4.2) 以上のバージョンでサポートされています。
トピック
oracle_fdw 拡張機能の有効化
oracle_fdw 拡張機能を使用するには、以下の手順を実行します。
oracle_fdw 拡張機能を有効化するには
-
rds_superuser
のアクセス許可を持つアカウントを使用して、次のコマンドを実行します。CREATE EXTENSION oracle_fdw;
例: Amazon RDS for Oracle Database にリンクされた外部サーバーの使用
以下は、Amazon RDS for Oracle のデータベースにリンクされた外部サーバーの使用例です。
RDS for Oracle データベースにリンクされた外部サーバーを作成するには
-
RDS for Oracle DB インスタンスの以下の点を書き留めます。
-
エンドポイント
-
ポート
-
データベース名
-
-
外部サーバーを作成します。
test=>
CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//
endpoint
:port
/DB_name
');CREATE SERVER
-
rds_superuser
の権限を持たないユーザーに、(例えばuser1
として) 使用を許可します。test=>
GRANT USAGE ON FOREIGN SERVER oradb TO user1;
GRANT
-
user1
として接続し、Oracle ユーザーへのマッピングを作成します。test=>
CREATE USER MAPPING FOR user1 SERVER oradb OPTIONS (user '
oracleuser'
, password 'mypassword'
);CREATE USER MAPPING
-
Oracle テーブルにリンクされた外部テーブルを作成します。
test=>
CREATE FOREIGN TABLE
mytab
(a int) SERVER oradb OPTIONS (table 'MYTABLE'
);CREATE FOREIGN TABLE
-
外部テーブルに対しクエリを実行します。
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 IntegrityACCEPTED
に設定されています。つまり、暗号化は Oracle データベースサーバーの設定に依存します。
データベースが RDS for Oracle 上にある場合の暗号化の設定については、「Oracle ネイティブネットワーク暗号化」を参照してください。
pg_user_mappings のビューおよび許可を理解する
PostgreSQL カタログ pg_user_mapping
は、Aurora PostgreSQL ユーザーからのマッピングを外部データ (リモート) サーバー上のユーザーに保存します。カタログへのアクセスは制限されていますが、pg_user_mappings
ビューをクリックすると、マッピングが表示されます。以下に、Oracle データベースの例で許可がどのように適用されるかを示す例がありますが、この情報は一般的に外部データラッパーに適用されます。
次の出力では、ロールとアクセス許可が、3 つの異なるサンプルユーザーにマップされていることが示されています。ここで、ユーザー rdssu1
と rdssu2
は rds_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
がすべてのユーザーマッピングを取得しようとすると、rdssu1
rds_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_mappings
と pg_catalog.pg_user_mappings
の間に実装上の違いがあるため、手動で作成された rds_superuser
が pg_catalog.pg_user_mappings
内のパスワードを表示する場合には、追加のアクセス許可が必要となります。
rds_superuser
が information_schema._pg_user_mappings
内のパスワードを表示する際には、追加のアクセス許可は必要ありません。
rds_superuser
ロールを持たないユーザーの場合、以下の条件の下でのみ、pg_user_mappings
内のパスワードを表示できます。
-
現在のユーザーはマップされているユーザーであり、サーバーの所有者であるか、そのサーバーに対する
USAGE
権限を保持しています。 -
現在のユーザーはサーバーの所有者であり、マッピングは
PUBLIC
となっています。
tds_fdw 拡張機能による SQL Server データベースの操作
PostgreSQL tds_fdw
拡張機能を使用して、Sybase や Microsoft SQL Server データベースなど、表形式データストリーム (TDS) プロトコルをサポートするデータベースにアクセスできます。この外部データラッパーを使用すると、 Aurora PostgreSQL DB クラスターを、Amazon RDS for Microsoft SQL Server を含む、TDS プロトコルを使用するデータベースに接続できます。詳細については、GitHub にある tds-fdw/tds_fdw
tds_fdw
拡張機能は、Amazon Aurora PostgreSQL バージョン 13.6 以降でサポートされています。
tds_fdw 拡張機能を使用するように Aurora PostgreSQL DB をセットアップする
次の手順では、tds_fdw
をセットアップして、Aurora 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) でのアクセスを提供する必要があります。Aurora 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 データベースに接続するには
rds_superuser
ロールがあるアカウントを使用して、Aurora PostgreSQL DB クラスターのプライマリインスタンスに接続します。psql --host=
your-cluster-name-instance-1
.aws-region
.rds.amazonaws.com --port=5432 --username=test –-passwordtds_fdw
拡張機能をインストールします。test=>
CREATE EXTENSION tds_fdw;
CREATE EXTENSION
Aurora PostgreSQL DB クラスター に拡張機能をインストールした後、外部サーバーをセットアップします。
外部サーバーを作成するには
rds_superuser
権限があるアカウントを使用する Aurora PostgreSQL DB クラスターで次のタスクを実行します。
Aurora 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
SQLServer 側で非 ASCII データにアクセスするには、Aurora PostgreSQL DB クラスターの character_set オプションを使用してサーバーリンクを作成します。
test=>
CREATE SERVER
sqlserverdb
FOREIGN DATA WRAPPER tds_fdw OPTIONS (servername 'mssql2019
.aws-region
.rds.amazonaws.com', port '1433', database 'tds_fdw_testing
', character_set'UTF-8'
);CREATE SERVER
rds_superuser
ロール権限を持たないユーザーに、(例えばuser1
として) 許可を付与します。test=>
GRANT USAGE ON FOREIGN SERVER
sqlserverdb
TOuser1
;user1 として接続し、SQL Server ユーザーへのマッピングを作成します。
test=>
CREATE USER MAPPING FOR user1 SERVER
sqlserverdb
OPTIONS (username 'sqlserveruser
', password 'password
');CREATE USER MAPPING
SQL Server テーブルにリンクされた外部テーブルを作成します。
test=>
CREATE FOREIGN TABLE mytab (a int) SERVER
sqlserverdb
OPTIONS (table 'MYTABLE
');CREATE FOREIGN TABLE
外部テーブルに対しクエリを実行します。
test=>
SELECT * FROM mytab;
a --- 1 (1 row)
接続に転送中の暗号化を使用する
Aurora 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 の使用」を参照してください。