How do I terminate a session in Google Cloud SQL for PostgreSQL?

Question

Since Google Cloud SQL for PostgreSQL doesn't give us a superuser (not even the postgres user), I can't see what queries other sessions are running from pg_stat_activity, nor can I terminate other sessions if needed.

For example:

postgres@testdb=> select pg_terminate_backend(1584);
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
Time: 23.800 ms

Without true superuser access, how do we do these things in Cloud SQL PostgreSQL instances? Only the cloudsqladmin account is superuser and AFAIK I can't become that:

postgres@testdb=> \dg
                                            List of roles
     Role name     |                         Attributes                         |      Member of
-------------------+------------------------------------------------------------+---------------------
 cloudsqladmin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 cloudsqlagent     | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqlreplica   | Replication                                                | {}
 cloudsqlsuperuser | Create role, Create DB                                     | {}
 don               | Create role, Create DB                                     | {cloudsqlsuperuser}
 postgres          | Create role, Create DB                                     | {cloudsqlsuperuser}

postgres@testdb=> set role cloudsqladmin;
ERROR:  42501: permission denied to set role "cloudsqladmin"
LOCATION:  call_string_check_hook, guc.c:9803
Time: 25.293 ms

FWIW, you can terminate a session if you log in as that session's user. Users can terminate any of their sessions, standard PostgreSQL stuff.

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

don@postgres=> select pg_terminate_backend(23644);
 pg_terminate_backend
----------------------
 t
(1 row)

Stopoff

Answer 1

pg_terminate_backend says:

pg_terminate_backend(pid int) - Terminate a backend. This is also allowed if the calling role is a member of the role whose backend is being terminated or the calling role has been granted pg_signal_backend, however only superusers can terminate superuser backends.

When you are a member of Google's cloudsqlsuperuser (default for gcloud sql users create) you can grant yourself:

GRANT pg_signal_backend TO myuser;

Then you can terminate any session except Superusers:

SELECT pg_terminate_backend(pid), * FROM pg_stat_activity
WHERE usename = 'rogue_user' AND pid <> pg_backend_pid();

pg_signal_backend was introduced in v9.6 - exactly the version on Google Cloud!

Alternative way is to be within other role to be able to terminate session being me:

GRANT other TO me;

Averse

Answer 2

It is not possible to give a superuser role to a PostgreSQL user in Cloud SQL. {1}

In any case you can consult information about the instance in the graphs and logs inside the Cloud SQL section in Cloud Console. {2}

{1}: https://cloud.google.com/sql/docs/postgres/users#other_postgresql_users

{2}: https://cloud.google.com/sql/docs/postgres/diagnose-issues

Mccowyn

I tried to comment earlier but apparently it's gone. Let's try this again. Re your link {2}, it probably wouldn't show me something like a zombie query or a blocking lock. I need visibility into pg_stat_activity which we also don't get in Google Cloud SQL (other than username and application). I need things like client address/hostname, connection and query start time, and the query itself. Either way, even if I did have that information, there is still no mechanism for me as the DBA to terminate a session that I need to. – Stopoff

I've created a feature request regarding this question. You can check the status and updates here: issuetracker.google.com/issues/110195685 – Beccafico

Thanks, I've also submitted this on the google-cloud-sql-discuss Google Groups forum and got a response that basically relied on always being that same user. – Stopoff