Práctica: administrar usuarios mediante grupos en PostgreSQL

La administración de usuarios y sus respectivos privilegios sobre los objetos de la base de datos puede volverse una tarea inmanejable si contamos con muchos usuarios en el sistema y no llevamos un orden o método eficiente para hacerlo.

Tomemos como ejemplo, cuando nos solicitan crear un usuario X con los mismos permisos que tiene el usuario Y. En este caso, debemos acceder a la base de datos, buscar los permisos que tiene el usuario Y en los distintos tipos de objetos (leyendo directamente el código SQL o mediante tools como psql) y replicarlos para el usuario X mediante una gran cantidad de sentencias GRANT. Si hemos tenido suerte, el usuario X quedará con los mismos permisos que el usuario Y, aunque sabemos que, por Ley de Murphy, esto no será así, y faltará algo por retocar más de una vez.

Una solución un poco más práctica a la anterior, es la administración de privilegios mediante grupos, donde la idea apunta a configurar los permisos por única vez a un determinado perfil de usuario (*), y cuando nos soliciten la creación de uno nuevo, simplemente lo agregamos al grupo correspondiente.

¿Qué logramos con esto?

1- Obtenemos un método de trabajo eficiente.
2- Facilidad en la administración de privilegios.
3- Mayor seguridad al no tener "permisos sueltos" sobre "usuarios sueltos" en la base de datos.
4- Menor tiempo de trabajo en la creación de nuevos usuarios.

(*) Perfil de usuario: lo importante para la creación de grupos, es identificar distintos perfiles de usuarios para así poder agruparlos. Los pedidos más comunes que se reciben en toda base de datos son: usuarios con permisos de solo lectura (SELECT) o usuarios con permisos de escritura (SELECT, INSERT, UPDATE, DELETE).

Tomemos como ejemplo este caso donde salta a la vista qué grupos podemos crear:

1) Grupo de usuarios de sólo lectura
2) Grupo de usuarios de escritura

De esta manera, cuando nos soliciten crear un usuario X con permisos de lectura en la base de datos, simplemente lo agregamos al grupo lectura, idem escritura. Tomando el ejemplo inicial, si nos piden crear un usuario X con los mismos permisos que el usuario Y, simplemente nos fijamos a qué grupo pertenece Y, y agregamos también a X. Listo. Nos olvidamos de la búsqueda de permisos, la asignación de GRANT's, los posibles errores y los reclamos posteriores.

Dejémonos de chácharas y vayamos manos al código: la práctica de hoy es administrar privilegios de lectura y escritura de forma centralizada utilizando grupos.

1) Setup de la base de datos

Creamos una base de datos para jugar
CREATE DATABASE prueba;

Revocamos los permisos al grupo público
REVOKE ALL ON DATABASE prueba FROM public;

Conectados en la base prueba, revocamos los permisos sobre el esquema público al grupo público
REVOKE ALL ON SCHEMA public FROM public;

2) Setup de los grupos

Creamos los grupos
CREATE ROLE lectura WITH NOLOGIN;
CREATE ROLE escritura WITH NOLOGIN;

Asignamos permiso de conexión a la base de datos prueba
GRANT CONNECT ON DATABASE prueba TO lectura;
GRANT CONNECT ON DATABASE prueba TO escritura;

Asignamos permiso de uso en el esquema público
GRANT USAGE ON SCHEMA public TO lectura;
GRANT USAGE ON SCHEMA public TO escritura;

Para el grupo de sólo lectura, asignamos los respectivos permisos para tablas/vistas actuales
GRANT SELECT ON ALL TABLES IN SCHEMA public TO lectura;

Ahora lo dejamos configurado para cada tabla que se cree
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO lectura;

Para el grupo de escritura, asignamos los respectivos permisos para tablas/vistas actuales
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO escritura;

Ahora lo dejamos configurado para cada tabla que se cree
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO escritura;

Asignamos permiso de uso para las secuencias
GRANT USAGE ON ALL SEQUENCESIN SCHEMA public TO escritura;

Ahora lo dejamos configurado para cada secuencia que se cree
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO escritura;

3) Setup de usuarios

Si nos piden un usuario de sólo escritura en la base de datos
CREATE USER user_select WITH PASSWORD 'Una password segura' IN GROUP lectura;

Si nos piden un usuario de escritura en la base de datos
CREATE USER user_write WITH PASSWORD 'Una password segura' IN GROUP escritura;

Si nos piden un usuario X con los mismos permisos que el usuario Y
Nos fijamos a que grupo pertenece Y y agregamos a X.

Listo, terminamos. Ya tenemos una gestión de usuarios y privilegios un poco más eficiente :)

¡Nos vemos en la próxima!