Cómo terminar todas las conexiones de un usuario en PostgreSQL 💥

¡Hola a todos! La gestión de las conexiones de la base de datos es una tarea vital para los administradores. En ocasiones, es necesario forzar el cierre de todas las sesiones de un usuario, ya sea para realizar tareas de mantenimiento, resolver problemas de rendimiento o aplicar cambios de seguridad. Hacerlo de forma manual puede ser tedioso, pero con una simple línea de comando, puedes automatizar el proceso.

Aquí te muestro cómo terminar todas las sesiones activas de un usuario en PostgreSQL usando la consola, asumiendo que tienes permisos de administrador (como el usuario postgres).

---

Paso 1: Bloquear nuevas conexiones del usuario 🔒

Antes de empezar a cerrar las sesiones existentes, es crucial evitar que el usuario se conecte nuevamente. Esto asegura que no se creen nuevas sesiones mientras tú terminas las actuales.

Abre la terminal y ejecuta el siguiente comando, reemplazando <user_name> con el nombre del usuario al que quieres terminar las sesiones:

psql -t -c "ALTER USER <user_name> WITH NOLOGIN;"

El comando ALTER USER <user_name> WITH NOLOGIN evita que el usuario inicie sesión.

---

Paso 2: Finalizar las sesiones activas 🔪

Ahora que el usuario no puede iniciar nuevas sesiones, podemos finalizar las que ya están abiertas. Para ello, utilizamos las vistas del sistema de PostgreSQL, pg_stat_activity y pg_roles, para encontrar los procesos activos del usuario y terminar cada uno.

Ejecuta el siguiente comando en la terminal:

psql -t -c "SELECT 'SELECT pg_terminate_backend(' || pid || ');' FROM pg_stat_activity WHERE usename = '<user_name>' AND pid <> pg_backend_pid();" | psql

Análisis del comando:

• La primera parte (psql -t -c "SELECT ..."):

  • psql -t: Ejecuta el comando en modo de solo salida de tuplas, sin cabeceras.

  • SELECT 'SELECT pg_terminate_backend(' || pid || ');' : Esta es la parte más importante. Genera una sentencia SQL que llama a la función pg_terminate_backend() para cada proceso activo del usuario.

  • WHERE usename = '<user_name>': Filtra los procesos por el nombre del usuario.

  • AND pid <> pg_backend_pid(): Excluye el proceso actual (psql) para que no intentes terminar tu propia sesión.

• La segunda parte (| psql):

  • El operador | (pipe) toma la salida del primer comando y la redirige como entrada al segundo comando psql. De esta forma, cada sentencia SELECT pg_terminate_backend... que se genera se ejecuta, terminando las conexiones.

---

Paso 3: Habilitar nuevamente las conexiones del usuario ✅

Una vez que todas las sesiones han sido terminadas, debes permitirle al usuario volver a conectarse.

Ejecuta el siguiente comando en la terminal:

psql -t -c "ALTER USER <user_name> WITH LOGIN;"

Con estos pasos, tendrás un control total sobre las sesiones de los usuarios, lo que te permitirá realizar tareas de administración de manera segura y eficiente.

¿Qué otros trucos de administración de bases de datos te gustaría aprender? ¡Déjanos un comentario y comparte tu experiencia!