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

Neste tutorial, descrevemos como usar visualizações seguras parametrizadas no AlloyDB para PostgreSQL para restringir o acesso do usuário a visualizações parametrizadas usando o AlloyDB Studio ou o psql. Exemplos são incluídos para ilustrar os recursos das visualizações seguras parametrizadas. Esses exemplos são apenas para fins de demonstração.

Objetivos

  • Crie visualizações parametrizadas seguras com parâmetros de visualização nomeados.
  • Crie a função de banco de dados usada pelo aplicativo para se conectar ao banco de dados e acessar visualizações seguras parametrizadas.
  • Conceda as permissões da nova função às visualizações seguras e revogue o acesso às tabelas de base.
  • Conecte-se usando a nova função e verifique se não é possível acessar as tabelas restritas.
  • 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 projeção de uso deste tutorial, use a calculadora de preços.

Novos usuários do Google Cloud podem estar qualificados para uma avaliação gratuita.

Ao concluir as tarefas descritas neste documento, é possível evitar o faturamento contínuo excluindo os recursos criados. Saiba mais em Limpeza.

Antes de começar

Solicitar acesso

Antes de usar as visualizações seguras parametrizadas, solicite acesso a elas no AlloyDB e aguarde a confirmação de ativação antes de começar.

Ativar o faturamento e as APIs necessárias

  1. No console Google Cloud , selecione um projeto.

    Acessar o seletor de projetos

  2. Verifique se o faturamento foi ativado para o projeto Google Cloud .

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

    Ativar a API

    1. Na etapa Confirmar projeto, clique em Próxima para confirmar o nome do projeto que você vai mudar.

    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 principal 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 você precisa configurar visualizações parametrizadas, o banco de dados e os papéis 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 visualizações parametrizadas. Para mais informações, consulte Criar um banco de dados.

Criar papéis 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 sejam contidas no namespace desse esquema e não entrem em conflito com as APIs atuais.

  3. Crie a função administrativa do AlloyDB, que possui 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. É 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 os 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 de 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. É uma função do 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 não é possível acessar a tabela de base.

    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.

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.

  1. No console Google Cloud , 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, digite 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 Google Cloud e clique em Excluir rede VPC.

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

    psql database -U postgres

  8. Exclua 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;

A seguir