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

Selecciona una versión de la documentación:

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

Se incluyen ejemplos para ilustrar las capacidades de las vistas seguras parametrizadas. Estos ejemplos se proporcionan solo con fines 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 las vistas seguras parametrizadas.
  • Otorga los permisos del rol nuevo 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.

Instala una base de datos y conéctate a ella

  1. Instala AlloyDB Omni(para Kubernetes, para contenedores).
  2. Crea un clúster y su instancia principal.
  3. Conéctate a tu instancia (para Kubernetes, para contenedores)

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 la base de datos, la extensión parameterized_view y el esquema de la aplicación.

Antes de usar vistas seguras parametrizadas, debes hacer lo siguiente una vez en cada contenedor de postgres nuevo. Cada parámetro de configuración se puede aplicar con ALTER SYSTEM o editando el postgresql.conf directamente:

  1. Agrega "parameterized_views" a shared_preload_libraries.
  2. Para habilitar la función, configura parameterized_views.enabled=on.
  3. Reinicia Postgres para que se apliquen los cambios.

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 psql, conéctate a la base de datos como el usuario postgres o como un usuario con privilegios de superusuario de AlloyDB Omni.

    psql database -U postgres

    Para obtener más información, consulta Acerca de la administración de usuarios de la base de datos en AlloyDB Omni.

  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 estén contenidas en el espacio de nombres de ese esquema y para que esas APIs no entren en conflicto con las APIs existentes.

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

    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 Omni que la aplicación usa para conectarse y acceder a la base de datos para ejecutar consultas con acceso limitado a funciones o objetos públicos al conjunto mínimo requerido.

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

  5. Conéctate como usuario administrador.

    SET role TO admin_user;

  6. Crea el esquema que contiene las tablas.

    CREATE SCHEMA schema;

  7. Crea las tablas y, luego, 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 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 Omni que la aplicación usa para conectarse y acceder a la base de datos para ejecutar consultas.

  1. Conéctate como un 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 con lenguaje natural, integra tus vistas seguras parametrizadas con lenguaje natural.

Realiza una limpieza

Para realizar la limpieza, puedes desinstalar la instancia de AlloyDB Omni o conservar la instancia y borrar los objetos individuales.

¿Qué sigue?