Centro de Conocimiento DevOps
Centro de Conocimiento DevOps

Comandos Esenciales para la Gestión de Usuarios, Roles y Permisos en PostgreSQL

Domine los comandos SQL esenciales para una seguridad y gestión de usuarios robusta en PostgreSQL. Esta guía proporciona pasos prácticos para crear, modificar y eliminar roles, establecer atributos complejos (como LOGIN y CREATEDB) y gestionar la pertenencia a grupos. Aprenda a controlar el acceso con precisión utilizando los potentes comandos `GRANT` y `REVOKE`, definiendo permisos a nivel de objeto en tablas, esquemas y funciones. Implemente el principio de mínimo privilegio con ejemplos detallados y descubra comandos clave de psql para auditar la configuración de seguridad actual.

372 vistas

Comandos Esenciales para Gestionar Usuarios, Roles y Permisos en PostgreSQL

La seguridad de la base de datos depende de un control de acceso robusto. En PostgreSQL, este control se gestiona principalmente a través de Roles y los Permisos (o privilegios) asociados. Comprender cómo crear, modificar y gestionar estas entidades de seguridad es fundamental para cualquier administrador de bases de datos o desarrollador.

Esta guía completa explora los comandos SQL esenciales necesarios para gestionar de forma segura los usuarios, definir políticas de acceso e implementar el principio de mínimo privilegio dentro de su entorno PostgreSQL. Cubriremos la creación de roles, el establecimiento de atributos, la pertenencia a grupos y la gestión detallada de permisos de objetos utilizando GRANT y REVOKE.

Entendiendo los Roles de PostgreSQL

A diferencia de otros sistemas de bases de datos que diferencian estrictamente entre usuarios y grupos, PostgreSQL utiliza un concepto unificado: el Rol. Un rol puede representar:

  1. Un usuario de base de datos (una entidad que puede iniciar sesión, generalmente con el atributo LOGIN).
  2. Un grupo de usuarios (una entidad utilizada únicamente para agrupar privilegios, generalmente carente del atributo LOGIN).

La seguridad efectiva comienza definiendo roles que reflejen con precisión las responsabilidades de las personas o aplicaciones que acceden a la base de datos.

1. Gestión de Roles: Creación, Modificación y Eliminación

Los comandos fundamentales para gestionar los principales de la base de datos giran en torno a CREATE ROLE, ALTER ROLE y DROP ROLE.

Creación de Nuevos Roles

Al crear un rol, debe especificar sus atributos, particularmente si puede iniciar sesión (LOGIN) y sus credenciales de autenticación (PASSWORD).

Creación de un Usuario de Inicio de Sesión Básico

Para crear un rol de usuario estándar que requiera una contraseña para la conexión:

CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password_here';

Creación de un Rol con Atributos Específicos

Los roles pueden tener varios atributos que definen sus capacidades y permisos del sistema:

Atributo Descripción
LOGIN Permite al rol conectarse a la base de datos.
SUPERUSER Otorga todos los privilegios de la base de datos (úsese con moderación).
CREATEROLE Permite al rol crear, alterar y eliminar otros roles.
CREATEDB Permite al rol crear nuevas bases de datos.
REPLICATION Permite al rol iniciar la replicación en streaming.
INHERIT / NOINHERIT Controla si el rol hereda automáticamente los privilegios de los roles de los que es miembro (el valor predeterminado es INHERIT).

Ejemplo: Creación de un Rol Administrador (No Superusuario)

CREATE ROLE db_admin WITH
    LOGIN
    PASSWORD 'admin_secret'
    CREATEROLE
    CREATEDB
    VALID UNTIL '2025-01-01'; -- Fecha de caducidad opcional

Modificación de Roles Existentes

Utilice ALTER ROLE para cambiar atributos o actualizar la contraseña de un rol existente.

-- Cambiar la contraseña de un usuario existente
ALTER ROLE app_user WITH PASSWORD 'new_strong_password';

-- Revocar la capacidad de inicio de sesión (convertir un usuario en un grupo)
ALTER ROLE db_admin NOLOGIN;

-- Bloquear a un usuario
ALTER ROLE old_employee NOLOGIN;

Eliminación de Roles

Antes de eliminar un rol, asegúrese de que el rol no sea propietario de ningún objeto de base de datos (tablas, esquemas, etc.). Si lo es, primero debe transferir la propiedad usando REASSIGN OWNED.

-- Reasignar todos los objetos propiedad del rol a 'postgres'
REASSIGN OWNED BY old_employee TO postgres;

-- Luego, eliminar el rol
DROP ROLE old_employee;

Advertencia: Eliminar un rol es irreversible. Tenga precaución, especialmente con los roles que son propietarios de muchos objetos.

2. Gestión de Pertenencia a Grupos

Los roles a menudo funcionan como grupos para simplificar la gestión de permisos. En lugar de otorgar permisos a 50 usuarios individuales, los otorga a un rol de grupo y luego hace que los usuarios sean miembros de ese grupo.

Creación de un Rol de Grupo

Los grupos se crean típicamente sin el atributo LOGIN.

CREATE ROLE data_analysts NOLOGIN;

Otorgar y Revocar Pertenencia a Grupos

Use el comando GRANT para agregar miembros a un rol de grupo, y REVOKE para eliminarlos.

-- Agregar al usuario 'alice' y al usuario 'bob' al grupo 'data_analysts'
GRANT data_analysts TO alice, bob;

-- Remover a 'bob' del grupo 'data_analysts'
REVOKE data_analysts FROM bob;

La Opción WITH ADMIN OPTION

Si incluye WITH ADMIN OPTION al otorgar la membresía, el rol receptor puede entonces otorgar la membresía en ese grupo a otros, y también puede DROP (eliminar) el rol de grupo.

GRANT data_analysts TO supervisor WITH ADMIN OPTION;

3. Gestión de Permisos de Objetos (Privilegios)

Los permisos definen qué acciones puede realizar un rol sobre qué objetos de la base de datos (tablas, vistas, funciones, esquemas, etc.). Este es el núcleo de la seguridad de la base de datos.

Sintaxis del Comando GRANT

GRANT lista_de_privilegios ON tipo_de_objeto nombre_de_objeto TO nombre_de_rol [WITH GRANT OPTION];

Privilegios Comunes y Ejemplos

Privilegios de Tabla

Privilegio Acción Permitida
SELECT Leer datos de la tabla.
INSERT Agregar nuevas filas.
UPDATE Modificar filas existentes.
DELETE Eliminar filas existentes.
TRUNCATE Vaciar la tabla completamente.
REFERENCES Crear restricciones de clave externa.

Ejemplo: Otorgar acceso de solo lectura a una tabla específica.

GRANT SELECT ON TABLE production.orders TO data_analysts;

-- Otorgar todas las operaciones DML en una tabla
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE staging.temp_data TO app_user;

Privilegios de Base de Datos y Esquema

Los privilegios de base de datos y esquema son cruciales para controlar la estructura del entorno.

Objeto Privilegio Clave Propósito
DATABASE CONNECT Permite la conexión a la base de datos.
DATABASE CREATE Permite la creación de nuevos esquemas, tablespaces, etc.
SCHEMA USAGE Permite el acceso a objetos dentro del esquema.
SCHEMA CREATE Permite la creación de nuevos objetos dentro del esquema.

Ejemplo: Otorgar Acceso a Esquema

Si un usuario necesita acceder a tablas dentro de app_schema, debe tener USAGE en ese esquema.

GRANT USAGE ON SCHEMA app_schema TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app_schema TO app_user;

Privilegios de Secuencia y Función

El uso de secuencias (para IDs autoincrementables) y la ejecución de funciones requieren privilegios específicos.

-- Permitir al usuario avanzar la secuencia (necesario para INSERTs)
GRANT USAGE, SELECT ON SEQUENCE app_schema.user_id_seq TO app_user;

-- Permitir la ejecución de un procedimiento almacenado o función específica
GRANT EXECUTE ON FUNCTION audit_log_insert(text) TO app_user;

Revocación de Permisos

Use el comando REVOKE para eliminar permisos específicos previamente otorgados. La sintaxis refleja a GRANT.

-- Revocar la capacidad de insertar nuevos registros en la tabla orders
REVOKE INSERT ON TABLE production.orders FROM app_user;

-- Revocar todos los privilegios previamente otorgados (nota: no revoca la propiedad)
REVOKE ALL PRIVILEGES ON TABLE production.orders FROM data_analysts;

Nota sobre la Revocación: Si un privilegio se otorgó a un rol, y ese rol es miembro de otro grupo, revocar el privilegio solo afecta el permiso otorgado directamente. Si el rol todavía hereda el privilegio a través de la pertenencia al grupo, conserva el acceso.

Aplicación de Permisos a Objetos Futuros

Gestionar permisos para objetos que aún no existen requiere el uso de ALTER DEFAULT PRIVILEGES. Esto es esencial para esquemas donde las aplicaciones crean frecuentemente nuevas tablas.

Ejemplo: Asegurar que un usuario pueda hacer SELECT en todas las futuras tablas creadas por el rol app_owner dentro del esquema app_schema:

ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app_schema
    GRANT SELECT ON TABLES TO app_user;

4. Visualización de Permisos Actuales

Para auditar la configuración de seguridad, PostgreSQL proporciona varias herramientas y vistas de catálogo.

Comando/Vista Descripción
\du (en psql) Lista todos los roles y sus atributos.
\du+ nombre_de_rol (en psql) Muestra los atributos detallados del rol y la pertenencia.
\dp nombre_de_tabla (en psql) Lista los permisos (privilegios) otorgados en una tabla.
pg_roles Vista del catálogo del sistema que contiene las definiciones de roles.
information_schema.table_privileges Vista que muestra los privilegios otorgados en las tablas.

Ejemplo: Comprobación de privilegios en una tabla a través de psql

=> \dp production.orders

Mejores Prácticas de Seguridad para la Gestión de Usuarios

  1. Principio de Mínimo Privilegio (PoLP): Otorgue solo el conjunto mínimo de permisos necesarios para que un rol realice su función. Evite otorgar ALL PRIVILEGES o SUPERUSER a menos que sea absolutamente necesario.
  2. Roles de Aplicación Separados: Utilice roles dedicados para aplicaciones (ej. api_service_role) separados de los roles de DBA humanos. Las aplicaciones típicamente solo deberían tener permisos DML (SELECT, INSERT, UPDATE, DELETE) y no permisos DDL (CREATE, DROP).
  3. Use Grupos Extensivamente: Cree roles sin inicio de sesión (grupos) para gestionar conjuntos de permisos (ej. read_only_group, etl_writer_group). Asigne usuarios individuales a estos grupos en lugar de otorgar permisos individualmente.
  4. Evite Usar Roles Predeterminados: Nunca utilice el rol superusuario postgres para tareas generales de aplicación o desarrollo.
  5. Autenticación Segura: Utilice siempre contraseñas seguras y, siempre que sea posible, aproveche la autenticación por certificado de cliente o soluciones centralizadas de gestión de identidades en lugar de la autenticación basada en contraseñas.

Conclusión

La gestión eficaz de roles y permisos es la base de la seguridad de PostgreSQL. Al dominar CREATE ROLE, ALTER ROLE, GRANT y REVOKE, los administradores de bases de datos pueden implementar un control granular, asegurando que cada usuario o aplicación que acceda a la base de datos tenga exactamente el acceso requerido y nada más. Aplicar consistentemente el principio de mínimo privilegio y utilizar roles de grupo simplifica el mantenimiento a largo plazo y fortalece su postura de seguridad de la base de datos contra accesos no autorizados.