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:
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.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:
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 quechecked_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áusulaWHERE
usandoCOLUMN = $@PARAMETER_NAME
. $@PARAMETER_NAME
indica um parâmetro de visualização com nome. O valor é fornecido quando usa a APIexecute_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
a9
). - 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
.
- Crie a vista através da opção
Conceda
SELECT
na vista a qualquer utilizador da base de dados que tenha autorização para consultar a vista.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:
- 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.
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.
- Conceda as novas autorizações de funções às vistas seguras, que normalmente incluem privilégios
SELECT
nas vistas eUSAGE
nos esquemas. - 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.
- Conceda as novas autorizações de funções às vistas seguras, que normalmente incluem privilégios
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ãoparameterized_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áusulaFROM
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.
- Esta lista tem de ter o mesmo tamanho que a lista
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áusulaFROM
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 listaparam_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 SCROLL
WITHOUT 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 consultaRESTRICTED
.POSITIONAL_PARAM_VALUES
: os valores reais que são substituídos pelos parâmetros posicionais definidos na declaraçãoPREPARE
.VIEW_PARAM_NAME
: o nome do parâmetro esperado pelas vistas parametrizadas referenciadas na consultaRESTRICTED
.VIEW_PARAM_VALUE
: os valores reais que estão a ser transmitidos aos parâmetrosviewParamName
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 deEXECUTE .. 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
epg_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çõesCALL
e declaraçõesDO
. - As declarações DML, como
INSERT
,UPDATE
eDELETE
, não são permitidas. - As declarações DDL, como
CREATE TABLE
eALTER TABLE
, não são permitidas. - Outros tipos de declarações, como
LOAD
,SET
,CLUSTER
,LOCK
,CHECKPOINT
eEXPLAIN
, não são permitidos.
- São permitidas declarações
- 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?
- Saiba mais sobre as vistas seguras parametrizadas.
- Saiba como proteger e controlar o acesso aos dados das aplicações através de vistas seguras parametrizadas.