Usa vistas seguras con parámetros

En este documento, se describe cómo usar vistas seguras con parámetros en Cloud SQL 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 las vistas seguras parametrizadas en Cloud SQL y cómo proteger y controlar el acceso a los datos de la aplicación con vistas seguras parametrizadas.

Antes de comenzar

En este documento, se supone que ya creaste una instancia de Cloud SQL para PostgreSQL.

Antes de que puedas usar vistas seguras parametrizadas, debes hacer lo siguiente:

  1. Habilita la marca de base de datos cloudsql.enable_parameterized_views para tu instancia de Cloud SQL. El cambio de esta marca requiere que se reinicie la base de datos. Para obtener más información, consulta Configura marcas de base de datos.

  2. Usa Cloud SQL Studio o psql para crear la extensión parameterized_views en cualquier base de datos en la que se cree una vista parametrizada:

    -- Requires cloudsql.enable_parameterized_views set to on
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 CREATE VIEW DDL con la opción security_barrier:

    CREATE VIEW VIEW_NAME WITH (security_barrier) AS
SELECT COLUMN_NAME, COLUMN_NAME_N
FROM TABLE_NAME ALIAS
WHERE CONDITION;

    Reemplaza lo siguiente:

    • VIEW_NAME: El nombre de la vista segura con parámetros.
    • TABLE_NAME: El nombre de la tabla que se usará en la vista segura parametrizada
    • ALIAS: Es el alias del nombre de la tabla que se usará en la vista segura parametrizada.
    • COLUMNNAME o COLUMN_NAMEN: El nombre de la columna o las columnas de la tabla que se usarán en la vista segura parametrizada

    • CONDITION: Es la instrucción de condición que se usa 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 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 tienen los siguientes requisitos:

      • Los parámetros de vistas con nombre deben comenzar con una letra (a-z) o un guion bajo (_).
      • Los caracteres que le siguen pueden ser letras, guiones bajos o dígitos (0 a 9).
      • 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.

      A continuación, se muestra un ejemplo de cómo crear una vista segura con parámetros que usa un parámetro de vista con nombre:

      CREATE VIEW user_specific_items WITH (security_barrier) AS
SELECT item_id, item_name, description, owner_id
FROM items t
WHERE owner_id = $@app_user_id;

  2. Otorga SELECT en la vista a cualquier usuario de la base de datos que pueda 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 la vista segura parametrizada como usuario administrador. Este es un usuario de la base de datos de Cloud SQL que realiza operaciones administrativas para la aplicación.

  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 Cloud SQL que la aplicación usa para conectarse y acceder a la base de datos.

    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 y administra usuarios.

Cómo consultar una vista segura con parámetros

Para consultar una vista segura con parámetros, usa una de las siguientes opciones:

  • Basada en JSON: Usa esta API para ejecutar la búsqueda 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.
  • Sentencia PREPARE .. AS RESTRICTED: Usa PREPARE .. AS RESTRICTED para definir el plan de consultas y, luego, ejecuta EXECUTE ... WITH VIEW PARAMETERS (...) para ejecutarlo con parámetros específicos para la vista.

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

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.

El uso de esta API limita el tamaño del conjunto de resultados por tamaño (en KB) 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

Ejecuta la función execute_parameterized_query() con un nombre de cursor, que crea y devuelve un CURSOR con alcance de transacción:

-- Must be in a transaction block
BEGIN;

SELECT * FROM
parameterized_views.execute_parameterized_query(
    query => SQL_QUERY,
    cursor_name => CURSOR_NAME,
    param_names => ARRAY [PARAMETER_NAMES],
    param_values => ARRAY [PARAMETER_VALUES]
);

FETCH ALL FROM CURSOR_NAME;

END;

Instrucciones preparadas

El método de sentencias preparadas te permite preparar un plan de consulta una vez y, luego, ejecutarlo varias veces con diferentes valores para los parámetros posicionales de la consulta y los parámetros con nombre de la vista.

Para usar una sentencia preparada, haz lo siguiente:

  1. Crea la sentencia preparada.

    -- Prepare the statement
PREPARE PREPARED_STATEMENT_NAME (QUERY_PARAM_TYPE_1, QUERY_PARAM_TYPE_N)
AS RESTRICTED SQL_QUERY;

    Reemplaza lo siguiente:

    • PREPARED_STATEMENT_NAME: El nombre de la instrucción preparada
    • QUERY_PARAM_TYPE_N: Es el tipo de datos del parámetro de consulta, como TEXT.
    • SQL_QUERY: Es la consulta en SQL que se ejecutará como parte de la instrucción preparada con el valor o los valores proporcionados.

  2. Ejecuta la sentencia preparada.

    -- Execute the statement with query parameters and view parameters
EXECUTE PREPARED_STATEMENT_NAME (QUERY_VALUE_1, QUERY_VALUE_N)
WITH VIEW PARAMETERS (PARAMETER_NAME_1 := 'PARAMETER_VALUE_1', PARAMETER_NAME_N := 'PARAMETER_VALUE_N');

    Reemplaza lo siguiente:

    • PREPARED_STATEMENT_NAME: Es el nombre de la sentencia preparada.
    • QUERY_VALUE_N: Es el valor o los valores que se proporcionan como parámetro o parámetros a la consulta en SQL.
    • PARAMETER_NAME_N: Es el nombre del parámetro o los parámetros de la vista con nombre que definiste cuando creaste la vista segura parametrizada. Creas el parámetro de vista con nombre a partir de la columna de la tabla.
    • PARAMETER_VALUE_N: Es el valor o los valores del parámetro de vista con nombre que restringen la vista segura parametrizada a las filas asociadas con el valor.

  3. Limpia la sentencia preparada.

    -- Cleanup
DEALLOCATE PREPARED_STATEMENT_NAME>;

En el siguiente ejemplo, se usa una vista segura con parámetros llamada user_specific_items que requiere el parámetro de vista con nombre $@app_user_id.

-- Prepare a query with a positional parameter $1 for the item_name pattern
PREPARE get_items_by_name (TEXT) AS RESTRICTED
SELECT item_id, item_name FROM user_specific_items
WHERE item_name LIKE $1;

Después de crear la instrucción preparada, puedes ejecutarla varias veces y asignar diferentes valores tanto para la consulta como para el parámetro de vista con nombre.

Por ejemplo, la primera ejecución de la consulta:

-- Execute for user 123, looking for items like '%Laptop%'
EXECUTE get_items_by_name ('%Laptop%')
WITH VIEW PARAMETERS (app_user_id := '123');

La segunda ejecución de la consulta:

-- Execute for user 456, looking for items like '%Book%'
EXECUTE get_items_by_name ('%Book%')
WITH VIEW PARAMETERS (app_user_id := '456');

La cláusula WITH VIEW PARAMETERS es donde se proporcionan los parámetros de vista con nombre (123, 456) para la vista segura parametrizada, separados de los parámetros posicionales para la consulta preparada (%Laptop%, %Book%).

Por último, limpia la sentencia preparada.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Restricciones en las consultas

A continuación, se indica el conjunto de operaciones restringidas para las consultas que ejecutas con las opciones que se describen en Cómo consultar una vista segura con parámetros:

  • Solo lectura: Solo se permiten instrucciones SELECT de solo lectura. Se prohíben DML (INSERT, UPDATE, DELETE) y DDL (CREATE, ALTER).
  • Sin anidación: Se prohíben las llamadas recursivas a execute_parameterized_query.
  • Límites de extensiones: No se permiten ciertas extensiones que inician nuevas sesiones en segundo plano (p.ej., dblink, pg_cron).
  • No se permiten las instrucciones EXPLAIN para evitar posibles filtraciones de información a través de los planes de consultas.

Enumera todas las vistas con parámetros

Usa la vista parameterized_views.all_parameterized_views para enumerar todas las vistas parametrizadas (las que contienen al menos un parámetro con nombre $@...).

SELECT * FROM parameterized_views.all_parameterized_views;

¿Qué sigue?