Proteger las lecturas y escrituras de los expositores

Introducción

Fern Studio y Cedar Works se están preparando para una pequeña feria de artesanía. Cada expositor debe mantener sus propias inscripciones de carga en una tabla PostgreSQL compartida. El equipo de la feria debe seguir viendo a todos y asignando puestos. Un filtro en un comando de cliente resulta útil para consultar, pero un expositor puede omitirlo; la base de datos debe imponer el límite.

Guardarás y aplicarás políticas de filas y después usarás inicios de sesión ordinarios separados con contraseña para completar inscripciones reales. PostgreSQL y las cuentas con alcance limitado están preparados. Necesitas conocimientos básicos para moverte por la terminal y de SELECT/WHERE, INSERT, UPDATE y tablas de PostgreSQL. Los roles, la seguridad a nivel de fila, las expresiones de políticas, las transacciones y la sintaxis necesaria del intérprete del sistema se explican antes de usarlos. No se requiere programación Python. Usa Terminal durante todo el laboratorio. La duración de 95 minutos es una estimación del autor que incluye lectura y errores habituales, no un tiempo de finalización medido con principiantes.

Trabaja en /home/labex/project/exhibitor-desk. Escribe read-access.sql y write-access.sql, cambia políticas de filas sobre fair.registrations y crea o corrige únicamente las dos inscripciones nuevas especificadas más adelante. Conserva todos los registros originales, la identidad y las definiciones de tabla, los permisos y cuentas preparados, las credenciales, las herramientas y los servicios. Los archivos de políticas deben ser SQL autocontenido: sin comandos del intérprete del sistema, cambios de conexión ni inclusiones externas. Se aceptan nombres equivalentes de políticas y configuraciones correctas de políticas. Eres el operador del curso y puedes elegir deliberadamente cada cuenta de prueba preparada; este ejercicio protege las identidades de base de datos entre sí, no las credenciales frente al operador con sudo de la VM. No implementa autenticación de sitios web ni promete protección frente a todos los canales laterales de información.

Limitar la visibilidad manteniendo el acceso del equipo de la feria

En este paso, darás a cada expositor una vista completa de sus propias inscripciones y mantendrás disponible el trabajo del organizador entre expositores. Las escrituras de los expositores seguirán sin estar disponibles hasta que el siguiente paso añada sus políticas de escritura.

Empieza en Terminal. cd cambia de directorio; esta ruta absoluta funciona independientemente de tu directorio actual:

cd /home/labex/project/exhibitor-desk

./ ejecuta un programa del directorio actual. El lanzador preparado db autentica una conexión real de PostgreSQL y ejecuta psql, el cliente PostgreSQL. No filtra filas ni inventa resultados. Enumera sus alias de cuenta:

./db accounts

Los alias seleccionan estas cuentas preparadas por separado:

Un alias es una comodidad para ti como operador, no un permiso de base de datos otorgado a Fern. Elegir cedar usa deliberadamente las credenciales separadas de Cedar. Dentro de una conexión de Fern, cambiar a Cedar debe seguir prohibido.

-c proporciona una cadena de comando. Las comillas dobles mantienen juntos sus espacios para el intérprete. session_user identifica la cuenta autenticada; current_user identifica el rol cuyos permisos usa actualmente la instrucción. Deben coincidir en estas conexiones ordinarias:

./db fern -c "SELECT session_user, current_user;"

Deberías ver fern_studio en ambas columnas. Lee primero el registro normal conocido de Fern. El número de cajas es un recuento entero; cero significa un cero conocido, no una inscripción ausente:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations WHERE registration_id = 'R101';"

R101 pertenece a Fern, tiene contacto Fern desk, 2 cajas, puesto A1 y una tarifa de 1200 centavos. fair es el esquema que agrupa la tabla. Estos datos originales de negocio deben permanecer sin cambios.

Ahora omite el filtro opcional del cliente:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Al principio Fern ve R101/R102 y R201/R202 de Cedar. WHERE owner_role = 'fern_studio' los ocultaría en esa única consulta, pero no impediría esta consulta sin filtro. Cedar tiene el mismo problema.

PostgreSQL tiene dos capas de permisos relevantes. Un GRANT permite una operación sobre una tabla o columnas concretas; los permisos preparados ya permiten a estos usuarios acceder a las operaciones que necesitan. La seguridad a nivel de fila, o RLS, decide además qué filas pueden leer o cambiar. Activar RLS sin una política aplicable deniega por defecto el acceso a las filas; no inventa automáticamente la regla de propiedad.

editor es propietario de esta tabla del curso y puede cambiar políticas. Los propietarios normalmente eluden RLS, por lo que una consulta correcta del editor no demuestra que un expositor esté restringido. Usa el editor para inspeccionar definiciones y las cuentas ordinarias para probar el resultado. \d es un comando de descripción de psql, no SQL:

./db editor -c "\d fair.registrations"

Las seis columnas son registration_id, owner_role, contact, crates, booth_label y fee_cents. Una inscripción nueva tiene por defecto el puesto Pending y tarifa 0. Los permisos de columna preparados permiten a los expositores insertar ID, propietario, contacto y cajas, y actualizar propietario, contacto y cajas. No pueden actualizar ID, puestos ni tarifas, borrar filas ni truncar la tabla. La política debe seguir rechazando un cambio de propietario, aunque un permiso UPDATE permita acceder a esa columna.

Inspecciona las políticas existentes mediante la vista de catálogo pg_policies de PostgreSQL:

./db editor -c "SELECT policyname, roles, cmd, qual, with_check FROM pg_policies WHERE schemaname = 'fair' AND tablename = 'registrations' ORDER BY policyname;"

legacy_open_access permite el acceso a todos. Las políticas son normalmente permisivas: las políticas aplicables se combinan con OR. Añadir una política estrecha junto a esta amplia seguiría permitiendo el resultado amplio. Retira la política antigua como parte del cambio.

La expresión USING de una política decide si una fila existente es visible. owner_role = current_user compara el propietario de la fila con la identidad real de base de datos. FOR SELECT limita esta política a lectura. El organizador necesita una política separada, limitada con TO fair_team, que permita sus propias operaciones legítimas sobre todas las filas; true significa que todas las filas pasan para ese rol nombrado.

Guarda la primera migración. cat > read-access.sql escribe el texto siguiente en el archivo y reemplaza una copia anterior. <<'SQL' inicia un here-document: el marcador entre comillas mantiene literal el SQL encerrado hasta que aparece SQL solo en una línea. BEGIN y COMMIT convierten este cambio de políticas en una transacción, de modo que las instrucciones se confirmen juntas o se reviertan ante un error. DROP POLICY IF EXISTS permite también corregir y volver a ejecutar esta migración:

cat > read-access.sql <<'SQL'
BEGIN;
ALTER TABLE fair.registrations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS legacy_open_access ON fair.registrations;
DROP POLICY IF EXISTS own_read ON fair.registrations;
DROP POLICY IF EXISTS organizer_access ON fair.registrations;
CREATE POLICY own_read ON fair.registrations
  FOR SELECT TO fern_studio, cedar_works
  USING (owner_role = current_user);
CREATE POLICY organizer_access ON fair.registrations
  TO fair_team USING (true) WITH CHECK (true);
COMMIT;
SQL

Si el intérprete sigue esperando porque omitiste el marcador final, escribe SQL solo. Lee el archivo guardado antes de ejecutarlo:

cat read-access.sql

-f indica a psql que lea comandos de un archivo. Aplícalo como el propietario de tabla con alcance limitado:

./db editor -f read-access.sql

Deberías ver mensajes BEGIN, ALTER TABLE, DROP POLICY, CREATE POLICY y COMMIT. Un aviso de que uno de tus nombres nuevos de política todavía no existía es inocuo. El lanzador activa ON_ERROR_STOP: un error de sintaxis en un archivo detiene la ejecución con estado 3 de psql. Al cerrar la conexión se revierte una transacción sin terminar. Corrige el archivo y vuelve a ejecutarlo; no reinicies la VM. Ante un error de nombre de archivo o ruta, usa el cd absoluto, ejecuta ls, lee el nombre real y vuelve a intentarlo. No hace falta borrar ni recrear ninguna fila original.

Usa cada identidad ordinaria para inspeccionar el conjunto completo permitido:

./db fern -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, crates FROM fair.registrations ORDER BY registration_id;"

Fern ve R101/R102 con recuentos 2/0. Cedar ve R201/R202 con recuentos 3/1. El organizador sigue viendo los cuatro originales:

./db organizer -c "SELECT registration_id, owner_role, booth_label FROM fair.registrations ORDER BY registration_id;"

Este es un estado intermedio deliberado: los expositores tienen una política SELECT, pero todavía ninguna de INSERT/UPDATE. Mantén sin cambios sus permisos preparados. Ejecuta la comprobación del resultado; sudo permite al comprobador preparado inspeccionar el estado protegido. Lee el negocio real y prueba el SQL de políticas sobre datos desechables, sin completar el trabajo que te falte:

sudo python3 /opt/labex-exhibitors/verify.py visibility

Espera confirmación de que ambas cuentas ven sus propios registros y el organizador sigue siendo utilizable. A continuación añade las reglas separadas para escrituras propuestas.

Comprobar tanto las filas existentes como los valores propuestos

En este paso, permitirás a los expositores añadir y actualizar sus propias inscripciones sin crear ni transferir registros para otro propietario. Empieza en Terminal, en /home/labex/project/exhibitor-desk.

Una política SELECT por sí sola no autoriza un INSERT. Una política de escritura debe considerar dos momentos. Para UPDATE, USING selecciona las filas existentes que el rol puede cambiar; WITH CHECK comprueba la fila nueva propuesta. Para INSERT no hay fila antigua, por lo que su política usa solo WITH CHECK. En ambos casos, el propietario propuesto debe ser igual a current_user.

Guarda la migración de escritura con el mismo formato de here-document entre comillas. Añade políticas a la tabla ya protegida por read-access.sql:

cat > write-access.sql <<'SQL'
BEGIN;
DROP POLICY IF EXISTS own_insert ON fair.registrations;
DROP POLICY IF EXISTS own_update ON fair.registrations;
CREATE POLICY own_insert ON fair.registrations
  FOR INSERT TO fern_studio, cedar_works
  WITH CHECK (owner_role = current_user);
CREATE POLICY own_update ON fair.registrations
  FOR UPDATE TO fern_studio, cedar_works
  USING (owner_role = current_user)
  WITH CHECK (owner_role = current_user);
COMMIT;
SQL

Léela y aplícala después como editor:

cat write-access.sql

./db editor -f write-access.sql

Espera que las dos instrucciones CREATE POLICY se confirmen. Corregir el archivo y volver a ejecutarlo reemplaza solo estas políticas de escritura con nombre; no reinicia las inscripciones. Si vuelves a empezar desde tus archivos guardados de políticas, aplica primero read-access.sql y después write-access.sql.

Pueden funcionar configuraciones equivalentes de políticas. Una política ALL o UPDATE con solo USING usa implícitamente esa expresión para su WITH CHECK ausente; omitir esa cláusula no es automáticamente un defecto. Aquí las políticas separadas por comando hacen explícita cada tarea. Lo que importa es el comportamiento de todas las operaciones permitidas y prohibidas, no los nombres de políticas ni la escritura exacta.

Primero ejercita una escritura legítima sin dejar datos de prueba. BEGIN inicia una transacción. RETURNING muestra la fila realmente almacenada por INSERT/UPDATE. ROLLBACK descarta los cambios de la transacción y deja intactos los cuatro originales:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FERN', 'fern_studio', 'Loading test', 0); UPDATE fair.registrations SET crates = 2 WHERE registration_id = 'TRY-FERN' RETURNING registration_id, owner_role, crates; ROLLBACK;"

Deberías ver TRY-FERN, propietario fern_studio y 2 cajas, seguido de ROLLBACK. La inserción normal acepta un cero real y la actualización funciona; una regla que deniegue todas las escrituras no cumpliría este requisito.

Ahora prueba una fila existente oculta. Esta instrucción debe ejecutarse correctamente como comando, pero no encontrar ninguna fila permitida que actualizar:

./db fern -c "UPDATE fair.registrations SET contact = 'Wrong contact' WHERE registration_id = 'R201' RETURNING registration_id;"

No debe devolver ninguna fila y debe mostrar UPDATE 0. R201 pertenece a Cedar. Un resultado de cero filas es filtrado de filas, no un fallo de conexión ni un error de base de datos.

Los dos comandos siguientes son fallos SQL previstos. Ambos envuelven la prueba en una transacción: si una política incorrecta la aceptara por accidente, el ROLLBACK final la descartaría igualmente. Si un error detiene el comando antes de ROLLBACK, cerrar la conexión también descarta su transacción sin terminar.

Prueba a crear un registro propiedad de Cedar autenticado como Fern. Omite deliberadamente RETURNING: una comprobación de política SELECT sobre filas devueltas no debe sustituir a la regla de escritura sobre filas propuestas:

./db fern -c "BEGIN; INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('TRY-FOREIGN', 'cedar_works', 'Wrong owner', 1); ROLLBACK;"

Deberías ver new row violates row-level security policy. $? es el estado del comando del intérprete inmediatamente anterior; imprímelo antes de ejecutar cualquier otra cosa:

echo $?

Espera estado 1 para este comando SQL -c fallido. Un estado 2 con un error de conexión o autenticación es un fallo diferente y no demuestra que funcionara la política de filas. El lanzador elige las credenciales preparadas en cada invocación; usa ./db accounts y vuelve a intentar el alias documentado en lugar de editar archivos de credenciales.

Después prueba a cambiar a Cedar el propietario de las filas visibles de Fern. El UPDATE constante no tiene WHERE ni RETURNING que lea una columna de tabla. Esto llega directamente a la comprobación de fila propuesta; la transacción envolvente mantiene reversible incluso una prueba aceptada por accidente:

./db fern -c "BEGIN; UPDATE fair.registrations SET owner_role = 'cedar_works'; ROLLBACK;"

echo $?

Espera de nuevo el error de seguridad de filas y estado 1. No deduzcas que solo necesitan protección las actualizaciones con RETURNING: el SQL permitido puede omitirlo.

Los permisos de columna proporcionan otro límite. Un expositor puede cambiar su propio contacto y recuento, pero el equipo de la feria asigna puestos y tarifas. Este intento de cambio de puesto debe producir un error de permiso de tabla, incluso para las filas propias de Fern:

./db fern -c "BEGIN; UPDATE fair.registrations SET booth_label = 'WRONG'; ROLLBACK;"

Deberías ver permission denied for table registrations, no un error de política de fila propuesta. Del mismo modo, una conexión de Fern no puede convertirse en Cedar solo por nombrar ese rol:

./db fern -c "SET ROLE cedar_works;"

Deberías ver permission denied to set role. Los expositores preparados son cuentas ordinarias sin pertenencia a otras cuentas, propiedad de tabla ni BYPASSRLS. Cambiar un ajuste de aplicación elegido libremente no autenticaría otra cuenta; la política compara el rol real de base de datos.

Confirma que las pruebas reversibles dejaron intactos los datos originales de negocio:

./db organizer -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Las cuatro filas originales, contactos, recuentos, puestos y tarifas deben coincidir con sus valores iniciales. Ejecuta la comprobación del límite de escritura:

sudo python3 /opt/labex-exhibitors/verify.py writes

Comprueba ambas identidades ordinarias, escrituras legítimas, inventarios diferentes, escrituras entre propietarios y acceso del organizador. Superarla por completo significa que ya puedes usar la tabla protegida para trabajo normal.

Completar inscripciones mediante las cuentas protegidas

En este paso, usarás los permisos funcionales para preparar dos inscripciones reales de carga. Empieza en Terminal, en /home/labex/project/exhibitor-desk. Estos dos registros nuevos son las únicas filas de negocio que puedes añadir o corregir; conserva todas las filas originales.

Fern inicia una inscripción nueva con un recuento conocido de cero cajas. Los valores predeterminados proporcionados dejan su puesto en Pending y la tarifa en 0 hasta que el organizador los asigne:

./db fern -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R301', 'fern_studio', 'Fern loading', 0) RETURNING registration_id, owner_role, contact, crates, booth_label, fee_cents;"

Deberías ver R301, fern_studio, Fern loading, 0, Pending, 0. Es un INSERT real confirmado, a diferencia de las pruebas anteriores en transacciones. Repetirlo provocaría un error de clave duplicada; no es un comando de creación idempotente.

Establece su recuento final de cajas en cuatro con una actualización ordinaria dirigida:

./db fern -c "UPDATE fair.registrations SET crates = 4 WHERE registration_id = 'R301' RETURNING registration_id, owner_role, crates;"

Cedar crea su propia inscripción nueva con una caja:

./db cedar -c "INSERT INTO fair.registrations (registration_id, owner_role, contact, crates) VALUES ('R302', 'cedar_works', 'Cedar freight', 1) RETURNING registration_id, owner_role, crates;"

Deberías ver R302, cedar_works y 1. Cada expositor tiene ahora tres registros visibles, incluidos sus dos originales.

La identidad ordinaria propia del organizador asigna el puesto y la tarifa acordados a cada registro nuevo. Las tarifas son centavos enteros; estos importes proporcionados son datos del escenario, no un cálculo fiscal ni contable:

./db organizer -c "UPDATE fair.registrations SET booth_label = 'C3', fee_cents = 1800 WHERE registration_id = 'R301' RETURNING registration_id, booth_label, fee_cents;"

./db organizer -c "UPDATE fair.registrations SET booth_label = 'D2', fee_cents = 600 WHERE registration_id = 'R302' RETURNING registration_id, booth_label, fee_cents;"

Deberías ver R301/C3/1800 y R302/D2/600. Este trabajo usa fair_team, no el propietario de la tabla; eludir las reglas como propietario no demostraría que la política del organizador siga siendo utilizable.

Lee los registros finales completos mediante cada expositor:

./db fern -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

./db cedar -c "SELECT registration_id, owner_role, contact, crates, booth_label, fee_cents FROM fair.registrations ORDER BY registration_id;"

Fern ve R101/R102/R301, con el nuevo R301 de recuento 4, puesto C3 y tarifa 1800. Cedar ve R201/R202/R302, con el nuevo R302 de recuento 1, puesto D2 y tarifa 600. Los registros originales deben permanecer sin cambios.

Si repites por accidente un INSERT, inspecciona ese registro nuevo indicado con la cuenta correspondiente. Si ya existe, usa un UPDATE dirigido de su contacto y recuento en lugar de borrarlo o reiniciar la base de datos. Por ejemplo, el registro nuevo de Fern puede corregirse con UPDATE fair.registrations SET contact = 'Fern loading', crates = 4 WHERE registration_id = 'R301';. Corrige solo los registros nuevos indicados; vuelve a ejecutar la asignación dirigida de puesto y tarifa del organizador si cometiste allí un error. Las migraciones guardadas también pueden corregirse y volver a aplicarse sin recrear datos.

Ejecuta la comprobación final del resultado:

sudo python3 /opt/labex-exhibitors/verify.py business

Espera confirmación de que ambas inscripciones nuevas están listas con la propiedad, los recuentos, los puestos y las tarifas correctos. Tienes SQL reutilizable de políticas y un registro compartido que los expositores ordinarios y el organizador pueden usar realmente. El desafío independiente aplica los mismos límites a otro registro de artesanos.

Resumen

Separaste los permisos de tabla y columna de la propiedad de las filas, retiraste una política permisiva amplia y comprobaste tanto las filas existentes como las escrituras propuestas mediante cuentas ordinarias reales. Dos inscripciones nuevas y sus puestos asignados por el organizador funcionan ahora sin exponer ni cambiar los registros de otro expositor.

El límite es la identidad autenticada de base de datos. Los propietarios de tablas y los administradores siguen siendo de confianza, y este ejercicio no proporciona un inicio de sesión web ni una garantía frente a todos los canales laterales de base de datos.