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

Este tutorial descreve como usar visualizações seguras parametrizadas no Cloud SQL para PostgreSQL para restringir o acesso do usuário a visualizações parametrizadas usando Cloud SQL Studio ou psql.

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.

Preparar o ambiente

  1. Ative a flag de banco de dados cloudsql.enable_parameterized_views para sua instância do Cloud SQL. Essa mudança de flag exige a reinicialização do banco de dados.

  2. Conecte-se ao banco de dados como o usuário postgres.

    psql -U postgres

  3. Crie a extensão parameterized_views no banco de dados.

    CREATE EXTENSION parameterized_views;

  4. Crie um novo papel de banco de dados para executar consultas.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. Crie um esquema e uma tabela para os dados do aplicativo.

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

Criar visualizações seguras parametrizadas e configurar privilégios de acesso

  1. Crie uma visualização segura parametrizada:

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. Conceda acesso à visualização e ao esquema ao papel do aplicativo.

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

  3. Revogue o acesso direto à tabela de base.

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

Verificar a segurança de dados

  1. Conecte-se como psv_user.

    psql -U psv_user -d postgres

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

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table 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 app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    O resultado só deve incluir itens em que owner_id seja 123.

A seguir