# 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_trusted_language_extension-hooks-reference.md)」を参照してください。
## 例: PostgreSQL フックを使用する拡張機能の作成
このセクションで説明する例では、PostgreSQL フックを使用して特定の SQL のオペレーション中に入力されたパスワードをチェックし、データベースユーザーが自分のパスワードを `password_check.bad_passwords` テーブルに含まれるパスワードに設定できないようにします。この表には、一般的によく使用されているものの、簡単に破られてしまうパスワードの選択肢の上位 10 件が掲載されています。
この例を 、RDS for PostgreSQL DB インスタンスに設定するには、Trusted Language Extensions が既にインストールされている必要があります。詳細については、「[ RDS for PostgreSQL DB インスタンスに Trusted Language Extensions を設定する](PostgreSQL_trusted_language_extension-setting-up.md)」を参照してください。
**パスワードチェックフックの例を設定するには**
1. `psql` を使用して、に接続します。RDS for PostgreSQL DB インスタンス。
```
psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
--port=5432 --username=postgres --password --dbname=labdb
```
1. [パスワードチェックフックコードリスト](#PostgreSQL_trusted_language_extension-example-hook_code_listing) のコードをコピーし、データベースに貼り付けます。
```
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 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)
```
1. データベースに接続したままで、拡張機能を作成できるようになりました。
```
CREATE EXTENSION my_password_check_rules;
```
1. 次の `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)
```
1. 別のターミナルセッションを開いて、AWS CLI を操作します。パスワードチェックフックを有効にするには、カスタム DB パラメータグループを変更する必要があります。そのためには、以下の例に示すように [modify-db-parameter-group](https://docs.aws.amazon.com/cli/latest/reference/rds/modify-db-parameter-group.html) 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"
```
パラメータが正常に有効になると、次のような出力が表示されます。
```
(
"DBParameterGroupName": "docs-lab-parameters-for-tle"
}
```
パラメータグループ設定の変更が適用されるまでには数分かかる場合があります。ただし、このパラメータは動的であるため、設定を有効にするために を再起動する必要はありません。
1. `psql` セッションを開き、データベースに問い合わせて password\$1check フックが有効になっていることを確認します。
```
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\$1check フックの影響を受けることを示しています。
```
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 拡張機能をデータベースから削除するTLE 拡張機能をデータベースから削除する](PostgreSQL_trusted_language_extension-creating-TLE-extensions.dropping-TLEs.md)」を参照してください。
### パスワードチェックフックコードリスト
ここに示すサンプルコードは、`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 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_$
);
```