Gerenciar segurança de dados de aplicativos usando visualizações seguras parametrizadas do AlloyDB Omni

Selecione uma versão da documentação:

É possível usar visualizações seguras parametrizadas no AlloyDB Omni para limitar o acesso a dados com base em parâmetros nomeados específicos do aplicativo, como credenciais de usuário. As visualizações seguras parametrizadas melhoram a segurança e o controle de acesso, estendendo a funcionalidade das visualizações do PostgreSQL. Essas visualizações também atenuam os riscos de executar consultas não confiáveis de aplicativos, aplicando restrições automaticamente a qualquer consulta executada.

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

Antes de começar

O suporte a visualizações parametrizadas do AlloyDB AI é fornecido por parameterized_views, que é uma extensão do AlloyDB para PostgreSQL.

Esta página pressupõe que você instalou o AlloyDB Omni. Consulte Instalar o AlloyDB Omni no Linux.

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 servidor PostgreSQL para que as mudanças entrem em vigor.

    -- See the current shared_preload_libraries
SHOW shared_preload_libraries;
ALTER SYSTEM SET shared_preload_libraries="...,parameterized_views";
ALTER SYSTEM SET parameterized_views.enabled=on;

  4. Use psql para criar a parameterized_views extensão em qualquer banco de dados em que você queira criar uma visualização parametrizada:

    -- Requires parameterized_views.enabled set to true
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, conforme mostrado no exemplo a seguir:

    CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;

    No exemplo anterior, a visualização segura parametrizada permite o acesso a três colunas de uma tabela chamada checked_items. A visualização limita os resultados a linhas em que checked_items.customer_id corresponde a um parâmetro obrigatório. Use os seguintes atributos:

    • Crie a visualização usando a opção security_barrier.
    • Para restringir os usuários do aplicativo para que eles só possam visualizar as linhas a que têm acesso, adicione os parâmetros necessários na definição da visualização usando a sintaxe $@PARAMETER_NAME. Um caso de uso comum é verificar o valor de uma coluna na cláusula WHERE usando 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 nomeados precisam começar com uma letra (a-z).
      • É possível usar letras com acentos e letras não latinas, além de um sublinhado (_).
      • Os caracteres subsequentes podem ser letras, sublinhados ou dígitos (0-9).
      • Os parâmetros de visualização nomeados não podem conter $.
      • Os parâmetros de visualização nomeados diferenciam maiúsculas de minúsculas. Por exemplo, $@PARAMETER_NAME é interpretado de maneira diferente de $@parameter_name.

  2. Conceda SELECT na visualização a qualquer usuário do banco de dados que tenha permissão para 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 tenha permissão para 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 aplicativos usando visualizações seguras parametrizadas, siga estas etapas:

  1. Crie as visualizações seguras parametrizadas como um usuário administrativo. Esse usuário é um usuário do banco de dados do AlloyDB Omni que realiza operações administrativas para o aplicativo, incluindo configuração do banco de dados e administração de segurança.
  2. Crie um novo papel de banco de dados para executar consultas em visualizações seguras parametrizadas. Esse é um papel de banco de dados do AlloyDB Omni que o aplicativo usa para se conectar e fazer login no banco de dados e para executar consultas em visualizações parametrizadas.
  3. Conceda permissões de novo papel às visualizações seguras, que normalmente incluem privilégios SELECT para as visualizações e USAGE nos esquemas.
  4. Limite os objetos que esse papel pode acessar ao conjunto mínimo necessário de funções e objetos públicos de que o aplicativo precisa. Evite fornecer acesso a esquemas e tabelas que não são públicos.
  5. Ao consultar as visualizações, o aplicativo fornece os valores dos parâmetros de visualização necessários, que estão vinculados à identidade do usuário do aplicativo.

Consultar uma visualização segura parametrizada

Para consultar uma visualização segura parametrizada, use uma das opções a seguir que melhor oferece suporte ao seu caso de uso:

  • Baseada em JSON: use essa API para executar a consulta em uma única 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. A função execute_parameterized_query fornecida pela extensão parameterized_views aceita um nome de cursor.
  • Instrução PREPARE EXECUTE: use essa instrução para instruções preparadas que podem ser executadas várias vezes com valores de parâmetros diferentes.

Para consultar visualizações seguras parametrizadas, use a função execute_parameterized_query() fornecida pela extensão parameterized_views.

API JSON

Essa API tem limitações porque declara um cursor para a consulta especificada. Como resultado, a consulta precisa ser compatível com os cursores do PostgreSQL. Por exemplo, a API CURSOR não oferece suporte a instruções DO ou SHOW.

Essa API também não restringe os resultados por tamanho ou pelo número de linhas retornadas.

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]
)

Substitua:

  • SQL_QUERY: uma consulta SQL cuja cláusula FROM se refere a uma ou mais visualizações seguras parametrizadas.
  • PARAMETER_NAMES: uma lista de nomes de parâmetros a serem transmitidos como strings.
  • PARAMETER_VALUES: uma lista de valores de parâmetros a serem transmitidos.
    • Essa lista precisa ter o mesmo tamanho da lista param_names, em que a ordem dos valores corresponde à ordem dos nomes.
    • O tipo exato dos valores é inferido da consulta e da definição de visualização parametrizada. As conversões de tipo são realizadas quando necessário e possível para o valor de parâmetro especificado. Em caso de incompatibilidade de tipo, um erro é gerado.

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.

Use o exemplo a seguir para consultar uma visualização segura parametrizada:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

O uso dessa API limita o tamanho do conjunto de resultados pelo tamanho expresso em kilobytes (kB) dos resultados 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.

Essa API tem limitações porque declara um cursor para a consulta especificada. Como resultado, a consulta precisa ser compatível com os cursores do PostgreSQL. Por exemplo, a API CURSOR não oferece suporte a instruções DO ou SHOW.

Essa API também não restringe os resultados por tamanho ou pelo número de linhas retornadas.

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]
)

Substitua:

  • SQL_QUERY: uma consulta SQL cuja cláusula FROM se refere a uma ou mais visualizações seguras parametrizadas.
  • PARAMETER_NAMES: uma lista de nomes de parâmetros a serem transmitidos como strings.
  • PARAMETER_VALUES: uma lista de valores de parâmetros a serem transmitidos.
  • Essa lista precisa ter o mesmo tamanho da lista param_names, em que a ordem dos valores corresponde à ordem dos nomes.
  • O tipo exato dos valores é inferido da consulta e da definição de visualização parametrizada. As conversões de tipo são realizadas quando necessário e possível para o valor de parâmetro especificado. Em caso de incompatibilidade de tipo, um erro é gerado.

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.

Use o exemplo a seguir para consultar uma visualização segura parametrizada:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

O uso dessa API limita o tamanho do conjunto de resultados pelo tamanho expresso em kilobytes (kB) dos resultados 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

Essa API tem limitações porque declara um cursor para a consulta especificada. Como resultado, a consulta precisa ser compatível com os cursores do PostgreSQL. Por exemplo, a API CURSOR não oferece suporte a instruções DO ou SHOW.

Essa API também não restringe os resultados por tamanho ou pelo número de linhas retornadas.

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]
)

Substitua:

  • SQL_QUERY: uma consulta SQL cuja cláusula FROM se refere a uma ou mais visualizações seguras parametrizadas.
  • PARAMETER_NAMES: uma lista de nomes de parâmetros a serem transmitidos como strings.
  • PARAMETER_VALUES: uma lista de valores de parâmetros a serem transmitidos.
    • Essa lista precisa ter o mesmo tamanho da lista param_names, em que a ordem dos valores corresponde à ordem dos nomes.
    • O tipo exato dos valores é inferido da consulta e da definição de visualização parametrizada. As conversões de tipo são realizadas quando necessário e possível para o valor de parâmetro especificado. Em caso de incompatibilidade de tipo, um erro é gerado.

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.

Use o exemplo a seguir para consultar uma visualização segura parametrizada:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

O uso dessa API limita o tamanho do conjunto de resultados pelo tamanho expresso em kilobytes (kB) dos resultados 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.

Instrução PREPARE

Use o comando PREPARE .. AS RESTRICTED para criar uma instrução preparada que faça referência a visualizações parametrizadas. Essas instruções preparadas oferecem suporte a parâmetros posicionais e aplicam várias restrições quando você as executa. Para mais informações, consulte Mecanismo de segurança.

Esse recurso estende os comandos PREPARE e EXECUTE para oferecer suporte a parâmetros de visualização nomeados. Use instruções preparadas para evitar a sobrecarga de análise, análise e reescrita sempre que a instrução for executada, o que pode resultar em ganhos de desempenho significativos, especialmente para consultas complexas ou executadas com frequência. Uma instrução preparada é um objeto do lado do servidor que pode otimizar o desempenho pré-compilando e armazenando uma instrução SQL parametrizada para execução posterior.

Essa API tem limitações porque a instrução precisa ser permitida em uma instrução PREPARE, o que significa que apenas as instruções SELECT e VALUES são aceitas.

Essa API também não restringe os resultados por tamanho ou número de linhas retornadas.

Para criar uma instrução preparada que faça referência a visualizações parametrizadas, execute o comando PREPARE .. AS RESTRICTED:

PREPARE pquery (/POSITIONAL_PARAM_TYPES/)
        AS RESTRICTED query % a query that may refer to parameterized views
EXECUTE pquery (/POSITIONAL_PARAM_VALUES/)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Substitua:

  • POSITIONAL_PARAM_TYPES: um ou mais parâmetros posicionais usados na consulta RESTRICTED.
  • POSITIONAL_PARAM_VALUES: os valores reais que são substituídos pelos parâmetros posicionais definidos na instrução PREPARE.
  • VIEW_PARAM_NAME: o nome do parâmetro esperado pelas visualizações parametrizadas referenciadas na consulta RESTRICTED.
  • VIEW_PARAM_VALUE: os valores reais transmitidos aos parâmetros viewParamName correspondentes das visualizações parametrizadas.

Para incluir parâmetros em uma instrução preparada, forneça uma lista de tipos de dados na instrução PREPARE. Na instrução que você prepara, faça referência aos parâmetros por posição usando, por exemplo, $1 e $2.

Use o comando EXECUTE .. WITH VIEW PARAMETERS para executar uma instrução preparada anteriormente que você criou usando o comando PREPARE .. AS RESTRICTED. Se a instrução PREPARE que criou a instrução especificou parâmetros posicionais, transmita um conjunto compatível de parâmetros para a instrução EXECUTE. Transmita todos os parâmetros de visualização nomeados exigidos por visualizações parametrizadas na cláusula WITH VIEW PARAMETERS.

Use o exemplo a seguir para consultar uma visualização segura parametrizada:

PREPARE pquery (timestamp) AS RESTRICTED SELECT * FROM secure_checked_items WHERE timestamp > $1;

EXECUTE pquery (current_date - 1) WITH VIEW PARAMETERS (app_end_userid = 40);
EXECUTE pquery (current_date - 30) WITH VIEW PARAMETERS (app_end_userid = 40);

Restrições aplicadas a consultas

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

  • Qualquer invocação recursiva de APIs (execute_parameterized_query ou usando EXECUTE .. WITH VIEW PARAMETERS) é proibida, para que apenas os valores especificados pelo aplicativo sejam usados. Essa restrição também impede que a consulta seja usada para contornar o envelope de segurança do conjunto de valores de parâmetros especificado.
  • Algumas extensões que iniciam uma nova sessão em segundo plano não são permitidas, incluindo as extensões dblink, pg_cron e pg_background.

  • A lista a seguir mostra o conjunto de construções de consulta permitidas que são restritas:

    • Instruções SELECT somente leitura são permitidas.
    • Instruções SHOW, CALL e DO somente leitura são permitidas.
    • Instruções DML, como INSERT, UPDATE e DELETE, não são permitidas.
    • Instruções DDL, como CREATE TABLE e ALTER TABLE, não são permitidas.
    • Outros tipos de instrução, como LOAD, SET, CLUSTER, LOCK, CHECKPOINT e EXPLAIN, não são permitidos.

  • As instruções EXPLAIN não são permitidas para evitar a possibilidade de ataques de canal encoberto usando planos de consulta. Para mais informações, consulte Canal encoberto.

  • As visualizações seguras parametrizadas fornecem configurações para ajudar a gerenciar recursos usados pelas APIs para consultar visualizações parametrizadas, como parameterized_views.statement_timeout. Para mais informações, consulte Sinalizações do AlloyDB para PostgreSQL.

Listar todas as visualizações parametrizadas

Use a extensão parameterized_views para listar todas as visualizações parametrizadas no banco de dados usando a visualização all_parameterized_views. A saída dessa visualização é a mesma da pg_views visualização de catálogo, mas all_parameterized_views lista apenas visualizações com parâmetros de visualização nomeados.

Para listar visualizações parametrizadas, use o exemplo a seguir:

postgres=# select * from parameterized_views.all_parameterized_views ;
schemaname |      viewname      | viewowner |                       definition
-----------+--------------------+-----------+---------------------------------------------------------
public     | checked_items_view | postgres  |  SELECT checked_items.bag_id,                          +
           |                    |           |     checked_items."timestamp",                         +
           |                    |           |     checked_items.location                             +
           |                    |           |    FROM checked_items                                  +
           |                    |           |   WHERE (checked_items.customer_id = $@app_end_userid);

Para listar uma visualização parametrizada em all_parameterized_views, verifique se a visualização parametrizada contém pelo menos um parâmetro de visualização nomeado na definição.

A seguir