Faça a gestão da segurança dos dados das aplicações através de vistas seguras parametrizadas do AlloyDB

Esta página descreve como usar vistas seguras parametrizadas no AlloyDB para PostgreSQL, que lhe permitem limitar o acesso aos dados com base em parâmetros nomeados específicos da aplicação, como as credenciais do utilizador da aplicação. As vistas seguras parametrizadas melhoram a segurança e o controlo de acesso ao expandir a funcionalidade das vistas do PostgreSQL. Estas vistas também mitigam os riscos de executar consultas não fidedignas a partir de aplicações, aplicando automaticamente restrições a qualquer consulta executada.

Para mais informações, consulte o artigo Vista geral das vistas seguras parametrizadas e o artigo Proteja e controle o acesso aos dados da aplicação através de vistas seguras parametrizadas.

Antes de começar

Esta página pressupõe que criou um cluster e uma instância do AlloyDB. Para mais informações, consulte Crie uma base de dados.

Antes de usar visualizações seguras parametrizadas, tem de fazer o seguinte:

  1. Ative a flag da base de dados parameterized_views.enabled, que carrega as bibliotecas de extensões necessárias. Tem de ativar esta flag, mesmo que tenha sido ativada anteriormente pela equipa do AlloyDB. Para mais informações sobre como ativar a flag da base de dados, consulte o artigo Configure as flags da base de dados de uma instância.

  2. Use o AlloyDB Studio ou o psql para criar a extensão parameterized_views em qualquer base de dados onde queira criar uma vista parametrizada:

    -- Requires parameterized_views.enabled set to true
    CREATE EXTENSION parameterized_views;
    

    Quando a extensão é criada, o sistema também cria um esquema denominado parameterized_views para que as APIs estejam contidas no espaço de nomes desse esquema e não entrem em conflito com as APIs existentes.

Crie uma vista segura parametrizada

Para criar uma visualização segura parametrizada, siga estes passos:

  1. Execute o comando DDL CREATE VIEW, conforme mostrado no exemplo seguinte:

    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 vista segura parametrizada permite o acesso a três colunas de uma tabela denominada checked_items. Os limites de visualização restringem os resultados às linhas em que checked_items.customer_id corresponde a um parâmetro obrigatório.

    Use os seguintes atributos:

    • Crie a vista através da opção security_barrier.
    • Para restringir os utilizadores da aplicação de modo que só possam ver as linhas às quais têm permissão de acesso, adicione os parâmetros necessários na definição da vista através da sintaxe $@PARAMETER_NAME. Um exemplo de utilização comum é verificar o valor de uma coluna na cláusula WHERE usando COLUMN = $@PARAMETER_NAME.
    • $@PARAMETER_NAME indica um parâmetro de visualização com nome. O valor é fornecido quando usa a API execute_parameterized_query. Os parâmetros de visualização com nome têm os seguintes requisitos:
      • Os parâmetros de visualização com nome têm de começar por uma letra (a-z).
      • Pode usar letras com sinais diacríticos e letras não latinas, bem como um sublinhado (_).
      • Os carateres subsequentes podem ser letras, sublinhados ou dígitos (0 a 9).
      • Os parâmetros de visualização com nome não podem conter $.
      • Os parâmetros de visualização com nome são sensíveis a maiúsculas e minúsculas. Por exemplo, $@PARAMETER_NAME é interpretado de forma diferente de $@parameter_name.
  2. Conceda SELECT na vista a qualquer utilizador da base de dados que tenha autorização para consultar a vista.

  3. Conceda USAGE no esquema que contém as tabelas definidas na vista a qualquer utilizador da base de dados que possa consultar a vista.

Para mais informações, consulte o artigo Proteja e controle o acesso aos dados da aplicação através de vistas seguras parametrizadas.

Configure a segurança da sua aplicação

Para configurar a segurança das suas aplicações através de vistas seguras parametrizadas, siga estes passos:

  1. Crie as visualizações parametrizadas seguras como utilizador administrativo. Este utilizador é um utilizador da base de dados do AlloyDB que realiza operações administrativas para a aplicação, incluindo a configuração da base de dados e a administração da segurança.
  2. Crie uma nova função de base de dados para executar consultas em visualizações seguras parametrizadas. Esta é uma função da base de dados do AlloyDB que a aplicação usa para estabelecer ligação e iniciar sessão na base de dados, bem como para executar consultas em visualizações parametrizadas.

    1. Conceda as novas autorizações de funções às vistas seguras, que normalmente incluem privilégios SELECT nas vistas e USAGE nos esquemas.
    2. Limite os objetos aos quais esta função pode aceder ao conjunto mínimo necessário de funções e objetos públicos de que a aplicação precisa. Evite conceder acesso a esquemas e tabelas que não sejam públicos.

    Quando consulta as visualizações, a aplicação fornece os valores dos parâmetros de visualização obrigatórios, que estão associados à identidade do utilizador da aplicação.

    Para mais informações, consulte o artigo Crie um utilizador da base de dados.

Consultar uma vista segura parametrizada

Para consultar uma vista segura parametrizada, use uma das seguintes opções que melhor suportam o seu exemplo de utilização:

  • Baseada em JSON: use esta API para executar a consulta de uma só vez e devolver linhas JSON.
  • Com base no CURSOR: use esta API quando tiver consultas de execução mais longa ou quando tiver consultas grandes e quiser obter o resultado em lotes. A função execute_parameterized_query fornecida pela extensão parameterized_views aceita um nome de cursor.
  • PREPARE EXECUTE statement: use isto para declarações preparadas que podem ser executadas várias vezes com valores de parâmetros diferentes.

Para consultar vistas seguras parametrizadas, usa a função execute_parameterized_query() fornecida pela extensão parameterized_views.

API JSON

Esta API tem limitações porque declara um cursor para a consulta especificada. Como resultado, a consulta tem de ser compatível com cursores PostgreSQL. Por exemplo, a API CURSOR não suporta declarações DO nem SHOW.

Esta API também não restringe os resultados por tamanho nem pelo número de linhas devolvidas.

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 o seguinte:

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

A função devolve 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 seguinte para consultar uma vista 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']
)

A utilização desta API limita o tamanho do conjunto de resultados pelo tamanho expresso em kilobytes (kB) dos resultados e pelo número de linhas. Pode configurar estes limites através do parameterized_views.json_results_max_size e do parameterized_views.json_results_max_rows.

CURSOR API

Execute a função execute_parameterized_query(), que cria e devolve um CURSOR ao nível da transação que usa para obter 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 o seguinte:

  • SQL_QUERY: uma consulta SQL cuja cláusula FROM se refere a uma ou mais vistas seguras parametrizadas.
  • CURSOR_NAME: nome do cursor a declarar.
  • PARAMETER_NAMES: uma lista de nomes de parâmetros a transmitir como strings.
  • PARAMETER_VALUES: uma lista de valores de parâmetros a transmitir. Esta lista tem de ter o mesmo tamanho que a lista param_names, em que a ordem dos valores corresponde à ordem dos nomes. O tipo exato dos valores é inferido a partir da consulta e da definição da vista parametrizada. As conversões de tipo são realizadas quando necessário e quando possível para o valor do parâmetro fornecido. Em caso de incompatibilidade de tipos, é gerado um erro.

Use o exemplo seguinte para consultar uma vista segura parametrizada:

  -- start a transaction as the that 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']
  );

  -- then, to actually fetch the results
  FETCH ALL FROM mycursor;
  -- end the transaction, which will clean up the cursor
  END;

O cursor devolvido é um cursor NO SCROLLWITHOUT HOLD. Não pode usar o cursor para obter linhas de forma não sequencial, por exemplo, numa direção inversa. Não pode usar o cursor fora da transação que o criou.

Declaração PREPARE

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

Esta funcionalidade expande os elementos PREPARE e EXECUTE commands para suportar parâmetros de visualização com nome. Use declarações preparadas para evitar a sobrecarga da análise, da análise e da reescrita de cada vez que a declaração é executada, o que pode resultar em ganhos de desempenho significativos, especialmente para consultas executadas com frequência ou complexas. Uma declaração preparada é um objeto do lado do servidor que pode otimizar o desempenho através da pré-compilação e do armazenamento de uma declaração SQL parametrizada para execução posterior.

Esta API tem limitações porque a declaração tem de ser permitida numa declaração PREPARE statement, o que significa que apenas as declarações SELECT e VALUES são suportadas.

Esta API também não restringe os resultados por tamanho nem pelo número de linhas devolvidas.

Para criar uma declaração preparada que faça referência a vistas 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 o seguinte:

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

Para incluir parâmetros numa declaração preparada, fornece uma lista de tipos de dados na declaração PREPARE. Na declaração que preparar, refere-se aos parâmetros por posição usando, por exemplo, $1 e $2.

Use o comando EXECUTE .. WITH VIEW PARAMETERS para executar uma declaração preparada anteriormente que criou com o comando PREPARE .. AS RESTRICTED. Se a declaração PREPARE que criou a declaração especificou parâmetros posicionais, tem de transmitir um conjunto compatível de parâmetros à declaração EXECUTE. Tem de transmitir todos os parâmetros de visualização com nome exigidos pelas visualizações parametrizadas na cláusula WITH VIEW PARAMETERS.

Use o exemplo seguinte para consultar uma vista 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 às consultas

Segue-se a lista do conjunto de operações restritas para consultas que executa usando as opções descritas em Consultar uma vista segura parametrizada:

  • Qualquer invocação recursiva de APIs,execute_parameterized_query, ou através da utilização de EXECUTE .. WITH VIEW PARAMETERS, é proibida, para que apenas sejam utilizados os valores especificados pela aplicação. Esta 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 seguinte indica o conjunto de construções de consultas permitidas que são restritas:
    • São permitidas declarações SELECT só de leitura.
    • São permitidas declarações SHOW só de leitura, declarações CALL e declarações DO.
    • As declarações DML, como INSERT, UPDATE e DELETE, não são permitidas.
    • As declarações DDL, como CREATE TABLE e ALTER TABLE, não são permitidas.
    • Outros tipos de declarações, como LOAD, SET, CLUSTER, LOCK, CHECKPOINT e EXPLAIN, não são permitidos.
  • As declarações EXPLAIN não são permitidas para evitar a possibilidade de ataques de canais encobertos através de planos de consulta. Para mais informações, consulte Canal encoberto.
  • As visualizações seguras parametrizadas fornecem definições para ajudar a gerir os recursos usados pelas APIs para consultar visualizações parametrizadas, como parameterized_views.statement_timeout. Para mais informações, consulte o artigo Flags de base de dados suportadas.

Apresentar todas as visualizações parametrizadas

Use a extensão parameterized_views para listar todas as vistas parametrizadas na base de dados através da vista all_parameterized_views. O resultado desta vista é o mesmo que o da vista de catálogo pg_views, mas all_parameterized_views apenas lista vistas com parâmetros de vista denominados.

Para listar vistas 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 vista parametrizada em all_parameterized_views, certifique-se de que a vista parametrizada contém, pelo menos, um parâmetro de vista com nome na respetiva definição.

O que se segue?