Administra la seguridad de los datos de las aplicaciones con vistas seguras parametrizadas de AlloyDB Omni

Selecciona una versión de la documentación:

Puedes usar vistas seguras con parámetros en AlloyDB Omni para limitar el acceso a los datos en función de los 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

La compatibilidad con las vistas con parámetros de AlloyDB AI se proporciona a través de parameterized_views, que es una extensión de AlloyDB para PostgreSQL.

En esta página, se supone que instalaste AlloyDB Omni. Consulta Instala AlloyDB Omni (para contenedores, para Kubernetes).

Antes de usar vistas seguras con parámetros, debes hacer lo siguiente una vez en cada contenedor postgres nuevo. Cada configuración se puede aplicar con ALTER SYSTEM o editando postgresql.conf directamente.

  1. Agrega parameterized_views a shared_preload_libraries.
  2. Para habilitar la función, configura parameterized_views.enabled=on.

  3. Reinicia el servidor de PostgreSQL para que los cambios surtan efecto.

    -- 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. Usa psql para crear la parameterized_views extensión en cualquier base de datos en la que desees 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:

  1. Ejecuta el CREATE VIEW DDL comando, 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 de acceder, agrega 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 WHERE cláusula 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 signos diacríticos 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.

  2. Otorga SELECT en la vista a cualquier usuario de la base de datos que tenga permiso para consultar la vista.

  3. 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:

  1. Crea las vistas seguras con parámetros como usuario administrador. Este usuario es un usuario de la base de datos de AlloyDB Omni que realiza operaciones administrativas para la aplicación, incluida la configuración de la base de datos y la administración de seguridad.
  2. Crea un nuevo rol de base de datos para ejecutar consultas en vistas seguras con parámetros. Este es un rol de base de datos de AlloyDB Omni que la aplicación usa para conectarse y acceder a la base de datos, y para ejecutar consultas en vistas con parámetros.
  3. Otorga permisos de rol nuevos a las vistas seguras, que suelen incluir privilegios SELECT para las vistas y USAGE en los esquemas.
  4. 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.
  5. 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.

Consulta una vista segura con parámetros

Para consultar una vista segura con parámetros, usa una de las siguientes opciones que mejor admita 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 desees 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 cadenas.
  • 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 arroja 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.

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

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

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 comandos PREPARE y EXECUTE 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 sentencia 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 sustituyen 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 previamente 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 compatible de parámetros 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 con EXECUTE .. WITH VIEW PARAMETERS), de modo 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, CALL y DO de solo lectura.
    • 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.

  • No se permiten las declaraciones EXPLAIN para evitar la posibilidad de ataques de canales encubiertos con planes de consulta. Para obtener más información, consulta Canal encubierto.

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

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 pg_views vista de 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?