Protege y controla el acceso a los datos de las aplicaciones mediante vistas seguras parametrizadas

En este tutorial se describe cómo usar vistas seguras parametrizadas en AlloyDB para PostgreSQL para restringir el acceso de los usuarios a vistas parametrizadas mediante AlloyDB Studio o psql.

Se incluyen ejemplos para ilustrar las funciones de las vistas seguras parametrizadas. Estos ejemplos se incluyen únicamente a modo de demostración.

Objetivos

  • Crea vistas parametrizadas seguras con parámetros de vista con nombre.
  • Crea el rol de base de datos que usa la aplicación para conectarse a la base de datos y acceder a vistas seguras parametrizadas.
  • Concede permisos al nuevo rol para acceder a las vistas protegidas y revoca el acceso a las tablas base.
  • Conéctate con el nuevo rol y comprueba que no se puede acceder a las tablas restringidas.
  • Ejecuta consultas en la vista segura parametrizada mediante la función execute_parameterized_query.

Costes

En este documento, se utilizan los siguientes componentes facturables de Google Cloud:

Para generar una estimación de costes basada en el uso previsto, utiliza la calculadora de precios.

Los usuarios nuevos Google Cloud pueden disfrutar de una prueba gratuita.

Cuando termines las tareas que se describen en este documento, puedes evitar que se te siga facturando eliminando los recursos que has creado. Para obtener más información, consulta la sección Limpiar.

Antes de empezar

Habilitar la facturación y las APIs necesarias

  1. En la Google Cloud consola, selecciona un proyecto.

    Ir al selector de proyectos

  2. Comprueba que la facturación esté habilitada en tu Google Cloud proyecto.

  3. Habilita las APIs de Cloud necesarias para crear una instancia de AlloyDB para PostgreSQL y conectarte a ella.

    Habilitar la API

    1. En el paso Confirm project (Confirmar proyecto), haz clic en Next (Siguiente) para confirmar el nombre del proyecto en el que vas a hacer cambios.

    2. En el paso Habilitar APIs, haz clic en Habilitar para habilitar lo siguiente:

      • API de AlloyDB

Crear una base de datos y conectarse a ella

  1. Crea un clúster y su instancia principal.
  2. Conéctate a tu instancia y crea una base de datos.

Prepara tu entorno

Para preparar la ejecución de consultas en una vista segura parametrizada, primero debes configurar las vistas parametrizadas, la base de datos y los roles de la base de datos, la extensión parameterized_view y el esquema de la aplicación.

Habilita la extensión necesaria

Habilita la marca de base de datos parameterized_views.enabled, que carga las bibliotecas de extensiones necesarias. Para obtener más información, consulta el artículo sobre cómo configurar las marcas de la base de datos de una instancia.

Configurar la base de datos

  • Crea una base de datos llamada database para los datos de la aplicación y las vistas parametrizadas. Para obtener más información, consulta Crear una base de datos.

Crear roles de base de datos, la extensión y el esquema de la aplicación

  1. Conéctate a la base de datos con AlloyDB Studio o psql como usuario postgres o como usuario con privilegios de superusuario de AlloyDB.

    psql database -U postgres

    Para obtener más información, consulta Información sobre la gestión de usuarios de bases de datos en AlloyDB.

  2. Crea la extensión parameterized_views en la base de datos.

    -- 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 ya existentes.

  3. Crea el rol administrativo de AlloyDB, que es propietario de la base de datos y la gestiona.

    CREATE ROLE admin_user WITH LOGIN PASSWORD '...';
 GRANT ALL PRIVILEGES ON DATABASE database TO admin_user;

    Para obtener más información, consulta CREATE USER.

  4. Crea un rol de base de datos para ejecutar consultas en vistas seguras parametrizadas. Es un rol de AlloyDB que usa la aplicación para conectarse y acceder a la base de datos para ejecutar consultas con acceso limitado a funciones u objetos públicos al conjunto mínimo necesario.

    CREATE ROLE psv_user WITH LOGIN PASSWORD '...';

    Para obtener más información, consulta CREATE USER.

  5. Conéctate como usuario administrativo.

    SET role TO admin_user;

  6. Crea el esquema que contiene las tablas.

    CREATE SCHEMA schema;

  7. Crea las tablas e inserta los datos.

    CREATE TABLE schema.checked_items(bag_id INT,timestamp TIMESTAMP, loc_code CHAR(3), scan_type CHAR(1), location TEXT, customer_id INT);

INSERT INTO schema.checked_items (bag_id, timestamp, loc_code, scan_type, location, customer_id) VALUES
(101, '2023-10-26 10:00:00', 'ABC', 'I', 'Warehouse A', 123),
(102, '2023-10-26 10:15:30', 'DEF', 'O', 'Loading Dock B', 456),
(103, '2023-10-26 10:30:45', 'GHI', 'I', 'Conveyor Belt 1', 789),
(104, '2023-10-26 11:00:00', 'JKL', 'O', 'Shipping Area C', 101),
(105, '2023-10-26 11:45:15', 'MNO', 'I', 'Sorting Station D', 202),
(106, '2023-10-26 12:00:00', 'PQR', 'O', 'Truck Bay E', 303);

Crear vistas parametrizadas seguras y configurar privilegios de acceso

Para crear vistas parametrizadas seguras y configurar los privilegios de acceso adecuados para la tabla base y las vistas, sigue estos pasos:

  1. Conéctate a la base de datos como admin_user mediante AlloyDB Studio o psql.

    psql database -U admin_user

  2. Para proporcionar acceso limitado a la vista, crea una vista segura parametrizada:

    CREATE VIEW schema.secure_checked_items WITH (security_barrier) AS
SELECT bag_id, timestamp, location
FROM schema.checked_items t
WHERE customer_id = $@app_end_userid;

  3. Concede acceso a la vista.

    GRANT SELECT ON schema.secure_checked_items TO psv_user;

  4. Para acceder a la vista, concede acceso al esquema.

    GRANT USAGE ON SCHEMA schema TO psv_user;

  5. Revoca el acceso directo a la tabla base.

    REVOKE ALL PRIVILEGES ON schema.checked_items FROM psv_user;

Verificar la seguridad de los datos

Para verificar que las vistas seguras parametrizadas restringen el acceso a las vistas designadas, ejecuta los siguientes comandos como psv_user. Se trata de un rol de base de datos de AlloyDB que la aplicación usa para conectarse y acceder a la base de datos para ejecutar consultas.

  1. Conéctate como usuario de vistas seguras parametrizadas.

    psql database -U psv_user

  2. Comprueba que no se puede acceder a la tabla base.

    SELECT * FROM schema.checked_items;
ERROR:  permission denied for table checked_items

  3. Accede a la vista segura parametrizada mediante la función execute_parameterized_query:

    SELECT * FROM parameterized_views.execute_parameterized_query(
  query => 'SELECT * from schema.secure_checked_items',
  param_names => ARRAY ['app_end_userid'],
  param_values => ARRAY ['303']
);

  4. Para mejorar la seguridad de las consultas generadas en lenguaje natural, integra tus vistas seguras parametrizadas mediante el lenguaje natural de AlloyDB AI.

Limpieza

Para evitar que los recursos utilizados en este tutorial se cobren en tu cuenta de Google Cloud, elimina el proyecto que contiene los recursos o conserva el proyecto y elimina los recursos.

  1. En la Google Cloud consola, ve a la página Clusters.

    Ir a Clústeres

  2. En la columna Nombre del recurso, haz clic en el nombre de tu clúster, my-cluster.

  3. Haz clic en Eliminar clúster.

  4. En Eliminar clúster my-cluster, introduce my-cluster para confirmar que quieres eliminar el clúster.

  5. Haz clic en Eliminar.

  6. Si has creado una conexión privada al crear un clúster, ve a la página Redes de la consola Google Cloud y haz clic en Eliminar red de VPC.

  7. Conéctate a la base de datos como postgres mediante AlloyDB Studio o psql.

    psql database -U postgres

  8. Elimina los objetos que hayas creado.

    DROP VIEW schema.secure_checked_items;
DROP TABLE schema.checked_items;
DROP SCHEMA schema;
DROP ROLE psv_user;
DROP ROLE admin_user;
DROP EXTENSION parameterized_views;

Siguientes pasos