Base de datos, Esquema y search_path

Una base de datos es un catálogo aislado de esquemas. Un esquema agrupa tablas y funciones. search_path controla cómo se resuelven los nombres no calificados. Los errores de producción a menudo provienen de rutas ambiguas, no de tablas faltantes.

Receta

SHOW search_path;
 
ALTER ROLE app_user SET search_path = app, public;
 
-- Prefiere la calificación explícita en las migraciones
CREATE TABLE app.orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES app.customers(id)
);

Cuándo usar esto: Diseñas bases de datos multiaplicación, clústeres compartidos o valores predeterminados específicos del rol.

Ejemplo de Trabajo

CREATE SCHEMA billing AUTHORIZATION postgres;
CREATE SCHEMA reporting AUTHORIZATION postgres;
 
CREATE TABLE billing.invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0)
);
 
CREATE VIEW reporting.invoice_totals AS
SELECT date_trunc('month', now()) AS month, sum(amount) AS total
FROM billing.invoices;
 
SET search_path = reporting, billing;
SELECT * FROM invoice_totals;

Lo que esto demuestra:

• Múltiples esquemas coexisten en una base de datos con referencias entre esquemas.
• Las vistas pueden residir en un esquema diferente al de las tablas base.
• El orden de search_path determina qué objeto prevalece para los nombres no calificados.

Análisis Profundo

Orden de Resolución

1. PostgreSQL verifica los esquemas listados en search_path de izquierda a derecha.
2. Las tablas temporales residen en esquemas pg_temp que se anteponen para la sesión.
3. Los catálogos del sistema siempre son accesibles a través de pg_catalog.

Valores Predeterminados de Rol y Base de Datos

• ALTER DATABASE ... SET search_path se aplica al conectar a menos que el rol lo anule.
• ALTER ROLE ... SET search_path es común para roles de aplicación.
• Las migraciones deben establecer una ruta explícita al principio: SET search_path = app;

Nota de Seguridad

• Los objetos en public son creables por defecto en muchas instalaciones (privilegio CREATE para PUBLIC).
• Bloquea public o usa esquemas dedicados más concesiones de mínimo privilegio.

Trampas

• public es un error común - Cualquier rol puede crear objetos que sombreen las tablas de la aplicación. Solución: REVOKE CREATE ON SCHEMA public FROM PUBLIC; usa esquemas dedicados.
• Nombres no calificados duplicados - app.users y archive.users ambos llamados users. Solución: Califica explícitamente en el SQL enviado por las aplicaciones.
• search_path en funciones SECURITY DEFINER - Los derechos de definidor más una ruta mutable permiten el secuestro. Solución: SET search_path = pg_temp, pg_catalog; en funciones definidoras.
• Orden de volcado/restauración - Los esquemas deben existir antes que los objetos que los referencian. Solución: Usa transacciones únicas de pg_dump o herramientas DDL ordenadas.
• Plegado de mayúsculas/minúsculas - Los identificadores sin comillas se pliegan a minúsculas. Solución: Usa comillas solo cuando sea necesario; prefiere snake_case.

Alternativas

Preguntas Frecuentes

Relacionado

• Conceptos Básicos de PostgreSQL - crear esquema y tabla
• Conceptos Básicos de Roles - valores predeterminados de GUC a nivel de rol
• Riesgos de SECURITY DEFINER - trampas de search_path

Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x y PostGIS 3.5+.