Proteja e controle o acesso aos dados das aplicações através de vistas seguras parametrizadas

Este tutorial descreve como usar vistas seguras parametrizadas no AlloyDB for PostgreSQL para restringir o acesso dos utilizadores a vistas parametrizadas através do AlloyDB Studio ou do psql.

São incluídos exemplos para ilustrar as capacidades das visualizações seguras parametrizadas. Estes exemplos destinam-se apenas a fins de demonstração.

Pré-requisitos

Ative a faturação e as APIs necessárias

  1. Na Google Cloud consola, selecione um projeto.

    Aceder ao seletor de projetos

  2. Certifique-se de que a faturação está ativada para o seu Google Cloud projeto.

  3. Ative as APIs Cloud necessárias para criar e estabelecer ligação ao AlloyDB para PostgreSQL.

    Ative a API

    1. No passo Confirmar projeto, clique em Seguinte para confirmar o nome do projeto ao qual vai fazer alterações.

    2. No passo Ativar APIs, clique em Ativar para ativar o seguinte:

      • API AlloyDB

Crie e associe a uma base de dados

  1. Crie um cluster e a respetiva instância principal.
  2. Associe à sua instância e crie uma base de dados.

Prepare o seu ambiente

Para se preparar para executar consultas numa vista segura parametrizada, primeiro tem de configurar vistas parametrizadas, a base de dados e as funções da base de dados, a extensão parameterized_view e o esquema da aplicação.

Ative a extensão necessária

Ative a flag da base de dados parameterized_views.enabled, que carrega as bibliotecas de extensões necessárias. Para mais informações, consulte o artigo Configure as flags da base de dados de uma instância.

parameterized_views.enabled

Configure a base de dados

  • Crie uma base de dados denominada database para os dados da aplicação e as vistas parametrizadas. Para mais informações, consulte Crie uma base de dados.

Crie funções de base de dados, a extensão e o esquema da aplicação

  1. Usando o AlloyDB Studio ou o psql, estabeleça ligação à base de dados como o utilizador postgres ou como um utilizador com privilégios de superutilizador do AlloyDB.

    psql database -U postgres

    Para mais informações, consulte o artigo Acerca da gestão de utilizadores da base de dados no AlloyDB.

  2. Crie a extensão parameterized_views na base 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 denominado parameterized_views para que as APIs estejam contidas no espaço de nomes desse esquema e para que essas APIs não entrem em conflito com as APIs existentes.

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

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

    Para mais informações, consulte CREATE USER.

  4. Crie uma nova função de base de dados para executar consultas em vistas seguras parametrizadas. Esta é uma função do AlloyDB que a aplicação usa para se ligar e iniciar sessão na base 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 mais informações, consulte CREATE USER.

  5. Estabeleça ligação como utilizador 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);

Crie vistas parametrizadas seguras e configure privilégios de acesso

Para criar vistas parametrizadas seguras e configurar privilégios de acesso adequados para a tabela base e as vistas, siga estes passos:

  1. Usando o AlloyDB Studio ou o psql, estabeleça ligação à base de dados como admin_user.

    psql database -U admin_user

  2. Para conceder acesso limitado à vista, crie uma vista 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 à vista.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. Para aceder à vista, 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;

Valide a segurança dos dados

Para verificar se as visualizações seguras parametrizadas estão a restringir o acesso às visualizações designadas, execute os seguintes comandos como psv_user. Esta é uma função da base de dados do AlloyDB que a aplicação usa para estabelecer ligação e iniciar sessão na base de dados para executar consultas.

  1. Estabelecer ligação como utilizador de vistas seguras parametrizadas.

    psql database -U psv_user

  2. Confirme que não é possível aceder à tabela base.

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

  3. Aceda à vista segura parametrizada através da 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 as suas vistas seguras parametrizadas através da linguagem natural da IA do AlloyDB.