Esquemas y roles

PostgreSQL tiene un sistema de organización y permisos más fino que MySQL. Los esquemas te permiten agrupar tablas dentro de una misma base de datos (como carpetas), y los roles unifican usuarios y grupos con permisos heredables. En esta lección aprenderás a organizar una base de datos profesional, crear un rol de aplicación y uno de solo lectura, y darás tus primeros pasos con Row Level Security, la característica que usa Supabase para proteger los datos de cada usuario.

Concepto

La jerarquía completa de PostgreSQL:

Cluster (servidor)
└── Base de datos  (tienda)            ← te conectas a una
    └── Esquema    (public, ventas...)  ← como carpetas
        └── Objetos (tablas, vistas, funciones, secuencias, tipos)

Analogía: la base de datos es un edificio de oficinas, los esquemas son los pisos (Ventas, Contabilidad, Sistemas) y las tablas son los archiveros de cada piso. Los roles son los gafetes: determinan a qué pisos y archiveros puede entrar cada persona.

	MySQL	PostgreSQL
"Base de datos"	= esquema	Contenedor de esquemas
JOIN entre "bases"	Sí (otra_bd.tabla)	Entre esquemas sí; entre bases no
Usuarios y grupos	Usuarios + roles (8.0)	Todo son roles

Esquemas

Toda base de datos nueva trae el esquema public. Si no indicas esquema, tus tablas van ahí.

-- Crear esquemas para organizar la tienda
CREATE SCHEMA ventas;
CREATE SCHEMA inventario;
CREATE SCHEMA reportes;

-- Crear tablas dentro de un esquema
CREATE TABLE inventario.productos (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nombre TEXT NOT NULL,
  precio NUMERIC(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0
);

CREATE TABLE ventas.pedidos (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  cliente_id INT NOT NULL,
  fecha DATE NOT NULL DEFAULT CURRENT_DATE
);

-- Consultar con el nombre calificado esquema.tabla
SELECT * FROM inventario.productos;

-- JOIN entre esquemas de la misma base: sin problema
-- SELECT ... FROM ventas.pedidos p JOIN inventario.productos pr ON ...

-- Listar esquemas
-- \dn   (en psql)

Usos típicos de los esquemas:

Uso	Ejemplo
Separar áreas de negocio	ventas, inventario, rrhh
Separar datos de reportes	reportes con vistas y vistas materializadas
Extensiones	extensions (Supabase lo hace así)
Multi-inquilino (multi-tenant)	Un esquema por cliente: cliente_acme, cliente_globex
Staging de importaciones	importacion con tablas temporales de carga

search_path: el "PATH" de PostgreSQL

Cuando escribes SELECT * FROM productos sin esquema, PostgreSQL busca en los esquemas listados en search_path, en orden:

SHOW search_path;
-- "$user", public

"$user" significa "un esquema con el mismo nombre que mi rol, si existe". Puedes cambiarlo:

-- Solo para esta sesión
SET search_path TO inventario, ventas, public;
SELECT * FROM productos;     -- encuentra inventario.productos

-- Permanente para un rol
ALTER ROLE tienda_app SET search_path TO inventario, ventas, public;

-- Permanente para una base de datos
ALTER DATABASE tienda SET search_path TO inventario, ventas, public;

Buena práctica

En el código de la aplicación y en las funciones, escribe nombres calificados (ventas.pedidos) o fija el search_path explícitamente. Depender del search_path de cada sesión causa errores difíciles de rastrear ("relation does not exist" en producción pero no en tu PC).

El esquema public desde PostgreSQL 15

A partir de PostgreSQL 15, los usuarios normales ya no pueden crear tablas en public por defecto (antes cualquiera podía). Si tu rol de app recibe permission denied for schema public, dale permiso explícito (abajo) o usa un esquema propio.

Roles

En PostgreSQL todo es un rol. Un rol con LOGIN es lo que llamarías "usuario"; uno sin LOGIN funciona como "grupo".

-- "Usuario": puede iniciar sesión
CREATE ROLE tienda_app WITH LOGIN PASSWORD 'Clave-App-2024!';
-- CREATE USER es un atajo de CREATE ROLE ... LOGIN
CREATE USER ana WITH PASSWORD 'Clave-Ana!';

-- "Grupo": no inicia sesión, solo agrupa permisos
CREATE ROLE lectura NOLOGIN;
CREATE ROLE escritura NOLOGIN;

-- Hacer a ana miembro del grupo lectura (hereda sus permisos)
GRANT lectura TO ana;

-- Listar roles
-- \du   (en psql)

Atributos de un rol:

Atributo	Significado
LOGIN / NOLOGIN	Puede o no iniciar sesión
SUPERUSER	Salta todas las comprobaciones de permisos (como root)
CREATEDB	Puede crear bases de datos
CREATEROLE	Puede crear otros roles
INHERIT (por defecto)	Hereda automáticamente los permisos de los roles de los que es miembro
CONNECTION LIMIT n	Máximo de conexiones simultáneas
VALID UNTIL 'fecha'	La contraseña expira
BYPASSRLS	Ignora Row Level Security

ALTER ROLE ana WITH CONNECTION LIMIT 5 VALID UNTIL '2025-12-31';
ALTER ROLE ana WITH PASSWORD 'Nueva-Clave!';
DROP ROLE IF EXISTS ana;

Tu app nunca debe usar postgres

El rol postgres es superusuario. Si tu aplicación se conecta con él y alguien explota una inyección SQL, puede leer archivos del servidor, borrar todas las bases o ejecutar comandos. Crea un rol por aplicación sin SUPERUSER.

Permisos (GRANT) por niveles

En PostgreSQL los permisos se dan por capas. Para que un rol lea una tabla necesita:

1. CONNECT en la base de datos
2. USAGE en el esquema
3. SELECT en la tabla

Si falta cualquiera, verás permission denied.

GRANT CONNECT ON DATABASE tienda TO tienda_app;
GRANT USAGE ON SCHEMA ventas, inventario TO tienda_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ventas, inventario TO tienda_app;
-- Las columnas IDENTITY/SERIAL usan secuencias: también hacen falta permisos
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA ventas, inventario TO tienda_app;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA ventas TO tienda_app;

DEFAULT PRIVILEGES: el detalle que todos olvidan

GRANT ... ON ALL TABLES solo afecta a las tablas que ya existen. Si mañana una migración crea ventas.pagos, la app no podrá usarla. Solución:

-- "A partir de ahora, las tablas que cree el rol tienda_owner en estos esquemas
--  tendrán estos permisos para tienda_app"
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA ventas, inventario
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tienda_app;

ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA ventas, inventario
  GRANT USAGE, SELECT ON SEQUENCES TO tienda_app;

FOR ROLE indica qué rol creará los objetos futuros (normalmente el que ejecuta las migraciones).

Plantilla profesional: dueño, app y solo lectura

-- Ejecutar como superusuario (postgres)

-- 1. Rol dueño: crea y modifica la estructura (lo usan las migraciones)
CREATE ROLE tienda_owner WITH LOGIN PASSWORD 'CAMBIAR_owner';
CREATE DATABASE tienda OWNER tienda_owner;

\c tienda

-- Quitar permisos genéricos de public (buena práctica)
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE tienda FROM PUBLIC;

-- Esquema de la aplicación, propiedad del owner
CREATE SCHEMA app AUTHORIZATION tienda_owner;

-- 2. Rol de la aplicación: solo datos (CRUD)
CREATE ROLE tienda_app WITH LOGIN PASSWORD 'CAMBIAR_app';
GRANT CONNECT ON DATABASE tienda TO tienda_app;
GRANT USAGE ON SCHEMA app TO tienda_app;
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO tienda_app;
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO tienda_app;
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA app
  GRANT EXECUTE ON FUNCTIONS TO tienda_app;

-- 3. Grupo de solo lectura y un analista que lo usa
CREATE ROLE solo_lectura NOLOGIN;
GRANT CONNECT ON DATABASE tienda TO solo_lectura;
GRANT USAGE ON SCHEMA app TO solo_lectura;
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO solo_lectura;

CREATE ROLE analista WITH LOGIN PASSWORD 'CAMBIAR_analista' CONNECTION LIMIT 3;
GRANT solo_lectura TO analista;

-- 4. search_path cómodo para todos
ALTER DATABASE tienda SET search_path TO app, public;

Ahora las migraciones se ejecutan como tienda_owner, la app se conecta como tienda_app y el analista como analista, cada uno con lo justo.

-- Comprobar permisos
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'tienda_app';

-- ¿Puede este rol hacer X?
SELECT has_table_privilege('analista', 'app.pedidos', 'DELETE');   -- false

Permisos por columna

-- Marketing ve clientes pero no su email ni teléfono
GRANT SELECT (id, nombre, ciudad, fecha_registro) ON app.clientes TO marketing;

Row Level Security (RLS)

Los permisos normales controlan qué tablas puede usar un rol. RLS controla qué filas de una tabla puede ver o modificar. Es la base de la seguridad de Supabase: cada usuario solo ve sus propios pedidos, aunque todos consulten la misma tabla.

CREATE TABLE app.notas (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  propietario TEXT NOT NULL DEFAULT current_user,
  contenido TEXT NOT NULL
);

-- 1. Activar RLS en la tabla
ALTER TABLE app.notas ENABLE ROW LEVEL SECURITY;

-- 2. Política: cada rol ve y modifica solo sus filas
CREATE POLICY notas_del_propietario ON app.notas
  FOR ALL                                   -- SELECT, INSERT, UPDATE, DELETE
  USING (propietario = current_user)        -- qué filas puede ver/modificar
  WITH CHECK (propietario = current_user);  -- qué filas puede insertar/dejar tras un UPDATE

GRANT SELECT, INSERT, UPDATE, DELETE ON app.notas TO ana, luis;
GRANT USAGE ON SCHEMA app TO ana, luis;

-- Conectado como ana
INSERT INTO app.notas (contenido) VALUES ('Comprar café');
SELECT * FROM app.notas;           -- solo ve sus notas

-- Conectado como luis
SELECT * FROM app.notas;           -- no ve las de ana
UPDATE app.notas SET contenido = 'hackeado';   -- UPDATE 0: no hay filas visibles

En aplicaciones web, todos los usuarios usan el mismo rol de base de datos, así que la política no puede basarse en current_user. Se usa una variable de sesión que la app define en cada petición:

CREATE POLICY pedidos_del_cliente ON app.pedidos
  FOR SELECT
  USING (cliente_id = current_setting('app.cliente_id')::int);

-- La app, al inicio de cada transacción:
-- SET LOCAL app.cliente_id = '42';

Supabase lo hace con auth.uid(), una función que lee el usuario autenticado del token JWT. Lo verás en PostgreSQL en la nube.

RLS y el dueño de la tabla

El dueño de la tabla y los superusuarios ignoran RLS por defecto. Para aplicarlo también al dueño: ALTER TABLE app.notas FORCE ROW LEVEL SECURITY;. Y recuerda: con RLS activo y sin políticas, nadie (salvo el dueño) ve ninguna fila.

Ejercicios

1. Crea el esquema reportes y una vista reportes.ventas_por_dia sobre tus tablas de pedidos.
2. Crea el rol dashboard (con LOGIN) que solo pueda leer el esquema reportes, incluidas las vistas que se creen en el futuro por tienda_owner.
3. Te llega el error permission denied for schema app al conectarte como tienda_app. ¿Qué permiso falta?
4. Crea una política RLS para que el rol vendedor_norte solo vea los clientes con region = 'norte'.

Ver solución

-- 1
CREATE SCHEMA reportes AUTHORIZATION tienda_owner;
CREATE VIEW reportes.ventas_por_dia AS
SELECT p.fecha, SUM(d.cantidad * d.precio_unitario) AS ventas
FROM app.pedidos p
JOIN app.detalle_pedido d ON d.pedido_id = p.id
GROUP BY p.fecha;

-- 2
CREATE ROLE dashboard WITH LOGIN PASSWORD 'Clave-Dashboard!';
GRANT CONNECT ON DATABASE tienda TO dashboard;
GRANT USAGE ON SCHEMA reportes TO dashboard;
GRANT SELECT ON ALL TABLES IN SCHEMA reportes TO dashboard;   -- incluye vistas existentes
ALTER DEFAULT PRIVILEGES FOR ROLE tienda_owner IN SCHEMA reportes
  GRANT SELECT ON TABLES TO dashboard;

Nota: para leer la vista basta el permiso sobre la vista (se ejecuta con los permisos de su dueño), no sobre las tablas de app.

3. Falta GRANT USAGE ON SCHEMA app TO tienda_app; (el permiso sobre las tablas no sirve sin acceso al esquema).

-- 4
ALTER TABLE app.clientes ENABLE ROW LEVEL SECURITY;
CREATE POLICY clientes_norte ON app.clientes
  FOR SELECT TO vendedor_norte
  USING (region = 'norte');
GRANT USAGE ON SCHEMA app TO vendedor_norte;
GRANT SELECT ON app.clientes TO vendedor_norte;

Resumen

• Jerarquía: cluster → base de datos → esquema → tablas. Los esquemas organizan una base como carpetas.
• search_path define dónde se buscan los nombres sin esquema; en código, califica los nombres.
• Todo es un rol: con LOGIN es usuario, sin LOGIN es grupo; los miembros heredan permisos.
• Permisos por capas: CONNECT (BD) → USAGE (esquema) → SELECT/INSERT... (tabla) + secuencias.
• ALTER DEFAULT PRIVILEGES cubre las tablas futuras. Separa roles: dueño, app y solo lectura.
• RLS filtra filas por usuario con políticas USING y WITH CHECK.