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

Las vistas seguras parametrizadas en AlloyDB para PostgreSQL te permiten restringir el acceso a las filas según los parámetros con nombre específicos de la aplicación, como las credenciales de usuario de la aplicación. Estas vistas extienden las vistas de PostgreSQL para proteger tu base de datos cuando se ejecutan consultas no confiables, ya que aplican restricciones automáticamente en cada consulta.

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

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 parametrizadas, debes hacer lo siguiente:

  1. Habilita la marca de base de datos parameterized_views.enabled, que carga las bibliotecas de extensiones 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.

  2. Usa AlloyDB Studio o 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 estén contenidas en el espacio de nombres de ese esquema y para que no entren en conflicto con las APIs existentes.

Crea una vista segura parametrizada

Para crear una vista segura parametrizada, sigue estos pasos:

  1. Ejecuta el comando de DDL CREATE VIEW, 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 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 acceso, 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.

  2. Otorga SELECT en la vista a cualquier usuario de la base de datos que tenga permitido 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 parametrizadas.

Configura la seguridad de tu aplicación

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

  1. Crea las vistas parametrizadas seguras como un 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 la seguridad.

  2. Crea un rol de base de datos nuevo para ejecutar consultas en vistas seguras parametrizadas. 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 parametrizadas.

    1. Otorga los permisos del rol nuevo 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 necesario de funciones y objetos públicos que requiere 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 en cualquier esquema u 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 requeridos, que están vinculados a la identidad del usuario de la aplicación.

    Para obtener más información sobre cómo crear usuarios de bases de datos, consulta Crea un usuario de base de datos.

Consulta una vista segura parametrizada

Para consultar una vista segura parametrizada, 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 devolver filas JSON.

  • Basada en CURSOR: Usa esta API cuando tengas consultas de mayor duración 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.

  • Sentencia PREPARE EXECUTE: Úsala para sentencias 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

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: Es una consulta en SQL cuya cláusula FROM hace referencia a una o más vistas seguras parametrizadas.

  • PARAMETER_NAMES: Es una lista de nombres de parámetros que se pasarán como strings.

  • PARAMETER_VALUES: Es una lista de valores de parámetros que se deben 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 la vista parametrizada. 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 devuelve una tabla de objetos JSON. Cada fila de la tabla equivale al valor ROW_TO_JSON() de la fila del resultado 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 según el tamaño expresado en kilobytes (kB) de los resultados y la cantidad de filas. Puedes configurar estos límites con las marcas 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 las declaraciones DO ni SHOW.

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

Ejecuta la función execute_parameterized_query(), que crea y devuelve 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: Es una consulta en SQL cuya cláusula FROM hace referencia a una o más vistas seguras parametrizadas.

  • CURSOR_NAME: Es el nombre del cursor que se declarará.

  • PARAMETER_NAMES: Es una lista de nombres de parámetros que se pasarán como strings.

  • PARAMETER_VALUES: Es una lista de valores de parámetros que se deben 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 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 determinado. En caso de que no coincidan los tipos, se arroja un error.

Usa el siguiente ejemplo para consultar una vista segura parametrizada:

  -- Start a transaction, which 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']
  );

  -- Fetch the results
  FETCH ALL FROM mycursor;
  -- End the transaction, which cleans 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 hacia atrás. 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 sentencia preparada que haga referencia a vistas parametrizadas. Estas sentencias preparadas admiten parámetros posicionales y aplican varias restricciones cuando las ejecutas. Para obtener más información sobre cómo las vistas seguras parametrizadas aplican la seguridad, consulta Mecanismo de seguridad.

Esta función extiende los comandos PREPARE y EXECUTE para admitir parámetros de vista con nombre. Usa sentencias preparadas para evitar la sobrecarga de analizar, analizar y reescribir cada vez que se ejecuta la sentencia, lo que puede generar mejoras significativas en el rendimiento, 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 compilando previamente y almacenando una instrucción de SQL parametrizada para su ejecución posterior.

Esta API tiene limitaciones porque la sentencia debe permitirse en una sentencia PREPARE, lo que significa que solo se admiten las sentencias SELECT y VALUES.

Esta API tampoco restringe los resultados por tamaño ni por cantidad de filas devueltas.

Para crear una sentencia preparada que haga referencia a vistas parametrizadas, ejecuta el comando PREPARE .. AS RESTRICTED:

PREPARE pquery (POSITIONAL_PARAM_TYPES)
        AS RESTRICTED SQL_QUERY;
EXECUTE pquery (POSITIONAL_PARAM_VALUES)
      WITH VIEW PARAMETERS (VIEW_PARAM_NAME1 = VIEW_PARAM_VALUE1[, ...]);

Reemplaza lo siguiente:

  • POSITIONAL_PARAM_TYPES: Es uno o más tipos de datos de parámetros posicionales que se usan en la consulta RESTRICTED.

  • SQL_QUERY: Es una consulta en SQL que puede hacer referencia a vistas parametrizadas.

  • POSITIONAL_PARAM_VALUES: Son los valores reales que se sustituyen por los parámetros posicionales definidos en la sentencia PREPARE.

  • VIEW_PARAM_NAME: Es el nombre del parámetro que esperan las vistas parametrizadas a las que se hace referencia en la consulta RESTRICTED.

  • VIEW_PARAM_VALUE: Son los valores reales que se pasan a los parámetros VIEW_PARAM_NAME correspondientes de las vistas parametrizadas.

Para incluir parámetros en una sentencia preparada, debes proporcionar una lista de tipos de datos en la sentencia PREPARE. En la sentencia 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 sentencia preparada anteriormente que creaste con el comando PREPARE .. AS RESTRICTED. Si la sentencia PREPARE que creó la sentencia especificó parámetros posicionales, debes pasar un conjunto de parámetros compatible a la sentencia EXECUTE. Debes pasar cualquier parámetro 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 aplicadas a las consultas

A continuación, se muestra el conjunto de operaciones restringidas para las consultas que ejecutas con las opciones descritas en Consulta una vista segura parametrizada:

  • Se prohíbe cualquier invocación recursiva de cualquier API, ya sea execute_parameterized_query o mediante 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 entorno de seguridad del conjunto de valores de parámetros determinado.

  • No se permiten algunas extensiones que inician una nueva sesión en segundo plano, incluidas las extensiones dblink, pg_cron y pg_background.

  • Se permiten o restringen las siguientes construcciones de consultas:

    • 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 sentencias DML, como INSERT, UPDATE y DELETE.

    • No se permiten las declaraciones de 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 se permiten de forma predeterminada para evitar la posibilidad de ataques de canal encubierto 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 parameterized_views.enable_explain a nivel de la sesión en on.

  • Las vistas seguras parametrizadas proporcionan parámetros de configuración que te ayudan a administrar los recursos que usan las APIs para consultar vistas parametrizadas, como parameterized_views.statement_timeout. Para obtener más información, consulta Marcas de base de datos compatibles.

Enumera todas las vistas parametrizadas

Usa la extensión parameterized_views para enumerar todas las vistas parametrizadas en la base de datos con 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 enumera las vistas con parámetros de vista con nombre.

Para enumerar las vistas parametrizadas, ejecuta la siguiente consulta:

SELECT * FROM parameterized_views.all_parameterized_views;

El resultado es similar a lo siguiente:

schemaname |       viewname       | viewowner |                       definition
-----------+----------------------+-----------+---------------------------------------------------------
public     | secure_checked_items | 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úrate de que contenga al menos un parámetro de vista con nombre en su definición.

¿Qué sigue?