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

As visualizações seguras parametrizadas no AlloyDB para PostgreSQL permitem restringir o acesso do usuário do aplicativo a linhas específicas do banco de dados com base em parâmetros nomeados. Neste tutorial, você vai criar e consultar uma visualização segura parametrizada usando o AlloyDB Studio ou o psql.

Os exemplos neste tutorial ilustram os recursos das visualizações seguras parametrizadas apenas para fins de demonstração.

Objetivos

  • Criar visualizações parametrizadas seguras com parâmetros de visualização nomeados.
  • Crie o papel de banco de dados usado pelo aplicativo para se conectar ao banco de dados e acessar visualizações seguras parametrizadas.
  • Conceda as permissões do novo papel às visualizações seguras e revogue o acesso às tabelas base.
  • Conecte-se usando o novo papel e verifique se as tabelas restritas não podem ser acessadas.
  • Execute consultas na visualização segura parametrizada usando a função execute_parameterized_query.

Custos

Neste documento, você vai usar os seguintes componentes faturáveis do Google Cloud:

Para gerar uma estimativa de custo baseada na sua projeção de uso, use a calculadora de preços.

Novos usuários do Google Cloud podem estar qualificados para um teste sem custo financeiro.

Ao concluir as tarefas descritas neste documento, é possível evitar o faturamento contínuo excluindo os recursos criados. Para mais informações, consulte Limpeza.

Antes de começar

Ativar o faturamento e as APIs necessárias

  1. No Google Cloud console, selecione um projeto.

    Acessar o seletor de projetos

  2. Verifique se o faturamento está ativado para seu projeto do Google Cloud .

  3. Ative as APIs do Cloud necessárias para criar e se conectar ao AlloyDB.

    Ativar a API

    1. Na etapa Confirmar projeto, clique em Avançar para confirmar o nome do projeto no qual você vai fazer alterações.

    2. Na etapa Ativar APIs, clique em Ativar para ativar o seguinte:

      • API AlloyDB

Criar e se conectar a um banco de dados

  1. Crie um cluster e a instância primária dele.
  2. Conecte-se à instância e crie um banco de dados.

Preparar o ambiente

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

Ativar a extensão necessária

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

Configurar o banco de dados

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

Criar papéis de banco de dados, a extensão e o esquema do aplicativo

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

    psql database -U postgres

    Para mais informações, consulte Sobre o gerenciamento de usuários de 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 desse esquema e não entrem em conflito com as APIs atuais.

  3. Crie o papel administrativo do AlloyDB, que é proprietário e gerencia o banco de dados.

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

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

  4. Crie um novo papel de banco de dados para executar consultas em visualizações seguras parametrizadas. Esse é um papel 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 mais informações sobre como criar usuários do PostgreSQL, 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. Criar as tabelas e inserir 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 os privilégios de acesso adequados para a tabela base e as visualizações, siga estas etapas:

  1. Para se conectar ao banco de dados como admin_user, use o AlloyDB Studio ou o psql.

    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 seguintes comandos como psv_user. Esse é um papel de banco de dados do AlloyDB que o aplicativo usa para se conectar e fazer login no banco de dados para executar consultas.

  1. Conecte-se como um usuário de visualizações seguras parametrizadas.

    psql database -U psv_user

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

    SELECT * FROM schema.checked_items;

    O resultado será o seguinte:

    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.

Limpar

Para evitar cobranças na sua conta do Google Cloud pelos recursos usados no tutorial, exclua o projeto que os contém ou mantenha o projeto e exclua os recursos individuais.

Se você quiser manter o cluster e remover apenas os objetos de banco de dados criados neste tutorial, execute os seguintes comandos:

  1. Para se conectar ao banco de dados como postgres, use o AlloyDB Studio ou psql:

    psql database -U postgres

  2. Remova os objetos que você 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;

Para excluir todo o cluster do AlloyDB e a rede VPC, siga estas etapas:

  1. No Google Cloud console, acesse a página Clusters.

    Acessar Clusters

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

  3. Clique em Excluir cluster.

  4. Em Excluir cluster my-cluster, insira my-cluster para confirmar que você quer excluir o cluster.

  5. Clique em Excluir.

  6. Se você criou uma conexão particular ao criar um cluster, acesse a página Rede do console do Google Cloud e clique em Excluir rede VPC.

A seguir