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

Selecione uma versão da documentação:

Neste tutorial, descrevemos como usar visualizações seguras parametrizadas no AlloyDB Omni para restringir o acesso do usuário a visualizações parametrizadas usando o AlloyDB Omni Studio ou o psql.

Incluímos exemplos para ilustrar os recursos de visualizações seguras parametrizadas. Esses exemplos são apenas para fins de demonstração.

Objetivos

  • Criar visualizações seguras parametrizadas com parâmetros de visualização nomeados.
  • Criar o papel de banco de dados usado pelo aplicativo para se conectar ao banco de dados e acessar visualizações seguras parametrizadas.
  • Conceder permissões de novo papel às visualizações seguras e revogar o acesso às tabelas de base.
  • Conectar usando o novo papel e verificar se as tabelas restritas não podem ser acessadas.
  • Executar consultas na visualização segura parametrizada usando a função execute_parameterized_query.

Instalar e conectar a um banco de dados

  1. Instale o AlloyDB Omni usando o RPM.

Preparar o ambiente

Para se preparar para executar consultas em uma visualização segura parametrizada, primeiro configure as 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.

Antes de usar visualizações seguras parametrizadas, faça o seguinte uma vez no ambiente Linux. Cada configuração pode ser aplicada usando ALTER SYSTEM ou editando postgresql.conf diretamente:

  1. Adicione "parameterized_views" a shared_preload_libraries.
  2. Ative o recurso definindo parameterized_views.enabled=on.
  3. Reinicie o postgres para que as mudanças entrem em vigor.

Configurar o banco de dados

  • Crie um banco de dados chamado database para os dados do aplicativo e as visualizações parametrizadas.

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

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

    /usr/lib/postgresql/18/bin/psql -h localhost -U postgres

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

  3. Crie o papel administrativo do AlloyDB Omni, 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 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 Omni 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.

    Para mais informações, consulte CREATE USER.

  5. Conecte-se como o 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 seguras parametrizadas e configurar privilégios de acesso

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

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

    /usr/lib/postgresql/18/bin/psql -h localhost -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 comandos a seguir como psv_user. Esse é um papel de banco de dados do AlloyDB Omni 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.

    /usr/lib/postgresql/18/bin/psql -h localhost -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 linguagem natural.

Limpar

Para limpar, desinstale a instância do AlloyDB Omni ou mantenha a instância e exclua os objetos individuais.

A seguir