PostgreSQL 12 High Availability Cookbook - Third Edition

How to do it...

Perform the following steps to prepare pg_stat_activity for generalized use:

1. Execute this SQL statement as a database superuser to create a function:

CREATE OR REPLACE FUNCTION pg_stat_activity() 
RETURNS SETOF pg_stat_activity AS $$ 
    SELECT * FROM pg_stat_activity; 
$$ LANGUAGE sql SECURITY DEFINER; 

2. Execute this SQL statement to secure the function we created:

REVOKE ALL ON FUNCTION pg_stat_activity() FROM PUBLIC; 

3. Create a user dedicated to monitoring with this SQL statement:

CREATE USER db_mon WITH PASSWORD 'somepass'; 

4. Grant the monitoring user the ability to use our function with this SQL statement:

GRANT EXECUTE ON FUNCTION pg_stat_activity() TO db_mon; 

5. Now, connect to PostgreSQL as the db_mon user and examine ...