Step 3: Create pg_stat_statements Helpers

Because Aiven for PostgreSQL does not give access to the pg_monitor role that can read all queries from pg_stat_statements, you'll need to create a set of SECURITY DEFINER helper functions to collect full query stats.

For each user whose queries you would like to monitor, you'll need to log in to your database and create a function like the following (replace <username> with the actual user name):

CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements_<username>(showtext boolean = true)
RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements(showtext) WHERE userid = '<username>'::regrole;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

For example, if you have two users, app and analytics, you'll need to log in as app and run:

CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements_app(showtext boolean = true)
RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements(showtext) WHERE userid = 'app'::regrole;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Then, log in as analytics and run:

CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements_analytics(showtext boolean = true)
RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements(showtext) WHERE userid = 'analytics'::regrole;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Make sure to also log in as avnadmin and create a function for that user:

CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements_avnadmin(showtext boolean = true)
RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM public.pg_stat_statements(showtext) WHERE userid = 'avnadmin'::regrole;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

Then, still logged in as avnadmin, create a helper function to combine these. For example, for the case of the three users above, the final helper function will look like this:

CREATE OR REPLACE FUNCTION pganalyze.get_stat_statements(showtext boolean = true)
RETURNS SETOF pg_stat_statements AS
$$
  SELECT * FROM pganalyze.get_stat_statements_app(showtext)
    UNION ALL
  SELECT * FROM pganalyze.get_stat_statements_analytics(showtext)
    UNION ALL
  SELECT * FROM pganalyze.get_stat_statements_avnadmin(showtext)
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

You will need to update these function definitions as new dataabse users are added and removed, but it will allow you to have access to all users' queries without the pg_monitor role.

Next we continue by installing the pganalyze collector:

Proceed to Step 4: Install the Collector

Couldn't find what you were looking for or want to talk about something specific?
Start a conversation with us →