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 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 Visão geral das visualizações seguras parametrizadas e Proteger e controlar o acesso aos dados do aplicativo usando visualizações seguras parametrizadas.

Antes de começar

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

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

Antes de usar as 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 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 às 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 possam visualizar apenas as linhas que têm permissão para acessar, 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 nomeada precisam começar com uma letra (a-z).
      • É possível usar letras com marcas diacríticas e letras 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 nomeados não podem conter $.
      • Os parâmetros de visualização nomeada 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 para 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 aos dados do aplicativo 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 a configuração do banco de dados e a administração de segurança.
  2. Crie um novo papel 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 e executar consultas em visualizações parametrizadas.
  3. Conceda as novas permissões de 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 sejam públicos.
  5. Quando você consulta 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 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 mais longas ou 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 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 fornecida. Como resultado, a consulta precisa ser compatível com os cursores do PostgreSQL. Por exemplo, a API CURSOR não é compatível com as 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 com uma cláusula FROM que 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âmetro 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 da visualização parametrizada. As conversões de tipo são realizadas quando necessário e quando possível para o valor de parâmetro fornecido. 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 fornecida. Como resultado, a consulta precisa ser compatível com os cursores do PostgreSQL. Por exemplo, a API CURSOR não é compatível com as 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 com uma cláusula FROM que 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âmetro 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 da visualização parametrizada. As conversões de tipo são realizadas quando necessário e quando possível para o valor de parâmetro fornecido. 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 fornecida. Como resultado, a consulta precisa ser compatível com os cursores do PostgreSQL. Por exemplo, a API CURSOR não é compatível com as 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 com uma cláusula FROM que 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âmetro 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 da visualização parametrizada. As conversões de tipo são realizadas quando necessário e quando possível para o valor de parâmetro fornecido. 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 faz referência a visualizações parametrizadas. Essas instruções preparadas são compatíveis com 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 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 de posição 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 que estão sendo transmitidos para os 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, 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 anteriormente que você criou usando o comando PREPARE .. AS RESTRICTED. Se a instrução PREPARE que criou a instrução especificou parâmetros posicionais, é necessário transmitir um conjunto compatível de parâmetros para a instrução EXECUTE. É necessário transmitir 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

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

  • Qualquer invocação recursiva de qualquer API, seja 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 burlar o envelope de segurança do conjunto de valores de parâmetro fornecido.
  • 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.

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

    • Instruções SELECT somente leitura são permitidas.
    • São permitidas instruções SHOW, CALL e DO somente leitura.
    • 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 é igual à 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 seguinte exemplo:

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