パラメータ化されたセキュアなビューを使用して、アプリケーション データへのアクセスの保護と制御を行う。

環境を準備する

パラメータ化されたセキュアビューでクエリを実行する準備をするには、まずパラメータ化されたビュー、データベースとデータベースのロール、parameterized_view 拡張機能、アプリケーション スキーマを設定する必要があります。

パラメータ化されたビューとデータベースを設定する

1. 必要な拡張機能ライブラリを読み込む parameterized_views.enabled database フラグを有効にするよう AlloyDB チームにリクエストします。

2. アプリケーション データとパラメータ化されたビュー用に database というデータベースを作成します。詳細については、データベースを作成するをご覧ください。

データベース ロール、拡張機能、アプリケーション スキーマを作成する

1. AlloyDB Studio または psql を使用して、postgres ユーザーとして、または AlloyDB スーパーユーザーの権限を持つユーザーとしてデータベースに接続します。

   psql database -U postgres
   

   詳細については、AlloyDB でのデータベース ユーザー管理についてをご覧ください。

2. データベースに parameterized_views 拡張機能を作成します。

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

   拡張機能が作成されると、parameterized_views という名前のスキーマも作成されます。これにより、API がそのスキーマの名前空間に含まれ、API が既存の API と競合しないようになります。

3. データベースを所有して管理する AlloyDB 管理ロールを作成します。

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

   詳細については、CREATE USER をご覧ください。

4. パラメータ化されたセキュアビューに対してクエリを実行する新しいデータベース ロールを作成します。これは、アプリケーションがデータベースに接続してログインし、公開関数またはオブジェクトへのアクセスを最小限に制限してクエリを実行するために使用する AlloyDB ロールです。

   CREATE ROLE psv_user WITH LOGIN PASSWORD '...';
   

   詳細については、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. AlloyDB Studio または psql を使用して、admin_user としてデータベースに接続します。

   psql database -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 データベース ロールです。

1. パラメータ化されたセキュアビュー ユーザーとして接続します。

   psql database -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 AI の自然言語を使用してパラメータ化されたセキュアビューを統合します。