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

Vista previa

Esta función está sujeta a las "Condiciones de la oferta de la fase previa a la DG" en la sección Condiciones Generales del Servicio de las Condiciones Específicas del Servicio. Las funciones de la fase previa a la DG están disponibles "sin modificaciones" y pueden tener asistencia limitada. Para obtener más información, consulta las descripciones de la etapa de lanzamiento.

Para obtener más información sobre el acceso a esta versión, consulta la página de solicitud de acceso.

En este documento, se describe cómo usar vistas seguras con parámetros en AlloyDB para PostgreSQL, que te permiten limitar el acceso a los datos según 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, 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 una serie de 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 instructivo sobre las vistas seguras parametrizadas.

Antes de comenzar

En este documento, 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 que puedas usar vistas seguras parametrizadas, debes hacer lo siguiente:

1. Solicita acceso a las vistas seguras con parámetros y espera a recibir la confirmación de habilitación antes de comenzar.

2. Espera a que el equipo de AlloyDB habilite la marca de base de datos parameterized_views.enabled, que carga las bibliotecas de extensión necesarias. Esta marca de base de datos debe estar habilitada antes de que puedas comenzar.

   Después de que el equipo de AlloyDB habilite la marca de base de datos parameterized_views.enabled, tu base de datos se reiniciará para que se apliquen estos cambios.

3. Usa AlloyDB Studio o psql para crear la extensión parameterized_views en cualquier base de datos en la que se cree 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 existentes.

Crea una vista segura con parámetros

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

1. Ejecuta el comando 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 con parámetros permite el acceso a tres columnas de una tabla llamada /users/checked_items/. La vista limita los resultados a las filas en las que /users.id/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 acceso, agrega los parámetros obligatorios con la sintaxis $@PARAMETER_NAME en la cláusula WHERE. Un caso de uso común es verificar el valor de una columna con WHERE 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 deben cumplir con los siguientes requisitos:
     • Los parámetros de la vista con nombre deben comenzar con una letra (de la a a la 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 a 9).
     • Los parámetros de vista con nombre no pueden contener $.
     • Los parámetros de vistas 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 pueda consultar la vista.

3. Otorga el permiso 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 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 nuevo rol de base de datos 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 protegidas, que suelen incluir privilegios de 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.

   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 parametrizada

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 búsqueda de una sola vez y devolver filas en formato 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

Esta API tiene limitaciones porque declara un cursor para la búsqueda determinada. Como resultado, la consulta debe ser compatible con los cursores de PostgreSQL. Por ejemplo, la API de CURSOR no admite instrucciones 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: Es una consulta en SQL cuya cláusula FROM hace referencia a una o más vistas seguras con parámetros.
• PARAMETER_NAMES: Es una lista de nombres de parámetros que se pasarán como cadenas.
• 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 de 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 haya una discrepancia de tipo, 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 búsqueda 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 según el tamaño expresado en kilobytes (kB) de los resultados y 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 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 cadenas.
• 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 de 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 discrepancia de tipos, se arroja 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 instrucción 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, consulta Mecanismo de seguridad.

Esta función extiende PREPARE y EXECUTE commands para admitir parámetros de vista con nombre. Usa sentencias preparadas para evitar la sobrecarga del análisis, la interpretación y la reescritura 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 instrucción 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 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 o cantidad de filas que se muestran.

Para crear una instrucció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: 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 de RESTRICTED.
• VIEW_PARAM_VALUE: Son los valores reales que se pasan a los parámetros viewParamName correspondientes de las vistas parametrizadas.

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

Nota: No puedes usar parámetros de vista con nombre directamente en la instrucción PREPARE.

Usa el comando EXECUTE .. WITH VIEW PARAMETERS para ejecutar una instrucción preparada previamente que creaste con el comando PREPARE .. AS RESTRICTED. Si la instrucción PREPARE que creó la instrucción especificó parámetros posicionales, debes pasar un conjunto compatible de parámetros a la instrucción 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 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 búsquedas

A continuación, se indica el conjunto de operaciones restringidas para las consultas que ejecutas con las opciones descritas en Cómo consultar 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 evita 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.
• A continuación, se indica el conjunto de construcciones de consultas permitidas que están restringidas:
  • Se permiten instrucciones SELECT de solo lectura.
  • Se permiten las instrucciones SHOW, CALL y DO de solo lectura.
  • No se permiten las declaraciones DML, como INSERT, UPDATE y DELETE.
  • No se permiten las sentencias DDL, como CREATE TABLE y ALTER TABLE.
  • No se permiten otros tipos de sentencias, como LOAD, SET, CLUSTER, LOCK, CHECKPOINT y EXPLAIN.
• No se permiten las instrucciones EXPLAIN para evitar la posibilidad de ataques de canal encubierto con planes de consultas. Para obtener más información, consulta Canal encubierto.

Enumera todas las vistas con parámetros

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, 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 más 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 parametrizadas.