TLE 拡張機能で PostgreSQL フックを使用する - Amazon Aurora

TLE 拡張機能で PostgreSQL フックを使用する

フックは PostgreSQL で利用できるコールバックメカニズムで、開発者は通常のデータベースオペレーション中にカスタム関数やその他のルーチンを呼び出すことができます。TLE 開発キットは PostgreSQL フックをサポートしているため、実行時にカスタム関数を PostgreSQL の動作と統合できます。例えば、フックを使用して認証プロセスを独自のカスタムコードに関連付けたり、特定のニーズに合わせてクエリの計画と実行プロセスを変更したりできます。

TLE 拡張機能にはフックを使用できます。フックの適用範囲がグローバルな場合、すべてのデータベースに適用されます。そのため、TLE 拡張機能がグローバルフックを使用している場合は、ユーザーがアクセスできるすべてのデータベースに TLE 拡張機能を作成する必要があります。

pg_tle 拡張機能を使用して独自の Trusted Language Extensions を構築する場合、SQL API の利用可能なフックを使用して拡張機能の関数を構築できます。すべてのフックを pg_tle に登録する必要があります。一部のフックでは、さまざまな設定パラメータを設定する必要がある場合もあります。例えば、passcode チェックフックをオン、オフ、または必須に設定できます。使用可能な pg_tle フックの特定の要件の詳細については、「Trusted Language Extensions for PostgreSQL のフックリファレンス」を参照してください。

例: PostgreSQL フックを使用する拡張機能の作成

このセクションで説明する例では、PostgreSQL フックを使用して特定の SQL のオペレーション中に入力されたパスワードをチェックし、データベースユーザーが自分のパスワードを password_check.bad_passwords テーブルに含まれるパスワードに設定できないようにします。この表には、一般的によく使用されているものの、簡単に破られてしまうパスワードの選択肢の上位 10 件が掲載されています。

この例を Aurora PostgreSQL DB クラスター、に設定するには、Trusted Language Extensions が既にインストールされている必要があります。詳細については、「Aurora PostgreSQL DB クラスターに Trusted Language Extensions を設定する」を参照してください。

パスワードチェックフックの例を設定するには
  1. psql を使用して、Aurora PostgreSQL DB クラスターのライターインスタンスに接続します。

    psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=labdb
  2. パスワードチェックフックコードリスト のコードをコピーし、データベースに貼り付けます。

    SELECT pgtle.install_extension ( 'my_password_check_rules', '1.0', 'Do not let users use the 10 most commonly used passwords', $_pgtle_$ CREATE SCHEMA password_check; REVOKE ALL ON SCHEMA password_check FROM PUBLIC; GRANT USAGE ON SCHEMA password_check TO PUBLIC; CREATE TABLE password_check.bad_passwords (plaintext) AS VALUES ('123456'), ('password'), ('12345678'), ('qwerty'), ('123456789'), ('12345'), ('1234'), ('111111'), ('1234567'), ('dragon'); CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext); CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean) RETURNS void AS $$ DECLARE invalid bool := false; BEGIN IF password_type = 'PASSWORD_TYPE_MD5' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE ('md5' || md5(bp.plaintext || username)) = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common password dictionary'; END IF; ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE bp.plaintext = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common common password dictionary'; END IF; END IF; END $$ LANGUAGE plpgsql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC; SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck'); $_pgtle_$ );

    拡張機能がデータベースに読み込まれると、次のような出力が表示されます。

    install_extension ------------------- t (1 row)
  3. データベースに接続したままで、拡張機能を作成できるようになりました。

    CREATE EXTENSION my_password_check_rules;
  4. 次の psql メタコマンドを使用して、拡張機能がデータベースに作成されたことを確認できます。

    \dx List of installed extensions Name | Version | Schema | Description -------------------------+---------+------------+------------------------------------------------------------- my_password_check_rules | 1.0 | public | Prevent use of any of the top-ten most common bad passwords pg_tle | 1.0.1 | pgtle | Trusted-Language Extensions for PostgreSQL plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (3 rows)
  5. 別のターミナルセッションを開いて、AWS CLI を操作します。パスワードチェックフックを有効にするには、カスタム DB パラメータグループを変更する必要があります。そのためには、以下の例に示すように modify-db-parameter-group CLI コマンドを使用します。

    aws rds modify-db-parameter-group \ --region aws-region \ --db-parameter-group-name your-custom-parameter-group \ --parameters "ParameterName=pgtle.enable_password_check,ParameterValue=on,ApplyMethod=immediate"

    パラメータグループ設定の変更が適用されるまでには数分かかる場合があります。ただし、このパラメータは動的であるため、設定を有効にするために Aurora PostgreSQL DB クラスター のライターインスタンスを再起動する必要はありません。

  6. psql セッションを開き、データベースに問い合わせて password_check フックが有効になっていることを確認します。

    labdb=> SHOW pgtle.enable_password_check; pgtle.enable_password_check ----------------------------- on (1 row)

パスワードチェックフックがアクティブになりました。次の例に示すように、新しいロールを作成し、不適切なパスワードを使用してテストできます。

CREATE ROLE test_role PASSWORD 'password'; ERROR: Cannot use passwords from the common password dictionary CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 21 at RAISE SQL statement "SELECT password_check.passcheck_hook( $1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

出力は、読みやすい形式にしてあります。

次の例では、pgsql インタラクティブメタコマンドの \password 動作が password_check フックの影響を受けることを示しています。

postgres=> SET password_encryption TO 'md5'; SET postgres=> \password Enter new password for user "postgres":***** Enter it again:***** ERROR: Cannot use passwords from the common password dictionary CONTEXT: PL/pgSQL function password_check.passcheck_hook(text,text,pgtle.password_types,timestamp with time zone,boolean) line 12 at RAISE SQL statement "SELECT password_check.passcheck_hook($1::pg_catalog.text, $2::pg_catalog.text, $3::pgtle.password_types, $4::pg_catalog.timestamptz, $5::pg_catalog.bool)"

この TLE 拡張機能モジュールを削除して、必要に応じてソースファイルをアンインストールできます。詳細については、「TLE 拡張機能をデータベースから削除する」を参照してください。

パスワードチェックフックコードリスト

ここに示すサンプルコードは、my_password_check_rules TLE 拡張機能の仕様を定義しています。このコードをコピーしてデータベースに貼り付けると、my_password_check_rules 拡張機能のコードがデータベースにロードされ、password_check フックが拡張機能で使用できるように登録されます。

SELECT pgtle.install_extension ( 'my_password_check_rules', '1.0', 'Do not let users use the 10 most commonly used passwords', $_pgtle_$ CREATE SCHEMA password_check; REVOKE ALL ON SCHEMA password_check FROM PUBLIC; GRANT USAGE ON SCHEMA password_check TO PUBLIC; CREATE TABLE password_check.bad_passwords (plaintext) AS VALUES ('123456'), ('password'), ('12345678'), ('qwerty'), ('123456789'), ('12345'), ('1234'), ('111111'), ('1234567'), ('dragon'); CREATE UNIQUE INDEX ON password_check.bad_passwords (plaintext); CREATE FUNCTION password_check.passcheck_hook(username text, password text, password_type pgtle.password_types, valid_until timestamptz, valid_null boolean) RETURNS void AS $$ DECLARE invalid bool := false; BEGIN IF password_type = 'PASSWORD_TYPE_MD5' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE ('md5' || md5(bp.plaintext || username)) = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common password dictionary'; END IF; ELSIF password_type = 'PASSWORD_TYPE_PLAINTEXT' THEN SELECT EXISTS( SELECT 1 FROM password_check.bad_passwords bp WHERE bp.plaintext = password ) INTO invalid; IF invalid THEN RAISE EXCEPTION 'Cannot use passwords from the common common password dictionary'; END IF; END IF; END $$ LANGUAGE plpgsql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION password_check.passcheck_hook TO PUBLIC; SELECT pgtle.register_feature('password_check.passcheck_hook', 'passcheck'); $_pgtle_$ );