Usar visualizações seguras parametrizadas

Este documento descreve como usar visualizações seguras parametrizadas no Cloud SQL para PostgreSQL, que permitem limitar o acesso aos 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 a visão geral das visualizações seguras parametrizadas e o tutorial sobre visualizações seguras parametrizadas.

Antes de começar

Este documento pressupõe que você 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 (tutorial).

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 seguras, 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 de visualização nomeada que você definiu ao criar a visualização segura parametrizada. O parâmetro de visualização nomeada é criado 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 aplicadas 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