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 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 restrições a qualquer consulta executada.

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

Antes de começar

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

Nesta página, presumimos que você já instalou o AlloyDB Omni. Consulte Instalar o AlloyDB Omni (para contêineres, para Kubernetes).

Antes de usar visualizações seguras parametrizadas, faça o seguinte uma vez em cada novo contêiner do Postgres. Cada configuração pode ser aplicada usando ALTER SYSTEM ou editando o 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 o psql para criar a extensão parameterized_views 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 ver 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 vista nomeada têm os seguintes requisitos:
      • Os parâmetros de visualização nomeada precisam começar com uma letra (a-z).
      • É possível usar letras com marcas diacríticas e não latinas, além de um sublinhado (_).
      • Os caracteres seguintes podem ser letras, sublinhados ou dígitos (0-9).
      • Os parâmetros de visualização nomeada não podem conter $.
      • Os parâmetros de visualização nomeada diferenciam maiúsculas de minúsculas. Por exemplo, $@PARAMETER_NAME é interpretado de forma diferente de $@parameter_name.

  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 as visualizações parametrizadas seguras 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 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 AlloyDB Omni que o aplicativo usa para se conectar e fazer login no banco de dados, além de executar consultas em visualizações parametrizadas.
  3. Conceda as permissões do novo papel às visualizações seguras, que geralmente incluem privilégios SELECT nas visualizações e USAGE nos esquemas.
  4. 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.
  5. 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.

Consultar uma visualização segura parametrizada

Para consultar uma visualização segura parametrizada, use uma das seguintes opções que melhor atenda ao seu caso de uso:

  • 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. A função execute_parameterized_query fornecida pela extensão parameterized_views aceita um nome de cursor.
  • Instrução PREPARE EXECUTE: use isso 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 cursores do PostgreSQL. Por exemplo, a API CURSOR não é compatível com 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 quando possível para o valor de parâmetro especificado. Em caso de uma 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 cursores do PostgreSQL. Por exemplo, a API CURSOR não é compatível com 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 quando possível para o valor de parâmetro especificado. Em caso de incompatibilidade de tipos, 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 cursores do PostgreSQL. Por exemplo, a API CURSOR não é compatível com 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 quando possível para o valor de parâmetro especificado. Em caso de uma 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 referencia visualizações parametrizadas. Essas instruções preparadas oferecem suporte a parâmetros posicionais e impõem várias restrições quando são executadas. 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 nomeada. Use instruções preparadas para evitar o trabalho de analisar, analisar e reescrever cada vez que a instrução é executada, o que pode resultar em ganhos significativos de desempenho, especialmente para consultas executadas com frequência ou complexas. 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 compatíveis.

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 preparada, você se refere 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 criada anteriormente com 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 à instrução EXECUTE. Você precisa transmitir todos os parâmetros de visualização nomeados exigidos pelas 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 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:

  • É proibida qualquer invocação recursiva de APIs, seja execute_parameterized_query ou usando EXECUTE .. WITH VIEW PARAMETERS, para que apenas os valores especificados pelo aplicativo sejam usados. Essa restrição também impede que a consulta seja usada para burlar o envelope de segurança do conjunto de valores de parâmetro 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 oculto usando planos de consulta. Para mais informações, consulte Canal oculto.

  • As visualizações seguras parametrizadas oferecem configurações para ajudar você a gerenciar recursos usados pelas APIs para consultar visualizações parametrizadas, como parameterized_views.statement_timeout. Para mais informações, consulte Flags 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 visualização de catálogo pg_views, 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 ela contém pelo menos um parâmetro de visualização nomeado na definição.

A seguir