Gestión de Usuarios y Permisos en PostgreSQL: Guía Completa de Seguridad

Introducción

El sistema de privilegios robusto y sofisticado de PostgreSQL es una de sus características destacadas, proporcionando a los administradores de bases de datos un control detallado sobre el acceso de usuarios y permisos. La gestión adecuada de usuarios y permisos en PostgreSQL es esencial para mantener la seguridad de datos, asegurar el cumplimiento de regulaciones y proteger información sensible del acceso o modificación no autorizados. En un entorno donde las brechas de datos pueden resultar en daños financieros y reputacionales severos, implementar un control de acceso integral no es opcional—es obligatorio.

A diferencia de sistemas de bases de datos más simples, PostgreSQL implementa un sistema de privilegios multi-capa que opera a nivel de clúster, base de datos, esquema, tabla, columna e incluso nivel de fila. Este enfoque granular permite a los administradores implementar el principio de mínimo privilegio efectivamente, asegurando que los usuarios solo puedan realizar operaciones necesarias para sus roles específicos. Comprender las sutilezas de este sistema, desde la creación básica de usuarios hasta políticas avanzadas de seguridad a nivel de fila, es crucial para cualquier persona responsable de la administración de bases de datos PostgreSQL.

Esta guía completa explora cada aspecto de la gestión de usuarios y permisos de PostgreSQL, cubriendo creación de roles, asignación de privilegios, control de acceso basado en esquemas, implementación de seguridad a nivel de fila y características avanzadas como etiquetas de seguridad y permisos a nivel de columna. Ya sea que estés configurando una nueva instalación de PostgreSQL o auditando una existente, esta guía proporciona el conocimiento y ejemplos prácticos necesarios para construir y mantener un entorno de base de datos seguro y bien administrado.

Requisitos Previos

Antes de sumergirte en la gestión de usuarios y permisos de PostgreSQL, asegúrate de tener:

Requisitos del Sistema

  • PostgreSQL 12+ instalado (PostgreSQL 14+ recomendado)
  • Acceso a un servidor Linux (Ubuntu 20.04+, CentOS 8+ o Debian 11+)
  • Acceso sudo o root al sistema operativo
  • Servicio PostgreSQL ejecutándose y accesible

Acceso a PostgreSQL

  • Capacidad para conectarse como superusuario postgres
  • Herramientas cliente de PostgreSQL (psql) instaladas
  • Conocimiento de las credenciales del usuario postgres

Requisitos de Conocimiento

  • Sintaxis básica de consultas SQL
  • Comprensión de conceptos de base de datos (bases de datos, esquemas, tablas)
  • Fundamentos de línea de comandos de Linux
  • Comprensión básica de control de acceso basado en roles

Herramientas Recomendadas

  • Interfaz de línea de comandos psql
  • pgAdmin 4 o herramienta GUI similar (opcional)
  • Editor de texto para archivos de configuración

Comprendiendo Roles y Privilegios de PostgreSQL

Concepto de Rol

En PostgreSQL, los términos "usuario" y "rol" se usan a menudo de manera intercambiable. Un rol puede ser:

  • Rol de Inicio de Sesión: Puede conectarse a la base de datos ("usuario" tradicional)
  • Rol de Grupo: Contiene otros roles (como un grupo)
  • Ambos: Un rol puede tener privilegios de inicio de sesión y contener otros roles

Atributos de Rol

Los roles pueden tener varios atributos:

  • LOGIN: Capacidad para conectarse a base de datos
  • SUPERUSER: Omitir todas las verificaciones de permisos
  • CREATEDB: Crear bases de datos
  • CREATEROLE: Crear otros roles
  • REPLICATION: Usado para conexiones de replicación
  • BYPASSRLS: Omitir seguridad a nivel de fila
  • CONNECTION LIMIT: Conexiones concurrentes máximas

Tipos de Privilegio

Privilegios a Nivel de Base de Datos:

  • CONNECT: Conectarse a la base de datos
  • CREATE: Crear esquemas dentro de la base de datos
  • TEMP/TEMPORARY: Crear tablas temporales

Privilegios a Nivel de Esquema:

  • CREATE: Crear objetos dentro del esquema
  • USAGE: Acceder a objetos dentro del esquema

Privilegios a Nivel de Tabla:

  • SELECT: Leer datos
  • INSERT: Agregar nuevas filas
  • UPDATE: Modificar filas existentes
  • DELETE: Eliminar filas
  • TRUNCATE: Vaciar tabla
  • REFERENCES: Crear restricciones de clave externa
  • TRIGGER: Crear disparadores

Privilegios a Nivel de Columna:

  • SELECT: Leer columnas específicas
  • INSERT: Insertar en columnas específicas
  • UPDATE: Actualizar columnas específicas
  • REFERENCES: Referenciar en claves externas

Privilegios de Secuencia:

  • USAGE: Usar secuencia con nextval, currval
  • SELECT: Ver valor de secuencia
  • UPDATE: Modificar valor de secuencia

Privilegios de Función/Procedimiento:

  • EXECUTE: Ejecutar función o procedimiento

Gestión de Usuarios Paso a Paso

Paso 1: Conectarse a PostgreSQL

Conectarse como superusuario postgres:

sudo -u postgres psql

O con autenticación por contraseña:

psql -U postgres -h localhost

Verificar tu rol y base de datos actual:

SELECT current_user, current_database();

Paso 2: Ver Roles Existentes

Listar todos los roles en el clúster PostgreSQL:

-- Listar todos los roles
\du

-- O usando SQL
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin
FROM pg_roles
ORDER BY rolname;

-- Ver información detallada de roles
SELECT rolname,
       rolsuper AS is_superuser,
       rolcreaterole AS can_create_role,
       rolcreatedb AS can_create_db,
       rolcanlogin AS can_login,
       rolconnlimit AS connection_limit,
       rolvaliduntil AS valid_until
FROM pg_roles;

-- Ver membresía de roles
SELECT r.rolname AS role,
       m.rolname AS member
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
ORDER BY r.rolname, m.rolname;

Paso 3: Crear Roles Básicos

Crear roles con diferentes atributos:

-- Crear rol básico de inicio de sesión (usuario)
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassword123!';

-- Crear rol con múltiples atributos
CREATE ROLE developer WITH
    LOGIN
    PASSWORD 'SecurePassword123!'
    CREATEDB
    VALID UNTIL '2025-12-31';

-- Crear rol con límite de conexión
CREATE ROLE limited_user WITH
    LOGIN
    PASSWORD 'SecurePassword123!'
    CONNECTION LIMIT 5;

-- Crear rol de grupo (sin inicio de sesión)
CREATE ROLE developers;

-- Crear rol de solo lectura
CREATE ROLE readonly WITH LOGIN PASSWORD 'SecurePassword123!';

-- Crear superusuario (usar con precaución)
CREATE ROLE admin_user WITH
    LOGIN
    PASSWORD 'SecurePassword123!'
    SUPERUSER
    CREATEDB
    CREATEROLE;

Paso 4: Modificar Roles Existentes

Alterar atributos de rol:

-- Cambiar contraseña
ALTER ROLE app_user WITH PASSWORD 'NewSecurePassword456!';

-- Agregar capacidad de inicio de sesión
ALTER ROLE developers WITH LOGIN;

-- Establecer expiración de contraseña
ALTER ROLE app_user VALID UNTIL '2025-12-31';

-- Eliminar expiración de contraseña
ALTER ROLE app_user VALID UNTIL 'infinity';

-- Cambiar límite de conexión
ALTER ROLE app_user CONNECTION LIMIT 10;

-- Otorgar privilegio CREATEDB
ALTER ROLE developer WITH CREATEDB;

-- Revocar privilegio CREATEDB
ALTER ROLE developer WITH NOCREATEDB;

-- Renombrar rol
ALTER ROLE old_name RENAME TO new_name;

Paso 5: Otorgar Privilegios a Nivel de Base de Datos

Otorgar acceso y privilegios de base de datos:

-- Otorgar conexión a base de datos
GRANT CONNECT ON DATABASE myapp_db TO app_user;

-- Otorgar capacidad de crear esquemas
GRANT CREATE ON DATABASE myapp_db TO developer;

-- Otorgar creación de tabla temporal
GRANT TEMP ON DATABASE myapp_db TO app_user;

-- Otorgar todos los privilegios de base de datos
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO admin_user;

-- Revocar conexión
REVOKE CONNECT ON DATABASE myapp_db FROM app_user;

Paso 6: Otorgar Privilegios a Nivel de Esquema

Controlar acceso a esquemas:

-- Conectarse a la base de datos
\c myapp_db

-- Otorgar uso de esquema (requerido para acceder a objetos)
GRANT USAGE ON SCHEMA public TO app_user;

-- Otorgar capacidad de crear objetos en esquema
GRANT CREATE ON SCHEMA public TO developer;

-- Otorgar tanto uso como creación
GRANT USAGE, CREATE ON SCHEMA public TO developer;

-- Revocar acceso a esquema
REVOKE ALL ON SCHEMA public FROM app_user;

-- Otorgar acceso a todos los esquemas
GRANT USAGE ON ALL SCHEMAS IN DATABASE myapp_db TO app_user;

Paso 7: Otorgar Privilegios a Nivel de Tabla

Otorgar privilegios en tablas:

-- Otorgar SELECT en tabla específica
GRANT SELECT ON users TO readonly;

-- Otorgar múltiples privilegios en tabla
GRANT SELECT, INSERT, UPDATE ON users TO app_user;

-- Otorgar todos los privilegios en tabla
GRANT ALL PRIVILEGES ON users TO admin_user;

-- Otorgar privilegios en todas las tablas en esquema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

-- Otorgar privilegios en todas las tablas y establecer como predeterminado para tablas futuras
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Otorgar con opción de otorgamiento (puede otorgar a otros)
GRANT SELECT ON users TO app_user WITH GRANT OPTION;

-- Revocar privilegios
REVOKE INSERT, UPDATE, DELETE ON users FROM app_user;

Paso 8: Otorgar Privilegios a Nivel de Columna

Implementar seguridad a nivel de columna:

-- Crear tabla con columnas sensibles
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    ssn VARCHAR(11)
);

-- Otorgar SELECT solo en columnas específicas
GRANT SELECT (id, name, email, department) ON employees TO app_user;

-- Otorgar UPDATE en columnas específicas
GRANT UPDATE (email, department) ON employees TO app_user;

-- Sin acceso a columnas salary y ssn para app_user

-- Verificar privilegios de columna
SELECT grantee, privilege_type, column_name
FROM information_schema.column_privileges
WHERE table_name = 'employees';

Paso 9: Implementar Jerarquía de Roles

Crear jerarquías de roles para gestión más fácil:

-- Crear roles de grupo
CREATE ROLE app_readers;
CREATE ROLE app_writers;
CREATE ROLE app_admins;

-- Otorgar privilegios a roles de grupo
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_writers;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admins;

-- Crear roles de usuario y agregar a grupos
CREATE ROLE john WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE jane WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE admin WITH LOGIN PASSWORD 'SecurePassword123!';

-- Otorgar membresía de grupo
GRANT app_readers TO john;
GRANT app_writers TO jane;
GRANT app_admins TO admin;

-- Ver membresías de roles
\du john

Paso 10: Establecer Privilegios Predeterminados

Configurar privilegios predeterminados para objetos futuros:

-- Establecer privilegios predeterminados para tablas creadas por rol developer
ALTER DEFAULT PRIVILEGES FOR ROLE developer IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

-- Establecer privilegios predeterminados para secuencias
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE, SELECT ON SEQUENCES TO app_user;

-- Establecer privilegios predeterminados para funciones
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO app_user;

-- Ver privilegios predeterminados
\ddp

Escenarios Avanzados de Permisos

Implementando Seguridad a Nivel de Fila (RLS)

La seguridad a nivel de fila de PostgreSQL permite filtrar filas basándose en el usuario actual:

-- Crear tabla con datos multi-inquilino
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    owner VARCHAR(50),
    department VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Habilitar seguridad a nivel de fila
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Crear política: usuarios solo pueden ver sus propios documentos
CREATE POLICY user_documents ON documents
    FOR SELECT
    USING (owner = current_user);

-- Crear política: usuarios pueden insertar solo con su nombre de usuario
CREATE POLICY user_insert ON documents
    FOR INSERT
    WITH CHECK (owner = current_user);

-- Crear política: usuarios pueden actualizar solo sus propios documentos
CREATE POLICY user_update ON documents
    FOR UPDATE
    USING (owner = current_user);

-- Crear política: gerentes de departamento ven todos los documentos del departamento
CREATE POLICY department_managers ON documents
    FOR SELECT
    USING (
        department = (
            SELECT department
            FROM users
            WHERE username = current_user
            AND role = 'manager'
        )
    );

-- Crear política permisiva (lógica OR con otras políticas)
CREATE POLICY admin_all ON documents
    FOR ALL
    TO admin_role
    USING (true);

-- Ver políticas
\d+ documents

Implementando Control de Acceso Basado en Esquemas

Organizar objetos en esquemas para mejor control de acceso:

-- Crear esquemas para diferentes propósitos
CREATE SCHEMA app_data;
CREATE SCHEMA app_staging;
CREATE SCHEMA app_reporting;

-- Crear roles para cada esquema
CREATE ROLE data_user WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE staging_user WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE report_user WITH LOGIN PASSWORD 'SecurePassword123!';

-- Otorgar acceso a esquema
GRANT USAGE ON SCHEMA app_data TO data_user;
GRANT ALL ON SCHEMA app_staging TO staging_user;
GRANT USAGE ON SCHEMA app_reporting TO report_user;

-- Otorgar privilegios de tabla dentro de esquemas
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO data_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app_staging TO staging_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_reporting TO report_user;

-- Establecer privilegios predeterminados para nuevos objetos
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO data_user;

-- Revocar acceso a esquema público
REVOKE ALL ON SCHEMA public FROM PUBLIC;

Implementando Seguridad Basada en Funciones

Controlar acceso a través de funciones security definer:

-- Crear función con SECURITY DEFINER
CREATE OR REPLACE FUNCTION update_user_email(
    p_user_id INTEGER,
    p_new_email VARCHAR
)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
    -- Validar formato de email
    IF p_new_email !~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' THEN
        RAISE EXCEPTION 'Invalid email format';
    END IF;

    -- Actualizar solo si el usuario posee el registro
    UPDATE users
    SET email = p_new_email,
        updated_at = NOW()
    WHERE id = p_user_id
      AND username = current_user;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Permission denied or user not found';
    END IF;
END;
$$;

-- Otorgar privilegio de ejecución a usuarios de aplicación
GRANT EXECUTE ON FUNCTION update_user_email(INTEGER, VARCHAR) TO app_user;

-- Los usuarios pueden llamar a la función sin acceso directo a tabla
-- Revocar acceso directo a tabla
REVOKE UPDATE ON users FROM app_user;

Creando Vistas para Seguridad de Datos

Usar vistas para ocultar columnas sensibles:

-- Crear tabla base con datos sensibles
CREATE TABLE customer_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    credit_card VARCHAR(16),
    ssn VARCHAR(11),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Crear vista sin columnas sensibles
CREATE VIEW customer_basic AS
SELECT id, name, email, phone, created_at
FROM customer_data;

-- Crear vista con datos sensibles enmascarados
CREATE VIEW customer_masked AS
SELECT
    id,
    name,
    email,
    phone,
    CONCAT('****-****-****-', RIGHT(credit_card, 4)) AS credit_card_masked,
    CONCAT('***-**-', RIGHT(ssn, 4)) AS ssn_masked,
    created_at
FROM customer_data;

-- Otorgar acceso solo a vistas
GRANT SELECT ON customer_basic TO app_user;
GRANT SELECT ON customer_masked TO support_user;

-- Revocar acceso a tabla base
REVOKE ALL ON customer_data FROM app_user, support_user;

Implementación de Mejores Prácticas de Seguridad

Implementar Seguridad de Conexión

Configurar pg_hba.conf para conexiones seguras:

sudo nano /etc/postgresql/14/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Conexiones locales usan autenticación peer
local   all             postgres                                peer

# Requerir SSL para conexiones de red
hostssl all             all             0.0.0.0/0              scram-sha-256
hostssl replication     replication     192.168.1.0/24         scram-sha-256

# Rechazar conexiones de red sin SSL
hostnossl all           all             0.0.0.0/0              reject

# Permitir red local sin SSL (solo desarrollo)
host    all             all             127.0.0.1/32           scram-sha-256
host    all             all             ::1/128                scram-sha-256

Habilitar SSL en postgresql.conf:

sudo nano /etc/postgresql/14/main/postgresql.conf

ssl = on
ssl_cert_file = '/etc/postgresql/14/main/server.crt'
ssl_key_file = '/etc/postgresql/14/main/server.key'
ssl_ca_file = '/etc/postgresql/14/main/root.crt'

Reiniciar PostgreSQL:

sudo systemctl restart postgresql

Implementar Políticas de Contraseña

Configurar requisitos de contraseña:

-- Instalar extensión passwordcheck
CREATE EXTENSION IF NOT EXISTS passwordcheck;

-- La extensión automáticamente aplica:
-- - Longitud mínima (predeterminado 8 caracteres)
-- - Debe contener números
-- - Debe contener caracteres especiales
-- - Contraseña no igual al nombre de usuario

-- Probar fortaleza de contraseña
CREATE ROLE test_user WITH PASSWORD 'weak';  -- Fallará
CREATE ROLE test_user WITH PASSWORD 'StrongP@ss123!';  -- Tendrá éxito

Configurar en postgresql.conf:

passwordcheck.minimum_length = 12

Implementar Registro de Auditoría

Habilitar registro de auditoría completo:

-- Instalar extensión pgaudit
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configurar ajustes de auditoría
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';
ALTER SYSTEM SET pgaudit.log_catalog = off;
ALTER SYSTEM SET pgaudit.log_parameter = on;
ALTER SYSTEM SET pgaudit.log_relation = on;

-- Recargar configuración
SELECT pg_reload_conf();

-- Verificar ajustes
SHOW pgaudit.log;

En postgresql.conf:

# Registro de auditoría
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
pgaudit.log_catalog = off
pgaudit.log_level = 'log'

Implementar Límites de Conexión

Controlar uso de conexión:

-- Establecer límite de conexión a nivel de base de datos
ALTER DATABASE myapp_db CONNECTION LIMIT 100;

-- Establecer límites de conexión por rol
ALTER ROLE app_user CONNECTION LIMIT 10;
ALTER ROLE readonly CONNECTION LIMIT 5;

-- Ver límites de conexión
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolconnlimit <> -1;

-- Ver conexiones actuales
SELECT usename, COUNT(*)
FROM pg_stat_activity
GROUP BY usename;

-- Matar conexiones inactivas
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '1 hour';

Monitoreo y Auditoría

Monitorear Sesiones Activas

Ver conexiones actuales de base de datos:

-- Ver todas las conexiones activas
SELECT pid, usename, application_name, client_addr, backend_start, state, query
FROM pg_stat_activity
WHERE state = 'active';

-- Contar conexiones por usuario
SELECT usename, COUNT(*)
FROM pg_stat_activity
GROUP BY usename
ORDER BY count DESC;

-- Ver consultas de larga duración
SELECT pid, usename, client_addr, query_start, state, query
FROM pg_stat_activity
WHERE state != 'idle'
  AND query_start < NOW() - INTERVAL '5 minutes'
ORDER BY query_start;

-- Terminar conexión específica
SELECT pg_terminate_backend(12345);  -- Reemplazar con PID real

-- Terminar todas las conexiones para un usuario
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user';

Auditar Privilegios de Roles

Crear consultas de auditoría de privilegios:

-- Ver todos los privilegios de rol
SELECT grantee, privilege_type, table_schema, table_name
FROM information_schema.table_privileges
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name;

-- Ver privilegios a nivel de columna
SELECT grantee, privilege_type, table_name, column_name
FROM information_schema.column_privileges
WHERE grantee NOT IN ('postgres')
ORDER BY grantee, table_name, column_name;

-- Ver roles con privilegios de superusuario
SELECT rolname FROM pg_roles WHERE rolsuper = true;

-- Ver membresías de roles
SELECT r.rolname AS role,
       ARRAY_AGG(m.rolname) AS members
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid
GROUP BY r.rolname
ORDER BY r.rolname;

-- Ver privilegios de base de datos
SELECT datname, datacl
FROM pg_database
WHERE datname NOT IN ('template0', 'template1');

Crear Script de Auditoría de Seguridad

#!/bin/bash
# Script de Auditoría de Seguridad de PostgreSQL

echo "=== Informe de Auditoría de Seguridad de PostgreSQL ==="
echo "Generado: $(date)"
echo ""

sudo -u postgres psql -d postgres << 'EOF'

\echo '=== Cuentas de Superusuario ==='
SELECT rolname, rolcanlogin
FROM pg_roles
WHERE rolsuper = true
ORDER BY rolname;

\echo ''
\echo '=== Roles Sin Contraseña ==='
SELECT rolname
FROM pg_authid
WHERE rolcanlogin = true
  AND rolpassword IS NULL
ORDER BY rolname;

\echo ''
\echo '=== Roles sin Límite de Conexión ==='
SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolcanlogin = true
  AND rolconnlimit = -1
ORDER BY rolname;

\echo ''
\echo '=== Privilegios de Esquema Público ==='
SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public'
  AND grantee = 'PUBLIC';

\echo ''
\echo '=== Conexiones Activas por Usuario ==='
SELECT usename, COUNT(*) as connections
FROM pg_stat_activity
GROUP BY usename
ORDER BY connections DESC;

\echo ''
\echo '=== Intentos de Conexión Fallidos Recientes ==='
-- Verificar archivo de registro para autenticación fallida
\! grep "authentication failed" /var/log/postgresql/postgresql-14-main.log | tail -10

EOF

Hacer ejecutable y ejecutar:

chmod +x pg_security_audit.sh
./pg_security_audit.sh

Verificación y Pruebas

Probar Permisos de Rol

# Probar conexión como rol específico
psql -U app_user -d myapp_db -h localhost

# Probar permisos específicos
psql -U app_user -d myapp_db -c "SELECT * FROM users LIMIT 1;"
psql -U app_user -d myapp_db -c "INSERT INTO test VALUES ('data');"
psql -U readonly -d myapp_db -c "DELETE FROM test;"  # Debería fallar

Verificar Configuración de Privilegios

-- Verificar si el rol puede conectarse a base de datos
SELECT has_database_privilege('app_user', 'myapp_db', 'CONNECT');

-- Verificar si el rol puede seleccionar de tabla
SELECT has_table_privilege('app_user', 'users', 'SELECT');

-- Verificar si el rol puede actualizar tabla
SELECT has_table_privilege('app_user', 'users', 'UPDATE');

-- Verificar privilegios de columna
SELECT has_column_privilege('app_user', 'employees', 'salary', 'SELECT');

-- Verificar uso de esquema
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

-- Verificar privilegio de ejecución de función
SELECT has_function_privilege('app_user', 'update_user_email(integer,varchar)', 'EXECUTE');

Probar Seguridad a Nivel de Fila

-- Crear usuarios de prueba
CREATE ROLE alice WITH LOGIN PASSWORD 'SecurePassword123!';
CREATE ROLE bob WITH LOGIN PASSWORD 'SecurePassword123!';

-- Otorgar privilegios básicos
GRANT CONNECT ON DATABASE myapp_db TO alice, bob;
GRANT USAGE ON SCHEMA public TO alice, bob;
GRANT SELECT, INSERT ON documents TO alice, bob;

-- Insertar datos de prueba como diferentes usuarios
SET ROLE alice;
INSERT INTO documents (title, content, owner) VALUES ('Alice Doc', 'Content', 'alice');

SET ROLE bob;
INSERT INTO documents (title, content, owner) VALUES ('Bob Doc', 'Content', 'bob');

-- Verificar RLS: Alice solo debería ver sus documentos
SET ROLE alice;
SELECT * FROM documents;  -- Muestra solo documentos de Alice

-- Verificar RLS: Bob solo debería ver sus documentos
SET ROLE bob;
SELECT * FROM documents;  -- Muestra solo documentos de Bob

-- Restablecer a superusuario
RESET ROLE;

Solución de Problemas

Errores de Permiso Denegado

Problema: ERROR: permission denied for table users

Solución: Otorgar privilegios necesarios:

-- Verificar privilegios actuales
\dp users

-- Verificar si el usuario tiene acceso a esquema
SELECT has_schema_privilege('app_user', 'public', 'USAGE');

-- Otorgar uso de esquema primero
GRANT USAGE ON SCHEMA public TO app_user;

-- Luego otorgar privilegios de tabla
GRANT SELECT ON users TO app_user;

-- Verificar que se otorgaron privilegios
\dp users

Errores de Rol No Existe

Problema: ERROR: role "app_user" does not exist

Solución: Crear el rol:

-- Verificar si el rol existe
SELECT rolname FROM pg_roles WHERE rolname = 'app_user';

-- Crear el rol si falta
CREATE ROLE app_user WITH LOGIN PASSWORD 'SecurePassword123!';

-- Verificar creación
\du app_user

Errores de Autenticación Fallida

Problema: FATAL: password authentication failed for user "app_user"

Solución: Restablecer contraseña y verificar pg_hba.conf:

-- Restablecer contraseña
ALTER ROLE app_user WITH PASSWORD 'NewSecurePassword456!';

Verificar pg_hba.conf:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Asegurar método de autenticación correcto:

host    all             app_user        127.0.0.1/32            scram-sha-256

Recargar configuración:

sudo systemctl reload postgresql

Límite de Conexión Excedido

Problema: FATAL: too many connections for role "app_user"

Solución: Aumentar límite de conexión o cerrar conexiones inactivas:

-- Verificar conexiones actuales
SELECT COUNT(*) FROM pg_stat_activity WHERE usename = 'app_user';

-- Aumentar límite de conexión
ALTER ROLE app_user CONNECTION LIMIT 20;

-- O terminar conexiones inactivas
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'app_user'
  AND state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes';

Problemas de Seguridad a Nivel de Fila

Problema: Políticas de seguridad a nivel de fila no funcionan como se espera

Solución: Verificar configuración RLS:

-- Verificar si RLS está habilitada
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename = 'documents';

-- Ver todas las políticas
SELECT * FROM pg_policies WHERE tablename = 'documents';

-- Deshabilitar RLS temporalmente para pruebas (como superusuario)
ALTER TABLE documents DISABLE ROW LEVEL SECURITY;

-- Re-habilitar RLS
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Forzar RLS incluso para propietario de tabla
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Resumen de Mejores Prácticas

1. Principio de Mínimo Privilegio

Otorgar solo los privilegios mínimos requeridos para que cada rol realice sus tareas. Comenzar con acceso mínimo y agregar privilegios según sea necesario.

2. Usar Jerarquías de Roles

Crear roles de grupo y otorgar membresía para simplificar la gestión:

-- Crear grupos
CREATE ROLE readers;
CREATE ROLE writers;

-- Otorgar privilegios a grupos
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readers;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO writers;

-- Agregar usuarios a grupos
GRANT readers TO user1;
GRANT writers TO user2;

3. Nunca Usar Superusuario para Aplicaciones

Siempre crear roles dedicados con privilegios específicos para conexiones de aplicación. Reservar acceso de superusuario solo para tareas administrativas.

4. Implementar Separación de Esquemas

Usar esquemas para organizar objetos y simplificar gestión de permisos:

-- Crear esquemas separados
CREATE SCHEMA application;
CREATE SCHEMA reporting;
CREATE SCHEMA staging;

-- Otorgar acceso apropiado
GRANT USAGE ON SCHEMA application TO app_role;
GRANT USAGE ON SCHEMA reporting TO report_role;

5. Usar Seguridad a Nivel de Fila para Aplicaciones Multi-Inquilino

Implementar políticas RLS para filtrar datos automáticamente basándose en el usuario actual, eliminando la necesidad de filtrado a nivel de aplicación.

6. Habilitar Cifrado de Conexión

Siempre usar SSL/TLS para conexiones de red. Configurar pg_hba.conf para requerir SSL:

hostssl all all 0.0.0.0/0 scram-sha-256

7. Auditorías de Seguridad Regulares

Programar auditorías de seguridad mensuales para:

  • Revisar todos los privilegios de rol
  • Identificar roles no usados
  • Verificar privilegios excesivos
  • Verificar políticas RLS
  • Revisar registros de auditoría

8. Documentar Propósitos de Roles

Mantener documentación completa:

  • Propósito del rol y propietario
  • Requisitos de privilegio
  • Calendario de revisión
  • Información de contacto
  • Historial de cambios

9. Implementar Registro de Auditoría

Habilitar pgaudit para registro completo de actividad:

CREATE EXTENSION pgaudit;
ALTER SYSTEM SET pgaudit.log = 'write, ddl, role';

10. Probar Cambios de Privilegios

Siempre probar cambios de privilegios en un entorno de desarrollo antes de aplicar a producción:

-- Comenzar transacción para pruebas
BEGIN;

-- Hacer cambios de privilegios
GRANT SELECT ON sensitive_table TO test_role;

-- Probar como el rol
SET ROLE test_role;
SELECT * FROM sensitive_table LIMIT 1;

-- Revertir si no es correcto
ROLLBACK;

-- O confirmar si es correcto
COMMIT;

Conclusión

La gestión efectiva de usuarios y permisos en PostgreSQL es esencial para mantener un entorno de base de datos seguro, conforme y bien organizado. Siguiendo esta guía completa, has aprendido cómo crear y gestionar roles con privilegios apropiados, implementar seguridad a nivel de fila para control de acceso detallado, usar esquemas para seguridad organizacional, asegurar conexiones con SSL/TLS y autenticación apropiada, auditar actividades y privilegios de usuarios, y solucionar problemas comunes de permisos.

El sofisticado sistema de privilegios de PostgreSQL proporciona las herramientas necesarias para implementar políticas de seguridad robustas que protejan tus datos mientras permiten a usuarios legítimos realizar su trabajo eficientemente. La clave es comprender los diferentes niveles de privilegio, usar jerarquías de roles efectivamente, implementar seguridad a nivel de fila donde sea apropiado, auditar y revisar permisos regularmente, y mantener documentación completa.

A medida que tu entorno PostgreSQL evoluciona, continúa revisando y refinando tu estructura de permisos para asegurar que cumple tanto con los requisitos de seguridad como con las necesidades operacionales. Implementa monitoreo automatizado y alertas para actividades sospechosas, conduce capacitación regular de seguridad para miembros del equipo, mantente actualizado con características y mejores prácticas de seguridad de PostgreSQL, mantén un proceso claro para solicitudes y aprobaciones de privilegios, y prueba regularmente tus configuraciones de seguridad a través de escenarios de ataque simulados.

La base que has construido con esta guía servirá como una base sólida para gestionar la seguridad de PostgreSQL en entornos de cualquier tamaño y complejidad. Recuerda que la seguridad es un proceso continuo que requiere atención continua, actualizaciones regulares y adaptación a nuevas amenazas y requisitos. Al mantener vigilancia y seguir las mejores prácticas descritas en esta guía, asegurarás que tus bases de datos PostgreSQL permanezcan seguras y conformes mientras apoyan las necesidades de gestión de datos de tu organización.