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

Selecione uma versão da documentação:

Pode usar vistas seguras parametrizadas no AlloyDB Omni para limitar o acesso aos dados com base em parâmetros nomeados específicos da aplicação, como credenciais de utilizador da aplicação. As vistas seguras parametrizadas melhoram a segurança e o controlo de acesso através da extensão da 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 a vista segura parametrizada vista geral e proteja e controle o acesso aos dados da aplicação através de vistas seguras parametrizadas.

Antes de começar

O suporte de vistas parametrizadas do AlloyDB AI é fornecido através do parameterized_views, que é uma extensão do AlloyDB para PostgreSQL.

Esta página pressupõe que instalou o AlloyDB Omni. Consulte Instalar o AlloyDB Omni (para contentores, para Kubernetes).

Antes de usar visualizações seguras parametrizadas, tem de fazer o seguinte uma vez em cada novo contentor do Postgres. Cada definição pode ser aplicada através de ALTER SYSTEM ou da edição direta do postgresql.conf.

  1. Adicione parameterized_views a shared_preload_libraries.
  2. Ative a funcionalidade definindo parameterized_views.enabled=on.

  3. Reinicie o servidor PostgreSQL para que as alterações 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 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. A vista limita os resultados a linhas onde 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 autorizaçã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 com 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 com uma letra (a-z).
      • Pode usar letras com sinais diacríticos e letras não latinas, e pode usar um sublinhado (_).
      • Os carateres subsequentes podem ser letras, sublinhados ou dígitos (0-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 visualização a qualquer utilizador da base de dados que tenha autorização para consultar a visualização.

  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 Omni que realiza operações administrativas para a aplicação, incluindo a configuração da base de dados e a administração de segurança.
  2. Crie uma nova função de base de dados para executar consultas em vistas seguras parametrizadas. Esta é uma função da base de dados do AlloyDB Omni que a aplicação usa para estabelecer ligação e iniciar sessão na base de dados, e para executar consultas em vistas parametrizadas.
  3. Conceda as autorizações da nova função às vistas seguras, que normalmente incluem SELECT privilégios às vistas e USAGE nos esquemas.
  4. 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.
  5. 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.

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 tal, a consulta tem de ser compatível com os cursores do 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 usando parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

Esta API tem limitações porque declara um cursor para a consulta especificada. Como tal, a consulta tem de ser compatível com os cursores do 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 usando parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

CURSOR API

Esta API tem limitações porque declara um cursor para a consulta especificada. Como tal, a consulta tem de ser compatível com os cursores do 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 usando parameterized_views.json_results_max_size e parameterized_views.json_results_max_rows.

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 mecanismo de segurança.

Esta funcionalidade expande os comandos PREPARE e EXECUTE para suportar parâmetros de visualização denominados. Use declarações preparadas para evitar a sobrecarga da análise, da análise e da reescrita sempre 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 pré-compilando e armazenando 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 visualizações 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 seguinte lista apresenta o conjunto de construções de consultas permitidas que estão restritas:

    • São permitidas declarações SELECT só de leitura.
    • As declarações SHOW só de leitura, as declarações CALL e as declarações DO são permitidas.
    • Não são permitidas declarações DML, como INSERT, UPDATE e DELETE.
    • As declarações DDL, como CREATE TABLE e ALTER TABLE, não são permitidas.
    • Não são permitidos outros tipos de declarações, como LOAD, SET, CLUSTER, LOCK, CHECKPOINT e EXPLAIN.

  • As declarações EXPLAIN não são permitidas para evitar a possibilidade de ataques de canal oculto através de planos de consulta. Para mais informações, consulte Canal oculto.

  • 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 do AlloyDB para PostgreSQL.

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 visualização é o mesmo que o da visualização do catálogo pg_views, mas all_parameterized_views apenas apresenta visualizações com parâmetros de visualização com nome.

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?