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

En este instructivo, se describe cómo usar vistas seguras con parámetros en Cloud SQL para PostgreSQL para restringir el acceso de los usuarios a las vistas con parámetros a través de Cloud SQL Studio o psql.

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 con parámetros.
  • Otorga permisos del nuevo rol a las vistas seguras y revoca el acceso a las tablas básicas.
  • Conéctate con el nuevo rol 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.

Prepara el entorno

  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.

  2. Conéctate a tu base de datos como el usuario postgres.

    psql -U postgres

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

    CREATE EXTENSION parameterized_views;

  4. Crea un nuevo rol de base de datos para ejecutar consultas.

    CREATE ROLE psv_user WITH LOGIN PASSWORD 'PASSWORD';

  5. Crea un esquema y una tabla para los datos de la aplicación.

    CREATE SCHEMA app_schema;
CREATE TABLE app_schema.items(item_id INT, item_name TEXT, description TEXT, owner_id INT);

INSERT INTO app_schema.items (item_id, item_name, description, owner_id) VALUES
(1, 'Book', 'A great read', 123),
(2, 'Laptop', 'Work machine', 456),
(3, 'Pencil', 'For writing', 123);

Crea vistas parametrizadas seguras y configura privilegios de acceso

  1. Crea una vista segura parametrizada:

    CREATE VIEW app_schema.user_items_view WITH (security_barrier) AS
SELECT item_id, item_name, description
FROM app_schema.items
WHERE owner_id = $@current_user_id;

  2. Otorga acceso a la vista y al esquema al rol de la aplicación.

    GRANT USAGE ON SCHEMA app_schema TO psv_user;
GRANT SELECT ON app_schema.user_items_view TO psv_user;

  3. Revoca el acceso directo a la tabla base.

    REVOKE ALL PRIVILEGES ON app_schema.items FROM psv_user;

Verifica la seguridad de los datos

  1. Conéctate como psv_user.

    psql -U psv_user -d postgres

  2. Verifica que no se pueda acceder a la tabla base.

    SELECT * FROM app_schema.items;
-- ERROR:  permission denied for table 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 app_schema.user_items_view',
  param_names => ARRAY ['current_user_id'],
  param_values => ARRAY ['123']
);

    El resultado solo debe incluir elementos en los que owner_id sea 123.

¿Qué sigue?