Protege y controla el acceso a los datos de la aplicación con vistas seguras parametrizadas

Vista previa

Esta función está sujeta a las "Condiciones de la oferta 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.

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

Se incluyen ejemplos para ilustrar las capacidades de las vistas seguras parametrizadas. Estos ejemplos están destinados solo a fines de demostración.

Objetivos

• Crea vistas seguras parametrizadas 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 las vistas seguras parametrizadas.
• Otorga permisos de rol nuevos a las vistas seguras y revoca el acceso a las tablas base.
• Conéctate con el rol nuevo y verifica que no se pueda acceder a las tablas restringidas.
• Ejecuta consultas en la vista segura parametrizada con la función execute_parameterized_query.

Costos

En este documento, usarás los siguientes componentes facturables de Google Cloud:

• AlloyDB for PostgreSQL

Para generar una estimación de costos en función del uso previsto, usa la calculadora de precios.

Es posible que los usuarios de Google Cloud nuevos cumplan con los requisitos para acceder a una prueba gratuita.

Cuando completes las tareas que se describen en este documento, podrás borrar los recursos que creaste para evitar que se te siga facturando. Para obtener más información, consulta Realiza una limpieza.

Antes de comenzar

Habilita la facturación y las APIs requeridas

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

   Ir al selector de proyectos

2. Asegúrate de que la facturación esté habilitada para tu Google Cloud proyecto.

3. Habilita las API de Cloud necesarias para crear AlloyDB para PostgreSQL y conectarte a él.

   Habilitar la API

   1. En el paso Confirmar proyecto, haz clic en Siguiente para confirmar el nombre del proyecto al que realizarás cambios.

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

      • API de AlloyDB

Crea una base de datos y conéctate a ella

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

Prepara el entorno

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

Habilita la extensión requerida

Habilita la marca de base de datos parameterized_views.enabled, que carga las bibliotecas de extensión requeridas. Para obtener más información, consulta Configura las marcas de base de datos de una instancia.

Nota: Después de habilitar la marca de base de datos parameterized_views.enabled, se reinicia la base de datos para que estos cambios surtan efecto.

Configura 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 Crea una base de datos.

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

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

   psql database -U postgres
   

   Para obtener más información, consulta Acerca de la administració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 para que esas APIs no entren en conflicto con las APIs existentes.

   Nota: Debes crear una extensión en cada base de datos en la que se creen vistas parametrizadas.

3. Crea el rol administrativo de AlloyDB, que posee y administra la base de datos.

   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 nuevo para ejecutar consultas en vistas seguras parametrizadas. Este es un rol de AlloyDB que la aplicación usa 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 requerido.

   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 y, luego, inserta 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);
   

Crea vistas seguras parametrizadas y configura privilegios de acceso

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

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

   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. Otorga acceso a la vista.

   GRANT SELECT ON schema.secure_checked_items TO psv_user;
   

4. Para acceder a la vista, otorga 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;
   

Verifica la seguridad de los datos

Para verificar que las vistas seguras parametrizadas restrinjan el acceso a las vistas designadas, ejecuta los siguientes comandos como psv_user. Este es 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. Verifica que no se pueda acceder a la tabla base.

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

3. Accede a la vista segura parametrizada con 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 con el lenguaje natural de AlloyDB AI.

Limpia

Para evitar que se apliquen cargos a tu cuenta de Google Cloud por los recursos usados en este instructivo, borra el proyecto que contiene los recursos o conserva el proyecto y borra los recursos individuales.

1. En la Google Cloud consola de, ve a la página Clústeres.

   Ir a los clústeres

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

3. Haz clic en Borrar clúster.

4. En Borrar clúster my-cluster, ingresa my-cluster para confirmar que deseas borrar tu clúster.

5. Haz clic en Borrar.

6. Si creaste una conexión privada cuando creaste un clúster, ve a la Google Cloud página Redes de la consola y haz clic en Borrar red de VPC.

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

   psql database -U postgres
   

8. Descarta los objetos que creaste.

   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;
   

¿Qué sigue?

• Obtén información sobre las vistas seguras parametrizadas.
• Obtén información para administrar la seguridad de los datos de la aplicación con vistas seguras parametrizadas.