Usar visualizações seguras parametrizadas

Este documento descreve como usar visualizações seguras parametrizadas no Cloud SQL para PostgreSQL, que permitem limitar o acesso a dados com base em parâmetros nomeados específicos do aplicativo, como credenciais de usuário do aplicativo. As visualizações seguras parametrizadas melhoram a segurança e o controle de acesso ao estender a funcionalidade das visualizações do PostgreSQL. Essas visualizações também reduzem os riscos de executar consultas não confiáveis de aplicativos, aplicando automaticamente várias restrições a qualquer consulta executada.

Para mais informações, consulte visões seguras parametrizadas no Cloud SQL e proteja e controle o acesso aos dados do aplicativo usando visões seguras parametrizadas.

Antes de começar

Este documento pressupõe que você já criou uma instância do Cloud SQL para PostgreSQL.

Antes de usar visualizações seguras parametrizadas, faça o seguinte:

  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 uma reinicialização do banco de dados. Para mais informações, consulte Configurar flags de banco de dados.

  2. Use o Cloud SQL Studio ou psql para criar a extensão parameterized_views em qualquer banco de dados em que uma visualização parametrizada seja criada:

    -- Requires cloudsql.enable_parameterized_views set to on
CREATE EXTENSION parameterized_views;

    Quando a extensão é criada, um esquema chamado parameterized_views também é criado pelo sistema para que as APIs sejam contidas no namespace desse esquema e não entrem em conflito com as APIs atuais.

Criar uma visualização segura parametrizada

Para criar uma visualização segura parametrizada, siga estas etapas:

  1. Execute o comando DDL CREATE VIEW usando a opção security_barrier:

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

    Substitua:

    • VIEW_NAME: o nome da visualização segura parametrizada
    • TABLE_NAME: o nome da tabela a ser usada na visualização segura parametrizada.
    • ALIAS: o alias do nome da tabela a ser usado na visualização segura parametrizada.
    • COLUMNNAME ou COLUMN_NAMEN: o nome da coluna ou das colunas da tabela a serem usadas na visualização segura parametrizada

    • CONDITION: a instrução de condição usada para restringir os usuários do aplicativo para que eles só possam ver as linhas a que têm permissão de acesso. Adicione os parâmetros obrigatórios usando a sintaxe $@PARAMETER_NAME na cláusula WHERE. Um caso de uso comum é verificar o valor de uma coluna usando WHERE COLUMN = $@PARAMETER_NAME.

      $@PARAMETER_NAME indica um parâmetro de visualização nomeado. O valor é fornecido quando você usa a API execute_parameterized_query. Os parâmetros de visualização nomeados têm os seguintes requisitos:

      • Os parâmetros de visualização nomeada precisam começar com uma letra (a-z) ou um sublinhado (_).
      • Os caracteres seguintes podem ser letras, sublinhados ou dígitos (0-9).
      • Os parâmetros de visualização nomeada diferenciam maiúsculas de minúsculas. Por exemplo, $@PARAMETER_NAME é interpretado de forma diferente de $@parameter_name.

      Confira um exemplo de criação de uma visualização segura parametrizada que usa um parâmetro de visualização nomeado:

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

  2. Conceda SELECT na visualização a qualquer usuário do banco de dados que possa consultar a visualização.

  3. Conceda USAGE no esquema que contém as tabelas definidas na visualização a qualquer usuário do banco de dados que possa consultar a visualização.

Para mais informações, consulte Proteger e controlar o acesso a dados de aplicativos usando visualizações seguras parametrizadas.

Configurar a segurança do aplicativo

Para configurar a segurança dos seus aplicativos usando visualizações seguras parametrizadas, siga estas etapas:

  1. Crie a visualização segura parametrizada como um usuário administrativo. Esse é um usuário do banco de dados do Cloud SQL que realiza operações administrativas para o aplicativo.

  2. Crie uma nova função de banco de dados para executar consultas em visualizações seguras parametrizadas. Essa é uma função de banco de dados do Cloud SQL que o aplicativo usa para se conectar e fazer login no banco de dados.

    1. Conceda as novas permissões de papel às visualizações protegidas, que normalmente incluem privilégios SELECT nas visualizações e USAGE nos esquemas.
    2. Limite os objetos que essa função pode acessar ao conjunto mínimo necessário de funções e objetos públicos de que o aplicativo precisa. Evite dar acesso a esquemas e tabelas que não são públicos.

    Quando você consulta as visualizações, o aplicativo fornece os valores dos parâmetros de visualização obrigatórios, que estão vinculados à identidade do usuário do aplicativo.

    Para mais informações, consulte Criar e gerenciar usuários.

Consultar uma visualização segura parametrizada

Para consultar uma visualização segura parametrizada, use uma das seguintes opções:

  • Baseada em JSON: use essa API para executar a consulta de uma só vez e retornar linhas JSON.
  • Baseada em CURSOR: use essa API quando tiver consultas de execução mais longa ou quando tiver consultas grandes e quiser buscar o resultado em lotes.
  • Instrução PREPARE .. AS RESTRICTED: use PREPARE .. AS RESTRICTED para definir o plano de consulta e execute EXECUTE ... WITH VIEW PARAMETERS (...) com parâmetros específicos para a visualização.

API JSON

Execute a função execute_parameterized_query(), que tem a seguinte sintaxe:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

A função retorna uma tabela de objetos JSON. Cada linha na tabela é equivalente ao valor row_to_json() da linha de resultado da consulta original.

O uso dessa API limita o tamanho do conjunto de resultados por tamanho (em KB) e pelo número de linhas. É possível configurar esses limites usando parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

API CURSOR

Execute a função execute_parameterized_query() com um nome de cursor, que cria e retorna um CURSOR no escopo da transação:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Instruções preparadas

Com o método de instruções preparadas, é possível preparar um plano de consulta uma vez e executá-lo várias vezes com valores diferentes para os parâmetros posicionais da consulta e os parâmetros nomeados da visualização.

Para usar uma instrução preparada, faça o seguinte:

  1. Crie a instrução preparada.

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

    Substitua:

    • PREPARED_STATEMENT_NAME: o nome da instrução preparada
    • QUERY_PARAM_TYPE_N: o tipo de dados do parâmetro de consulta, como TEXT
    • SQL_QUERY: a consulta SQL a ser executada como parte da instrução preparada com o valor ou valores especificados.

  2. Execute a instrução preparada.

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    Substitua:

    • PREPARED_STATEMENT_NAME: o nome da instrução preparada.
    • QUERY_VALUE_N: o valor ou os valores a serem fornecidos como um parâmetro ou parâmetros para a consulta SQL.
    • PARAMETER_NAME_N: o nome do parâmetro ou dos parâmetros da visualização nomeada que você definiu ao criar a visualização segura parametrizada. Você cria o parâmetro de visualização nomeada com base na coluna da tabela.
    • PARAMETER_VALUE_N: o valor ou os valores do parâmetro de visualização nomeada que restringe a visualização segura parametrizada às linhas associadas ao valor.

  3. Limpe a instrução preparada.

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

O exemplo a seguir usa uma visualização segura parametrizada chamada user_specific_items, que exige o parâmetro de visualização nomeado $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Depois de criar a instrução preparada, é possível executá-la várias vezes e atribuir valores diferentes para a consulta e o parâmetro de visualização nomeada.

Por exemplo, a primeira execução de consulta:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

A segunda execução da consulta:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

A cláusula WITH VIEW PARAMETERS é onde os parâmetros de visualização nomeada (123, 456) para a visualização segura parametrizada são fornecidos, separados dos parâmetros posicionais para a consulta preparada (%Laptop%, %Book%).

Por fim, limpe a instrução preparada.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Restrições em consultas

A seguir, listamos o conjunto de operações restritas para consultas executadas usando as opções descritas em Consultar uma visualização segura parametrizada:

  • Somente leitura: apenas instruções SELECT somente leitura são permitidas. DML (INSERT, UPDATE, DELETE) e DDL (CREATE, ALTER) são proibidos.
  • Não é permitido aninhamento: chamadas recursivas para execute_parameterized_query são proibidas.
  • Limites de extensão: algumas extensões que iniciam novas sessões em segundo plano (por exemplo, dblink, pg_cron) não são permitidas.
  • As instruções EXPLAIN não são permitidas para evitar possível vazamento de informações usando planos de consulta.

Listar todas as visualizações parametrizadas

Use a visualização parameterized_views.all_parameterized_views para listar todas as visualizações parametrizadas (aquelas que contêm pelo menos um parâmetro nomeado $@...).

SELECT * FROM parameterized_views.all_parameterized_views;

A seguir