파라미터화된 보안 뷰 사용

MySQL   |  PostgreSQL   |  SQL Server

미리보기 - 파라미터화된 보안 뷰

이 기능에는 서비스별 약관의 일반 서비스 약관 섹션에 있는 'GA 이전 제품 또는 서비스 약관'이 적용됩니다. GA 이전 기능은 '있는 그대로' 제공되며 지원이 제한될 수 있습니다. 자세한 내용은 출시 단계 설명을 참조하세요.

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

자세한 내용은 파라미터화된 보안 뷰 개요 및 파라미터화된 보안 뷰 튜토리얼을 참조하세요.

시작하기 전에

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

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

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

2. psql

   -- 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 절은 준비된 쿼리의 위치 매개변수 (%Laptop%, %Book%)와 별도로 파라미터화된 보안 뷰의 이름이 지정된 뷰 파라미터 (123, 456)가 제공되는 위치입니다.

마지막으로 준비된 문을 정리합니다.

-- 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;

다음 단계

• 파라미터화된 보안 뷰 개요.
• 파라미터화된 보안 뷰를 사용하여 애플리케이션 데이터 보호 및 액세스 제어 (튜토리얼).