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 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 con parámetros 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 en cualquier consulta que se ejecute.

Para obtener más información, consulta la descripción general de las vistas seguras con parámetros y el instructivo sobre las vistas seguras con parámetros.

Antes de comenzar

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

Antes de que puedas usar vistas seguras con parámetros, debes hacer lo siguiente:

  1. Habilita la marca de base de datos cloudsql.enable_parameterized_views para tu instancia de Cloud SQL. Este cambio de marca requiere que se reinicie la base de datos. Para obtener más información, consulta Configura marcas de bases 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 con parámetros:

    -- 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 DDL CREATE VIEW 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 con parámetros
    • ALIAS: el alias del nombre de la tabla que se usará en la vista segura con parámetros
    • COLUMNNAME o COLUMN_NAMEN: el nombre de la columna o las columnas de la tabla que se usarán en la vista segura con parámetros

    • CONDITION: 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 para 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 vista con nombre deben comenzar con una letra (a-z) o un guion bajo (_).
      • Los caracteres posteriores pueden ser letras, guiones bajos o dígitos (0-9).
      • 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.

      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 tenga permiso para 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 con parámetros (instructivo).

Configura la seguridad de tu aplicación

Para configurar la seguridad de tus aplicaciones con vistas seguras con parámetros, sigue estos pasos:

  1. Crea la vista segura con parámetros 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 rol de base de datos nuevo para ejecutar consultas en vistas seguras con parámetros. Este es un rol de base de datos de Cloud SQL que usa la aplicación para conectarse y acceder a la base de datos.

    1. Otorga 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 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.

Consulta 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 consulta de una sola vez y mostrar filas JSON.
  • Basada en CURSOR: Usa esta API cuando tengas consultas de ejecución más larga o cuando tengas consultas grandes y quieras recuperar el resultado en lotes.
  • Instrucción PREPARE .. AS RESTRICTED: Usa PREPARE .. AS RESTRICTED para definir el plan de consulta 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 muestra una tabla de objetos JSON. Cada fila de la tabla equivale al valor row_to_json() de la fila de resultado de la consulta 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 muestra 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 instrucciones 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 instrucción preparada, haz lo siguiente:

  1. Crea la instrucción 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: el tipo de datos del parámetro de consulta, como TEXT
    • SQL_QUERY: la consulta en SQL que se ejecutará como parte de la instrucción preparada con el valor o los valores proporcionados

  2. Ejecuta la instrucción 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: el nombre de la instrucción preparada.
    • QUERY_VALUE_N: el valor o los valores que se proporcionarán como parámetro o parámetros a la consulta en SQL
    • PARAMETER_NAME_N: el nombre del parámetro o los parámetros de vista con nombre que definiste cuando creaste la vista segura con parámetros. Creas el parámetro de vista con nombre a partir de la columna de la tabla.
    • PARAMETER_VALUE_N: el valor o los valores para el parámetro de vista con nombre que restringe la vista segura con parámetros a las filas asociadas con el valor.

  3. Limpia la instrucción 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 para la consulta y 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 con parámetros, separados de los parámetros posicionales para la consulta preparada (%Laptop%, %Book%).

Por último, limpia la instrucción preparada.

-- Clean up the get_item_by_name prepared statement
DEALLOCATE get_items_by_name;

Restricciones aplicadas en las consultas

En la siguiente lista, se incluye el conjunto de operaciones restringidas para las consultas que ejecutas con las opciones descritas en Consulta una vista segura con parámetros:

  • Solo lectura: Solo se permiten las 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 extensión: No se permiten ciertas extensiones que inician sesiones nuevas en segundo plano (p.ej., dblink, pg_cron).
  • No se permiten las instrucciones EXPLAIN para evitar posibles filtraciones de información con planes de consulta.

Enumera todas las vistas con parámetros

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

SELECT * FROM parameterized_views.all_parameterized_views;

¿Qué sigue?