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      | 1.2.3.4
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


solution
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.

Comments

Popular posts from this blog

PostgreSQL pgBadger

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

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart