Gerenciar a segurança dos dados do aplicativo usando visualizações seguras parametrizadas do AlloyDB

As visualizações seguras parametrizadas no AlloyDB para PostgreSQL permitem restringir o acesso a linhas com base em parâmetros nomeados específicos do aplicativo, como credenciais de usuário do aplicativo. Essas visualizações estendem as visualizações do PostgreSQL para proteger seu banco de dados ao executar consultas não confiáveis, aplicando automaticamente restrições a cada consulta.

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

Nesta página, presumimos que você criou um cluster e uma instância do AlloyDB. Para mais informações, consulte Criar um banco de dados.

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

  1. Ative a flag de banco de dados parameterized_views.enabled, que carrega as bibliotecas de extensão necessárias. É preciso ativar essa flag, mesmo que ela tenha sido ativada anteriormente pela equipe do AlloyDB. Para mais informações sobre como ativar a flag de banco de dados, consulte Configurar as flags de banco de dados de uma instância.

  2. Use o AlloyDB Studio ou 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 à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 forma 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 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 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 que o aplicativo usa para se conectar e fazer login no banco de dados e executar consultas em visualizações parametrizadas.

    1. 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.

    2. 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.

    3. Para reduzir os riscos de segurança, revogue o acesso desse papel em esquemas ou objetos sensíveis que não sejam estritamente necessários para o papel.

    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.

    Para mais informações sobre como criar usuários de banco de dados, consulte Criar um usuário de banco de dados.

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âmetro diferentes.

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

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

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 as sinalizações 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 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 cria e retorna um CURSOR com escopo de transação que você usa para recuperar os resultados da consulta:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    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.

  • CURSOR_NAME: o nome do cursor a ser declarado.

  • 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.

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

  -- Start a transaction, which is the default lifetime of a CURSOR
  BEGIN;
  -- Create a cursor called 'mycursor'
  SELECT * FROM parameterized_views.execute_parameterized_query(
   query => 'SELECT * FROM secure_checked_items',
   cursor_name => 'mycursor',
   param_names => ARRAY ['app_end_userid'],
   param_values => ARRAY ['40']
  );

  -- Fetch the results
  FETCH ALL FROM mycursor;
  -- End the transaction, which cleans up the cursor
  END;

O cursor retornado é um cursor NO SCROLL WITHOUT HOLD. Não é possível usar o cursor para recuperar linhas de maneira não sequencial, por exemplo, na direção inversa. Não é possível usar o cursor fora da transação que o criou.

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 são compatíveis com parâmetros posicionais e aplicam várias restrições quando você as executa. Para mais informações sobre como as visualizações seguras parametrizadas aplicam a segurança, consulte Mecanismo de segurança.

Esse recurso estende os comandos PREPARE e EXECUTE para aceitar 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 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 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 SQL_QUERY;
EXECUTE pquery (POSITIONAL_PARAM_VALUES)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Substitua:

  • POSITIONAL_PARAM_TYPES: um ou mais tipos de dados de parâmetro posicional usados na consulta RESTRICTED.

  • SQL_QUERY: uma consulta SQL que pode se referir a visualizações parametrizadas.

  • 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 VIEW_PARAM_NAME 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 com as opções descritas em Consultar uma visualização segura parametrizada:

  • Qualquer invocação recursiva de qualquer API, execute_parameterized_query ou usando EXECUTE .. WITH VIEW PARAMETERS, é proibida, para que somente 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.

  • As seguintes construções de consulta são permitidas ou 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 são proibidas por padrão para evitar a possibilidade de ataques de canal oculto usando planos de consulta. Para mais informações, consulte Canal oculto. No entanto, para executar instruções EXPLAIN, qualquer usuário do banco de dados com privilégios de superusuário do AlloyDB pode definir o GUC parameterized_views.enable_explain no nível da sessão como on.

  • 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 de banco de dados compatíveis.

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 as visualizações parametrizadas, execute a seguinte consulta:

SELECT * FROM parameterized_views.all_parameterized_views;

O resultado será o seguinte:

schemaname |       viewname       | viewowner |                       definition
-----------+----------------------+-----------+---------------------------------------------------------
public     | secure_checked_items | 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