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.

Objetivos

  • Crie vistas parametrizadas seguras com parâmetros de visualização com nome.
  • Crie a função da base de dados que é usada pela aplicação para se ligar à base de dados e aceder a vistas seguras parametrizadas.
  • Conceda autorizações à nova função para as vistas seguras e revogue o acesso às tabelas base.
  • Estabeleça ligação através da nova função e verifique se não é possível aceder às tabelas restritas.
  • Execute consultas na vista segura parametrizada através da função execute_parameterized_query.

Custos

Neste documento, usa os seguintes componentes faturáveis do Google Cloud:

Para gerar uma estimativa de custos com base na sua utilização projetada, use a calculadora de preços.

Os novos Google Cloud utilizadores podem ser elegíveis para uma avaliação gratuita.

Quando terminar as tarefas descritas neste documento, pode evitar a faturação contínua eliminando os recursos que criou. Para mais informações, consulte o artigo Limpe.

Antes de começar

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.

Limpar

Para evitar incorrer em custos na sua conta do Google Cloud pelos recursos usados neste tutorial, elimine o projeto que contém os recursos ou mantenha o projeto e elimine os recursos individuais.

  1. Na Google Cloud consola, aceda à página Clusters.

    Aceda a Clusters

  2. Clique no nome do cluster, my-cluster, na coluna Nome do recurso.

  3. Clique em Eliminar cluster.

  4. Em Delete cluster my-cluster, introduza my-cluster para confirmar que quer eliminar o cluster.

  5. Clique em Eliminar.

  6. Se criou uma ligação privada quando criou um cluster, aceda à Google Cloud página de rede da consola e clique em Eliminar rede VPC.

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

    psql database -U postgres

  8. Solte os objetos que criou.

    DROP VIEW schema.secure_checked_items;
DROP TABLE schema.checked_items;
DROP SCHEMA schema;
DROP ROLE psv_user;
DROP ROLE admin_user;
DROP EXTENSION parameterized_views;

O que se segue?