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):
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.
-- -[ 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.
On Wednesday, June 13, 2018 at 5:30:59 PM UTC-5, Kenworth (Google Cloud Platform) wrote:
On Wednesday, June 13, 2018 at 5:30:59 PM UTC-5, Kenworth (Google Cloud Platform) wrote:
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.
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:
You received this message because you are subscribed to the Google Groups "Google Cloud SQL discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-cloud-sql-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-cloud-sql-discuss/5806f5f6-93f4-4ae7-9cea-833151d0b07a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
No comments:
Post a Comment