Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Cancel Query As Non-Superuser In Postgresql

  A non-superuser on a PostgreSQL 9.1 database, and they have just created a bad trigger function that won't stop. They can see the process, e.g.:

thedb=> SELECT * FROM pg_stat_activity WHERE procpid = 45678;
-[ RECORD 1 ]----+-------------------------------------------------------------------
datid            | 596281
datname          | thedb
procpid          | 45678
usesysid         | 596282
usename          | myuser
application_name | QGIS
client_addr      |
client_hostname  |
client_port      | 12345
backend_start    | 2015-04-16 13:45:27.482691+12
xact_start       | 2015-04-16 14:17:34.633156+12
query_start      | 2015-04-16 14:17:34.633665+12
waiting          | f
current_query    | UPDATE ...

But they  can't stop it:
thedb=> SELECT pg_terminate_backend(45678);
ERROR:  must be superuser to signal other server processes

You must either be the superuser or logged in as the same user who owns the session you wish to cancel.

So connect as user myuser and you'll be able to pg_cancel_backend, or, if that doesn't respond, pg_terminate_backend.


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

PostgreSQL Introduction