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

문서 버전을 선택합니다.

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

파라미터화된 보안 뷰의 기능을 보여주기 위해 예시가 포함되어 있습니다. 이 예시는 데모용으로만 제공됩니다.

목표

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

데이터베이스 설치 및 연결

  1. Linux에 AlloyDB Omni 설치

개발 환경 준비

파라미터화된 보안 뷰에서 쿼리를 실행하기 위해서는 먼저 파라미터화된 뷰, 데이터베이스 및 데이터베이스 역할, parameterized_view 확장 프로그램, 애플리케이션 스키마를 설정해야 합니다.

파라미터화된 보안 뷰를 사용하기 전에 Linux 환경에서 다음을 한 번 수행해야 합니다. 각 설정은 ALTER SYSTEM을 사용하거나 postgresql.conf를 직접 수정하여 적용할 수 있습니다.

  1. shared_preload_libraries에 'parameterized_views'를 추가합니다.
  2. parameterized_views.enabled=on을 설정하여 기능을 사용 설정합니다.
  3. postgres를 다시 시작하여 변경사항을 적용합니다.

데이터베이스 설정

  • 애플리케이션 데이터 및 파라미터화된 뷰를 위해 database라는 데이터베이스를 만듭니다.

데이터베이스 역할, 확장 프로그램, 애플리케이션 스키마 만들기

  1. psql을 사용하여 postgres 사용자 또는 AlloyDB Omni 수퍼유저 권한이 있는 사용자로 데이터베이스에 연결합니다.

    /usr/lib/postgresql/18/bin/psql -h localhost -U postgres

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

    -- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;

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

  3. 데이터베이스를 소유하고 관리하는 AlloyDB Omni 관리 역할을 만듭니다.

    CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;

    자세한 내용은 CREATE USER를 참고하세요.

  4. 파라미터화된 보안 뷰에 대해 쿼리를 실행하기 위한 새 데이터베이스 역할을 만듭니다. 이 역할은 애플리케이션이 데이터베이스에 연결하고 로그인하여 공개 함수나 객체에 대해 최소한의 액세스 권한만으로 쿼리를 실행할 때 사용되는 AlloyDB Omni 역할입니다.

    자세한 내용은 CREATE USER를 참고하세요.

  5. 관리 사용자로 연결합니다.

    SET role TO admin_user;

  6. 테이블이 포함되는 스키마를 만듭니다.

    CREATE SCHEMA schema;

  7. 테이블을 만들고 데이터를 삽입합니다.

    CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO schema.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

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

파라미터화된 보안 뷰를 만들고 기본 테이블 및 뷰에 대한 적절한 액세스 권한을 설정하려면 다음 단계를 따르세요.

  1. psql을 사용하여 admin_user로 데이터베이스에 연결합니다.

    /usr/lib/postgresql/18/bin/psql -h localhost -U admin_user

  2. 뷰에 제한된 액세스 권한을 제공하기 위해 파라미터화된 보안 뷰를 만듭니다.

    CREATE VIEW schema.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM schema.checked_items t
WHERE customer_id = $@app_end_userid;

  3. 뷰에 대한 액세스 권한을 부여합니다.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. 뷰에 액세스할 수 있도록 스키마에 대한 액세스 권한을 부여합니다.

    GRANT USAGE ON SCHEMA schema TO psv_user;

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

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

데이터 보안 확인

파라미터화된 보안 뷰가 지정된 뷰에 대한 액세스를 제한하고 있는지 확인하려면 psv_user로 다음 명령어를 실행합니다. 이는 애플리케이션이 데이터베이스에 연결하고 로그인하여 쿼리를 실행할 때 사용하는 AlloyDB Omni 데이터베이스 역할입니다.

  1. 파라미터화된 보안 뷰 사용자로 연결합니다.

    /usr/lib/postgresql/18/bin/psql -h localhost -U psv_user

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

    SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

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

    SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from schema.secure_checked_items',
  param_names => ARRAY ['app_end_userid'],
  param_values => ARRAY ['303']
);

  4. 자연어를 사용하여 파라미터화된 보안 뷰를 통합하여 자연어 기반 쿼리의 보안을 강화하세요.

삭제

정리하려면 AlloyDB Omni 인스턴스를 제거하거나 인스턴스를 유지하고 개별 객체를 삭제하면 됩니다.

다음 단계