Aurora PostgreSQL で pg_tle を使って独自パスワードポリシーを実装する

こんにちは、近藤（りょう）です！

今回は、Aurora PostgreSQL で pg_tle（Trusted Language Extensions）を利用した独自のパスワードポリシーの実装と動作を検証しました。

pg_tle を利用することで PL/pgSQL を用いた独自のパスワードポリシーを実装できます。本記事では、pg_tle を利用したパスワードポリシーの実装方法と実装時のポイントについてご紹介します。

pg_tleとは
- サポートされるバージョン
- リポジトリ
手順
注意事項
まとめ

pg_tleとは

pg_tle（Trusted Language Extensions）は、Amazon RDS for PostgreSQL および Aurora PostgreSQLで利用できるAWS提供の拡張機能です。

ネイティブな C 言語の共有ライブラリを作成することなく、PL/pgSQL などの信頼された言語を利用して独自の拡張機能を実装できます。

今回利用する passcheck フックをはじめ、PostgreSQLの各種フックを利用した機能を実装できる点が特徴です。

Amazon RDS for PostgreSQLおよびAurora PostgreSQLでは、デフォルトではパスワードポリシーを適用する機能がありません。pg_tle を利用して、対象データベースのDBユーザーに対して「14文字以上」「英大文字・英小文字・数字を含む」といった独自のパスワードポリシーを実装する方法を検証しました。

repost.aws

サポートされるバージョン

RDS for PostgreSQL バージョン

Trusted Language Extensions for PostgreSQL を使用した操作 - Amazon Relational Database Service

Aurora PostgreSQL バージョン

Trusted Language Extensions for PostgreSQL を使用するための要件 - Amazon Aurora

リポジトリ

github.com

手順

本記事では、以下のAWS公式ドキュメントを参考にしています。

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

パラメータグループで pg_tle, pgtle.enable_password_check を有効にします。

shared_preload_libraries = 'pg_tle' 
pgtle.enable_password_check = 'on'

2. 拡張機能の有効化

DBインスタンスへ接続し、pg_tle 拡張機能を有効化します。

CREATE EXTENSION pg_tle;

pgtle.install_extension()を実行するため、プライマリユーザーへ pgtle_admin ロールを付与します。（デフォルト: postgres）

GRANT pgtle_admin TO postgres;

■ 補足

pg_tle をセットアップおよび設定するには、rds_superuser ロールが必要です。

Trusted Language Extensions for PostgreSQL を使用するための要件 - Amazon Relational Database Service

3. パスワードチェック関数の定義

以下の SQL では、14文字以上かつ英大文字・英小文字・数字を含むことをチェックする passcheck フックを定義します。

SELECT pgtle.install_extension(
  -- 拡張機能名
  'password_check',

  -- バージョン
  '1.0',

  -- 拡張機能の説明
  'Password policy: 14+ chars, uppercase, lowercase, digit required',

$_pgtle_$

  -- パスワードチェック用スキーマを作成
  CREATE SCHEMA password_check;

  -- PUBLIC にスキーマ作成権限を付与しない
  REVOKE ALL ON SCHEMA password_check FROM PUBLIC;

  -- passcheckフックから呼び出せるよう利用権限のみ付与
  GRANT USAGE ON SCHEMA password_check TO PUBLIC;

  -- パスワードチェック関数を作成
  CREATE FUNCTION password_check.passcheck_hook(
    username text,
    password text,
    password_type pgtle.password_types,
    valid_until timestamptz,
    valid_null boolean
  )
  RETURNS void AS $$
  BEGIN

    -- パスワードは14文字以上
    IF length(password) < 14 THEN
      RAISE EXCEPTION 'password must be at least 14 characters';
    END IF;

    -- 英大文字を含む
    IF password !~ '[A-Z]' THEN
      RAISE EXCEPTION 'password must contain an uppercase letter';
    END IF;

    -- 英小文字を含む
    IF password !~ '[a-z]' THEN
      RAISE EXCEPTION 'password must contain a lowercase letter';
    END IF;

    -- 数字を含む
    IF password !~ '[0-9]' THEN
      RAISE EXCEPTION 'password must contain a digit';
    END IF;

  END
  $$ LANGUAGE plpgsql SECURITY DEFINER;

  -- passcheckフックから実行できるよう権限付与
  GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC;

  -- passcheckフックとして登録
  SELECT pgtle.register_feature(
    'password_check.passcheck_hook',
    'passcheck'
  );

$_pgtle_$
);

■ 補足

postgres（マスターユーザー）で普通にログインして、pgtle_admin ロールの権限がなく、pgtle.install_extension(...) を呼ぶと権限エラーになります。

xxxxx=> SELECT pgtle.install_extension(...);
ERROR:  permission denied for function install_extension

4. password_check拡張機能を有効化する

install_extension()は拡張機能の定義を登録するだけです。

実際にpassword_checkを利用するため、CREATE EXTENSIONで有効化します。

CREATE EXTENSION password_check;

登録されたことを確認します。

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'password_check';

 extname        | extversion
----------------+-----------
 password_check | 1.0

5. 動作確認

-- （失敗）14文字未満
xxxxx=> CREATE ROLE test_too_short WITH LOGIN PASSWORD 'Short1A';
ERROR: password must be at least 14 characters

-- （失敗）13文字
xxxxx=> CREATE ROLE test_boundary_13 WITH LOGIN PASSWORD 'StrongPw0rd12';
ERROR: password must be at least 14 characters

-- （失敗）英大文字なし
xxxxx=> CREATE ROLE test_no_upper WITH LOGIN PASSWORD 'longpassword12';
ERROR: password must contain an uppercase letter

-- （失敗）英小文字なし
xxxxx=> CREATE ROLE test_no_lower WITH LOGIN PASSWORD 'LONGPASSWORD12';
ERROR: password must contain a lowercase letter

-- （失敗）数字なし
xxxxx=> CREATE ROLE test_no_digit WITH LOGIN PASSWORD 'LongPasswordXY';
ERROR: password must contain a digit

-- （成功）条件を満たすパスワード
xxxxx=> CREATE ROLE test_strong WITH LOGIN PASSWORD 'StrongPassw0rd123';
CREATE ROLE

期待どおり、各条件を満たさないパスワードではエラーとなり、すべての条件を満たすパスワードのみロールを作成できることを確認できました。

注意事項

passcheck フックは、現在接続しているデータベースに登録された関数を実行します。

そのため、この機能を利用したいすべてのデータベースで、pg_tle の拡張機能を有効化し、passcheck フックとして利用するパスワードチェック関数を登録する必要があります。

pg_tle のドキュメントにも次のように記載されています。

By default, the passcheck feature executes functions that are registered in the current database. You should ensure that you install the pg_tle extension via CREATE EXTENSION and register passcheck functions in all of your databases for which you wish to enable this feature.

github.com