0Pricing
SQL Academy · Lección

Auditoría de accesos

Registre quién puede ver cada dato

Auditoría de accesos es una lección gratuita de SQL Academy en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de SQL Academy, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Academy incluye 4 lecciones en total.

¿Por qué auditar el acceso?

Saber quién accedió a qué datos y cuándo es un pilar de la seguridad de las bases de datos. La auditoría crea un registro fiable de eventos que permite detectar accesos no autorizados, investigar incidentes y cumplir requisitos normativos como GDPR, HIPAA o SOC 2.

En esta lección aprenderá a diseñar tablas de auditoría, capturar eventos de acceso automáticamente con triggers, utilizar las funciones de registro integradas de PostgreSQL y consultar el registro de auditoría para responder a la pregunta: ¿quién puede ver qué?

Diseño de una tabla de registro de auditoría

El primer paso es contar con una tabla dedicada que registre cada evento importante. Un buen registro de auditoría almacena el nombre de la tabla, el tipo de operación, los valores antiguos y nuevos, el usuario que realizó la acción y la marca de tiempo exacta.

El ejemplo siguiente crea una tabla audit_log de propósito general que utiliza columnas JSONB para almacenar instantáneas de las filas, con la flexibilidad suficiente para gestionar cualquier tabla sin cambios en el esquema.

CREATE TABLE audit_log (
  id          BIGSERIAL PRIMARY KEY,
  event_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
  db_user     TEXT NOT NULL DEFAULT current_user,
  app_user    TEXT,
  table_name  TEXT NOT NULL,
  operation   TEXT NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE','SELECT')),
  row_id      BIGINT,
  old_data    JSONB,
  new_data    JSONB
);

Registro del usuario actual

PostgreSQL proporciona varias funciones integradas para identificar quién ejecuta una consulta. current_user devuelve el nombre del rol activo después de cualquier SET ROLE. session_user siempre devuelve el rol original de inicio de sesión, independientemente de los cambios de rol.

En aplicaciones que utilizan un único rol de base de datos compartido, pero pasan un usuario de nivel de aplicación mediante SET LOCAL app.current_user, puede leer ese valor con current_setting().

-- Who is the database user right now?
SELECT current_user,
       session_user;

-- Read an application-level user injected by the app layer
SELECT current_setting('app.current_user', true) AS app_user;

Escritura de una función trigger de auditoría

Una función trigger es la forma más fiable de capturar eventos de cambio de datos, porque se ejecuta automáticamente y ningún código de la aplicación puede omitirla. La función siguiente registra cada INSERT, UPDATE y DELETE realizado en cualquier tabla a la que esté asociada, y almacena los valores antiguos y nuevos de la fila como JSONB.

Observe el uso de TG_TABLE_NAME (la tabla que activó el trigger) y de row_to_json() para convertir los valores de la fila a un formato almacenable.

CREATE OR REPLACE FUNCTION fn_audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (
    db_user,
    app_user,
    table_name,
    operation,
    row_id,
    old_data,
    new_data
  ) VALUES (
    current_user,
    current_setting('app.current_user', true),
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE row_to_json(OLD)::JSONB END,
    CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE row_to_json(NEW)::JSONB END
  );
  RETURN NULL;
END;
$$;

Asociación del trigger a una tabla

Una vez creada la función trigger, puede asociarla a cada tabla que desee auditar mediante una instrucción CREATE TRIGGER. Usar AFTER garantiza que los datos se hayan escrito realmente antes de crear la entrada del registro. La cláusula FOR EACH ROW ejecuta el trigger una vez por cada fila modificada.

Aquí el trigger se aplica a una tabla hipotética patients y registra automáticamente cada INSERT, UPDATE y DELETE.

CREATE TRIGGER trg_audit_patients
AFTER INSERT OR UPDATE OR DELETE
ON patients
FOR EACH ROW
EXECUTE FUNCTION fn_audit_changes();

Auditoría de consultas SELECT

Los triggers de cambios de datos solo capturan escrituras. Para auditar el acceso de lectura necesita un enfoque diferente. Una opción es un trigger a nivel de instrucción AFTER SELECT (compatible con PostgreSQL 14+ en ciertos contextos). Un patrón más habitual consiste en registrar explícitamente las lecturas dentro de una función o vista que envuelva la tabla confidencial.

El ejemplo siguiente envuelve una tabla confidencial en una función que registra cada lectura antes de devolver los resultados.

CREATE OR REPLACE FUNCTION get_patient_record(p_id INT)
RETURNS SETOF patients
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  -- Log the read access
  INSERT INTO audit_log (db_user, app_user, table_name, operation, row_id)
  VALUES (
    current_user,
    current_setting('app.current_user', true),
    'patients',
    'SELECT',
    p_id
  );

  RETURN QUERY
  SELECT * FROM patients WHERE id = p_id;
END;
$$;

Registro integrado de PostgreSQL

El archivo postgresql.conf de PostgreSQL ofrece un potente registro del lado del servidor que no requiere código de la aplicación. Configurar log_min_duration_statement registra cualquier consulta que supere un umbral. Configurar log_connections y log_disconnections registra quién inicia y cierra sesión.

La consulta siguiente utiliza la vista del sistema pg_stat_activity para consultar las sesiones activas en ese momento, una forma ligera de supervisar el acceso en tiempo real.

-- See who is currently connected and what they are running
SELECT pid,
       usename        AS db_user,
       application_name,
       client_addr,
       state,
       query_start,
       LEFT(query, 80) AS current_query
FROM   pg_stat_activity
WHERE  datname = current_database()
ORDER  BY query_start DESC;

Consultar el registro de auditoría

Un registro de auditoría solo es valioso si puede consultarlo de forma eficaz. Entre las preguntas habituales se incluyen: qué usuario accedió más recientemente a un registro, qué cambió en una fila con el tiempo y cuántas lecturas de datos sensibles se realizaron en las últimas 24 horas.

La consulta siguiente busca todos los usuarios que accedieron a un registro de paciente específico, ordenados de más reciente a más antiguo.

SELECT event_time,
       db_user,
       app_user,
       operation,
       old_data,
       new_data
FROM   audit_log
WHERE  table_name = 'patients'
  AND  row_id = 42
ORDER  BY event_time DESC
LIMIT  20;

Detectar patrones de acceso sospechosos

Una vez recopilados los datos de auditoría, puede escribir consultas que señalen anomalías. Por ejemplo, que un usuario lea de repente muchas más filas de lo habitual o que se acceda varias veces al mismo registro sensible en un intervalo corto puede indicar un intento de exfiltración de datos.

La consulta siguiente cuenta los eventos SELECT por usuario de la aplicación durante la última hora y señala a quienes hayan leído más de 100 filas.

SELECT app_user,
       COUNT(*) AS records_accessed
FROM   audit_log
WHERE  operation = 'SELECT'
  AND  event_time >= now() - INTERVAL '1 hour'
GROUP  BY app_user
HAVING COUNT(*) > 100
ORDER  BY records_accessed DESC;

Proteger el propio registro de auditoría

Un registro de auditoría que se puede modificar no es confiable. Debe protegerlo para que los usuarios normales y los roles de la aplicación no puedan eliminar ni actualizar filas. El enfoque más seguro consiste en conceder únicamente INSERT al rol de la aplicación y reservar SELECT para un rol de auditoría dedicado.

También puede imponer la inmutabilidad mediante un trigger que lance una excepción si alguien intenta actualizar o eliminar una fila de auditoría.

-- Only the app role may insert; nobody may update or delete
REVOKE ALL     ON audit_log FROM PUBLIC;
GRANT  INSERT  ON audit_log TO app_role;
GRANT  SELECT  ON audit_log TO auditor_role;

-- Trigger to block any tampering
CREATE OR REPLACE FUNCTION fn_protect_audit()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log rows are immutable';
  RETURN NULL;
END;
$$;

CREATE TRIGGER trg_protect_audit
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION fn_protect_audit();

Auditar las decisiones de las políticas de RLS

Cuando la seguridad de nivel de fila está activa, PostgreSQL oculta silenciosamente las filas en lugar de generar errores. Esto dificulta saber si un usuario intentó leer una fila que no tenía permiso para ver. Una técnica consiste en añadir una política permisiva que siempre inserte un registro de auditoría antes de que la política restrictiva filtre las filas.

La consulta siguiente muestra cómo inspeccionar qué políticas de RLS existen en una tabla y a qué roles se aplican.

-- View all RLS policies on the patients table
SELECT polname       AS policy_name,
       polcmd        AS command,
       polroles::TEXT AS applies_to,
       polqual::TEXT  AS using_expression,
       polwithcheck::TEXT AS with_check_expression
FROM   pg_policy
WHERE  polrelid = 'patients'::REGCLASS
ORDER  BY polname;

Comprobación de conocimientos

Compruebe su comprensión de la auditoría de accesos en SQL.

Resumen de la lección

En esta lección exploró cómo crear un sistema completo de auditoría de accesos en PostgreSQL:

  • Tabla de registro de auditoría — una tabla basada en JSONB que registra quién hizo qué y cuándo.
  • Función trigger — registra automáticamente los eventos INSERT, UPDATE y DELETE de cualquier tabla asociada mediante TG_TABLE_NAME, row_to_json() y current_user.
  • Auditoría de lecturas — envuelve las tablas sensibles en funciones que registran los eventos SELECT antes de devolver los datos.
  • Supervisión integrada — pg_stat_activity muestra las sesiones activas; el registro del servidor captura las consultas sin cambios en el código.
  • Detección de anomalías — las consultas de agregación sobre el registro de auditoría pueden señalar volúmenes de acceso inusuales.
  • Inmutabilidad — revoca UPDATE/DELETE en la tabla de auditoría y añade un trigger de bloqueo para evitar manipulaciones.

Un registro de auditoría bien diseñado es la herramienta más fiable para responder a quién accedió a qué y constituye la base de cualquier investigación de cumplimiento o seguridad.

Preguntas frecuentes

¿La lección «Auditoría de accesos» es gratis?

Sí — el texto completo de «Auditoría de accesos» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de SQL Academy, actualiza a CoddyKit PRO. El curso de SQL Academy incluye 4 lecciones en total.

¿Qué aprenderé en «Auditoría de accesos»?

Registre quién puede ver cada dato Practicas SQL Academy con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar SQL Academy?

No se requiere experiencia previa. SQL Academy en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Auditoría de accesos»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de SQL Academy?

Sí. Cada lección de SQL Academy incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

  1. Roles y privilegios
  2. Políticas de seguridad a nivel de fila
  3. Permisos a nivel de columna
  4. Auditoría de accesos
← Volver a SQL Academy