How to disconnect NOLOGIN users from PostgreSQL

In this article, we will see how to disconnect NOLOGIN users from PostgreSQL. In order to make sure that all users whose login privileges has been revoked are disconnected right away.

Query to disconnect NOLOGIN users from PostgreSQL:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity sa
JOIN pg_roles r ON sa.usename=r.rolname and not rolcanlogin; 

From active users, user ‘james’ has been granted ‘nologin’. So, with the above query we have disconnected user from PostgreSQL server. So, user cannot connect until granted to login privilege.

Now check the active connections whether NOLOGIN users are disconnected or not.

r2schools=# select usename from pg_stat_activity;
 usename
----------
 postgres

 postgres

This disconnect all users who no longer are allowed to connect by terminating the backends opened by these users.