pg_tle 1.3.0で実装されたパスワードチェックフックを利用してRDS for PostgreSQLで直近利用したパスワードへの変更制限を実装してみました

じゅんや

2024.05.17

初めに

先日RDS for PostgreSQLのpg_tle 1.4.0で追加されたの認証フックを試してみました。

上記記事でも記載させていただきましたが、RDS for PostgreSQLで利用可能なpg_tleのバージョンは1.1.1から1.4.0に一気に飛んでいます。

今回はその間に実装された機能の一つであるパスワードチェック時のフック処理を行ってみます(実装バージョンは1.3.0)。

やること

(こういった用途では使わない気がしますが)昔からよくあるパスワード変更の際に直近設定されていたパスワードを設定できないようにフック処理を組み込んでみます。

なお本記事ではわかりやすくするために直近利用されたパスワード情報を生のまま保存しますが、実際に利用する場合好ましくない場合がほとんどかと思いますのでハッシュ処理を加えたものを保存するなど適切な処理するようにしてください。

拡張機能の作成はpg_tleのリポジトリ内のドキュメントを参考にしました。

パラメータグループの設定

以下の設定を追加します。
pgtle.enable_password_checkは動的パラメータのため設定後の再起動は不要です。shared_preload_libraryの追加もある場合は静的パラメータのため再起動が必要となります。

パラメータ	値	備考
shared_preload_library	pg_tle	既存パラメータがある場合そこへの追加
pgtle.enable_password_check	on

拡張機能の追加クエリ

以下のクエリを流しパスワードチェック用の独自拡張機能を追加します。

CREATE EXTENSION pg_tle;
--change 'postgres' to your username
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA pgtle TO postgres;

SELECT pgtle.install_extension(
  'password_history_check',
  '1.0',
  'Notification login success',
$_pgtle_$
  CREATE SCHEMA password_history_check;

  -- 履歴保持用
  -- 絞り込み用に連番IDをPKeyにしているが、user_name + passwordでとってもいいかも(個別にチェックではなくユニーク制約でINSERT時のエラーで判別もできる)
  CREATE TABLE password_history_check.history (
    id SERIAL PRIMARY KEY,
    user_name  text,
    password   text,
    apply_date timestamp DEFAULT now()
  );
  CREATE INDEX history_user_name_index ON password_history_check.history(user_name);
  CREATE INDEX history_apply_date_index ON password_history_check.history(apply_date);

  -- 最大カウント保持
  CREATE TABLE password_history_check.max_try (
    cnt integer
  );
  INSERT INTO password_history_check.max_try(cnt) VALUES (5);

  CREATE FUNCTION password_history_check.hook_function(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean)
  RETURNS void AS $$
    DECLARE
      used boolean;
      context json;
      history_cnt integer;
      max_try integer := 5;
    BEGIN
      SELECT cnt FROM password_history_check.max_try LIMIT 1 INTO max_try;
      -- 直近で利用しているかチェック
      SELECT EXISTS(
        SELECT 1
        FROM(
            SELECT *
            FROM password_history_check.history
            WHERE user_name = username
            ORDER by apply_date desc
            LIMIT max_try
        ) AS latest_pw
        WHERE latest_pw.password =hook_function.password
      ) INTO used;
    
      --直近利用されいている場合は例外を吐いて終了する
      IF used = TRUE THEN
        RAISE EXCEPTION 'This password used in the last % times', max_try;
      END IF;
      -- 記録
      INSERT INTO password_history_check.history(user_name, password) VALUES (username, password);

      --カウントする以上のパスワード情報の保持は不要なので削除する
      SELECT COUNT(*)
      FROM password_history_check.history
      WHERE user_name = username
      INTO history_cnt;

      IF history_cnt > max_try THEN  
        DELETE FROM  password_history_check.history
        WHERE id in (
            SELECT id
            FROM password_history_check.history
            WHERE user_name = username
            ORDER BY apply_date DESC
            OFFSET max_try
        );
      END IF;
    END
  $$ LANGUAGE plpgsql;

  -- 最大保保持変更用
  CREATE FUNCTION password_history_check.change_max_try(num integer) RETURNS integer AS $$
    BEGIN
      TRUNCATE password_history_check.max_try;
      INSERT INTO password_history_check.max_try(cnt) VALUES (num);
    END
  $$ LANGUAGE plpgsql;

  --　上記の関数をパスワード変更時のフック処理として登録
  SELECT pgtle.register_feature('password_history_check.hook_function', 'passcheck');
  REVOKE ALL ON SCHEMA password_history_check FROM PUBLIC;
$_pgtle_$
);

拡張機能の有効化と後の動作確認のために作業ユーザが上記で作成されるpassword_history_checkスキーマにアクセスできるように権限を与えておきます。

CREATE EXTENSION password_history_check;
-- 権限過多かも
GRANT ALL ON SCHEMA password_history_check TO postgres;

確認

適当なユーザ(password_check)を作成し、パスワードを設定すると先ほど定義したhook_functionが稼働するため以下のようにその時点でのパスワードは記録されます。

postgres=> CREATE USER password_check;
CREATE ROLE
postgres=> ALTER USER password_check WITH PASSWORD 'password1';
ALTER ROLE
postgres=> SELECT * FROM password_history_check.history;
 id |   user_name    | password  |         apply_date
----+----------------+-----------+----------------------------
  1 | password_check | password1 | 2024-05-17 07:36:00.056783
(1 row)

この状態で同じパスワードに変更しようとするとすでに記録されている情報のためエラーとなります。

oostgres=> ALTER USER password_check WITH PASSWORD 'password1';
ERROR:  This password used in the last 5 times
CONTEXT:  PL/pgSQL function password_history_check.hook_function(text,text,pgtle.password_types,timestamp with time zone,boolean) line 24 at RAISE

適当に何度かパスワード変更したのちにエラーとなるパスワードに変更してみます。

postgres=> ALTER USER password_check WITH PASSWORD 'password2';
ALTER ROLE
postgres=> ALTER USER password_check WITH PASSWORD 'password3';
ALTER ROLE
postgres=> ALTER USER password_check WITH PASSWORD 'password4';
ALTER ROLE
postgres=> ALTER USER password_check WITH PASSWORD 'password5';
ALTER ROLE
postgres=> SELECT * FROM password_history_check.history;
 id |   user_name    | password  |         apply_date
----+----------------+-----------+----------------------------
  1 | password_check | password1 | 2024-05-17 07:36:00.056783
  2 | password_check | password2 | 2024-05-17 07:41:56.614471
  3 | password_check | password3 | 2024-05-17 07:41:58.502948
  4 | password_check | password4 | 2024-05-17 07:41:59.942234
  5 | password_check | password5 | 2024-05-17 07:42:01.465353
(5 rows)
postgres=> ALTER USER password_check WITH PASSWORD 'password1';
ERROR:  This password used in the last 5 times
CONTEXT:  PL/pgSQL function password_history_check.hook_function(text,text,pgtle.password_types,timestamp with time zone,boolean) line 24 at RAISE

この状態でエラーとなったpassword1でログインしようとすると認証エラーとなり、実行前のpassword5でログインを試行すると成功するためフック処理走りパスワード変更を拒否することに成功しています。

sh-4.2$ PGPASSWORD=password1  psql -U password_check -h database-2.xxxxx.ap-northeast-1.rds.amazonaws.com postgres
psql: error: connection to server at "database-2.xxxxx.ap-northeast-1.rds.amazonaws.com" (192.168.2.251), port 5432 failed: FATAL:  password authentication failed for user "password_check"
...
sh-4.2$ PGPASSWORD=password5  psql -U password_check -h database-2.xxxxx.ap-northeast-1.rds.amazonaws.com postgres
psql (14.8, server 15.6)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=>

設定したした数（今回は5)以上は履歴を削除するようにしているため6回目のパスワード変更を行うと最初のパスワードが削除されます。

postgres=> SELECT * FROM password_history_check.history;
 id |   user_name    | password  |         apply_date
----+----------------+-----------+----------------------------
  1 | password_check | password1 | 2024-05-17 07:36:00.056783
  2 | password_check | password2 | 2024-05-17 07:41:56.614471
  3 | password_check | password3 | 2024-05-17 07:41:58.502948
  4 | password_check | password4 | 2024-05-17 07:41:59.942234
  5 | password_check | password5 | 2024-05-17 07:42:01.465353
(5 rows)

postgres=> ALTER USER password_check WITH PASSWORD 'password6';
ALTER ROLE
postgres=> SELECT * FROM password_history_check.history;
 id |   user_name    | password  |         apply_date
----+----------------+-----------+----------------------------
  2 | password_check | password2 | 2024-05-17 07:41:56.614471
  3 | password_check | password3 | 2024-05-17 07:41:58.502948
  4 | password_check | password4 | 2024-05-17 07:41:59.942234
  5 | password_check | password5 | 2024-05-17 07:42:01.465353
  6 | password_check | password6 | 2024-05-17 07:53:45.816943
(5 rows)