Gestionar 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 parametrizadas en AlloyDB Omni para 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 parametrizadas mejoran la seguridad y el control de acceso al ampliar la funcionalidad de las vistas de PostgreSQL. Estas vistas también mitigan los riesgos de ejecutar consultas no fiables desde aplicaciones, ya que aplican automáticamente restricciones a cualquier consulta que se ejecute.

Para obtener más información, consulta la descripción general de las vistas seguras parametrizadas y el artículo Protege y controla el acceso a los datos de las aplicaciones mediante vistas seguras parametrizadas.

Antes de empezar

La compatibilidad con vistas parametrizadas 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 presupone que has instalado AlloyDB Omni. Consulta la sección sobre cómo instalar AlloyDB Omni (para contenedores y para Kubernetes).

Antes de usar vistas seguras parametrizadas, debes hacer lo siguiente una vez en cada contenedor de Postgres nuevo. Cada ajuste se puede aplicar con ALTER SYSTEM o editando directamente el postgresql.conf.

  1. Añade parameterized_views a shared_preload_libraries.
  2. Habilita la función configurando parameterized_views.enabled=on.

  3. Reinicia el servidor 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 extensión parameterized_views en cualquier base de datos en la que quieras crear una vista parametrizada:

    -- 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 ya existentes.

Crear una vista segura parametrizada

Para crear una vista segura parametrizada, siga estos pasos:

  1. Ejecuta el comando DDL CREATE VIEW, tal 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 parametrizada permite acceder 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. Utilice los siguientes atributos:

    • Crea la vista con la opción security_barrier.
    • Para restringir los usuarios de la aplicación de forma que solo puedan ver las filas a las que tienen acceso, añade los parámetros necesarios en la definición de la vista con la sintaxis $@PARAMETER_NAME. Un caso práctico habitual es comprobar el valor de una columna en la cláusula WHERE mediante COLUMN = $@PARAMETER_NAME.
    • $@PARAMETER_NAME indica un parámetro de vista con nombre. Su valor se proporciona cuando usas la API execute_parameterized_query. Los parámetros de vista con nombre deben cumplir los siguientes requisitos:
      • Los parámetros de las vistas con nombre deben empezar por una letra (de la a a la z).
      • Puedes usar letras con signos diacríticos y letras no latinas, así como el guion bajo (_).
      • Los caracteres posteriores pueden ser letras, guiones bajos o dígitos (0-9).
      • Los parámetros de vista con nombre no pueden contener $.
      • En los parámetros de las vistas con nombre se distingue entre mayúsculas y minúsculas. Por ejemplo, $@PARAMETER_NAME se interpreta de forma diferente a $@parameter_name.

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

  3. Concede 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 Proteger y controlar el acceso a los datos de las aplicaciones mediante vistas seguras parametrizadas.

Configurar la seguridad de tu aplicación

Para configurar la seguridad de tus aplicaciones mediante vistas seguras parametrizadas, sigue estos pasos:

  1. Crea las vistas parametrizadas seguras como usuario administrador. Este usuario es un usuario de la base de datos AlloyDB Omni que realiza operaciones administrativas para la aplicación, como la configuración de la base de datos y la administración de la seguridad.
  2. Crea un 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 e iniciar sesión en la base de datos, así como para ejecutar consultas en vistas parametrizadas.
  3. Concede los permisos del nuevo rol a las vistas seguras, que normalmente incluyen privilegios de SELECT en las vistas y USAGE en los esquemas.
  4. Limita los objetos a los que puede acceder este rol al conjunto mínimo necesario de funciones y objetos públicos que necesita la aplicación. No proporciones acceso a esquemas y tablas que no sean públicos.
  5. Cuando consulta 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.

Consultar una vista segura con parámetros

Para consultar una vista segura parametrizada, usa una de las siguientes opciones que mejor se adapte a tu caso práctico:

  • Basada en JSON: usa esta API para ejecutar la consulta de una sola vez y devolver filas JSON.
  • Basada en CURSOR: usa esta API cuando tengas consultas de larga duración o consultas grandes y quieras obtener el resultado en lotes. La función execute_parameterized_query proporcionada por 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 parametrizadas, se usa la función execute_parameterized_query() proporcionada por la extensión parameterized_views.

API JSON

Esta API tiene limitaciones porque declara un cursor para la consulta dada. Por lo tanto, la consulta debe ser compatible con los cursores de PostgreSQL. Por ejemplo, la API CURSOR no admite las instrucciones DO ni SHOW.

Esta API tampoco restringe los resultados por tamaño ni por el número de filas devueltas.

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

Haz los cambios siguientes:

  • SQL_QUERY: una consulta de SQL cuya cláusula FROM hace referencia a una o varias vistas seguras parametrizadas.
  • PARAMETER_NAMES: una lista de nombres de parámetros que se deben incluir como cadenas.
  • PARAMETER_VALUES: una lista de valores de parámetros que se van a enviar.
    • Esta lista debe tener el mismo tamaño que la lista param_names, y el orden de los valores debe coincidir con el orden de los nombres.
    • El tipo exacto de los valores se deduce de la consulta y de la definición de la vista parametrizada. Las conversiones de tipo se realizan cuando es necesario y cuando es posible para el valor de parámetro dado. En caso de que los tipos no coincidan, se produce un error.

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

Usa el siguiente ejemplo para consultar una 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']
)

El uso de esta API limita el tamaño del conjunto de resultados por el tamaño expresado en kilobytes (KB) de los resultados y por el número de filas. Puede configurar estos límites mediante parameterized_views.json_results_max_size y parameterized_views.json_results_max_rows.

Esta API tiene limitaciones porque declara un cursor para la consulta dada. Por lo tanto, la consulta debe ser compatible con los cursores de PostgreSQL. Por ejemplo, la API CURSOR no admite las instrucciones DO ni SHOW.

Esta API tampoco restringe los resultados por tamaño ni por el número de filas devueltas.

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

Haz los cambios siguientes:

  • SQL_QUERY: una consulta de SQL cuya cláusula FROM hace referencia a una o varias vistas seguras parametrizadas.
  • PARAMETER_NAMES: una lista de nombres de parámetros que se deben enviar como cadenas.
  • PARAMETER_VALUES: una lista de valores de parámetros que se van a enviar.
  • Esta lista debe tener el mismo tamaño que la lista param_names, y el orden de los valores debe coincidir con el orden de los nombres.
  • El tipo exacto de los valores se deduce de la consulta y de la definición de la vista parametrizada. Las conversiones de tipo se realizan cuando es necesario y cuando es posible para el valor del parámetro dado. En caso de que no coincidan los tipos, se produce un error.

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

Usa el siguiente ejemplo para consultar una 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']
)

El uso de esta API limita el tamaño del conjunto de resultados por el tamaño expresado en kilobytes (KB) de los resultados y por el número de filas. Puede configurar estos límites mediante parameterized_views.json_results_max_size y parameterized_views.json_results_max_rows.

API CURSOR

Esta API tiene limitaciones porque declara un cursor para la consulta dada. Por lo tanto, la consulta debe ser compatible con los cursores de PostgreSQL. Por ejemplo, la API CURSOR no admite las instrucciones DO ni SHOW.

Esta API tampoco restringe los resultados por tamaño ni por el número de filas devueltas.

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

Haz los cambios siguientes:

  • SQL_QUERY: una consulta de SQL cuya cláusula FROM hace referencia a una o varias vistas seguras parametrizadas.
  • PARAMETER_NAMES: una lista de nombres de parámetros que se deben incluir como cadenas.
  • PARAMETER_VALUES: una lista de valores de parámetros que se van a enviar.
    • Esta lista debe tener el mismo tamaño que la lista param_names, y el orden de los valores debe coincidir con el orden de los nombres.
    • El tipo exacto de los valores se deduce de la consulta y de la definición de la vista parametrizada. Las conversiones de tipo se realizan cuando es necesario y cuando es posible para el valor de parámetro dado. En caso de que los tipos no coincidan, se produce un error.

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

Usa el siguiente ejemplo para consultar una 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']
)

El uso de esta API limita el tamaño del conjunto de resultados por el tamaño expresado en kilobytes (KB) de los resultados y por el número de filas. Puede configurar estos límites mediante parameterized_views.json_results_max_size y parameterized_views.json_results_max_rows.

Instrucción PREPARE

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

Esta función amplía los comandos PREPARE y EXECUTE para admitir parámetros de vista con nombre. Usa instrucciones preparadas para evitar la sobrecarga de analizar y reescribir cada vez que se ejecute la instrucción, lo que puede suponer un aumento significativo del rendimiento, especialmente en el caso de las consultas complejas o que se ejecutan con frecuencia. Una instrucción preparada es un objeto del lado del servidor que puede optimizar el rendimiento precompilando y almacenando una instrucción SQL parametrizada para ejecutarla más adelante.

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

Esta API tampoco restringe los resultados por tamaño ni por número de filas devueltas.

Para crear una instrucción preparada que haga referencia a vistas parametrizadas, 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[, ...]);

Haz los cambios siguientes:

  • POSITIONAL_PARAM_TYPES: uno o varios 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 instrucción PREPARE.
  • VIEW_PARAM_NAME: el nombre del parámetro que esperan las vistas parametrizadas a las que se hace referencia en la consulta RESTRICTED.
  • VIEW_PARAM_VALUE: los valores reales que se transfieren a los parámetros viewParamName correspondientes de las vistas parametrizadas.

Para incluir parámetros en una instrucción preparada, debe proporcionar una lista de tipos de datos en la instrucción PREPARE. En la instrucción que prepares, harás referencia a los parámetros por posición usando, por ejemplo, $1 y $2.

Usa el comando EXECUTE .. WITH VIEW PARAMETERS para ejecutar una instrucción preparada que hayas creado con el comando PREPARE .. AS RESTRICTED. Si la instrucción PREPARE que creó la instrucción especificó parámetros posicionales, debes pasar un conjunto de parámetros compatibles a la instrucción EXECUTE. Debe transferir los parámetros de vista con nombre que requieran las vistas parametrizadas en la cláusula WITH VIEW PARAMETERS.

Usa el siguiente ejemplo para consultar una 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);
  ```

Restricciones implementadas en las consultas

A continuación, se muestra la lista de operaciones restringidas para las consultas que ejecutas con las opciones descritas en Consultar una vista segura con parámetros:

  • Se prohíbe cualquier invocación recursiva de cualquier API (execute_parameterized_query o mediante 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 utilice para eludir el envoltorio de seguridad del conjunto de valores de parámetros proporcionado.
  • No se permiten algunas extensiones que inician una nueva sesión en segundo plano, como las extensiones dblink, pg_cron y pg_background.

  • A continuación, se muestra la lista de elementos de consulta permitidos que están restringidos:

    • Se permiten las instrucciones SELECT de solo lectura.
    • Se permiten las instrucciones de solo lectura SHOW, CALL y DO.
    • No se permiten las instrucciones DML, como INSERT, UPDATE y DELETE.
    • No se permiten las instrucciones DDL, como CREATE TABLE y ALTER TABLE.
    • No se permiten otros tipos de instrucciones, como LOAD, SET, CLUSTER, LOCK, CHECKPOINT y EXPLAIN.

  • No se permiten las instrucciones EXPLAIN para evitar la posibilidad de ataques de canal encubierto mediante planes de consulta. Para obtener más información, consulta Canal encubierto.

  • Las vistas seguras parametrizadas proporcionan ajustes que le ayudan a gestionar los recursos que usan las APIs para consultar vistas parametrizadas, como parameterized_views.statement_timeout. Para obtener más información, consulta Flags de AlloyDB para PostgreSQL.

Mostrar todas las vistas parametrizadas

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

Para enumerar las vistas parametrizadas, utilice 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 parametrizada en all_parameterized_views, asegúrese de que la vista parametrizada contenga al menos un parámetro de vista con nombre en su definición.

Siguientes pasos