ノンインタラクティヴにPostgreSQLの認証情報を渡す

2015.08.11

はじめに

バッチ処理などでシェルスクリプトからSQLを実行したい時があります。 そこで少し面倒なのがパスワードの管理です。

日常オペレーションでコマンドラインツールからDBに接続する場合は、インタラクティヴにパスワードを入力すれば済みますが、バッチ用シェルスクリプトでは、ユーザーの手を介さず(=ノンインタラクティヴ)に認証を突破しなければいけません。

今回はPostgreSQL(をベースにしたRedshift)を対象に、認証情報を渡す方法についてまとめてみたいと思います。

ノンインタラクティブに認証情報を渡す

ノンインタラクティブに認証情報を渡す方法として

  • .pgpassファイル
  • 環境変数(非推奨)

を順に見ていきましょう。

認証情報を.pgpassファイルで渡す

まずは$HOME/.pgpassに接続・認証情報を記載します。 .pgpassファイルでは各項目を:で区切り、YOUR_HOST:YOUR_PORT:YOUR_DB_NAME:USER:PASSWORDと並べます。

$ vi ~/.pgpass
$ chmod 600 ~/.pgpass
$ cat ~/.pgpass
YOUR_HOST:YOUR_PORT:YOUR_DB_NAME:USER:PASSWORD

疎通確認的なSQLを投げてみましょう。

$ psql -h YOUR_HOST -p YOUR_PORT -U USR -d YOUR_TABLE_NAME -c 'select sysdate'
         timestamp
----------------------------
 2015-08-11 05:46:11.057386
(1 row)

パスワード入力を求められることなく、SQLを実行できました。

.pgpassファイルのパーミッション

.pgpassファイルはプロセスの実行ユーザだけが参照できるようにパーミッション(600)を設定します。

仮に644(rw-r--r--)のようなゆるいパーミッションの場合、以下のような警告メッセージが表示され、.pgpassファイルは利用されません。

$ psql -h YOUR_HOST
WARNING: password file "/home/jsmith/.pgpass" has group or world access; permissions should be u=rw (0600) or less
...

.pgpassファイルのパスをカスタマイズ

.pgpassのパスは環境変数PGPASSFILEでカスタマイズできます。 RedshiftのCOPYコマンドで実験してみましょう。

そのものズバリのBashスクリプト(redshift.sh )がAWS サイトの Articles & Tutorials として公開されています。

Scheduled Copy of Data from Amazon S3 to Amazon Redshift Using AWS Data Pipeline : Articles & Tutorials : Amazon Web Services

こちらの中身を確認してみましょう。

#!/bin/bash
DBHOST=YOUR_HOST
DBPORT=YOUR_PORT
DBNAME=YOUR_DB
DBTABLE=YOUR_TABLE_NAME
DBUSER=YOUR_DB_USER
DBPASS=YOUR_PASSWORD
S3PATH=s3://YOUR_CF_LOGS_PREFIX # {bucket-name}/{optional-prefix}{distribution-ID}
YESTERDAY=`date -d "yesterday" +"%Y-%m-%d"`
CF_LOG_PREFIX=$S3PATH.$YESTERDAY
AWS_ACCESS_KEY_ID=XXXXXXX
AWS_SECRET_ACCESS_KEY=XXXXXXXXXXXXXXX

# Secure temp files
export PGPASSFILE=`mktemp /tmp/pass.XXXXXX`
cmds=`mktemp /tmp/cmds.XXXXXX`
logs=`mktemp /tmp/logs.XXXXXX`

cat >$PGPASSFILE << EOF
$DBHOST:$DBPORT:$DBNAME:$DBUSER:$DBPASS
EOF

cat > $cmds << EOF
copy $DBTABLE from '$CF_LOG_PREFIX' CREDENTIALS 'aws_access_key_id=$AWS_ACCESS_KEY_ID;aws_secret_access_key=$AWS_SECRET_ACCESS_KEY' delimiter '\t' MAXERROR 200 IGNOREHEADER 2 gzip;
EOF

psql -d $DBNAME -h $DBHOST -p $DBPORT -U $DBUSER -f $cmds >$logs 2>&1

このスクリプトでは

  • PGPASSFILE(パスワードファイルのパス)
  • cmds(COPYコマンド文)
  • logs(実行ログ)

の3ファイルをmktempコマンドでランダムなファイル名で生成し、最後の行のpsqlコマンドでSQLを投げています。

実際にこのシェルスクリプトを実行すると、logsファイルには

psql:/tmp/cmds.4iIrc3:1: INFO:  Load into table 'customer' completed, 3000000 record(s) loaded successfully.
COPY

というようにログ出力されます。

認証情報を環境変数で渡す(非推奨)

PostgreSQLでは接続時の情報を環境変数で渡せます。

具体的には

  • PGHOST
  • PGPORT
  • PGDATABASE
  • PGUSER
  • PGPASSWORD

などで、各変数が定義している内容は変数名から自明かと思います。

この環境変数を使ってRedshiftにアクセスしてみましょう。

$ PGHOST=YOUR_HOST PGPORT=YOUR_PORT PGDATABASE=YOUR_TABLE_NAME PGUSER=USER PGPASSWORD=PASSWORD psql -c 'select sysdate'
         timestamp
----------------------------
 2015-08-11 06:05:00.411914
(1 row)

パスワード入力を求められることなく、SQLを実行できました。

この方式はお手軽ではあるのですが、プロセスの環境変数は覗き見しやすいため、セキュリティの観点からマニュアルでは"Use of this environment variable is not recommended for security reasons"というように非推奨となっています。

MySQLにもMYSQL_PWDという同様の環境変数が定義されています。 マニュアルでの扱いを確認したところ "This method of specifying your MySQL password must be considered extremely insecure and should not be used." というようにもっと強い口調で否定されていました。

特別な理由がない限り、環境変数でパスワードを渡すことは避けましょう。

参考サイト