PostgreSQL
Databases, schemas y search_path
Diferencia databases y schemas en PostgreSQL y explica cómo search_path resuelve nombres, afecta seguridad y organiza objetos por contexto.
- Última actualización
- Actualizada
- Nivel
- Fundamentos
PostgreSQL
Diferencia databases y schemas en PostgreSQL y explica cómo search_path resuelve nombres, afecta seguridad y organiza objetos por contexto.
Una instancia de PostgreSQL contiene varias databases; cada conexión entra a una sola database. Dentro de ella, los schemas organizan nombres y permisos. search_path decide qué objeto se encuentra cuando una consulta omite el schema.
La jerarquía lógica es:
instance / database cluster
├─ database A
│ ├─ schema public
│ ├─ schema app
│ └─ schema audit
└─ database B
└─ schema publicUna database posee su propio catálogo de schemas, tablas, funciones, extensiones y privilegios. Una sesión no puede hacer un join directo con otra database usando nombres normales. Un schema, en cambio, es un namespace dentro de la misma database y comparte transacciones, conexiones y recursos con los demás schemas.
Sin namespaces, todas las tablas y funciones competirían por nombres globales. Los schemas permiten:
Pero search_path introduce resolución implícita. Si no se controla, una consulta puede encontrar un objeto distinto del esperado o una función SECURITY DEFINER puede ejecutar código creado por un usuario no confiable.
Aporta un límite fuerte de catálogo y conexión.
Ventajas:
Costes:
Aporta un namespace dentro de una database.
Ventajas:
Costes:
search_path puede ocultar qué objeto se usa.SELECT id, status
FROM app.orders;app.orders es un nombre calificado. Expresa de forma explícita el schema y evita depender de resolución implícita.
En migraciones, funciones privilegiadas y consultas sensibles conviene calificar objetos. En consultas ordinarias de una aplicación puede utilizarse un search_path controlado, siempre que no admita schemas donde usuarios no confiables puedan crear objetos.
SHOW search_path;
SET search_path TO app, public;Cuando PostgreSQL encuentra un nombre sin schema:
SELECT * FROM orders;busca orders siguiendo el path. El primer objeto visible compatible gana.
Elementos especiales:
"$user": schema con el nombre del rol actual, si existe y es accesible.pg_catalog: contiene objetos del sistema y se busca implícitamente; puede incluirse explícitamente para controlar posición.Supón que una función privilegiada ejecuta:
SELECT calculate_total(order_id);Si su search_path incluye primero un schema donde otro usuario tiene CREATE, ese usuario podría definir una función con el mismo nombre y firma. La función privilegiada podría resolver el objeto malicioso.
Mitigación:
CREATE FUNCTION app.secure_operation(...)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, app
AS $$
BEGIN
PERFORM app.calculate_total(...);
END;
$$;También revoca CREATE en schemas compartidos a roles no confiables.
public existe por defecto en la mayoría de databases. No asumas sus privilegios sin inspeccionarlos:
SELECT nspname, nspowner::regrole
FROM pg_namespace
WHERE nspname = 'public';
\dn+En versiones e instalaciones modernas, defaults de ownership y CREATE pueden diferir de configuraciones históricas. Revisa el estado real.
Para utilizar un objeto dentro de un schema normalmente intervienen:
USAGE sobre schema
+
privilegio sobre objetoEjemplo:
GRANT USAGE ON SCHEMA app TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO app_role;Para objetos futuros:
ALTER DEFAULT PRIVILEGES FOR ROLE migration_role IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;Los default privileges pertenecen al rol que crea los objetos. Ejecutarlos como otro owner no afecta futuras tablas creadas por migration_role.
Un diseño posible:
app
→ tablas principales y funciones de aplicación
audit
→ eventos de auditoría
reporting
→ views y materialized views de lectura
staging
→ cargas temporales y validaciónEsto puede mejorar ownership y navegación, pero no conviertas cada carpeta del backend en un schema. El schema debe representar una frontera útil de nombres, permisos o lifecycle.
Puedes ocultar tablas internas y exponer views:
CREATE VIEW api.order_summary AS
SELECT id, customer_id, status, total_amount
FROM app.orders;
GRANT SELECT ON api.order_summary TO reporting_role;El consumidor depende de la view, no de columnas internas. Esto crea una API de datos, aunque también añade mantenimiento y debe considerar seguridad de views, ownership y cambios compatibles.
Modelo:
tenant_001.orders
tenant_002.orders
...Puede ofrecer separación lógica, pero introduce:
search_path por request.No es automáticamente más seguro que tablas compartidas con tenant_id y Row-Level Security. La elección depende de cantidad de tenants, aislamiento, operación y consultas cruzadas.
SET search_path TO tenant_123, app;Este cambio vive en la sesión. Si una conexión vuelve al pool sin reset, la siguiente request puede consultar el tenant anterior.
Opciones:
SET LOCAL dentro de una transacción.Herramientas como postgres_fdw o dblink permiten acceso externo, pero no crean foreign keys ni transacciones simples equivalentes a una sola database.
Úsalas cuando exista una razón real de integración. No separes datos estrechamente relacionados en databases para después reconstruir continuamente joins remotos.
Requisito:
Diseño:
CREATE SCHEMA app AUTHORIZATION migration_role;
CREATE SCHEMA reporting AUTHORIZATION migration_role;
CREATE TABLE app.orders (...);
CREATE VIEW reporting.orders AS
SELECT id, status, created_at
FROM app.orders;
GRANT USAGE ON SCHEMA app TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.orders TO app_role;
GRANT USAGE ON SCHEMA reporting TO reporting_role;
GRANT SELECT ON reporting.orders TO reporting_role;Flujo:
migration_role conserva ownership.app_role modifica únicamente objetos necesarios.reporting_role no accede directamente a la tabla.app.orders y archive.orders pueden coexistir. Una consulta sin calificar depende del path.
Algunas extensiones instalan objetos en un schema configurable; otras tienen requisitos propios. Revisa dónde quedan y quién puede ejecutar sus funciones.
Rompe SQL calificado, views, funciones y configuración externa. Es una migración de contrato.
Una tabla temporal puede ocultar un nombre permanente dentro de la sesión. Evita depender de resolución ambigua.
Un dump puede recrear ownership y ACLs. Verifica roles y opciones de restore en otro entorno.
Pierdes joins, FKs y transacciones sin obtener una frontera de lifecycle real.
El path puede variar por rol, database, función o conexión. Configúralo explícitamente.
Abre vectores de resolución y objetos no controlados.
Puede filtrar datos entre requests.
Los schemas comparten el mismo cluster, WAL y recursos.
SELECT current_database();
SELECT current_schema();
SHOW search_path;
SELECT current_schemas(true);
SELECT has_schema_privilege('app_role', 'app', 'USAGE');
SELECT has_table_privilege('app_role', 'app.orders', 'SELECT');Prueba con el rol real de aplicación, no solo como superuser.
search_path resuelve nombres en orden y puede ser un riesgo.USAGE, permisos de objetos y default privileges son controles distintos.search_path?ALTER DEFAULT PRIVILEGES debe ejecutarse para el rol creador correcto?Crear y evolucionar tablas transforma el modelo lógico en objetos físicos y explica cómo cambiar el esquema sin romper datos ni aplicaciones.