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

En esta página se describe cómo usar vistas seguras parametrizadas 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 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 Información general sobre las vistas seguras parametrizadas y Proteger y controlar el acceso a los datos de las aplicaciones mediante vistas seguras parametrizadas.

Antes de empezar

En esta página se da por hecho que ya ha creado un clúster y una instancia de AlloyDB. Para obtener más información, consulta Crear una base de datos.

Antes de usar vistas seguras parametrizadas, debe hacer lo siguiente:

  1. Habilita la marca de base de datos parameterized_views.enabled, que carga las bibliotecas de extensiones necesarias. Debes habilitar esta marca, aunque el equipo de AlloyDB la haya habilitado anteriormente. Para obtener más información sobre cómo habilitar la marca de la base de datos, consulta el artículo Configurar las marcas de la 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 se incluyan en el espacio de nombres de ese esquema y no entren en conflicto con las APIs que ya existen.

Crear una vista segura parametrizada

Para crear una vista segura parametrizada, siga estos pasos:

  1. 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 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ñada los parámetros necesarios en la definición de la vista mediante 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 el artículo 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 de AlloyDB 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. Se trata de un rol de base de datos de AlloyDB que la aplicación usa para conectarse e iniciar sesión en la base de datos, así como para ejecutar consultas en vistas parametrizadas.

    1. Concede los permisos del nuevo rol a las vistas seguras, que normalmente incluyen privilegios SELECT en 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 necesita la aplicación. No proporciones acceso a esquemas y tablas que no sean públicos.

    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.

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

Consultar una vista segura parametrizada

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. Puedes configurar estos límites mediante parameterized_views.json_results_max_size y parameterized_views.json_results_max_rows.

API CURSOR

Ejecuta la función execute_parameterized_query(), que crea y devuelve un CURSOR con ámbito de transacción que se usa para obtener 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]
)

Haz los cambios siguientes:

  • SQL_QUERY: una consulta de SQL cuya cláusula FROM hace referencia a una o varias vistas seguras parametrizadas.
  • CURSOR_NAME: nombre del cursor que se va a declarar.
  • 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, donde el orden de los valores coincide con el orden de los nombres. El tipo exacto de los valores se infiere a partir 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. Si los tipos no coinciden, se produce un error.

Usa el siguiente ejemplo para consultar una vista segura parametrizada:

  -- 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 devuelto es un NO SCROLL cursor WITHOUT HOLD. No puedes usar el cursor para recuperar filas de forma no secuencial, por ejemplo, hacia atrás. No puedes usar el cursor fuera de la transacción que lo ha creado.

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 Mecanismo de seguridad.

Esta función amplía PREPARE y EXECUTE commands 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 dar lugar a mejoras significativas del rendimiento, especialmente en el caso de las consultas complejas o que se ejecutan con frecuencia. Una sentencia preparada es un objeto del lado del servidor que puede optimizar el rendimiento precompilando y almacenando una sentencia 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 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 instrucciones SELECT de solo lectura.
    • Se permiten las instrucciones SHOW, CALL y DO de solo lectura.
    • No se permiten instrucciones DML como INSERT, UPDATE y DELETE.
    • No se permiten 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 que se produzcan 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 Marcas de bases de datos admitidas.

Mostrar todas las vistas parametrizadas

Usa la extensión parameterized_views para enumerar todas las vistas parametrizadas 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 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