Database Reference
In-Depth Information
necessary to allow the non-superusers to see at least the type of query (
SELECT
,
INSERT
,
DELETE
, or
UPDATE
) other users are running, but hide the exact contents.
To do so, you have to make two changes to the previous function.
First, replace the row for getting
current_query
with the following code snippet:
(CASE WHEN ( usename= session_user )
OR (select usesuper
from pg_user
where usename = session_user)
THEN
substring(query,1,qlen)
ELSE
substring(ltrim(query), 1, 6) || ' ***'
END )as query
This code snippet checks each row to see if the user running the function has per-
mission to see the full query. If the user is a superuser, then he has permission to
see the full query. If the user is a regular user, he will only see the full query for his
queries. All other rows will only show the first six characters followed by
***
to mark
it as a shortened query string.
The other key point to allowing ordinary users to run the function is to grant them
the appropriate rights to do so. When a function is created, the default behavior is
to run with Security Invoker rights, which means that the function will be called with
the rights of the user who called it. To easily grant the correct rights to call the func-
tion, the function needs to be created with Security Definer privileges. This causes
the function to execute with the privileges of the user that created the function, so
creating the function as a superuser will allow it to execute as a superuser.
Now you have a function which you can use to get the start of the five longest running
queries using the following query:
SELECT * FROM running_queries(5,25);
Or to get complete a query you can use:
