TLE 확장과 함께 PostgreSQL 후크 사용 - Amazon Aurora
예: PostgreSQL 후크를 사용하는 확장 생성

TLE 확장과 함께 PostgreSQL 후크 사용

후크는 PostgreSQL에서 사용할 수 있는 콜백 메커니즘으로, 개발자가 일반 데이터베이스 작업 중에 사용자 지정 함수 또는 기타 루틴을 호출할 수 있습니다. TLE 개발 키트는 PostgreSQL 후크를 지원하므로 런타임에 사용자 지정 함수를 PostgreSQL 동작과 통합할 수 있습니다. 예를 들어 후크를 사용하여 인증 프로세스를 사용자 지정 코드와 연결하거나 특정 요구 사항에 맞게 쿼리 계획 및 실행 프로세스를 수정할 수 있습니다.

TLE 확장은 후크를 사용할 수 있습니다. 후크의 범위가 전역적이면 모든 데이터베이스에 적용됩니다. 따라서 TLE 확장이 전역 후크를 사용하는 경우 사용자가 액세스할 수 있는 모든 데이터베이스에 TLE 확장을 생성해야 합니다.

pg_tle 확장을 사용하여 신뢰할 수 있는 언어 확장을 직접 구축하는 경우 SQL API에서 사용 가능한 후크를 사용하여 확장의 함수를 구축할 수 있습니다. 모든 후크는 pg_tle에 등록해야 합니다. 일부 후크의 경우 다양한 구성 파라미터를 설정해야 할 수도 있습니다. 예를 들어 passcode 확인 후크는 켜기, 해제 또는 필수로 설정할 수 있습니다. 사용 가능한 pg_tle 후크의 특정 요구 사항에 대한 자세한 내용은 PostgreSQL용 신뢰할 수 있는 언어 확장에 대한 후크 참조 섹션을 참조하세요.

예: PostgreSQL 후크를 사용하는 확장 생성

이 섹션에서 설명하는 예제에서는 PostgreSQL 후크를 사용하여 특정 SQL 작업 중에 제공된 암호를 확인하고 데이터베이스 사용자가 password_check.bad_passwords 테이블에 포함된 암호로 암호를 설정하지 못하도록 합니다. 이 테이블에는 가장 일반적으로 사용되지만 쉽게 깰 수 있는 상위 10개 암호가 나와 있습니다.

이 예제를 Aurora PostgreSQL DB 클러스터 에서 설정하려면 신뢰할 수 있는 언어 확장이 이미 설치되어 있어야 합니다. 세부 정보는 Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정을 참조하세요.

암호 확인 후크 예제를 설정하는 방법

  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)

이제 password-check 후크가 활성 상태입니다. 다음 예제와 같이 새 역할을 생성하고 잘못된 암호 중 하나를 사용하여 이를 테스트할 수 있습니다.

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