PostgreSQL용 신뢰할 수 있는 언어 확장 작업 - Amazon Aurora
용어신뢰할 수 있는 언어 확장을 사용하기 위한 요구 사항신뢰할 수 있는 언어 확장 설정신뢰할 수 있는 언어 확장 개요TLE 확장 생성 데이터베이스에서 TLE 확장 삭제신뢰할 수 있는 언어 확장 제거TLE 확장과 함께 PostgreSQL 후크 사용

PostgreSQL용 신뢰할 수 있는 언어 확장 작업

PostgreSQL용 신뢰할 수 있는 언어 확장은 PostgreSQL 확장을 구축하기 위한 오픈 소스 개발 키트입니다. 이를 통해 고성능 PostgreSQL 확장을 구축하고 Aurora PostgreSQL DB 클러스터에서 안전하게 실행할 수 있습니다. PostgreSQL용 신뢰할 수 있는 언어 확장(TLE)을 사용하면 PostgreSQL 기능 확장을 위해 문서화된 접근 방식을 따르는 PostgreSQL 확장을 생성할 수 있습니다. 자세한 내용은 PostgreSQL 설명서에서 Packaging Related Objects into an Extension을 참조하세요.

TLE의 주요 이점 중 하나는 PostgreSQL 인스턴스의 기반이 되는 파일 시스템에 대한 액세스를 제공하지 않는 환경에서 사용할 수 있다는 것입니다. 이전에는 새 확장을 설치하려면 파일 시스템에 액세스해야 했습니다. TLE는 이러한 제약을 제거합니다. TLE는 Aurora PostgreSQL DB 클러스터에서 실행되는 데이터베이스를 포함하여 모든 PostgreSQL 데이터베이스를 위한 새 확장을 생성할 수 있는 개발 환경을 제공합니다.

TLE는 TLE를 사용하여 만든 확장의 안전하지 않은 리소스에 대한 액세스를 방지하도록 설계되었습니다. 런타임 환경은 확장 결함이 미치는 영향을 단일 데이터베이스 연결로 제한합니다. 또한 TLE는 데이터베이스 관리자에게 확장을 설치할 수 있는 사용자를 세밀하게 제어할 수 있도록 하며, 확장을 실행하기 위한 권한 모델을 제공합니다.

TLE는 Aurora PostgreSQL 버전 14.5 이상 버전에서 지원됩니다.

신뢰할 수 있는 언어 확장 개발 환경 및 런타임은 pg_tle PostgreSQL 확장 버전 1.0.1로 패키징됩니다. JavaScript, Perl, Tcl, PL/pgSQL 및 SQL에서 확장 생성을 지원합니다. 다른 PostgreSQL 확장을 설치하는 것과 동일한 방식으로 Aurora PostgreSQL DB 클러스터에 pg_tle 확장을 설치합니다. pg_tle가 설정되면 개발자는 이를 사용하여 TLE 확장이라는 새 PostgreSQL 확장을 생성할 수 있습니다.

다음 주제에는 신뢰할 수 있는 언어 확장 설정 방법 및 자체 TLE 확장 생성 시작 방법에 대한 정보가 나와 있습니다.

주제

용어

신뢰할 수 있는 언어 확장을 더 잘 이해하려면 이 항목에서 사용되는 용어에 대한 다음 용어집을 참조하세요.

PostgreSQL용 신뢰할 수 있는 언어 확장

PostgreSQL용 신뢰할 수 있는 언어 확장은 pg_tle 확장으로 패키징된 오픈 소스 개발 키트의 공식 이름입니다. 모든 PostgreSQL 시스템에서 사용할 수 있습니다. 자세한 내용은 GitHub의 aws/pg_tle를 참조하세요.

신뢰할 수 있는 언어 확장

신뢰할 수 있는 언어 확장은 PostgreSQL용 신뢰할 수 있는 언어 확장의 약칭입니다. 이 설명서에서는 이 약칭과 약어(TLE)도 사용됩니다.

신뢰할 수 있는 언어

신뢰할 수 있는 언어는 특정 보안 속성을 가진 프로그래밍 또는 스크립팅 언어입니다. 예를 들어 신뢰할 수 있는 언어는 일반적으로 파일 시스템에 대한 액세스를 제한하며, 지정된 네트워킹 속성의 사용을 제한합니다. TLE 개발 키트는 신뢰할 수 있는 언어를 지원하도록 설계되었습니다. PostgreSQL은 신뢰할 수 있거나 신뢰할 수 없는 확장 생성에 사용되는 여러 언어를 지원합니다. 예제는 PostgreSQL 설명서의 Trusted and Untrusted PL/Perl을 참조하세요. 신뢰할 수 있는 언어 확장을 사용하여 생성한 확장은 기본적으로 신뢰할 수 있는 언어 메커니즘을 사용합니다.

TLE 확장

TLE 확장은 신뢰할 수 있는 언어 확장(TLE) 개발 키트를 사용하여 생성된 PostgreSQL 확장입니다.

PostgreSQL용 신뢰할 수 있는 언어 확장을 사용하기 위한 요구 사항

TLE 개발 키트를 설정하고 사용하기 위한 요구 사항은 다음과 같습니다.

  • Aurora PostgreSQL 버전 – 신뢰할 수 있는 언어 확장은 Aurora PostgreSQL 버전 14.5 이상 버전에서만 지원됩니다.

  • rds_superuser 권한 필요 - pg_tle 확장을 설정하고 구성하려면 데이터베이스 사용자 역할에 rds_superuser 역할의 권한이 있어야 합니다. 기본적으로 이 역할은 Aurora PostgreSQL DB 클러스터를 생성한 postgres 사용자에게 부여됩니다.

  • 사용자 지정 DB 파라미터 그룹 필요 - Aurora PostgreSQL DB 클러스터는 사용자 지정 DB 파라미터 그룹을 사용하여 구성해야 합니다. 사용자 지정 DB 파라미터 그룹은 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 사용합니다.

사용자 지정 DB 파라미터 그룹 생성 및 적용

다음 단계를 사용하여 사용자 지정 DB 파라미터 그룹을 생성하고 이를 사용하도록 Aurora PostgreSQL DB 클러스터를 구성합니다.

사용자 지정 DB 파라미터 그룹을 생성하고 Aurora PostgreSQL DB 클러스터에 사용하는 방법

  1. AWS Management Console에 로그인한 후 https://console.aws.amazon.com/rds/에서 Amazon RDS 콘솔을 엽니다.

  2. Amazon RDS 메뉴에서 Parameter groups(파라미터 그룹)를 선택합니다.

  3. Create parameter group(파라미터 그룹 생성)을 선택합니다.

  4. Parameter group details(파라미터 그룹 세부 정보) 페이지에서 다음 정보를 입력합니다.

    • Parameter group family(파라미터 그룹 패밀리)에서 aurora-postgresql14 를 선택합니다.

    • Type(유형)에서 DB Parameter Group을 선택합니다.

    • Group name(그룹 이름)에서 작업 컨텍스트에서 의미 있는 파라미터 그룹 이름을 지정합니다.

    • Description(설명)에 다른 팀원이 쉽게 찾을 수 있도록 유용한 설명을 입력합니다.

  5. Create(생성)를 선택합니다. 사용자 지정 DB 파라미터 그룹이 AWS 리전에 생성됩니다. 이제 다음 단계에 따라 파라미터 그룹을 사용하도록 Aurora PostgreSQL DB 클러스터를 수정할 수 있습니다.

  6. Amazon RDS 메뉴에서 Databases(데이터베이스)를 선택합니다.

  7. 나열된 항목 중에서 TLE와 함께 사용할 Aurora PostgreSQL DB 클러스터를 선택한 다음 Modify(수정)를 선택합니다.

  8. Modify DB Cluster settings(DB 클러스터 설정 수정) 페이지에서 데이터베이스 옵션을 찾은 다음 선택기를 사용하여 사용자 지정 DB 파라미터 그룹을 선택합니다.

  9. Continue(계속)를 선택하여 변경 내용을 저장합니다.

  10. Apply immediately(즉시 적용)를 선택하면 TLE를 사용하도록 Aurora PostgreSQL DB 클러스터를 계속 설정할 수 있습니다.

신뢰할 수 있는 언어 확장을 사용하도록 시스템을 계속 설정하려면 Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정 섹션을 참조하세요.

DB 클러스터 및 DB 파라미터 그룹 작업에 대한 자세한 내용은 DB 클러스터 파라미터 그룹 작업 섹션을 참조하세요.

기본 AWS 리전으로 AWS CLI를 구성하면 CLI 명령을 사용할 때 --region 인수를 지정하지 않아도 됩니다. 자세한 내용은 AWS Command Line Interface 사용 설명서의 구성 기초 섹션을 참조하세요.

사용자 지정 DB 파라미터 그룹을 생성하고 Aurora PostgreSQL DB 클러스터에 사용하는 방법

  1. create-db-parameter-group AWS CLI 명령을 사용하여 사용자 AWS 리전의 aurora-postgresql14 를 기반으로 사용자 지정 DB 파라미터 그룹을 만들 수 있습니다. 이 단계에서는 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 적용할 DB 파라미터 그룹을 생성합니다.

    Linux, macOS 또는 Unix 대상:

    aws rds create-db-parameter-group \
  --region aws-region \
  --db-parameter-group-name custom-params-for-pg-tle \
  --db-parameter-group-family aurora-postgresql14 \
  --description "My custom DB parameter group for Trusted Language Extensions"

    Windows의 경우:

    aws rds create-db-parameter-group ^
  --region aws-region ^
  --db-parameter-group-name custom-params-for-pg-tle ^
  --db-parameter-group-family aurora-postgresql14 ^
  --description "My custom DB parameter group for Trusted Language Extensions"

    사용자 지정 DB 파라미터 그룹은 AWS 리전에서 사용할 수 있으므로 파라미터 그룹을 사용하도록 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 수정할 수 있습니다.

  2. modify-db-instance AWS CLI 명령을 사용하여 사용자 지정 DB 파라미터 그룹을 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 적용합니다. 이 명령은 활성 인스턴스를 즉시 재부팅합니다.

    Linux, macOS 또는 Unix 대상:

    aws rds modify-db-instance \
  --region aws-region \
  --db-instance-identifier your-writer-instance-name \
  --db-parameter-group-name custom-params-for-pg-tle \
  --apply-immediately

    Windows의 경우:

    aws rds modify-db-instance ^
  --region aws-region ^
  --db-instance-identifier your-writer-instance-name ^
  --db-parameter-group-name custom-params-for-pg-tle ^
  --apply-immediately

신뢰할 수 있는 언어 확장을 사용하도록 시스템을 계속 설정하려면 Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정 섹션을 참조하세요.

자세한 내용은 DB 인스턴스의 DB 파라미터 그룹 작업 섹션을 참조하세요.

Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정

다음 단계에서는 사용자의 Aurora PostgreSQL DB 클러스터 가 사용자 지정 DB 클러스터 파라미터 그룹에 연결되어 있다고 가정합니다. 이러한 단계에 AWS Management Console 또는 AWS CLI를 사용할 수 있습니다.

Aurora PostgreSQL DB 클러스터 에 신뢰할 수 있는 언어 확장을 설정하는 경우 특정 데이터베이스 권한이 있는 데이터베이스 사용자가 사용할 수 있도록 특정 데이터베이스에 설치합니다.

신뢰할 수 있는 언어 확장 설정 방법

rds_superuser 그룹(역할)의 구성원인 계정을 사용하여 다음 단계를 수행합니다.

  1. AWS Management Console에 로그인한 후 https://console.aws.amazon.com/rds/에서 Amazon RDS 콘솔을 엽니다.

  2. 탐색 창에서 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 선택합니다.

  3. Aurora PostgreSQL DB 클러스터 라이터 인스턴스의 구성 탭을 엽니다. 인스턴스 세부 정보 중에서 파라미터 그룹 링크를 찾습니다.

  4. 링크를 선택하여 Aurora PostgreSQL DB 클러스터와 연결된 사용자 지정 파라미터를 엽니다.

  5. 파라미터 검색 필드에 shared_pre를 입력하여 shared_preload_libraries 파라미터를 찾습니다.

  6. 파라미터 편집을 선택하여 속성 값에 액세스합니다.

  7. 값 필드의 목록에 pg_tle를 추가합니다. 쉼표를 사용하여 값 목록에서 항목을 구분합니다.

    pg_tle가 추가된 shared_preload_libraries 파라미터 이미지.

  8. Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하여 shared_preload_libraries 파라미터 변경 사항이 적용되도록 합니다.

  9. 인스턴스를 사용할 수 있게 되면 pg_tle가 초기화되었는지 확인합니다. psql을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결하고 다음 명령을 실행합니다.

    SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pg_tle
(1 row)

  10. pg_tle 확장이 초기화되었으므로 이제 확장을 생성할 수 있습니다.

    CREATE EXTENSION pg_tle;

    다음 psql 메타 명령을 사용하여 확장이 설치되었는지 확인할 수 있습니다.

    labdb=> \dx
                         List of installed extensions
  Name   | Version |   Schema   |                Description
---------+---------+------------+--------------------------------------------
 pg_tle  | 1.0.1   | pgtle      | Trusted-Language Extensions for PostgreSQL
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

  11. PostgreSQL DB 인스턴스를 설정할 때 Aurora PostgreSQL DB 클러스터를 위해 만든 기본 사용자 이름에 pgtle_admin 역할을 부여합니다. 기본값을 수락했다면 postgres입니다.

    labdb=> GRANT pgtle_admin TO postgres;
GRANT ROLE

    다음 예제와 같이 psql 메타 명령을 사용하여 역할이 부여되었는지 확인할 수 있습니다. pgtle_admin 및postgres 역할만 출력에 표시됩니다. 자세한 내용은 PostgreSQL 역할 및 권한 이해. 섹션을 참조하세요.

    labdb=> \du
                          List of roles
    Role name    |           Attributes            |               Member of
-----------------+---------------------------------+-----------------------------------
pgtle_admin     | Cannot login                     | {}
postgres        | Create role, Create DB          +| {rds_superuser,pgtle_admin}
                | Password valid until infinity    |...

  12. \q 메타 명령을 사용하여psql 세션을 닫습니다.

    \q

TLE 확장 생성을 시작하려면 예: SQL을 사용하여 신뢰할 수 있는 언어 확장 생성 섹션을 참조하세요.

기본 AWS 리전으로 AWS CLI를 구성하면 CLI 명령을 사용할 때 --region 인수를 지정하지 않아도 됩니다. 자세한 내용은 AWS Command Line Interface 사용 설명서의 구성 기초 섹션을 참조하세요.

신뢰할 수 있는 언어 확장 설정 방법

  1. modify-db-parameter-group AWS CLI 명령을 사용하여 pg_tle를 shared_preload_libraries 파라미터에 추가합니다.

    aws rds modify-db-parameter-group \
   --db-parameter-group-name custom-param-group-name \
   --parameters "ParameterName=shared_preload_libraries,ParameterValue=pg_tle,ApplyMethod=pending-reboot" \
   --region aws-region

  2. reboot-db-instance AWS CLI 명령을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스를 재부팅하고 pg_tle 라이브러리를 초기화합니다.

    aws rds reboot-db-instance \
    --db-instance-identifier writer-instance \
    --region aws-region

  3. 인스턴스를 사용할 수 있게 되면 pg_tle가 초기화되었는지 확인할 수 있습니다. psql을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결하고 다음 명령을 실행합니다.

    SHOW shared_preload_libraries;
shared_preload_libraries 
--------------------------
rdsutils,pg_tle
(1 row)

    pg_tle이 초기화되었으므로 이제 확장을 생성할 수 있습니다.

    CREATE EXTENSION pg_tle;

  4. PostgreSQL DB 인스턴스를 설정할 때 Aurora PostgreSQL DB 클러스터를 위해 만든 기본 사용자 이름에 pgtle_admin 역할을 부여합니다. 기본값을 수락했다면 postgres입니다.

    GRANT pgtle_admin TO postgres;
GRANT ROLE

  5. 다음과 같이 psql 세션을 닫습니다.

    labdb=> \q

TLE 확장 생성을 시작하려면 예: SQL을 사용하여 신뢰할 수 있는 언어 확장 생성 섹션을 참조하세요.

PostgreSQL용 신뢰할 수 있는 언어 확장 개요

PostgreSQL용 신뢰할 수 있는 언어 확장은 다른 PostgreSQL 확장을 설정하는 것과 동일한 방식으로 Aurora PostgreSQL DB 클러스터에 설치하는 PostgreSQL 확장입니다. PgAdmin 클라이언트 도구에 있는 예제 데이터베이스의 다음 이미지에서 pg_tle 확장을 구성하는 일부 구성 요소를 볼 수 있습니다.

TLE 개발 키트를 구성하는 일부 구성 요소를 보여 주는 이미지.

다음 세부 정보를 볼 수 있습니다.

  1. PostgreSQL용 신뢰할 수 있는 언어 확장(TLE)은 pg_tle 확장으로 패키징된 개발 키트입니다. 따라서 pg_tle는 설치되는 데이터베이스의 사용 가능한 확장에 추가됩니다.

  2. TLE에는 자체 스키마 pgtle이 있습니다. 이 스키마에는 생성한 확장을 설치하고 관리하는 도우미 함수(3)가 포함되어 있습니다.

  3. TLE는 확장 설치, 등록, 관리를 위한 12개 이상의 도우미 함수를 제공합니다. 이러한 함수에 대한 자세한 내용은 PostgreSQL용 신뢰할 수 있는 언어 확장에 대한 함수 참조 섹션을 참조하세요.

pg_tle 확장의 기타 구성 요소에는 다음이 포함됩니다.

  • pgtle_admin 역할 - pg_tle 확장이 설치될 때 pgtle_admin 역할이 생성됩니다. 이 역할은 권한이 부여되므로 그에 따라 취급되어야 합니다. 데이터베이스 사용자에게 pgtle_admin 역할을 부여할 때는 최소 권한 원칙을 따르는 것이 좋습니다. 즉, 새 TLE 확장을 생성, 설치, 관리할 수 있는 데이터베이스 사용자에게만 pgtle_admin 역할을 부여합니다(예: postgres).

  • pgtle.feature_info 테이블 - pgtle.feature_info 테이블은 TLE, 후크, TLE와 후크가 사용하는 사용자 지정 저장 프로시저 및 함수에 대한 정보가 포함된 보호된 테이블입니다. pgtle_admin 권한이 있는 경우 다음과 같은 신뢰할 수 있는 언어 확장 함수를 사용하여 테이블에서 해당 정보를 추가하고 업데이트할 수 있습니다.

Aurora PostgreSQL용 TLE 확장 생성

pg_tle 확장이 설치된 Aurora PostgreSQL DB 클러스터 에서 TLE를 사용하여 생성한 확장을 설치할 수 있습니다. pg_tle 확장은 설치된 PostgreSQL 데이터베이스로 범위가 지정됩니다. TLE를 사용하여 생성한 확장은 동일한 데이터베이스로 범위가 지정됩니다.

다양한 pgtle 함수를 사용하여 TLE 확장을 구성하는 코드를 설치하세요. 다음과 같은 신뢰할 수 있는 언어 확장 함수는 모두 pgtle_admin 역할이 필요합니다.

예: SQL을 사용하여 신뢰할 수 있는 언어 확장 생성

다음 예제는 다양한 공식을 사용하여 거리를 계산하는 몇 가지 SQL 함수가 포함된 pg_distance라는 TLE 확장을 생성하는 방법을 보여 줍니다. 목록에서 맨해튼 거리를 계산하는 함수와 유클리드 거리를 계산하는 함수를 찾을 수 있습니다. 이러한 공식의 차이에 대한 자세한 내용은 Wikipedia의 Taxicab geometry와 Euclidean geometry를 참조하세요.

Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정에 설명된 대로 pg_tle 확장을 설정한 경우 자체 Aurora PostgreSQL DB 클러스터 에서 이 예제를 사용할 수 있습니다.

참고

이 절차를 수행하려면 pgtle_admin 역할의 권한이 있어야 합니다.

예제 TLE 확장을 생성하는 방법

다음 단계에서는 labdb라는 예제 데이터베이스를 사용합니다. 이 데이터베이스는 postgres 기본 사용자가 소유합니다. postgres 역할에는 pgtle_admin 역할의 권한도 있습니다.

  1. psql을 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결합니다. 

    psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
--port=5432 --username=postgres --password --dbname=labdb

  2. 다음 코드를 복사하고 psql 세션 콘솔에 붙여넣어 이름이 pg_distance인 TLE 확장을 생성합니다.

    SELECT pgtle.install_extension
(
 'pg_distance',
 '0.1',
  'Distance functions for two points',
$_pg_tle_$
    CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
    RETURNS float8
    AS $$
      SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
    $$ LANGUAGE SQL;

    CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
    RETURNS float8
    AS $$
      SELECT dist(x1, y1, x2, y2, 1);
    $$ LANGUAGE SQL;

    CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
    RETURNS float8
    AS $$
      SELECT dist(x1, y1, x2, y2, 2);
    $$ LANGUAGE SQL;
$_pg_tle_$
);

    출력은 다음과 같습니다.

    install_extension
---------------
 t
(1 row)

    pg_distance 확장을 구성하는 아티팩트가 이제 데이터베이스에 설치되었습니다. 이러한 아티팩트에는 제어 파일과 확장 코드가 포함되며, 이러한 항목은 CREATE EXTENSION 명령을 사용하여 확장을 생성하려면 있어야 하는 항목입니다. 즉, 데이터베이스 사용자가 확장의 함수를 사용할 수 있도록 하려면 확장을 생성해야 합니다.

  3. 확장을 생성하려면 다른 확장과 마찬가지로 CREATE EXTENSION 명령을 사용하세요. 다른 확장과 마찬가지로 데이터베이스 사용자는 데이터베이스에서 CREATE 권한이 있어야 합니다.

    CREATE EXTENSION pg_distance;

  4. pg_distance TLE 확장을 테스트하려면 TLE 확장을 사용하여 네 점 사이의 맨해튼 거리를 계산하면 됩니다.

    labdb=> SELECT manhattan_dist(1, 1, 5, 5);
8

    동일한 점 집합 간의 유클리드 거리를 계산하려면 다음을 사용하면 됩니다.

    labdb=> SELECT euclidean_dist(1, 1, 5, 5);
5.656854249492381

pg_distance 확장은 데이터베이스에 함수를 로드하여 데이터베이스에서 권한이 있는 모든 사용자가 사용할 수 있도록 합니다.

TLE 확장 수정

이 TLE 확장에 패키징된 함수의 쿼리 성능을 향상시키려면 다음 두 PostgreSQL 속성을 해당 사양에 추가합니다.

  • IMMUTABLE - IMMUTABLE 속성은 쿼리 최적화 프로그램이 최적화를 통해 쿼리 응답 시간을 개선할 수 있도록 합니다. 자세한 내용은 PostgreSQL 설명서에서 Function Volatility Categories를 참조하세요.

  • PARALLEL SAFE - PARALLEL SAFE 속성은 PostgreSQL이 병렬 모드에서 함수를 실행할 수 있도록 하는 또 다른 속성입니다. 자세한 내용은 PostgreSQL 설명서에서 CREATE FUNCTION을 참조하십시오.

다음 예제에서는 pgtle.install_update_path 함수를 사용하여 각 함수에 이러한 속성을 추가하여 pg_distance TLE 확장의 0.2 버전을 생성하는 방법을 확인할 수 있습니다. 이 함수에 대한 자세한 내용은 pgtle.install_update_path 단원을 참조하세요. 이 작업을 수행하려면 pgtle_admin 역할이 있어야 합니다.

기존 TLE 확장을 업데이트하고 기본 버전을 지정하는 방법

  1. psql 또는 pgAdmin 같은 다른 클라이언트 도구를 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스 에 연결합니다.

    psql --host=db-instance-123456789012.aws-region.rds.amazonaws.com
--port=5432 --username=postgres --password --dbname=labdb

  2. 다음 코드를 복사하고 psql 세션 콘솔에 붙여넣어 기존 TLE 확장을 수정합니다.

    SELECT pgtle.install_update_path
(
 'pg_distance',
 '0.1',
 '0.2',
$_pg_tle_$
    CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
    RETURNS float8
    AS $$
      SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
    $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;

    CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
    RETURNS float8
    AS $$
      SELECT dist(x1, y1, x2, y2, 1);
    $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;

    CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
    RETURNS float8
    AS $$
      SELECT dist(x1, y1, x2, y2, 2);
    $$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
$_pg_tle_$
);

    다음과 비슷한 응답이 나타납니다.

    install_update_path
---------------------
 t
(1 row)

    이 확장 버전을 기본 버전으로 지정하면 데이터베이스 사용자가 데이터베이스에서 확장을 생성 또는 업데이트할 때 버전을 지정하지 않아도 됩니다.

  3. TLE 확장의 수정된 버전(버전 0.2)을 기본 버전으로 지정하려면 다음 예제와 같이 pgtle.set_default_version 함수를 사용하세요.

    SELECT pgtle.set_default_version('pg_distance', '0.2');

    이 함수에 대한 자세한 내용은 pgtle.set_default_version 단원을 참조하세요.

  4. 코드가 준비되면 다음과 같이 ALTER EXTENSION ... UPDATE 명령을 사용하여 일반적인 방법으로 설치된 TLE 확장을 업데이트할 수 있습니다.

    ALTER EXTENSION pg_distance UPDATE;

데이터베이스에서 TLE 확장 삭제

다른 PostgreSQL 확장과 동일한 방식으로 DROP EXTENSION 명령을 사용하여 TLE 확장을 삭제할 수 있습니다. 확장을 삭제해도 확장을 구성하는 설치 파일은 제거되지 않으므로 사용자가 확장을 다시 생성할 수 있습니다. 확장 및 해당 설치 파일을 제거하려면 다음 2단계 프로세스를 수행하세요.

TLE 확장을 삭제하고 해당 설치 파일을 제거하는 방법

  1. psql 또는 다른 클라이언트 도구를 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결합니다.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=dbname

  2. 다른 PostgreSQL 확장과 같은 방법으로 확장을 삭제합니다.

    DROP EXTENSION your-TLE-extension

    예를 들어 예: SQL을 사용하여 신뢰할 수 있는 언어 확장 생성에 설명된 대로 pg_distance 확장을 생성한 경우 다음과 같이 확장을 삭제할 수 있습니다.

    DROP EXTENSION pg_distance;

    다음과 같이 확장이 삭제되었음을 확인하는 출력이 표시됩니다.

    DROP EXTENSION

    이 시점에서 확장은 더 이상 데이터베이스에서 활성 상태가 아닙니다. 그러나 설치 파일과 제어 파일은 여전히 데이터베이스에서 사용할 수 있으므로 데이터베이스 사용자는 원하는 경우 확장을 다시 생성할 수 있습니다.

    • 데이터베이스 사용자가 TLE 확장자를 생성할 수 있도록 확장 파일을 그대로 두려면 여기서 멈추면 됩니다.

    • 확장을 구성하는 모든 파일을 제거하려면 다음 단계를 계속합니다.

  3. 확장의 모든 설치 파일을 제거하려면 pgtle.uninstall_extension 함수를 사용하세요. 이 함수는 확장의 모든 코드와 제어 파일을 제거합니다.

    SELECT pgtle.uninstall_extension('your-tle-extension-name');

    예를 들어 모든 pg_distance 설치 파일을 제거하려면 다음 명령을 사용합니다.

    SELECT pgtle.uninstall_extension('pg_distance');
 uninstall_extension
---------------------
 t
(1 row)

PostgreSQL용 신뢰할 수 있는 언어 확장 제거

더 이상 TLE를 사용하여 TLE 확장을 직접 생성하지 않으려면 pg_tle 확장을 삭제하고 모든 아티팩트를 제거하면 됩니다. 이 작업에는 데이터베이스의 모든 TLE 확장 삭제 및 pgtle 스키마 삭제가 포함됩니다.

데이터베이스에서 pg_tle 확장 및 해당 스키마를 삭제하는 방법

  1. psql 또는 다른 클라이언트 도구를 사용하여 Aurora PostgreSQL DB 클러스터의 라이터 인스턴스에 연결합니다.

    psql --host=cluster-instance-1.111122223333.aws-region.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=dbname

  2. 데이터베이스에서 pg_tle 확장을 삭제합니다. 데이터베이스에서 자체 TLE 확장이 아직 실행 중인 경우 해당 확장도 삭제해야 합니다. 이를 위해 다음과 같이 CASCADE 키워드를 사용할 수 있습니다.

    DROP EXTENSION pg_tle CASCADE;

    pg_tle 확장이 데이터베이스에서 아직 활성 상태가 아닌 경우 CASCADE 키워드를 사용할 필요가 없습니다.

  3. pgtle 스키마를 삭제합니다. 이 작업을 수행하면 데이터베이스에서 모든 관리 함수가 제거됩니다.

    DROP SCHEMA pgtle CASCADE;

    이 명령은 프로세스가 완료되면 다음을 반환합니다.

    DROP SCHEMA

    pg_tle 확장, 해당 스키마와 함수, 모든 아티팩트가 제거됩니다. TLE를 사용하여 새 확장을 생성하려면 설정 프로세스를 다시 진행하세요. 자세한 내용은 Aurora PostgreSQL DB 클러스터에서 신뢰할 수 있는 언어 확장 설정 섹션을 참조하세요.

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