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

7 Steps to configure BDR replication in postgresql

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


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

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory