Proteger e controlar o acesso aos dados do aplicativo usando visualizações seguras parametrizadas

Preparar o ambiente

Para preparar as consultas em uma visualização segura parametrizada, primeiro é necessário configurar visualizações parametrizadas, os papéis do banco de dados e do banco de dados, a extensão parameterized_view e o esquema do aplicativo.

Configurar visualizações parametrizadas e o banco de dados

1. Peça à equipe do AlloyDB para ativar a flag parameterized_views.enabled database, que carrega as bibliotecas de extensão necessárias.

2. Crie um banco de dados chamado database para os dados do aplicativo e as visualizações parametrizadas. Para mais informações, consulte Criar um banco de dados.

Criar funções de banco de dados, a extensão e o esquema do aplicativo

1. Usando o AlloyDB Studio ou o psql, conecte-se ao banco de dados como o usuário postgres ou como um usuário com privilégios de superusuário do AlloyDB.

   psql database -U postgres
   

   Para mais informações, consulte Sobre o gerenciamento de usuários do banco de dados no AlloyDB.

2. Crie a extensão parameterized_views no banco de dados.

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

   Quando a extensão é criada, o sistema também cria um esquema chamado parameterized_views para que as APIs fiquem contidas no namespace do esquema e não entrem em conflito com as APIs existentes.

3. Crie a função administrativa do AlloyDB, que é proprietária e gerencia o banco de dados.

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

   Para ver mais informações, consulte CREATE USER.

4. Crie uma nova função de banco de dados para executar consultas em visualizações seguras parametrizadas. Essa é uma função do AlloyDB que o aplicativo usa para se conectar e fazer login no banco de dados para executar consultas com acesso limitado a funções ou objetos públicos ao conjunto mínimo necessário.

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

   Para ver mais informações, consulte CREATE USER.

5. Conecte-se como usuário administrativo.

   SET role TO admin_user;
   

6. Crie o esquema que contém as tabelas.

   CREATE SCHEMA schema;
   

7. Crie as tabelas e insira dados.

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

Criar visualizações parametrizadas seguras e configurar privilégios de acesso

Para criar visualizações parametrizadas seguras e configurar privilégios de acesso adequados para a tabela e as visualizações de base, siga estas etapas:

1. Usando o AlloyDB Studio ou o psql, conecte-se ao banco de dados como admin_user.

   psql database -U admin_user
   

2. Para fornecer acesso limitado à visualização, crie uma visualização segura parametrizada:

   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. Conceda acesso à visualização.

   GRANT SELECT ON schema.secure_checked_items TO psv_user;
   

4. Para acessar a visualização, conceda acesso ao esquema.

   GRANT USAGE ON SCHEMA schema TO psv_user;
   

5. Revogue o acesso direto à tabela base.

   REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;
   

Verificar a segurança de dados

Para verificar se as visualizações seguras parametrizadas estão restringindo o acesso às visualizações designadas, execute os comandos abaixo como psv_user. Essa é uma função de banco de dados do AlloyDB que o aplicativo usa para se conectar e fazer login no banco de dados para executar consultas.

1. Conectar-se como um usuário parametrizado de visualizações seguras.

   psql database -U psv_user
   

2. Verifique se a tabela de base não pode ser acessada.

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

3. Acesse a visualização segura parametrizada usando a função 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. Para melhorar a segurança das consultas geradas em linguagem natural, integre suas visualizações seguras parametrizadas usando a linguagem natural da IA do AlloyDB.