Kick a user off of Vertica?

[Josh]

Is there a way to kill a user session?

[JimKnicely]

Josh,

You can use the CLOSE_SESSION function to drop a user's session. This function has one parameter: session_id

So if I want to kick the cognos user off my server, first I have to find its session id from the sessions table:

Code: Select all

dbadmin=> SELECT user_name, session_id FROM sessions;
 user_name |            session_id
-----------+----------------------------------
 dbadmin   | vertica01.pgh.wpahs-774:0x1db57f
 cognos    | vertica01.pgh.wpahs-774:0x1db5bb
(2 rows)

Now that I have the session id I can call the CLOSE_SESSION function:

Code: Select all

dbadmin=> SELECT CLOSE_SESSION('vertica01.pgh.wpahs-774:0x1db5bb');
                           CLOSE_SESSION
--------------------------------------------------------------------
 Session close command sent. Check v_monitor.sessions for progress.
(1 row)

The cognos user's session will display a message indicating that the session was dropped when the user attempts to query the database. It will also try to reconnect:

Code: Select all

cognos=> SELECT COUNT(*) FROM dual;
server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.