Without superuser, how can I terminate rogue sessions?

[Don Seiler]

So Cloud SQL for PostgreSQL creates the postgres user as a non-superuser. This means that I can't view what queries other sessions are executing in pg_stat_activity, and also means that I can't cancel/terminate sessions that might be zombied or runaway sessions.

Just want to make sure I didn't miss something, but is there a way to accomplish these things in Cloud SQL for PG?

[Kenworth (Google Cloud Platform)]

True, the PostgreSQL user does not have the SUPERUSER attribute. You can show connections and threads to see the processes that are running on your database. Example:

select * from pg_stat_activity;

If you meant long-lived unused connections for rogue sessions, the connections with a GCE instance time out after 10 minutes of inactivity by default. 

[Don Seiler]

In pg_stat_activity, I can only see username, dbname and application. It doesn't show me client hostname or address, connection or query start time, or the query they are running. This is all important information to be able to troubleshoot a problem.

I'm not concerned with inactive sessions for now, I'm talking about runaway zombie sessions that need to be killed.

Don.

[Kenworth (Google Cloud Platform)]

1- I attached a screenshot of running the following command that gives client hostname, query start time, etc which are all available on pg_stat_activity.

select client_hostname, client_addr, query_start from pg_stat_activity;

2- For zombie sessions, you don't need a 'super' user to do this. See this doc on (9.5) Loosen security checks for viewing queries in pg_stat_activity. Here is a sample command to kill the connections: 

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'your_database_name' AND pid <> pg_backend_pid() AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled') AND state_change < current_timestamp - INTERVAL '15' MINUTE;

[Don Seiler]

I connected to my Cloud SQL instance as my user don, and then in a separate session as postgres. When I query pg_stat_activity from postgres, I see this (ignoring the cloudsqladmin/agent sessions):

-[ RECORD 3 ]----+--------------------------------

datid            | 12996

datname          | postgres

pid              | 23644

usesysid         | 18039

usename          | don

application_name | psql

client_addr      | [NULL]

client_hostname  | [NULL]

client_port      | [NULL]

backend_start    | [NULL]

xact_start       | [NULL]

query_start      | [NULL]

state_change     | [NULL]

wait_event_type  | [NULL]

wait_event       | [NULL]

state            | [NULL]

backend_xid      | [NULL]

backend_xmin     | [NULL]

query            | <insufficient privilege>

-[ RECORD 4 ]----+--------------------------------

datid            | 12996

datname          | postgres

pid              | 23650

usesysid         | 16388

usename          | postgres

application_name | psql

client_addr      | 67.52.214.50

client_hostname  | [NULL]

client_port      | 4850

backend_start    | 2018-06-14 03:35:47.766247+00

xact_start       | 2018-06-14 03:35:52.783823+00

query_start      | 2018-06-14 03:35:52.783823+00

state_change     | 2018-06-14 03:35:52.783826+00

wait_event_type  | [NULL]

wait_event       | [NULL]

state            | active

backend_xid      | [NULL]

backend_xmin     | 183866

query            | select * from pg_stat_activity;

You can see that it shows me very little information for don's session. I can see plenty of information for my own session. I can see information for other sessions owned by the same usename as the one I'm querying pg_stat_activity with, as is normal with postgres. But non-superuser accounts aren't able to view information for sessions owned by other users. I'm guessing in your screenshot you are using the same username.

As to point two, I can't run pg_terminate_backed as postgres for don's session:

postgres@postgres=> select pid, usename from pg_stat_activity;

  pid  |    usename

-------+---------------

 23707 | cloudsqlagent

 23677 | cloudsqladmin

 23644 | don

 23650 | postgres

 23680 | don

(5 rows)

postgres@postgres=> select pg_terminate_backend(23644);

ERROR:  42501: must be a member of the role whose process is being terminated or member of pg_signal_backend

LOCATION:  pg_terminate_backend, misc.c:319

I can, however, run it as don:

don@postgres=> select pg_terminate_backend(23644);

 pg_terminate_backend

----------------------

 t

(1 row)

So it boils down to having to be that user in order to see what any of that user's sessions are doing or killing one of them. However if you have a case of a blocking lock and not knowing which user's sessions it might be, you're going to be going through trial and error logging in as each active user to view their sessions.

Don.

[Kenworth (Google Cloud Platform)]

I agree with your points that you have to be that same user to accomplish the above and having a 'superuser' will give you more flexibility to do it on different users.

My colleague opened this feature request to check and terminate the rogue sessions for all users. I recommend you also create a feature request to view pg_stat_activity for all users as described in this article. Please feel free to star the issues to put proper weight on it and CC on the thread to get updates on the progress of the feature.