Administra la seguridad de los datos de la aplicación con vistas seguras parametrizadas de AlloyDB

En esta página, se describe cómo usar vistas seguras con parámetros en AlloyDB para PostgreSQL, que te permiten limitar el acceso a los datos en función de parámetros con nombre específicos de la aplicación, como las credenciales de usuario de la aplicación. Las vistas seguras con parámetros mejoran la seguridad y el control de acceso, ya que extienden la funcionalidad de las vistas de PostgreSQL. Estas vistas también mitigan los riesgos de ejecutar consultas no confiables desde las aplicaciones, ya que aplican automáticamente restricciones en cualquier consulta que se ejecute.

Para obtener más información, consulta Descripción general de las vistas seguras con parámetros y Protege y controla el acceso a los datos de la aplicación con vistas seguras con parámetros.

Antes de comenzar

En esta página, se supone que creaste un clúster y una instancia de AlloyDB. Para obtener más información, consulta Crea una base de datos.

Antes de usar vistas seguras con parámetros, debes hacer lo siguiente:

Habilita la marca de base de datos parameterized_views.enabled, que carga las bibliotecas de extensión requeridas. Debes habilitar esta marca, incluso si el equipo de AlloyDB la habilitó anteriormente. Para obtener más información sobre cómo habilitar la marca de base de datos, consulta Configura las marcas de base de datos de una instancia.

Nota: Después de habilitar la marca de base de datos parameterized_views.enabled, se reinicia la base de datos para que estos cambios surtan efecto.
Usa AlloyDB Studio o psql para crear la extensión parameterized_views en cualquier base de datos en la que quieras crear una vista con parámetros:
```
-- Requires parameterized_views.enabled set to true
CREATE EXTENSION parameterized_views;
```
Cuando se crea la extensión, el sistema también crea un esquema llamado parameterized_views para que las APIs se incluyan en el espacio de nombres de ese esquema y no entren en conflicto con las APIs existentes.

Crea una vista segura con parámetros

Para crear una vista segura con parámetros, sigue estos pasos:

Ejecuta el comando CREATE VIEW DDL, como se muestra en el siguiente ejemplo:
```
CREATE VIEW secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM checked_items t
WHERE customer_id = $@app_end_userid;
```
En el ejemplo anterior, la vista segura con parámetros permite el acceso a tres columnas de una tabla llamada checked_items. La vista limita los resultados a las filas en las que checked_items.customer_id coincide con un parámetro obligatorio.

Usa los siguientes atributos:
- Crea la vista con la opción security_barrier.
- Para restringir a los usuarios de la aplicación de modo que solo puedan ver las filas a las que tienen permiso para acceder, agrega los parámetros obligatorios en la definición de la vista con la sintaxis $@PARAMETER_NAME. Un caso de uso común es verificar el valor de una columna en la cláusula WHERE con COLUMN = $@PARAMETER_NAME.
- $@PARAMETER_NAME indica un parámetro de vista con nombre. Su valor se proporciona cuando usas la API de execute_parameterized_query. Los parámetros de vista con nombre tienen los siguientes requisitos:
  - Los parámetros de vista con nombre deben comenzar con una letra (a-z).
  - Puedes usar letras con marcas diacríticas y letras no latinas, y puedes usar un guion bajo (_).
  - Los caracteres que le siguen pueden ser letras, guiones bajos o dígitos (0-9).
  - Los parámetros de vista con nombre no pueden contener $.
  - Los parámetros de vista con nombre distinguen mayúsculas de minúsculas. Por ejemplo, $@PARAMETER_NAME se interpreta de manera diferente que $@parameter_name.
Otorga SELECT en la vista a cualquier usuario de la base de datos que tenga permiso para consultar la vista.
Otorga USAGE en el esquema que contiene las tablas definidas en la vista a cualquier usuario de la base de datos que tenga permiso para consultar la vista.

Para obtener más información, consulta Protege y controla el acceso a los datos de la aplicación con vistas seguras con parámetros.

Configura la seguridad de tu aplicación

Para configurar la seguridad de tus aplicaciones con vistas seguras con parámetros, sigue estos pasos:

Crea las vistas seguras con parámetros como usuario administrador. Este usuario es un usuario de la base de datos de AlloyDB que realiza operaciones administrativas para la aplicación, incluida la configuración de la base de datos y la administración de seguridad.
Crea un rol de base de datos nuevo para ejecutar consultas en vistas seguras con parámetros. Este es un rol de base de datos de AlloyDB que la aplicación usa para conectarse y acceder a la base de datos, y para ejecutar consultas en vistas con parámetros.
1. Otorga permisos de rol nuevos a las vistas seguras, que suelen incluir privilegios SELECT para las vistas y USAGE en los esquemas.
2. Limita los objetos a los que puede acceder este rol al conjunto mínimo requerido de funciones y objetos públicos que necesita la aplicación. Evita proporcionar acceso a esquemas y tablas que no sean públicos.
3. Para mitigar los riesgos de seguridad, revoca el acceso de este rol a cualquier esquema o objeto sensible que no sea estrictamente necesario para el rol.
Cuando consultas las vistas, la aplicación proporciona los valores de los parámetros de vista obligatorios, que están vinculados a la identidad del usuario de la aplicación.

Para obtener más información, consulta Crea un usuario de base de datos.

Consulta una vista segura con parámetros

Para consultar una vista segura con parámetros, usa una de las siguientes opciones que mejor se adapte a tu caso de uso:

Basada en JSON: Usa esta API para ejecutar la consulta de una sola vez y mostrar filas JSON.
Basada en CURSOR: Usa esta API cuando tengas consultas de ejecución más larga o cuando tengas consultas grandes y quieras recuperar el resultado en lotes. La función execute_parameterized_query que proporciona la extensión parameterized_views acepta un nombre de cursor.
Declaración PREPARE EXECUTE: Úsala para las declaraciones preparadas que se pueden ejecutar varias veces con diferentes valores de parámetros.

Para consultar vistas seguras con parámetros, usa la función execute_parameterized_query() que proporciona la extensión parameterized_views.

API de JSON

Esta API tiene limitaciones porque declara un cursor para la consulta determinada. Como resultado, la consulta debe ser compatible con los cursores de PostgreSQL. Por ejemplo, la API de CURSOR no admite declaraciones DO ni SHOW.

Esta API tampoco restringe los resultados por tamaño ni por la cantidad de filas que se muestran.

Ejecuta la función execute_parameterized_query(), que tiene la siguiente sintaxis:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
)

Reemplaza lo siguiente:

SQL_QUERY: Una consulta en SQL cuya cláusula FROM hace referencia a una o más vistas seguras con parámetros.
PARAMETER_NAMES: Una lista de nombres de parámetros para pasar como strings.
PARAMETER_VALUES: Una lista de valores de parámetros para pasar.
- Esta lista debe tener el mismo tamaño que la lista param_names, en la que el orden de los valores coincide con el orden de los nombres.
- El tipo exacto de los valores se infiere de la consulta y la definición de vista con parámetros. Las conversiones de tipo se realizan cuando es necesario y cuando es posible para el valor del parámetro determinado. En caso de que no coincidan los tipos, se muestra un error.

La función muestra una tabla de objetos JSON. Cada fila de la tabla equivale al valor ROW_TO_JSON() de la fila de resultado de la consulta original.

Usa el siguiente ejemplo para consultar una vista segura con parámetros:

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => 'SELECT * FROM secure_checked_items',
    param_names => ARRAY ['app_end_userid'],
    param_values => ARRAY ['40']
)

El uso de esta API limita el tamaño del conjunto de resultados por tamaño expresado en kilobytes (kB) de los resultados y por la cantidad de filas. Puedes configurar estos límites con parameterized_views.json_results_max_size y parameterized_views.json_results_max_rows.

API de CURSOR

Ejecuta la función execute_parameterized_query(), que crea y muestra un CURSOR con alcance de transacción que usas para recuperar los resultados de la 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]
)

Reemplaza lo siguiente:

SQL_QUERY: Una consulta en SQL cuya cláusula FROM hace referencia a una o más vistas seguras con parámetros.
CURSOR_NAME: Nombre del cursor que se declarará.
PARAMETER_NAMES: Una lista de nombres de parámetros para pasar como strings.
PARAMETER_VALUES: Una lista de valores de parámetros para pasar. Esta lista debe tener el mismo tamaño que la lista param_names, en la que el orden de los valores coincide con el orden de los nombres. El tipo exacto de los valores se infiere de la consulta y la definición de vista con parámetros. Las conversiones de tipo se realizan cuando es necesario y cuando es posible para el valor del parámetro determinado. En caso de que no coincidan los tipos, se muestra un error.

Usa el siguiente ejemplo para consultar una vista segura con parámetros:

  -- 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;

El cursor que se muestra es un cursor NO SCROLL WITHOUT HOLD. No puedes usar el cursor para recuperar filas de forma no secuencial, por ejemplo, en dirección inversa. No puedes usar el cursor fuera de la transacción que lo creó.

Declaración PREPARE

Usa el comando PREPARE .. AS RESTRICTED para crear una declaración preparada que haga referencia a vistas con parámetros. Estas declaraciones preparadas admiten parámetros posicionales y aplican varias restricciones cuando las ejecutas. Para obtener más información, consulta Mecanismo de seguridad.

Esta función extiende los PREPARE y EXECUTE commands para admitir parámetros de vista con nombre. Usa declaraciones preparadas para evitar la sobrecarga de analizar, analizar y reescribir cada vez que se ejecuta la declaración, lo que puede generar ganancias de rendimiento significativas, en especial para las consultas complejas o que se ejecutan con frecuencia. Una declaración preparada es un objeto del servidor que puede optimizar el rendimiento mediante la compilación previa y el almacenamiento de una instrucción de SQL con parámetros para su ejecución posterior.

Esta API tiene limitaciones porque la declaración debe permitirse en una declaración PREPARE, lo que significa que solo se admiten las declaraciones SELECT y VALUES.

Esta API tampoco restringe los resultados por tamaño ni por la cantidad de filas que se muestran.

Para crear una declaración preparada que haga referencia a vistas con parámetros, ejecuta el 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[, ...]);

Reemplaza lo siguiente:

POSITIONAL_PARAM_TYPES: Uno o más parámetros posicionales que se usan en la consulta RESTRICTED.
POSITIONAL_PARAM_VALUES: Los valores reales que se reemplazan por los parámetros posicionales definidos en la declaración PREPARE.
VIEW_PARAM_NAME: El nombre del parámetro que esperan las vistas con parámetros a las que se hace referencia en la consulta RESTRICTED.
VIEW_PARAM_VALUE: Los valores reales que se pasan a los parámetros viewParamName correspondientes de las vistas con parámetros.

Para incluir parámetros en una declaración preparada, debes proporcionar una lista de tipos de datos en la declaración PREPARE. En la declaración que preparas, haces referencia a los parámetros por posición con, por ejemplo, $1 y $2.

Usa el comando EXECUTE .. WITH VIEW PARAMETERS para ejecutar una declaración preparada anteriormente que creaste con el comando PREPARE .. AS RESTRICTED. Si la declaración PREPARE que creó la declaración especificó parámetros posicionales, debes pasar un conjunto de parámetros compatibles a la declaración EXECUTE. Debes pasar cualquier parámetro de vista con nombre que requieran las vistas con parámetros en la cláusula WITH VIEW PARAMETERS.

Usa el siguiente ejemplo para consultar una vista segura con parámetros:

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

Restricciones aplicadas en las consultas

En la siguiente lista, se muestra el conjunto de operaciones restringidas para las consultas que ejecutas con las opciones descritas en Consulta una vista segura con parámetros:

Se prohíbe cualquier invocación recursiva de cualquier API (execute_parameterized_query o mediante el uso de EXECUTE .. WITH VIEW PARAMETERS) para que solo se usen los valores especificados por la aplicación. Esta restricción también impide que la consulta se use para eludir el sobre de seguridad del conjunto determinado de valores de parámetros.
No se permiten algunas extensiones que inician una nueva sesión en segundo plano, incluidas las extensiones dblink, pg_cron y pg_background.
En la siguiente lista, se muestra el conjunto de construcciones de consultas permitidas que están restringidas:
- Se permiten las declaraciones SELECT de solo lectura.
- Se permiten las declaraciones SHOW de solo lectura, las declaraciones CALL y las declaraciones DO.
- No se permiten las declaraciones DML, como INSERT, UPDATE y DELETE.
- No se permiten las declaraciones DDL, como CREATE TABLE y ALTER TABLE.
- No se permiten otros tipos de declaraciones, como LOAD, SET, CLUSTER, LOCK, CHECKPOINT y EXPLAIN.
Las declaraciones EXPLAIN no están permitidas de forma predeterminada para evitar la posibilidad de ataques de canales encubiertos con planes de consulta. Para obtener más información, consulta Canal encubierto. Sin embargo, para ejecutar declaraciones EXPLAIN, cualquier usuario de la base de datos con privilegios de superusuario de AlloyDB puede establecer el GUC a nivel de sesión parameterized_views.enable_explain en on.
Las vistas seguras con parámetros proporcionan parámetros de configuración para ayudarte a administrar los recursos que usan las APIs para consultar vistas con parámetros, como parameterized_views.statement_timeout. Para obtener más información, consulta Marcas de base de datos compatibles.

Enumera todas las vistas con parámetros

Usa la extensión parameterized_views para enumerar todas las vistas con parámetros de la base de datos con la vista all_parameterized_views El resultado de esta vista es el mismo que el de la vista de pg_views catálogo, pero all_parameterized_views solo enumera las vistas con parámetros de vista con nombre.

Para enumerar vistas con parámetros, usa el siguiente ejemplo:

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 enumerar una vista con parámetros en all_parameterized_views, asegúrate de que la vista con parámetros contenga al menos un parámetro de vista con nombre en su definición.

¿Qué sigue?

Obtén información sobre las vistas seguras con parámetros.
Obtén información para proteger y controlar el acceso a los datos de la aplicación con vistas seguras con parámetros.

Administra la seguridad de los datos de la aplicación con vistas seguras parametrizadas de AlloyDB Organiza tus páginas con colecciones Guarda y categoriza el contenido según tus preferencias.

Antes de comenzar

Crea una vista segura con parámetros

Configura la seguridad de tu aplicación

Consulta una vista segura con parámetros

API de JSON

API de CURSOR

Declaración PREPARE

Restricciones aplicadas en las consultas

Enumera todas las vistas con parámetros

¿Qué sigue?

Administra la seguridad de los datos de la aplicación con vistas seguras parametrizadas de AlloyDB