Patrones GRANT/REVOKE

Los privilegios de PostgreSQL son explícitos por defecto. Los nuevos objetos no otorgan automáticamente acceso a los roles de aplicación. Modela deliberadamente los privilegios de esquema, tabla, columna y secuencia.

Receta

-- Ruta de lectura de la aplicación
GRANT USAGE ON SCHEMA app TO app_readers;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_readers;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA app TO app_readers;
 
-- Ruta de escritura de la aplicación (más restringida)
GRANT USAGE ON SCHEMA app TO app_writers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_writers;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_writers;

Cuándo usar esto: Cada esquema nuevo, cada migración que crea tablas y cada rol de microservicio nuevo.

Ejemplo de Trabajo

CREATE SCHEMA app AUTHORIZATION app_owner;
 
CREATE ROLE app_api LOGIN PASSWORD 'vault';
CREATE ROLE app_ro LOGIN PASSWORD 'vault';
GRANT app_readers TO app_ro;
GRANT app_writers TO app_api;
 
-- Nivel de tabla con restricción de columna
CREATE TABLE app.users (
  id bigint PRIMARY KEY,
  email text NOT NULL,
  ssn_last4 char(4)  -- sensible
);
 
GRANT SELECT (id, email) ON app.users TO app_ro;
GRANT SELECT, INSERT, UPDATE ON app.users TO app_api;
REVOKE ALL ON app.users FROM PUBLIC;
 
-- Verificar privilegios efectivos
SELECT grantee, privilege_type, column_name
FROM information_schema.column_privileges
WHERE table_schema = 'app' AND table_name = 'users';

Lo que esto demuestra:

• Se requiere USAGE de esquema antes del acceso a la tabla.
• GRANT SELECT (columnas) a nivel de columna oculta campos sensibles de los analistas.
• REVOKE ALL FROM PUBLIC cierra el agujero de PUBLIC por defecto en clústeres nuevos (al endurecer la seguridad).

Análisis Profundo

Capas de Privilegios

PUBLIC y ACLs por Defecto

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp TO app_readers;

• PostgreSQL otorga históricamente algunos derechos a PUBLIC en el esquema public.
• Las bases endurecidas revocan PUBLIC y otorgan explícitamente a roles de grupo.
• ALTER DEFAULT PRIVILEGES maneja objetos futuros (ver página dedicada).

Propiedad vs Privilegios

• El propietario del objeto omite los privilegios para sus objetos (excepto RLS).
• Las migraciones deben ejecutarse como app_owner NOLOGIN; la conexión en tiempo de ejecución es como app_api.
• REASSIGN OWNED mueve la propiedad durante el cambio de nombre de roles.

Trampas Comunes

• Se olvidó USAGE de esquema - permission denied for schema app a pesar del GRANT de tabla. Solución: GRANT USAGE ON SCHEMA app.
• Secuencia no concedida - INSERT funciona con serial hasta que la secuencia se agota en una nueva fila. Solución: GRANT USAGE, SELECT ON SEQUENCE.
• ALL TABLES no incluye tablas futuras - La migración añade una tabla; la aplicación devuelve 403 hasta que se vuelve a ejecutar el GRANT. Solución: ALTER DEFAULT PRIVILEGES para el rol migrator.
• GRANT de columna + SELECT * - La aplicación usa SELECT * y falla de forma inconsistente en columnas ocultas. Solución: Listas de columnas explícitas en las consultas de la aplicación.
• Superusuario en la cadena de conexión - Los privilegios son irrelevantes; pesadilla de auditoría. Solución: Rol de aplicación de mínimo privilegio siempre.

Alternativas

Preguntas Frecuentes

Relacionado

• Conceptos Básicos de Roles - modelo de roles de grupo
• Privilegios por Defecto - grants automáticos en objetos nuevos
• Row-Level Security - filtros de fila encima de los grants

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+.