파라미터화된 보안 뷰 사용

이 문서에서는 애플리케이션 사용자 인증 정보와 같은 애플리케이션별로 이름이 지정된 파라미터를 기반으로 데이터 액세스를 제한할 수 있는 PostgreSQL용 Cloud SQL의 파라미터화된 보안 뷰를 사용하는 방법을 설명합니다. 파라미터화된 보안 뷰는 PostgreSQL 뷰의 기능을 확장하여 보안 및 액세스 제어를 개선합니다. 이러한 뷰는 실행되는 모든 쿼리에 여러 제한을 자동으로 적용하여 애플리케이션에서 신뢰할 수 없는 쿼리를 실행하는 위험도 완화합니다.

자세한 내용은 Cloud SQL의 파라미터화된 보안 뷰 및 파라미터화된 보안 뷰를 사용하여 애플리케이션 데이터 보호 및 액세스 제어를 참조하세요.

시작하기 전에

이 문서에서는 PostgreSQL용 Cloud SQL 인스턴스를 이미 만들었다고 가정합니다.

파라미터화된 보안 뷰를 사용하려면 먼저 다음을 수행해야 합니다.

  1. Cloud SQL 인스턴스에 cloudsql.enable_parameterized_views 데이터베이스 플래그를 사용 설정합니다. 이 플래그를 변경하려면 데이터베이스를 다시 시작해야 합니다. 자세한 내용은 데이터베이스 플래그 구성을 참조하세요.

  2. Cloud SQL Studio 또는 psql을 사용하여 파라미터화된 뷰가 생성된 데이터베이스에서 parameterized_views 확장 프로그램을 만듭니다.

    -- Requires cloudsql.enable_parameterized_views set to on
    CREATE EXTENSION parameterized_views;
    

    확장 프로그램이 생성되면 API가 해당 스키마의 네임스페이스에 포함되고 API가 기존 API와 충돌하지 않도록 시스템에서 parameterized_views라는 스키마도 생성됩니다.

파라미터화된 보안 뷰 만들기

파라미터화된 보안 뷰를 만들려면 다음 단계를 따르세요.

  1. security_barrier 옵션을 사용하여 CREATE VIEW DDL 명령어를 실행합니다.

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
    SELECT COLUMN_NAME, COLUMN_NAME_N
    FROM TABLE_NAME ALIAS
    WHERE CONDITION;

    다음을 바꿉니다.

    • VIEW_NAME: 파라미터화된 보안 뷰의 이름
    • TABLE_NAME: 파라미터화된 보안 뷰에서 사용할 테이블의 이름
    • ALIAS: 파라미터화된 보안 뷰에서 사용할 테이블 이름의 별칭
    • COLUMNNAME 또는 COLUMN_NAMEN: 파라미터화된 보안 뷰에서 사용할 테이블 열의 이름입니다.
    • CONDITION: 애플리케이션 사용자가 액세스하도록 허용된 행만 볼 수 있도록 제한하는 데 사용되는 조건문입니다. WHERE 절에서 $@PARAMETER_NAME 문법을 사용하여 필수 매개변수를 추가합니다. 일반적인 사용 사례는 WHERE COLUMN = $@PARAMETER_NAME을 사용하여 열의 값을 확인하는 것입니다.

      $@PARAMETER_NAME은 이름이 지정된 뷰 파라미터를 나타냅니다. 이 값은 execute_parameterized_query API를 사용할 때 제공됩니다. 이름이 지정된 뷰 파라미터 요구사항은 다음과 같습니다.

      • 이름이 지정된 뷰 파라미터는 영문자(a~z) 또는 밑줄(_)로 시작해야 합니다.
      • 첫 글자 이후에는 문자, 밑줄, 숫자(0~9)가 올 수 있습니다.
      • 이름이 지정된 뷰 파라미터는 대소문자를 구분합니다. 예를 들어 $@PARAMETER_NAME은 $@parameter_name과 다르게 해석됩니다.

      다음은 이름이 지정된 뷰 파라미터를 사용하는 파라미터화된 보안 뷰를 만드는 예시입니다.

      CREATE VIEW user_specific_items WITH (security_barrier) AS
      SELECT item_id, item_name, description, owner_id
      FROM items t
      WHERE owner_id = $@app_user_id;
      
  2. 뷰를 쿼리할 수 있는 데이터베이스 사용자에게 해당 뷰에 대한 SELECT 권한을 부여합니다.

  3. 뷰를 쿼리할 수 있는 모든 데이터베이스 사용자에게 뷰에 정의된 테이블이 포함된 스키마에 대한 USAGE 권한을 부여합니다.

자세한 내용은 파라미터화된 보안 뷰를 사용하여 애플리케이션 데이터 보호 및 액세스 제어를 참조하세요.

애플리케이션의 보안 구성

파라미터화된 보안 뷰를 사용하여 애플리케이션의 보안을 구성하려면 다음 단계를 따르세요.

  1. 관리 권한 사용자로 파라미터화된 보안 뷰를 만듭니다. 이 사용자는 애플리케이션의 관리 작업을 수행하는 Cloud SQL 데이터베이스 사용자입니다.
  2. 파라미터화된 보안 뷰에 대해 쿼리를 실행하기 위한 새 데이터베이스 역할을 만듭니다. 애플리케이션이 데이터베이스에 연결하고 로그인하는 데 사용하는 Cloud SQL 데이터베이스 역할입니다.

    1. 보안 뷰에 새 역할 권한을 부여합니다. 여기에는 일반적으로 뷰에 대한 SELECT 권한과 스키마에 대한 USAGE 권한이 포함됩니다.
    2. 이 역할이 액세스할 수 있는 객체를 애플리케이션에 필요한 최소한의 필수 공개 함수 및 객체 집합으로 제한합니다. 공개가 아닌 스키마 및 테이블에 대한 액세스 권한을 제공하지 마세요.

    뷰를 쿼리할 때 애플리케이션은 애플리케이션 사용자 ID에 연결된 필수 뷰 파라미터의 값을 제공합니다.

    자세한 내용은 사용자 만들기 및 관리를 참조하세요.

파라미터화된 보안 뷰 쿼리

파라미터화된 보안 뷰를 쿼리하려면 다음 옵션 중 하나를 사용하세요.

  • JSON 기반: 이 API를 사용하여 한 번에 쿼리를 실행하고 JSON 행을 반환합니다.
  • 커서 기반: 장기 실행 쿼리가 있거나 대규모 쿼리가 있고 결과를 일괄적으로 가져오려는 경우 이 API를 사용합니다.
  • PREPARE .. AS RESTRICTED 문: PREPARE .. AS RESTRICTED를 사용하여 쿼리 계획을 정의한 후 EXECUTE ... WITH VIEW PARAMETERS (...)를 실행하여 뷰의 특정 파라미터로 실행합니다.

JSON API

다음 구문이 있는 execute_parameterized_query() 함수를 실행합니다.

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

이 함수는 JSON 객체 테이블을 반환합니다. 테이블의 각 행은 원래 쿼리 결과 행의 row_to_json() 값과 동일합니다.

이 API를 사용하면 결과 집합의 크기가 크기(KB) 및 행 수로 제한됩니다. parameterized_views.json_results_max_size 및 parameterized_views.json_results_max_rows를 사용하여 이러한 한도를 구성할 수 있습니다.

CURSOR API

커서 이름으로 execute_parameterized_query() 함수를 실행합니다. 이 함수는 트랜잭션 범위 커서를 만들고 반환합니다.

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

준비된 문

준비된 문 메서드를 사용하면 쿼리 계획을 한 번 준비한 후 쿼리의 위치 파라미터와 뷰의 이름이 지정된 파라미터 모두에 대해 서로 다른 값으로 여러 번 실행할 수 있습니다.

준비된 문을 사용하려면 다음을 수행합니다.

  1. 준비된 문을 만듭니다.

    -- Prepare the statement
    PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
    AS RESTRICTED SQL_QUERY;

    다음을 바꿉니다.

    • PREPARED_STATEMENT_NAME: 준비된 문의 이름
    • QUERY_PARAM_TYPE_N: 쿼리 매개변수의 데이터 유형(예: TEXT)
    • SQL_QUERY: 지정된 값으로 준비된 문의 일부로 실행할 SQL 쿼리
  2. 준비된 문을 실행합니다.

    -- Execute the statement with query parameters and view parameters
    EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
    WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    다음을 바꿉니다.

    • PREPARED_STATEMENT_NAME: 준비된 문의 이름입니다.
    • QUERY_VALUE_N: SQL 쿼리에 파라미터로 제공할 값입니다.
    • PARAMETER_NAME_N: 파라미터화된 보안 뷰를 만들 때 정의한 이름이 지정된 뷰 파라미터의 이름입니다. 테이블의 열에서 이름이 지정된 뷰 파라미터를 만듭니다.
    • PARAMETER_VALUE_N: 파라미터화된 보안 뷰를 값과 연결된 행으로 제한하는 이름이 지정된 뷰 파라미터의 값입니다.
  3. 준비된 문을 정리합니다.

    -- Cleanup
    DEALLOCATE PREPARED_STATEMENT_NAME>;

다음 예시에서는 이름이 지정된 뷰 파라미터 $@app_user_id가 필요한 user_specific_items라는 파라미터화된 보안 뷰를 사용합니다.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

준비된 문을 만든 후에는 준비된 문을 여러 번 실행하고 쿼리와 이름이 지정된 뷰 파라미터 모두에 서로 다른 값을 할당할 수 있습니다.

예를 들어 첫 번째 쿼리 실행은 다음과 같습니다.

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

두 번째 쿼리 실행:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

WITH VIEW PARAMETERS 절은 파라미터화된 보안 뷰의 이름이 지정된 뷰 파라미터(123, 456)가 제공되는 위치이며, 준비된 쿼리의 위치 파라미터(%Laptop%, %Book%)와는 별개입니다.

마지막으로 준비된 문을 삭제합니다.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

쿼리 제한사항

다음은 파라미터화된 보안 뷰 쿼리에 설명된 옵션을 사용하여 실행하는 쿼리에 적용되는 일련의 제한된 작업 목록입니다.

  • 읽기 전용: 읽기 전용 SELECT 문만 허용됩니다. DML(INSERT, UPDATE, DELETE) 및 DDL(CREATE, ALTER)은 금지됩니다.
  • 중첩 없음: execute_parameterized_query에 대한 재귀 호출은 금지됩니다.
  • 확장 프로그램 제한: 새 백그라운드 세션을 시작하는 특정 확장 프로그램(예: dblink, pg_cron)은 허용되지 않습니다.
  • 쿼리 계획을 사용한 잠재적인 정보 유출을 방지하기 위해 EXPLAIN 문은 허용되지 않습니다.

모든 파라미터화된 뷰 나열

parameterized_views.all_parameterized_views 뷰를 사용하여 모든 파라미터화된 뷰(하나 이상의 이름 지정된 파라미터 $@...가 포함된 뷰)를 나열합니다.

SELECT * FROM parameterized_views.all_parameterized_views;

다음 단계