파라미터화된 보안 뷰를 사용하여 애플리케이션 데이터 보호 및 액세스 제어

이 튜토리얼에서는 PostgreSQL용 Cloud SQL에서 파라미터화된 보안 뷰를 사용하여 Cloud SQL Studio 또는 psql을 사용하여 파라미터화된 뷰에 대한 사용자 액세스를 제한하는 방법을 설명합니다.

목표

  • 이름이 지정된 뷰 파라미터를 사용하여 파라미터화된 보안 뷰 만들기
  • 애플리케이션이 데이터베이스에 연결하고 파라미터화된 보안 뷰에 액세스할 때 사용할 데이터베이스 역할 만들기
  • 새 역할에 보안 뷰에 대한 권한을 부여하고 기본 테이블에 대한 액세스 권한 취소하기
  • 새 역할을 사용해 연결한 후 제한된 테이블에 액세스할 수 없는지 확인하기
  • execute_parameterized_query 함수를 사용하여 파라미터화된 보안 뷰에서 쿼리 실행하기

개발 환경 준비

  1. Cloud SQL 인스턴스에 cloudsql.enable_parameterized_views 데이터베이스 플래그를 사용 설정합니다. 이 플래그를 변경하려면 데이터베이스를 다시 시작해야 합니다.

  2. postgres 사용자로 데이터베이스에 연결합니다.

    psql -U postgres

  3. 데이터베이스에 parameterized_views 확장 프로그램을 만듭니다.

    CREATE EXTENSION parameterized_views;

  4. 쿼리를 실행하기 위한 새 데이터베이스 역할을 만듭니다.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. 애플리케이션 데이터의 스키마와 테이블을 만듭니다.

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

파라미터화된 보안 뷰 만들기 및 액세스 권한 설정

  1. 파라미터화된 보안 뷰를 만듭니다.

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. 애플리케이션 역할에 뷰 및 스키마에 대한 액세스 권한을 부여합니다.

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

  3. 기본 테이블에 대한 직접 액세스 권한을 취소합니다.

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

데이터 보안 확인

  1. psv_user로 연결합니다.

    psql -U psv_user -d postgres

  2. 기본 테이블에 액세스할 수 없는지 확인합니다.

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table items

  3. execute_parameterized_query 함수를 사용하여 파라미터화된 보안 뷰에 액세스합니다.

    SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    결과에는 owner_id가 123인 항목만 포함되어야 합니다.

다음 단계