En esta página

Seguridad y backups

12 min lectura TextoCap. 5 — SQL en producción

Seguridad y backups

Una base de datos sin seguridad es como una caja fuerte sin cerradura. En esta lección aprenderás a implementar el principio de mínimo privilegio con roles y permisos, a usar Row Level Security para protección a nivel de fila, y a realizar copias de seguridad confiables.

El modelo de seguridad de PostgreSQL

PostgreSQL usa un sistema de roles que combina usuarios y grupos. En versiones modernas, CREATE USER es simplemente un alias de CREATE ROLE WITH LOGIN:

-- Conectado como superusuario (postgres)

-- Crear un rol de aplicación (sin login directo)
CREATE ROLE app_role;

-- Crear usuarios con contraseña
CREATE ROLE api_user WITH
  LOGIN
  PASSWORD 'contraseña_segura_2024!'
  CONNECTION LIMIT 20;      -- Máximo 20 conexiones simultáneas

CREATE ROLE admin_user WITH
  LOGIN
  PASSWORD 'admin_password!'
  SUPERUSER;                -- Privilegios de superusuario (¡usar con cuidado!)

-- Crear rol de solo lectura (reporting)
CREATE ROLE readonly_user WITH
  LOGIN
  PASSWORD 'readonly_pass!'
  VALID UNTIL '2025-12-31'; -- Contraseña expira

-- Ver todos los roles
SELECT rolname, rolcanlogin, rolsuper, rolconnlimit, rolvaliduntil
FROM pg_roles
ORDER BY rolname;

GRANT y REVOKE — Gestionar permisos

El principio de mínimo privilegio: cada rol debe tener solo los permisos que necesita para su función:

-- Conectar a la base de datos de la aplicación
\c mi_aplicacion

-- GRANT en base de datos
GRANT CONNECT ON DATABASE mi_aplicacion TO api_user;
GRANT CONNECT ON DATABASE mi_aplicacion TO readonly_user;

-- GRANT en esquema
GRANT USAGE ON SCHEMA public TO api_user;
GRANT USAGE ON SCHEMA public TO readonly_user;

-- GRANT en tablas específicas
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE usuarios TO api_user;
GRANT SELECT ON TABLE usuarios TO readonly_user;

-- GRANT en todas las tablas del esquema (para API)
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO api_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_user;

-- Para tablas futuras (grant predeterminado)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO api_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly_user;

-- REVOKE: quitar permisos
REVOKE DELETE ON TABLE usuarios FROM api_user;
REVOKE ALL ON DATABASE mi_aplicacion FROM readonly_user;

-- Asignar un rol a otro rol (herencia de permisos)
GRANT app_role TO api_user;   -- api_user hereda los permisos de app_role

Permisos granulares

-- Solo columnas específicas
GRANT SELECT (id, nombre, email) ON usuarios TO readonly_user;
-- readonly_user solo puede ver esas 3 columnas

GRANT UPDATE (nombre, telefono) ON usuarios TO api_user;
-- api_user solo puede actualizar nombre y teléfono (no email, no contraseña)

-- Permiso para ejecutar funciones
GRANT EXECUTE ON FUNCTION calcular_descuento(INTEGER, NUMERIC) TO api_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO api_user;

-- Ver los permisos actuales de una tabla
\dp usuarios
-- O con SQL:
SELECT grantee, privilege_type, is_grantable
FROM information_schema.role_table_grants
WHERE table_name = 'usuarios'
ORDER BY grantee, privilege_type;

Row Level Security (RLS)

RLS es la característica de seguridad más poderosa de PostgreSQL para aplicaciones multi-tenant. Permite que diferentes usuarios vean diferentes filas de la misma tabla, sin que la aplicación deba filtrar manualmente:

-- Ejemplo: sistema multi-tenant donde cada empresa solo ve sus datos

CREATE TABLE empresas (
  id     SERIAL PRIMARY KEY,
  nombre VARCHAR(200) NOT NULL
);

CREATE TABLE documentos (
  id          SERIAL    PRIMARY KEY,
  empresa_id  INTEGER   NOT NULL REFERENCES empresas(id),
  titulo      VARCHAR(300) NOT NULL,
  contenido   TEXT,
  creado_por  INTEGER   NOT NULL REFERENCES usuarios(id),
  creado_en   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

INSERT INTO empresas (nombre) VALUES ('Empresa A'), ('Empresa B');

-- Paso 1: Habilitar RLS en la tabla
ALTER TABLE documentos ENABLE ROW LEVEL SECURITY;

-- Paso 2: Crear políticas de acceso
-- Política de SELECT: cada usuario solo ve documentos de su empresa
CREATE POLICY politica_select_documentos ON documentos
  FOR SELECT
  USING (
    empresa_id = (
      SELECT empresa_id FROM usuarios WHERE id = current_setting('app.user_id')::INTEGER
    )
  );

-- Política de INSERT: solo puede insertar en su propia empresa
CREATE POLICY politica_insert_documentos ON documentos
  FOR INSERT
  WITH CHECK (
    empresa_id = (
      SELECT empresa_id FROM usuarios WHERE id = current_setting('app.user_id')::INTEGER
    )
  );

-- Política de UPDATE/DELETE: solo sus propios documentos
CREATE POLICY politica_update_documentos ON documentos
  FOR UPDATE
  USING (
    creado_por = current_setting('app.user_id')::INTEGER
  );

CREATE POLICY politica_delete_documentos ON documentos
  FOR DELETE
  USING (
    creado_por = current_setting('app.user_id')::INTEGER
    AND empresa_id = (
      SELECT empresa_id FROM usuarios WHERE id = current_setting('app.user_id')::INTEGER
    )
  );

-- Uso en la aplicación: establecer el contexto del usuario antes de las consultas
SET app.user_id = '42';  -- El ID del usuario autenticado
SELECT * FROM documentos;  -- Solo verá los documentos de su empresa

RLS con Supabase (patrón moderno)

-- Patrón común en Supabase/Auth: usar auth.uid() de JWT
CREATE TABLE perfiles (
  id        UUID PRIMARY KEY REFERENCES auth.users(id),
  nombre    VARCHAR(150) NOT NULL,
  empresa   VARCHAR(200)
);

ALTER TABLE perfiles ENABLE ROW LEVEL SECURITY;

-- Los usuarios solo pueden ver/editar su propio perfil
CREATE POLICY "usuarios ven su propio perfil"
  ON perfiles FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "usuarios editan su propio perfil"
  ON perfiles FOR UPDATE
  USING (auth.uid() = id);

-- Los administradores ven todos los perfiles
CREATE POLICY "admins ven todo"
  ON perfiles FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM perfiles
      WHERE id = auth.uid() AND rol = 'admin'
    )
  );

-- BYPASSRLS: los superusuarios ignoran RLS
-- Para que la API ignore RLS (conexión de servidor, no de usuario):
SET ROLE postgres;  -- Superusuario ignora todas las políticas RLS

Backups con pg_dump

-- Los backups se ejecutan desde la línea de comandos (terminal), no desde psql
# Backup completo en formato SQL (texto plano)
pg_dump -U postgres -d mi_aplicacion > backup_$(date +%Y%m%d).sql

# Backup en formato personalizado (comprimido, restauración más rápida)
pg_dump -U postgres -d mi_aplicacion -F c -f backup_$(date +%Y%m%d).dump

# Backup solo del esquema (estructura sin datos)
pg_dump -U postgres -d mi_aplicacion --schema-only -F c -f esquema.dump

# Backup solo de los datos (sin estructura)
pg_dump -U postgres -d mi_aplicacion --data-only -F c -f datos.dump

# Backup de una tabla específica
pg_dump -U postgres -d mi_aplicacion -t usuarios -F c -f usuarios.dump

# Backup completo de todas las bases de datos (incluyendo roles globales)
pg_dumpall -U postgres > backup_completo_$(date +%Y%m%d).sql

# Backup a servidor remoto con compresión
pg_dump -h mi-servidor.com -U postgres -d produccion -F c \
  | gzip > backup_$(date +%Y%m%d_%H%M%S).dump.gz

Restaurar con pg_restore

# Restaurar desde formato SQL (texto plano)
psql -U postgres -d mi_aplicacion_nueva < backup_20240115.sql

# Restaurar desde formato personalizado
pg_restore -U postgres -d mi_aplicacion_nueva backup_20240115.dump

# Restaurar solo una tabla específica
pg_restore -U postgres -d mi_aplicacion -t usuarios backup_20240115.dump

# Restaurar sin detener ante errores (continuar si hay errores menores)
pg_restore -U postgres -d mi_aplicacion --no-owner --no-acl \
  -e backup_20240115.dump

# Ver contenido de un backup sin restaurar
pg_restore -l backup_20240115.dump

# Restauración paralela (más rápida para backups grandes)
pg_restore -U postgres -d mi_aplicacion -j 4 backup_20240115.dump
# -j 4: usar 4 procesos paralelos

Seguridad de conexión (SSL)

-- Verificar si SSL está habilitado
SHOW ssl;

-- Verificar conexiones SSL activas
SELECT
  pid,
  usename,
  ssl,
  ssl_version,
  ssl_cipher,
  client_addr
FROM pg_stat_ssl
JOIN pg_stat_activity USING (pid)
WHERE ssl = true;
# Conectar con SSL forzado
psql "postgresql://usuario:contraseña@servidor:5432/basedatos?sslmode=require"

# sslmode opciones:
# disable: nunca usar SSL
# prefer: usar SSL si está disponible (predeterminado)
# require: siempre usar SSL, no verificar certificado
# verify-ca: verificar que el certificado es de una CA de confianza
# verify-full: verificar el certificado Y el hostname (más seguro)

Buenas prácticas de seguridad

-- 1. Cambiar la contraseña predeterminada de postgres
ALTER USER postgres WITH PASSWORD 'contraseña_muy_segura!';

-- 2. Crear usuarios específicos por aplicación (nunca conectar como postgres en producción)
CREATE ROLE app_produccion WITH LOGIN PASSWORD 'pass_produccion' CONNECTION LIMIT 50;

-- 3. Revocar permisos públicos que PostgreSQL crea por defecto
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE mi_aplicacion FROM PUBLIC;

-- 4. Usar search_path seguro (previene ataques de search path)
ALTER ROLE app_produccion SET search_path TO '$user', public;

-- 5. Registrar intentos de conexión fallidos
-- En postgresql.conf:
-- log_connections = on
-- log_disconnections = on
-- log_failed_connections = on

-- 6. Rotar credenciales regularmente
ALTER ROLE api_user WITH PASSWORD 'nueva_contraseña_2024!';

-- 7. Auditoría: quién accedió a qué
SELECT
  datname,
  usename,
  application_name,
  client_addr,
  backend_start
FROM pg_stat_activity
ORDER BY backend_start DESC;

[!warning] Nunca uses el usuario postgres en producción desde tu aplicación. El usuario postgres es superusuario y puede hacer cualquier cosa: borrar bases de datos, leer todas las tablas, deshabilitar RLS. Crea un usuario específico para tu aplicación con solo los permisos que necesita. Si ese usuario se ve comprometido, el daño potencial es limitado.

[!tip] Automatiza los backups y prueba la restauración. Un backup que nunca has probado restaurar no es un backup confiable. Configura pg_dump en un cron job diario, almacena las copias en un lugar diferente al servidor (S3, otro servidor), y cada mes prueba restaurar en un servidor de prueba. El comando pg_dump -F c (formato custom) es el más recomendado: comprimido, restauración más rápida y selectiva.

Con seguridad y backups dominados, tu base de datos está lista para producción. En la lección final pondrás en práctica todo lo aprendido diseñando e implementando una base de datos de e-commerce completa desde cero.

nextSteps:

  • slug: proyecto-final-sql label: "Siguiente: Proyecto final"