在您的 TLE 扩展中使用 PostgreSQL 挂钩 - Amazon Relational Database Service
示例：创建使用 PostgreSQL 挂钩的扩展

在您的 TLE 扩展中使用 PostgreSQL 挂钩

挂钩是 PostgreSQL 中可用的一种回调机制，它允许开发人员在常规数据库操作期间调用自定义函数或其他例程。TLE 开发套件支持 PostgreSQL 挂钩，因此您可以在运行时将自定义函数与 PostgreSQL 行为集成在一起。例如，您可以使用挂钩将身份验证过程与您自己的自定义代码关联起来，或者根据您的特定需求修改查询规划和执行流程。

您的 TLE 扩展可以使用挂钩。如果挂钩在作用域方面是全局的，则它适用于所有数据库。因此，如果您的 TLE 扩展使用全局挂钩，则需要在用户可以访问的所有数据库中创建 TLE 扩展。

当您使用 pg_tle 扩展构建自己的可信语言扩展时，您可以使用 SQL API 中的可用挂钩来构建扩展的功能。您应该向 pg_tle 注册任何挂钩。对于某些挂钩，您可能还需要设置各种配置参数。例如，可以将 passcode 检查挂钩设置为 on、off 或 require。有关可用 pg_tle 挂钩的特定要求的更多信息，请参阅 适用于 PostgreSQL 的可信语言扩展的挂钩参考。

示例：创建使用 PostgreSQL 挂钩的扩展

本节讨论的示例使用 PostgreSQL 挂钩检查在特定 SQL 操作期间提供的密码，并防止数据库用户将其密码设置为 password_check.bad_passwords 表中包含的任何密码。该表包含十大最常用但易于破解的密码选择。

要在 RDS for PostgreSQL 数据库实例中设置此示例，您必须已经安装了可信语言扩展。有关详细信息，请参阅在 RDS for PostgreSQL 数据库实例中设置可信语言扩展。

设置密码检查挂钩示例

  1. 使用 psql 连接到 RDS for PostgreSQL 数据库实例。

    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。您需要修改您的自定义数据库参数组才能开启密码检查挂钩。为此，请使用 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"

    成功开启此参数后，您将看到如下输出。

    (
    "DBParameterGroupName": "docs-lab-parameters-for-tle"
}

    对参数组设置进行的更改可能需要几分钟才能生效。但是，此参数是动态的，因此您无需重新启动 RDS for PostgreSQL 数据库实例，即可使该设置生效。

  6. 打开 psql 会话并查询数据库，以验证密码检查挂钩已开启。

    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 行为也受到密码检查挂钩的影响。

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_$
);