PostgreSQL の一般的な DBA タスク - Amazon Relational Database Service

PostgreSQL の一般的な DBA タスク

このセクションでは、PostgreSQL データベースエンジンを実行している DB インスタンスに関するいくつかの一般的な DBA タスクの Amazon RDS の実装について説明します。マネージド型サービスの操作性を実現するために、Amazon RDS では DB インスタンスへのシェルアクセスはできません。また、上位の権限を必要とする特定のシステムプロシージャやシステムテーブルへのアクセスが制限されます。

Amazon RDS での PostgreSQL ログファイルの操作に関する詳細は、「PostgreSQL データベースのログファイル」を参照してください。

ロールの作成

DB インスタンスを作成すると、作成したマスターユーザーシステムアカウントが rds_superuser ロールに割り当てられます。rds_superuser ロールは事前に定義された Amazon RDS ロールであり、PostgreSQL のスーパーユーザーロール (通常、ローカルインスタンスでは postgres という名前になります) と類似しています。ただし、制限がいくつかあります。PostgreSQL のスーパーユーザーロールと同様に、rds_superuser ロールは、DB インスタンスに対して最大の権限を持っています。また、ユーザーが DB インスタンスに対して最大のアクセス権を必要としない場合は、ユーザーにこのロールを割り当てないでください。

rds_superuser ロールでは以下の操作を実行できます。

  • Amazon RDS で使用できる拡張機能の追加 詳細については、「サポートされている PostgreSQL の一部の機能」および PostgreSQL ドキュメントを参照してください。

  • テーブルスペースを管理する (作成と削除も含む)。詳細については、 Amazon RDS の PostgreSQL のテーブルスペース と PostgreSQL のドキュメントの テーブルスペース セクションを参照してください。

  • rds_superuser コマンドを使用して、pg_stat_activity ロールが割り当てられていないすべてのユーザーを表示し、pg_terminate_backendpg_cancel_backend コマンドを使用して、それらのユーザーの接続を停止する。

  • rds_replication ロール以外のすべてのロールに対して rds_superuser ロールを付与する/取り消す。詳細については、PostgreSQL のドキュメントの「GRANT」セクションを参照してください。

次の例は、ユーザーを作成して、ユーザーに rds_superuser ロールを付与する方法を示しています。ユーザー定義のロール (rds_superuser など) を付与する必要があります。

create role testuser with password 'testuser' login; grant rds_superuser to testuser;

PostgreSQL データベースへのアクセスの管理

Amazon RDS for PostgreSQL ,では、どのユーザーがどのデータベースに接続する権限を持っているかを管理できます。他の PostgreSQL 環境では、pg_hba.conf ファイルを変更することでこの種の管理を実行できることがあります。Amazon RDS では、代わりにデータベース許可を使用できます。

PostgreSQL の新しいデータベースは、常にデフォルトの権限セットを使用して作成されます。デフォルト権限では、PUBLIC (すべてのユーザー) がデータベースに接続し、接続しながら一時テーブルを作成できます。

Amazon RDS で特定のデータベースに接続できるユーザーを管理するには、まずデフォルトの PUBLIC 権限を無効にします。次に、より細かく権限を付与しなおします。次のサンプルコードはその方法を示しています。

psql> revoke all on database <database-name> from public; psql> grant connect, temporary on database <database-name> to <user/role name>;

PostgreSQL データベースでの権限については、PostgreSQL のドキュメントで GRANT コマンドを参照してください。

PostgreSQL パラメータの使用

postgresql.conf ファイルでローカル PostgreSQL インスタンスに対して設定する PostgreSQL パラメータは、DB インスタンスの DB パラメータグループで保持されます。デフォルトのパラメータグループを使用して DB インスタンスを作成する場合、パラメータ設定は default.postgres9.6 というパラメータグループにあります。

DB インスタンスの作成時に、関連する DB パラメータグループ内のパラメータが読み込まれます。パラメータグループの値を変更することで、パラメータ値を変更できます。パラメータ値を変更するためのセキュリティ権限がある場合は、ALTER DATABASE、ALTER ROLE、および SET コマンドを使用して、パラメータ値を変更することもできます。ホストへのアクセス権がないため、コマンドラインで postgres コマンドと env PGOPTIONS コマンドのいずれも使用できません。

PostgreSQL パラメータの設定を把握しておくことが、難しいことがあります。次のコマンドを使用すると、現在のパラメータ設定とデフォルト値を表示することができます。

select name, setting, boot_val, reset_val, unit from pg_settings order by name;

出力値の説明については、PostgreSQL ドキュメントの「pg_settings」トピックを参照してください。

max_connections または shared_buffers のメモリ設定が過度に大きいと、PostgreSQL インスタンスが起動できなくなります。一部のパラメータでは馴染みのない単位が使用されます。例えば、shared_buffers はサーバーで使用される 8 KB 単位の共有メモリバッファの数を設定します。

次のエラーは、インスタンスを起動しようとしたが、誤ったパラメータ設定が原因で起動できなかった場合に、postgres.log ファイルに書き込まれます。

2013-09-18 21:13:15 UTC::@:[8097]:FATAL: could not map anonymous shared memory: Cannot allocate memory 2013-09-18 21:13:15 UTC::@:[8097]:HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 3514134274048 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

PostgreSQL パラメータには、静的と動的の 2 種類があります。静的パラメータを適用するには、DB インスタンスを再起動する必要があります。動的パラメータは、すぐに適用できます。次の表は、PostgreSQL DB インスタンスに対して変更できるパラメータと各パラメータの種類を示しています。

パラメータ名

Apply_Type

説明

application_name

動的 統計情報とログで報告されるアプリケーション名を設定します。

array_nulls

動的 配列での NULL 要素の入力を有効にします。

authentication_timeout

動的 クライアント認証の実行で許可する最大時間を設定します。

autovacuum

動的 autovacuum サブプロセスを起動します。

autovacuum_analyze_scale_factor

動的 分析する前のタプルの挿入、更新、削除の数 (reltuples の割合として指定)。

autovacuum_analyze_threshold

動的 分析する前のタプルの挿入、更新、削除の最小数。

autovacuum_naptime

動的 autovacuum の実行の間で休止状態になっている時間。

autovacuum_vacuum_cost_delay

動的 autovacuum でのバキューム処理のコスト遅延の値 (ミリ秒単位)。

autovacuum_vacuum_cost_limit

動的 autovacuum でバキューム処理を停止する制限値となるバキューム処理のコスト

autovacuum_vacuum_scale_factor

動的 バキューム処理する前のタプルの更新または削除の数 (reltuples の割合として指定)。

autovacuum_vacuum_threshold

動的 バキューム処理する前のタプルの更新また削除の最小数。

backslash_quote

動的 文字列リテラルでバックスラッシュ (\) を許可するかどうかを指定します。

bgwriter_delay

動的 ラウンド間でのバックグラウンドライターの休止時間。

bgwriter_lru_maxpages

動的 ラウンドあたりのフラッシュするバックグラウンドライター LRU ページの最大数。

bgwriter_lru_multiplier

動的 ラウンドあたりの解放される平均バッファー使用量の倍数。

bytea_output

動的 バイトの出力形式を設定します。

check_function_bodies

動的 CREATE FUNCTION の実行中に関数の本文をチェックします。

checkpoint_completion_target

動的 チェックポイント中にダーティバッファのフラッシュにかかった時間 (チェックポイント間隔の割合として指定)。

checkpoint_segments

動的 自動 WAL (先書きログ) チェックポイント間のログセグメントの最大間隔を設定します。

checkpoint_timeout

動的 自動 WAL チェックポイント間の最大時間を設定します。

checkpoint_warning

動的 チェックポイントセグメントがこの値よりも頻繁に満杯になる場合に警告を出します。

client_encoding

動的 クライアントの文字セットエンコードを設定します。

client_min_messages

動的 クライアントへ送信されるメッセージレベルを設定します。

commit_delay

動的 トランザクションのコミットからディスクへの WAL のフラッシュまでの遅延間隔をマイクロ秒単位で設定します。

commit_siblings

動的 commit_delay を実行する前に同時に開いている必要があるトランザクションの最少数を設定します。

constraint_exclusion

動的 クエリを最適化するために、プランナーが制約を使用できるようにします。

cpu_index_tuple_cost

動的 インデックススキャンの実行中に各インデックスエントリを処理する際にかかるコストに対するプランナーの見積もりを設定します。

cpu_operator_cost

動的 演算子の呼び出しや関数呼び出しのそれぞれを処理する際にかかるコストに対するプランナーの見積もりを設定します。

cpu_tuple_cost

動的 各タプル (行) の処理にかかるコストに対するプランナーの見積もりを設定します。

cursor_tuple_fraction

動的 取得されるカーソル行の割合に対するプランナーの見積もりを設定します。

datestyle

動的 日付と時刻の値の表示形式を設定します。

deadlock_timeout

動的 デッドロックをチェックするまでロックを待機する時間を設定します。

debug_pretty_print

動的 解析ツリーや計画ツリーの表示をインデントして見やすくします。

debug_print_parse

動的 各クエリの解析ツリーをログに記録します。

debug_print_plan

動的 各クエリの実行計画をログに記録します。

debug_print_rewritten

動的 各クエリの書き直された解析ツリーをログに記録します。

default_statistics_target

動的 デフォルトの統計情報の対象を設定します。

default_tablespace

動的 テーブルとインデックスを作成するためのデフォルトのテーブルスペースを設定します。

default_transaction_deferrable

動的 新しいトランザクションのデフォルトの遅延ステータスを設定します。

default_transaction_isolation

動的 新しい各トランザクションのトランザクション分離レベルを設定します。

default_transaction_read_only

動的 新しいトランザクションのデフォルトの読み取り専用ステータスを設定します。

default_with_oids

動的 デフォルトで OID を使用して新しいテーブルを作成します。

effective_cache_size

動的 ディスクキャッシュのサイズに関するプランナーの予測を設定します。

effective_io_concurrency

動的 ディスクサブシステムで効率的に処理できる同時リクエストの数。

enable_bitmapscan

動的 プランナーがビットマップスキャン計画を使用できるようにします。

enable_hashagg

動的 プランナーがハッシュされた集計計画を使用できるようにします。

enable_hashjoin

動的 プランナーがハッシュ結合計画を使用できるようにします。

enable_indexscan

動的 プランナーがインデックススキャン計画を使用できるようにします。

enable_material

動的 プランナーがマテリアル化を使用できるようにします。

enable_mergejoin

動的 プランナーがマージ結合計画を使用できるようにします。

enable_nestloop

動的 プランナーがネステッドループ結合計画を使用できるようにします。

enable_seqscan

動的 プランナーがシーケンシャルスキャン計画を使用できるようにします。

enable_sort

動的 プランナーが明示的なソートステップを使用できるようにします。

enable_tidscan

動的 プランナーが TID スキャン計画を使用できるようにします。

escape_string_warning

動的 通常の文字列リテラルにバックスラッシュ (\) が含まれている場合に警告を出します。

extra_float_digits

動的 浮動小数点値の表示桁数を設定します。

from_collapse_limit

動的 FROM リストのサイズを設定します。この値を超えるとサブクエリが折りたたまれなくなります。

fsync

動的 ディスクへの更新の同期を強制的に行います。

full_page_writes

動的 チェックポイントの後でページに最初の変更を加えた時点で、WAL にすべてのページを書き込みます。

geqo

動的 遺伝的クエリ最適化を有効にします。

geqo_effort

動的 GEQO: 他の GEQO パラメータのデフォルト値を設定するために使用されます。

geqo_generations

動的 GEQO: アルゴリズムの反復の数。

geqo_pool_size

動的 GEQO: 母集団内の個体の数。

geqo_seed

動的 GEQO: 無作為のパスを選択するための初期値。

geqo_selection_bias

動的 GEQO: 母集団内の選択圧。

geqo_threshold

動的 FROM 項目のしきい値を設定します。この値を超えると GEQO が使用されます。

gin_fuzzy_search_limit

動的 GIN による完全一致検索で許可される結果の最大数を設定します。

hot_standby_feedback

動的 ホットスタンバイがフィードバックメッセージをプライマリあるいはアップストリームスタンバイに送信するかを決定します。

intervalstyle

動的 間隔値の表示形式を設定します。

join_collapse_limit

動的 FROM リストのサイズを設定します。この値を超えると JOIN 構造が平坦化されなくなります。

lc_messages

動的 メッセージを表示する言語を設定します。

lc_monetary

動的 金額の書式のロケールを設定します。

lc_numeric

動的 数値の書式のロケールを設定します。

lc_time

動的 日付と時刻の書式のロケールを設定します。

log_autovacuum_min_duration

動的 autovacuum に関する最小実行時間を設定します。この値を超えると autovacuum アクションがログに記録されます。

log_checkpoints

動的 各チェックポイントをログに記録します。

log_connections

動的 成功した各接続をログに記録します。

log_disconnections

動的 セッションの終了をログに記録します (セッションの有効期間も含まれます)。

log_duration

動的 完了した各 SQL ステートメントの期間をログに記録します。

log_error_verbosity

動的 ログに記録されるメッセージの詳細を設定します。

log_executor_stats

動的 実行プログラムのパフォーマンスの統計情報をサーバーログに書き込みます。

log_filename

動的 ログファイルのファイル名のパターンを設定します。

log_hostname

動的 接続ログにホスト名を記録します。

log_lock_waits

動的 長期間にわたるロックの待機をログに記録します。

log_min_duration_statement

動的 最小実行時間を設定します。この値を超えるとステートメントがログに記録されます。

log_min_error_statement

動的 設定したレベル以上のエラーが発生したすべてのステートメントをログに記録します。

log_min_messages

動的 ログに記録するメッセージレベルを設定します。

log_parser_stats

動的 解析のパフォーマンスの統計情報をサーバーログに書き込みます。

log_planner_stats

動的 プランナーのパフォーマンスの統計情報をサーバーログに書き込みます。

log_rotation_age

動的 N 分が経過するとログファイルのローテーションが自動的に発生します。

log_rotation_size

動的 N キロバイトを超えるとログファイルのローテーションが自動的に発生します。

log_statement

動的 ログに記録するステートメントのタイプを設定します。

log_statement_stats

動的 累積処理のパフォーマンスの統計情報をサーバーログに書き込みます。

log_temp_files

動的 指定したサイズ (キロバイト) を超える一時ファイルの使用をログに記録します。

maintenance_work_mem

動的 メンテナンスオペレーションに使用するメモリの最大量を設定します。

max_stack_depth

動的 スタックの深度の最大値をキロバイト単位で指定します。

max_standby_archive_delay

動的 ホットスタンバイサーバーがアーカイブされた WAL データを処理しているときにクエリをキャンセルするまでの最大遅延間隔を設定します。

max_standby_streaming_delay

動的 ホットスタンバイサーバーがストリームされた WAL データを処理しているときにクエリをキャンセルするまでの最大遅延間隔を設定します。
max_wal_size 動的 チェックポイントをトリガーする WAL サイズを設定します。PostgreSQL バージョン 9.6 以前の場合、max_wal_size の単位は 16 MB です。PostgreSQL バージョン 10 以降の場合、max_wal_size の単位は 1 MB です。
min_wal_size 動的 WAL を縮小する最小サイズを設定します。PostgreSQL バージョン 9.6 以前の場合、min_wal_size の単位は 16 MB です。PostgreSQL バージョン 10 以降の場合、min_wal_size の単位は 1 MB です。

quote_all_identifiers

動的 SQL フラグメントを生成するときに、すべての識別子に引用符 (") を追加します。

random_page_cost

動的 非連続的に取得されたディスクページのコストに対するプランナーの見積もりを設定します。
rds.adaptive_autovacuum 動的 トランザクション ID のしきい値を超えるたびに、autovacuum パラメータを自動的に微調整します。

rds.log_retention_period

動的 N 分より古い PostgreSQL ログは Amazon RDS で削除されるようにログ保持期間を設定します。
rds.restrict_password_commands 静的 rds_password ロールを持つユーザーに対して、だれがパスワードを管理するかを制限します。パスワードの制限を有効にするには、このパラメータを 1 に設定します。デフォルトは 0 です。

search_path

動的 スキーマによって修飾されていない名前でスキーマを検索する順序を設定します。

seq_page_cost

動的 連続的に取得されたディスクページのコストに対するプランナーの見積もりを設定します。

session_replication_role

動的 トリガーと再書き込みルールに対するセッション動作を設定します。

sql_inheritance

動的 さまざまなコマンドにデフォルトでサブテーブルが取り込まれます。

ssl_renegotiation_limit

動的 暗号化キーを再度ネゴシエートする前に送受信されるトラフィックの量を設定します。

standard_conforming_strings

動的 ... 文字列をリテラルのバックスラッシュとして扱います。

statement_timeout

動的 すべてのステートメントに許可される最大実行時間を設定します。

synchronize_seqscans

動的 シーケンシャルスキャンの同期を有効にします。

synchronous_commit

動的 現在のトランザクションの同期レベルを設定します。

tcp_keepalives_count

動的 TCP キープアライブを再送信する最大回数。

tcp_keepalives_idle

動的 TCP キープアライブを発行する間隔の時間。

tcp_keepalives_interval

動的 TCP キープアライブを再送信する間隔の時間。

temp_buffers

動的 各セッションで使用される一時バッファの最大数を設定します。

temp_tablespaces

動的 一時テーブルとソートファイルで使用するテーブルスペースを設定します。

timezone

動的 表示やタイムスタンプの解釈で必要となるタイムゾーンを設定します。

track_activities

動的 コマンドの実行に関する情報を収集します。

track_counts

動的 データベースアクティビティの統計情報を収集します。

track_functions

動的 データベースアクティビティの関数レベルの統計情報を収集します。

track_io_timing

動的 データベース I/O アクティビティのタイミングに関する統計情報を収集します。

transaction_deferrable

動的 読み取り専用のシリアル化可能なトランザクションを、シリアル化が失敗する可能性がない状況で開始できるようになるまで延期するかどうかを示します。

transaction_isolation

動的 現在のトランザクションの分離レベルを設定します。

transaction_read_only

動的 現在のトランザクションの読み取り専用ステータスを設定します。

transform_null_equals

動的 expr=NULL を expr IS NULL として扱います。

update_process_title

動的 アクティブな SQL コマンドを表示するようにプロセスのタイトルを更新します。

vacuum_cost_delay

動的 バキューム処理のコスト遅延の値 (ミリ秒単位)。

vacuum_cost_limit

動的 バキューム処理を停止する制限値となるバキューム処理のコスト。

vacuum_cost_page_dirty

動的 バキューム処理によってダーティになったページに対するバキューム処理のコスト。

vacuum_cost_page_hit

動的 バッファキャッシュ内で検出されたページに対するバキューム処理のコスト。

vacuum_cost_page_miss

動的 バッファキャッシュ内で検出されなかったページに対するバキューム処理のコスト。

vacuum_defer_cleanup_age

動的 バキューム処理とホットクリーンアップが延期されるトランザクションの数 (存在する場合)。

vacuum_freeze_min_age

動的 バキューム処理でテーブルの行をフリーズする最小期間。

vacuum_freeze_table_age

動的 バキューム処理でテーブル全体をスキャンしタプルをフリーズするための期間。

wal_writer_delay

動的 WAL のフラッシュが行われる間の WAL ライターの休止時間。

work_mem

動的 クエリワークスペースに使用するメモリの最大量を設定します。

xmlbinary

動的 バイナリ値を XML にエンコードする方法を設定します。

xmloption

動的 黙示的な解析とシリアル化オペレーションでの XML データをドキュメントとして見なすか、コンテンツのフラグメントとして見なすかを設定します。

autovacuum_freeze_max_age

静的 トランザクション ID の循環を防ぐためにテーブルに対して autovacuum を実行する期間。

autovacuum_max_workers

静的 同時に実行される autovacuum ワーカープロセスの最大数を設定します。

max_connections

静的 同時接続の最大数を設定します。

max_files_per_process

静的 各サーバープロセスで同時に開くことができるファイルの最大数を設定します。

max_locks_per_transaction

静的 トランザクションあたりのロックの最大数を設定します。

max_pred_locks_per_transaction

静的 トランザクションあたりの述語ロックの最大数を設定します。

max_prepared_transactions

静的 同時に準備できるトランザクションの最大数を設定します。

shared_buffers

静的 サーバーで使用される共有メモリバッファの数を設定します。

ssl

静的 SSL 接続を有効にします。
temp_file_limit 静的 一時ファイルの最大サイズを KB 単位で設定します。

track_activity_query_size

静的 pg_stat_activity.current_query 用に予約するサイズをバイト単位で設定します。

wal_buffers

静的 WAL 用の共有メモリ内のディスクページバッファの数を設定します。

Amazon RDS では、すべてのパラメータについて PostgreSQL のデフォルトの単位を使用します。次の表は、各パラメータに対する PostgreSQL のデフォルトの単位と値を示しています。

パラメータ名

単位

effective_cache_size

8 KB

segment_size

8 KB

shared_buffers

8 KB

temp_buffers

8 KB

wal_buffers

8 KB

wal_segment_size

8 KB

log_rotation_size

KB

log_temp_files

KB

maintenance_work_mem

KB

max_stack_depth

KB

ssl_renegotiation_limit

KB
temp_file_limit KB

work_mem

KB

log_rotation_age

autovacuum_vacuum_cost_delay

ms

bgwriter_delay

ms

deadlock_timeout

ms

lock_timeout

ms

log_autovacuum_min_duration

ms

log_min_duration_statement

ms

max_standby_archive_delay

ms

max_standby_streaming_delay

ms

statement_timeout

ms

vacuum_cost_delay

ms

wal_receiver_timeout

ms

wal_sender_timeout

ms

wal_writer_delay

ms

archive_timeout

s

authentication_timeout

s

autovacuum_naptime

s

checkpoint_timeout

s

checkpoint_warning

s

post_auth_delay

s

pre_auth_delay

s

tcp_keepalives_idle

s

tcp_keepalives_interval

s

wal_receiver_status_interval

s

PostgreSQL DB インスタンスの監査ログ記録

いくつかのパラメータを設定して、PostgreSQL DB インスタンスで発生するアクティビティのログを作成できます。これらのパラメータには、以下が含まれます。

  • log_statement パラメータは PostgreSQL データベースのユーザー操作のログを作成するのに使用できます。詳細については、「PostgreSQL データベースのログファイル」を参照してください。

  • rds.force_admin_logging_level パラメータは、DB インスタンス上のデータベースでの RDS 内部ユーザー (rdsadmin) によるアクションをログに記録し、その出力を PostgreSQL エラーログに書き込みます。指定できる値は disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic です。デフォルト値は disabled です。

  • rds.force_autovacuum_logging_level パラメータは、DB インスタンス上のすべてのデータベースでの autovacuum ワーカーによるオペレーションをログに記録し、その出力を PostgreSQL エラーログに書き込みます。指定できる値は disabled、debug5、debug4、debug3、debug2、debug1、info、notice、warning、error、log、fatal、panic です。デフォルト値は disabled です。rds.force_autovacuum_logging_level: 用の Amazon RDS の推奨設定は LOG です。log_autovacuum_min_duration を 1000 または 5000 の値に設定します。この値を 5,000 に設定すると、5 秒を超えるアクティビティがログに書き込まれ、「バキュームのスキップ」メッセージが表示されます。このパラメータの詳細については、「PostgreSQL を使用するためのベストプラクティス」を参照してください。

pgaudit 拡張機能の使用

pgaudit 拡張機能により、RDS for PostgreSQL の詳細セッションおよびオブジェクト監査のログ記録が提供されます。この拡張機能を使用して、セッション監査またはオブジェクト監査を有効にできます。pgaudit 拡張機能は、次のバージョンで使用できます。

  • RDS for PostgreSQL バージョン 13、すべてのマイナーバージョン

  • RDS for PostgreSQL バージョン 12、すべてのマイナーバージョン

  • RDS for PostgreSQL バージョン 11、すべてのマイナーバージョン

  • RDS for PostgreSQL バージョン 10、すべてのマイナーバージョン

  • RDS for PostgreSQL バージョン 9.6.3 以降の 9.6 バージョン

  • RDS for PostgreSQL バージョン 9.5.7 以降の 9.5 バージョン

pgaudit 拡張機能の詳細については、GitHub プロジェクトのドキュメントをご覧ください。お使いの PostgreSQL バージョンのドキュメントを選択してください。

pgaudit 拡張機能を使用するには、カスタム DB パラメータグループが DB インスタンスに関連付けられていることを確認し、このカスタム DB パラメータグループでパラメータを設定します。パラメータグループの詳細については、「DB パラメータグループを使用する」を参照してください。

セッション監査では、さまざまなソースから監査イベントをログ記録できます。利用可能な場合は、完全修飾コマンドテキストも含めることができます。DB インスタンスに関連付けられているカスタムパラメータグループを変更して、shared_preload_librariespgaudit が含まれるようにしてから、次のいずれかのタイプのイベントをログ記録するように pgaudit.log パラメータを設定します。

  • READ - SELECT および COPY の監査 (ソースがリレーションまたはクエリの場合)。

  • WRITE - INSERTUPDATEDELETETRUNCATE、および COPY の監査 (送信先がリレーションの場合)。

  • FUNCTION - 関数呼び出し、および DO ブロックの監査。

  • ROLE - GRANTREVOKECREATE ROLEALTER ROLEDROP ROLE など、ロールと権限に関連するステートメントの監査。

  • DDL - ROLE クラスに含まれていない、すべてのデータ定義言語 (DDL) ステートメントの監査。

  • MISC - DISCARDFETCHCHECKPOINTVACUUMSET など、さまざまなコマンドの監査。

セッション監査でこれらのタイプのイベントを複数ログ記録するには、カンマ区切りリストを使用します。これらすべてのタイプのイベントをログ記録するには、pgaudit.logALL に設定します。変更を適用するために、DB インスタンスの再起動が必要になる場合があります。

オブジェクト監査では、監査のログ記録を絞り込み、特定のリレーションを操作できます。例えば、特定の数のテーブルで、READ オペレーションのログ記録を監査するよう指定できます。

pgaudit 拡張機能でオブジェクト監査を使用する方法

  1. 次のコマンドを使用して、rds_pgaudit というデータベースロールを作成します。

    CREATE ROLE rds_pgaudit;
  2. DB インスタンスに関連付けられているカスタムパラメータグループを変更して、次の操作を実行します。

    • pgaudit.role をロール rds_pgaudit に設定します。

    • pgaudit を含む共有プリロードライブラリを使用します。

    次のコマンドは、カスタムパラメータグループを変更します。

    aws rds modify-db-parameter-group \ --db-parameter-group-name my-parameter-group \ --parameters "ParameterName=pgaudit.role,ParameterValue=rds_pgaudit,ApplyMethod=pending-reboot" \ --region us-west-2 aws rds modify-db-parameter-group \ --db-parameter-group-name my-parameter-group \ --parameters "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \ --region us-west-2
  3. DB インスタンスを再起動し、パラメータグループへの変更をインスタンスに反映します。

    aws rds reboot-db-instance \ --db-instance-identifier rds-test-instance \ --region us-west-2
  4. 次のコマンドを実行して、pgaudit が初期化されたことを確認します。

    SHOW shared_preload_libraries; shared_preload_libraries -------------------------- rdsutils,pgaudit (1 row)
  5. pgaudit 拡張機能を作成するには、次のコマンドを実行します。

    CREATE EXTENSION pgaudit;
  6. 次のコマンドを実行して、pgaudit.rolerds_pgaudit に設定されたことを確認します。

    SHOW pgaudit.role; pgaudit.role ------------------ rds_pgaudit

監査ログをテストするには、監査対象として選択した複数のコマンドを実行します。例えば、次のコマンドを実行します。

CREATE TABLE t1 (id int); GRANT SELECT ON t1 TO rds_pgaudit; SELECT * FROM t1; id ---- (0 rows)

データベースログには、次のようなエントリが含まれます。

... 2017-06-12 19:09:49 UTC:...:rds_test@postgres:[11701]:LOG: AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.t1,select * from t1; ...

ログの表示方法については、「Amazon RDS データベースログファイルの操作」を参照してください。

pg_repack 拡張機能の使用

pg_repack 拡張機能を使用して、テーブルやインデックスの膨張を取り除くことができます。この拡張機能は Amazon RDS for PostgreSQL バージョン 9.6.3 以降でサポートされています。pg_repack 拡張機能の詳細については、GitHub プロジェクトのドキュメントをご覧ください。

pg_repack 拡張機能を使用するには

  1. 次のコマンドを実行して、Amazon RDS for PostgreSQL DB インスタンスに pg_repack 拡張機能をインストールします。

    CREATE EXTENSION pg_repack;
  2. 次のコマンドを実行して、pg_repack によって作成された一時ログテーブルを再パックするための書き込みアクセス権を付与します。

    ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC; ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;
  3. pg_repack クライアントユーティリティを使用してデータベースに接続します。rds_superuser 権限を持つデータベースロールを使用してデータベースに接続します。次の接続例で、rds_test ロールには rds_superuser 権限があり、使用されるデータベースエンドポイントは rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com です。

    pg_repack -h rds-test-instance.cw7jjfgdr4on8.us-west-2.rds.amazonaws.com -U rds_test -k postgres

    -k オプションを使用して接続します。-a オプションはサポートされていません。

  4. pg_repack クライアントからの応答により、再パッケージされる DB インスタンスのテーブルに関する情報が提供されます。

    INFO: repacking table "pgbench_tellers" INFO: repacking table "pgbench_accounts" INFO: repacking table "pgbench_branches"

pgBadger を使用した PostgreSQL でのログ分析

pgbadger などのログ分析ツールを使用して、PostgreSQL のログを分析できます。pgbadger のドキュメントでは %l パターン (セッションやプロセスに関するログの行) はプレフィックスに含める必要があると説明されています。ただし、最新の rds log_line_prefix をパラメータとして pgbadger に渡すことで、レポートを作成できます。

例えば、次のコマンドでは、pgbadger を使用して、2014 年 2 月 4 日の Amazon RDS for PostgreSQL のログファイルを適切にフォーマットします。

./pgbadger -f stderr -p '%t:%r:%u@%d:[%p]:' postgresql.log.2014-02-04-00

pg_config のコンテンツを表示する

PostgreSQL バージョン 9.6.1 では、新しいビューの view pg_config を使用して、現在インストールされている PostgreSQL のバージョンのコンパイル時設定パラメータを表示できます。次の例に示すように、pg_config 関数を呼び出してビューを使用できます。

select * from pg_config(); name | setting -------------------+--------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------- BINDIR | /rdsdbbin/postgres-9.6.1.R1/bin DOCDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc HTMLDIR | /rdsdbbin/postgres-9.6.1.R1/share/doc INCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include PKGINCLUDEDIR | /rdsdbbin/postgres-9.6.1.R1/include INCLUDEDIR-SERVER | /rdsdbbin/postgres-9.6.1.R1/include/server LIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib PKGLIBDIR | /rdsdbbin/postgres-9.6.1.R1/lib LOCALEDIR | /rdsdbbin/postgres-9.6.1.R1/share/locale MANDIR | /rdsdbbin/postgres-9.6.1.R1/share/man SHAREDIR | /rdsdbbin/postgres-9.6.1.R1/share SYSCONFDIR | /rdsdbbin/postgres-9.6.1.R1/etc PGXS | /rdsdbbin/postgres-9.6.1.R1/lib/pgxs/src/makefiles/pgxs.mk CONFIGURE | '--prefix=/rdsdbbin/postgres-9.6.1.R1' '--with-openssl' '--with-perl' '--with-tcl' '--with-ossp-uuid' '--with-libxml' '--with-libraries=/rdsdbbin /postgres-9.6.1.R1/lib' '--with-includes=/rdsdbbin/postgres-9.6.1.R1/include' '--enable-debug' CC | gcc CPPFLAGS | -D_GNU_SOURCE -I/usr/include/libxml2 -I/rdsdbbin/postgres-9.6.1.R1/include CFLAGS | -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict- aliasing -fwrapv -fexcess-precision=standard -g -O2 CFLAGS_SL | -fpic LDFLAGS | -L../../src/common -L/rdsdbbin/postgres-9.6.1.R1/lib -Wl,--as-needed -Wl, -rpath,'/rdsdbbin/postgres-9.6.1.R1/lib',--enable-new-dtags LDFLAGS_EX | LDFLAGS_SL | LIBS | -lpgcommon -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lrt -lcrypt -ldl -lm VERSION | PostgreSQL 9.6.1 (23 rows)

orafce 拡張機能を使用する

orafce 拡張機能では、商用データベースの一般的な機能が提供されており、商用データベースの PostgreSQL への移行を簡単に行うことができます。この拡張機能は Amazon RDS for PostgreSQL バージョン 9.6.6 以降でサポートされています。orafce についての詳細は、「GitHub に関する orafce プロジェクト」を参照してください。

注記

Amazon RDS for PostgreSQL は、utl_file 拡張の一部である orafce パッケージをサポートしていません。これは、utl_file スキーマ関数が、基になるモストへのスーパーユーザーアクセスに必要なオペレーティングシステムテキストファイルで読み書き操作を実行するためです。

orafce 拡張機能を使用するには

  1. DB インスタンスの作成で使用したマスターユーザー名を使用して DB インスタンスに接続します。

    注記

    同じインスタンスの別のデータベースで orafce を有効にする場合は、/c dbname psql コマンドを使用して、接続初期化後にプライマリデータベースから変更することができます。

  2. CREATE EXTENSION ステートメントを使用して、orafce 拡張機能を有効にします。

    CREATE EXTENSION orafce;
  3. ALTER SCHEMA ステートメントを使用して、oracle スキーマの所有権を rds_superuser ロールに転送します。

    ALTER SCHEMA oracle OWNER TO rds_superuser;
    注記

    oracle スキーマの所有者のリストを表示する場合は、\dn psql コマンドを使用します。

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');

oracle_fdw 拡張機能を使用した外部データへのアクセス

PostgreSQL oracle_fdw 拡張機能を使用すると、Oracleデータベースへの簡単かつ効率的なアクセスを実現するための、外部データラッパーを提供できます。詳細な説明については、「oracle_fdw」ドキュメントを参照してください。

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

oracle_fdw 拡張機能の有効化

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

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

    CREATE EXTENSION oracle_fdw;

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

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)

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

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_mapping および pg_user_mappings のアクセス許可

次の表に示すロールの例により、ユーザーマッピングでのアクセス許可の方法を確認できます。rdssu1 および rdssu2 ユーザーには rds_superuser ロールがありますが、user1 には付与されていません。

注記

既存のロールを一覧表示するには、\du メタコマンドを使用します。

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

ユーザーは、rds_superuser ロールを使用して pg_user_mapping テーブルをクエリすることはできません。次の例では rdssu1 を使用しています。

test=> SET SESSION AUTHORIZATION rdssu1; SET test=> select * from pg_user_mapping; ERROR: permission denied for table pg_user_mapping

RDS for PostgreSQL では、すべてのユーザーは (rds_superuser ロールがある場合も含め)、pg_user_mappings テーブル内にある自分のユーザーマッピング (umoptions) のみを参照できます。次に例を示します。

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 となっています。

パスワード管理を制限する

データベースユーザーのパスワードの管理担当者を特定のロールに限定できます。これにより、クライアント側でのパスワード管理をより柔軟にコントロールできます。

静的パラメータ rds.restrict_password_commands でパスワード管理の制限を有効にし、rds_password というロールを使用します。パラメータ rds.restrict_password_commands を 1 に設定すると、rds_password ロールのメンバーユーザーのみが特定の SQL コマンドを実行できます。制限された SQL コマンドは、データベースユーザーのパスワードとパスワードの有効期限を変更するコマンドです。

制限されたパスワード管理を使用するには、DB インスタンスで Amazon RDS for PostgreSQL 10.6 以上を実行している必要があります。rds.restrict_password_commands パラメータは静的であるため、このパラメータを変更するにはデータベースの再起動が必要です。

制限されたパスワード管理がデータベースで有効になっている場合、制限された SQL コマンドを実行しようとすると、「エラー: パスワードを変更するには rds_password のメンバーであることが必要です」というエラーが表示されます。

制限されたパスワード管理を有効にした場合に制限される SQL コマンドの例は以下のとおりです。

postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword'; postgres=> CREATE ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword' VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole WITH PASSWORD 'mypassword'; postgres=> ALTER ROLE myrole VALID UNTIL '2020-01-01'; postgres=> ALTER ROLE myrole RENAME TO myrole2;

ALTER ROLE が含まれている RENAME TO コマンドのいくつかも制限される場合があります。これらのコマンドが制限されるのは、MD5 パスワードが含まれている PostgreSQL ロールの名前を変更すると、パスワードが消去されるためです。

rds_superuser ロールは、rds_password ロールのデフォルトメンバーであるため、これを変更することはできません。他のロールに対して rds_password ロールのメンバーシップを付与するには、SQL の GRANT コマンドを使用します。rds_password のメンバーシップは、パスワード管理専用の少数のロールにのみ付与することをお勧めします。これらのロールは、他のロールを変更するために、CREATEROLE 属性を必要とします。

パスワード要件 (クライアント側の有効期限や必要な複雑さなど) を確認してください。クライアント側の独自のユーティリティを使用してパスワード関連の変更を制限することをお勧めします。このユーティリティには、rds_password のメンバーであり、CREATEROLE 属性を持つロールが必要です。