【Postgresql】特定ユーザのパスワードが設定されているかどうかをスクリプトで判定する

Postgres運用サーバでitamaeを走らせるたびにALTER USER postgres PASSWORD ...が実行されるのはなんだかなぁ、と思ったので初回のみ実行させる方法のメモ。

psqlコマンドで確認する

• STEP1. SELECT * FROM pg_shadow
  • pg_shadowテーブルのpasswdを見ればユーザごとのパスワード設定状況が分かる

terminal

[root@localhost /]# sudo -u postgres psql -c "SELECT * FROM pg_shadow;"
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+-------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            |                                     |          | 
 app01    |    16384 | f           | f        | f       | f            | md50b6effc1247fae0b2faeb89214119eee |          | 
 app02    |    16385 | f           | f        | f       | f            | md5d9f3ba661a8ff1b0bf285edc59d81bd0 |          | 
(3 行)

• STEP2. SELECT * FROM pg_shadow WHERE passwd IS NULL
  • WHERE句にpasswd IS NULLを加えれば、パスワードが設定されていないユーザのみを抽出できる

terminal

[root@localhost /]# sudo -u postgres psql -c "SELECT * FROM pg_shadow WHERE passwd IS NULL;"
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig 
----------+----------+-------------+----------+---------+--------------+--------+----------+-----------
 postgres |       10 | t           | t        | t       | t            |        |          | 
(1 行)

スクリプトに任せてみる

• STEP3. sudo -u postgres psql -tc "SELECT usename FROM pg_shadow WHERE usename = '<ユーザ名>' AND passwd IS NULL" | grep <ユーザ名>
  • grepコマンド用いることで、終了ステータスによるパスワード設定の有無を判断できる
    • exit code 0ならパスワード未設定、exit code 1ならパスワード設定済
  • psqlに-tオプションを指定し、結果行のみを出力させるのがポイント
  • 下記コマンドでは終了ステータス可視化のため、行末に; echo $?を付与して終了コードを出力しています

terminal

[root@localhost /]# sudo -u postgres psql -tc "SELECT usename FROM pg_shadow WHERE usename = 'postgres' AND passwd IS NULL;" | grep postgres ; echo $?
 postgres
0

応用例: 【itamae】postgresユーザのパスワード設定を初回のみ実行させる

• パスワードはyamlファイルで管理しており、#{node[:postgresql][:password]のように取り出す想定
  • gitで管理する場合はitamae-secretsで暗号化したほうが安全です👍
• 終了ステータスが0の場合(=パスワードが設定されていない初回実行時)のみ、ALTER USER postgres PASSWORD ...を実行する

itamae-script.rb

execute "sudo -u postgres psql -c \"ALTER USER postgres PASSWORD \'#{node[:postgresql][:password]}\';\"" do
  only_if "sudo -u postgres psql -tc \"SELECT usename FROM pg_shadow WHERE usename = \'postgres\' AND passwd IS NULL;\" | grep postgres"
end