はじめに

こんにちは！
クラウドインテグレーション事業部の 大嵩です。

「特定のOSユーザーに、EC2のシェルは触らせたくないけど、PostgreSQLの操作だけは許可したい…」
「しかも、そのユーザーが実行したクエリ結果をファイルとして取り出せるようにしたい…」

そんな少し特殊な要件に応える方法として、今回は AWS CLI と SSMドキュメントを組み合わせ、Session Manager接続を特定のOSユーザー・特定のプロンプト（psql）に限定し、さらにファイル出力も可能にする方法をご紹介します！

検証したこと

SSMドキュメントを使い、Session Manager経由でEC2インスタンスに接続する際に、特定のOSユーザーに切り替わり、かつ直接 PostgreSQL (psql) プロンプトが起動する状態を実現します。さらに、その psql 上で実行したクエリ結果をファイルに出力できるかを検証しました。

前提

  • 特定のOSユーザーへの切り替え: Session Managerのデフォルト接続ユーザー（例: ec2-user）ではなく、別途作成したOSユーザー（例: otake）として操作する必要があります。
  • psqlプロンプトへの直接接続: Session Manager接続開始と同時に、OSのシェルプロンプトを経由せず、直接 psql プロンプトが表示される状態が必要です。
  • ファイル出力先ディレクトリへの移動: psql の \copy コマンドなどでファイルを出力する場合、通常カレントディレクトリに出力されます。出力先を管理しやすくするため、接続時に特定のディレクトリ（今回は /tmp）に移動しておく必要があります。

SSMドキュメントとは

AWS Systems Manager (SSM) がEC2インスタンスやオンプレミスサーバー等の管理対象に対して、コマンド実行、設定変更、自動化といったタスクを実行する際の手順を定義した設定ファイルです。JSON または YAML 形式で記述します。
https://docs.aws.amazon.com/ja_jp/systems-manager/latest/userguide/documents.html

まずはSSMドキュメントを作成

前提条件を満たすため、SSMドキュメントを作成します。少し工夫が必要ですが、以下のように記述します。

ポイントは su コマンドの -c オプションです。これにより、「指定ユーザーに切り替えて (su – otake)、そのユーザーで特定のコマンド (cd /tmp && psql …) を実行する」という一連の動作を1行で実現できます。

---
schemaVersion: '1.0'
description: "Document to switch user and execute another document"
sessionType: "InteractiveCommands"
properties:
  linux:
    commands: 'sudo su - otake -c "cd /tmp && psql -h ホスト名 -U postgres -d mydatabase"'
    runAsElevated: false

実際に接続してみる

作成したSSMドキュメントを使って、ターミナルからSession Managerで接続してみましょう。
通常の start-session コマンドに –document-name オプションを追加して、作成したドキュメント名を指定します。
接続後、OSのプロンプト（例: sh-5.2$）ではなく、直接 psql のプロンプト (mydatabase=>) が表示されていることが確認できます。
ここで \q を実行すると、psql が終了し、そのままSession Managerのセッションも終了します。OSのシェル操作はできません。

# <INSTANCE_ID>は接続対象EC2のインスタンスIDに置き換え
❯ aws ssm start-session --target <INSTANCE_ID> --document-name otake-test-psql-user-ssm

Starting session with SessionId: XXXX
Password for user postgres: # パスワードを入力
psql (15.9, server 16.3)
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> # ← ここに注目！OSプロンプトではなく、psqlプロンプトが表示されている
mydatabase=> \q


Exiting session with sessionId: XXXX

実際にSQLを実行してみる

次に、この psql プロンプトで実際にSQLを実行してみましょう。
結果がしっかりと返ってきています。

❯ aws ssm start-session --target <INSTANCE_ID> --document-name otake-test-psql-user-ssm

Starting session with SessionId: XXXX
Password for user postgres: 
psql (15.9, server 16.3)
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> 
mydatabase=> select * from users;                          
  name  | age | country 
--------+-----+---------
 Tanaka |  30 | Japan
 Alice  |  25 | USA
 Suzuki |  42 | Japan
 Bob    |  35 | Canada
 Kim    |  28 | Korea
(5 rows)

mydatabase=>

クエリ結果をファイル出力！

それでは、このクエリ結果をファイルに出力してみます。
今回は psql のメタコマンド \copy を使用します。

\copy (select * from user) TO 'example.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',');

もうお分かりかと思いますが、この \copy コマンドは、指定したクエリ (select * from users) の結果を指定したファイル (example.csv) に出力します。
WITH 以下は出力オプションで、ここではファイル形式はCSV (FORMAT CSV)、先頭行にヘッダー (HEADER) を付け、区切り文字はコンマ (DELIMITER ‘,’) と指定しています。

説明はここまでにして、実際に実行してみます。

❯ aws ssm start-session --target <INSTANCE_ID> --document-name otake-test-psql-user-ssm

Starting session with SessionId: <ANOTHER_SESSION_ID>
Password for user postgres: 
psql (15.9, server 16.3)
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

mydatabase=> 
mydatabase=> \copy (select * from users) TO 'example.csv' WITH (FORMAT CSV, HEADER, DELIMITER ',');
COPY 5
mydatabase=>

はい。ご覧の通り、COPY 5と表示され、何も起こりません。
では、どこに出力されたのかといいますと、そうです。SSMドキュメントで指定したカレントディレクトリになります。

# 指定したコマンド
sudo su - otake -c "cd /tmp && psql -h ホスト名 -U postgres -d mydatabase

それでは見てみましょう。
現在のセッションは psql に制限されているため、一度終了し、通常のSession Manager接続でインスタンスに入り直して確認します。

# 通常のSession Managerで接続
❯ aws ssm start-session --target <INSTANCE_ID>

Starting session with SessionId: <ANOTHER_SESSION_ID>
sh-5.2$ sudo su - otake # otake ユーザーに切り替え
Last login: Sun Apr 20 08:09:52 UTC 2025 on pts/1
[otake@ip-10-0-4-184 ~]$ cd /tmp # /tmp ディレクトリに移動

[otake@ip-10-0-4-184 tmp]$ ls -l example.csv # ファイルを確認
-rw-r--r--. 1 otake otake 89 Apr 20 08:09 example.csv

[otake@ip-10-0-4-184 tmp]$ cat example.csv # ファイルの中身を表示
name,age,country
Tanaka,30,Japan
Alice,25,USA
Suzuki,42,Japan
Bob,35,Canada
Kim,28,Korea
[otake@ip-10-0-4-184 tmp]$

無事に、クエリ結果が /tmp/example.csv に出力されていることを確認できました！

別の方法: psqlコマンドオプションとリダイレクト

\copy メタコマンドを使わずに、SSMドキュメント内の psql コマンド自体でファイル出力を完結させる方法もあります。psql の -c オプションで実行するSQLを指定し、シェルのリダイレクト (>) を使ってファイルに書き込みます。

sudo su - otake -c "cd /tmp && psql -h ホスト名 -U postgres -d mydatabase -c \"select * from users;\" -W -A --csv -F \",\" > export.csv
  • -c \"select * from users;\": 実行するSQLを指定。
  • -A: 位置揃えなしモード。
  • --csv: CSV形式で出力。
  • -F \",\": フィールド区切り文字をコンマに指定。
  • > export.csv: 標準出力を export.csv ファイルにリダイレクト。

この方法だと、Session Managerで接続して \q で抜けるだけで、/tmp/export.csv が作成されます（対話的な操作はできません）。
用途に応じて \copy と使い分けると良いと思います！

これらの方法を用いることで、冒頭に挙げた前提条件をすべてクリアした環境を実現できることがわかりました！

まとめ

  • SSMドキュメント内で psql コマンドを指定することで、Session Manager接続をPostgreSQLプロンプトのみに簡単に制限できる。
  • su コマンドと cd コマンドを組み合わせることで、ユーザー切り替えとカレントディレクトリ変更を接続時に一度に実行できる。
  • psql 上から \copy メタコマンドを使うか、psql のオプションとリダイレクトを使うことで、クエリの実行結果をファイルに出力できる。

この記事が、特定のデータベース操作のみを許可したい、といったAWS環境のセキュリティや運用効率化のヒントとなれば幸いです！