postgresql - 如何终止 Google Cloud SQL for PostgreSQL 中的 session ？

Question

由于适用于 PostgreSQL 的 Google Cloud SQL 没有为我们提供 super 用户(甚至没有为 postgres 用户提供)，我无法从 pg_stat_activity 查看其他 session 正在运行哪些查询，也无法在需要时终止其他 session 。

例如:

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

如果没有真正的 super 用户访问权限，我们如何在 Cloud SQL PostgreSQL 实例中执行这些操作？只有 cloudsqladmin 帐户是 super 用户，据我所知，我不能成为 super 用户:

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，如果您以该 session 的用户身份登录，您可以终止该 session 。用户可以终止他们的任何 session ，标准的 PostgreSQL 东西。

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)

Answer 1

pg_terminate_backend说:

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.

当您是 Google 的 cloudsqlsuperuser 成员(gcloud sql users create 的默认值)时，您可以授予自己:

GRANT pg_signal_backend TO myuser;

然后你可以终止除 super 用户之外的任何 session :

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

pg_signal_backend在 v9.6 中引入 - 正是 Google Cloud 上的版本!

另一种方法是在 other 角色中以能够终止作为 me 的 session :

GRANT other TO me;