Privileged users should have a function to kill user connections (M)

[dassl]

Privileged users should have a function to kill user connections (M)

[smurthys]

I agree a function killConnections(userName) is required. BTW, is there a way to get the pid(s) for a user?

It is also necessary to understand when/why a connection may remain open.

[wildtayne]

the pid(s) for a user can be found in pg_stat_activity, ie. from the query above,

SELECT pid
FROM pg_stat_activity
WHERE usename ='<username>';

That query also calls pg_terminate_backend for all connections belonging to one user.

[smurthys]

Because pg_terminate_backend and related admin operations could be granted to users, we should grant that access to Instructor and DBManager roles.

Also, we could benefit from functions listConnections(userName) and killConnection(pid) granted to Instructor and DBManager. Please recommend the attributes included in the table listConnections returns.

[wildtayne]

listConnections(userName) should at a minimum return usename (not typo) and pid from pg_stat_activity. It may also be helpful to return application_name and client_addr to get an idea of what computers and applications are causing the connections. There are also several timestamps in pg_stat_activity: backend_start, xact_start, query_start, and state_change. I will try and better understand what these timestamps represent to decide which should be included.

Should we directly grant permissions on pg_terminate_backend and similar to instructor and dbManager, or just provide them with the functions we create?

[wildtayne]

Based on the pg_stat_activity docs, I think the two most useful timestamps are backend_start and query_start, which are the start time of the connection and start time of the last query respectively.

[wildtayne]

An initial version of listConnections(username), killUserConnections(username), and killConnection(pid) have been added in 1391d3d. There are some permissions considerations with these functions, which are being discussed in #27. There is also an additional issue with pg_stat_activity permissions I will create a new issue for.