What are the security implications of the privileges needed by the Bluemedora Postgres account? Blue Medora is a service for forwarding monitoring metrics from Postgres to Google Cloud Monitoring.
As part of their setup instructions they offer a SQL script to configure a least privilege user to be used to login to the account. (pasted below for reference)
If the bluemedora account were compromised, what information could be leaked or what else could it get up to?
(obviously replacing tmppassword in the script is a given)
An obvious one is that it can pull all the stats that it reports on, including reporting queries that are being executed which could lead to leakage of PII if it's included in those queries.
Based on the info present an attacker could find out the names of users and superusers of the db, all the tables being used and with the queries probably a good deal of the table structures. Which could then be used to craft a targeted attack.
Are there other more direct ways to access the data stored in the tables with this account, or to escalate privileges?
Here's the script
CREATE SCHEMA bluemedora;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE OR REPLACE FUNCTION bluemedora.pg_stat_statements() RETURNS SETOF pg_stat_statements AS
$$
SELECT * FROM public.pg_stat_statements;
$$ LANGUAGE sql VOLATILE SECURITY DEFINER;

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain json
    )
RETURNS SETOF json AS
$$
BEGIN
  RETURN QUERY EXECUTE 'explain (format json) ' || l_query;
END;
$$
LANGUAGE 'plpgsql'
VOLATILE
RETURNS NULL ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

CREATE USER bluemedora WITH PASSWORD 'tmppassword';
GRANT SELECT ON pg_database TO bluemedora;
GRANT SELECT ON pg_stat_bgwriter TO bluemedora;
GRANT SELECT ON pg_stat_database TO bluemedora;
GRANT SELECT ON pg_stat_user_indexes TO bluemedora;
GRANT SELECT ON pg_stat_user_tables TO bluemedora;
GRANT SELECT ON pg_statio_all_sequences TO bluemedora;
GRANT SELECT ON pg_statio_user_indexes TO bluemedora;
GRANT SELECT ON pg_statio_user_tables TO bluemedora;
GRANT SELECT ON pg_tables TO bluemedora;
GRANT SELECT ON pg_tablespace TO bluemedora;
GRANT SELECT ON pg_user TO bluemedora;
GRANT SELECT ON pg_stat_replication TO bluemedora;
GRANT SELECT ON pg_stat_database_conflicts TO bluemedora;
GRANT SELECT ON pg_trigger TO bluemedora;
GRANT SELECT ON pg_stat_activity TO bluemedora;
GRANT SELECT ON pg_stat_statements TO bluemedora;
GRANT USAGE ON SCHEMA bluemedora TO bluemedora;
GRANT EXECUTE ON FUNCTION public.explain_this(l_query text, out explain text) TO bluemedora;
The big security problem here is that it doesn't revoke execution on its created functions from PUBLIC, which means that users other than bluemedora can use them, which is evidently not what is intended.  For pg_stat_statement this isn't really a problem, as it is located in a schema that no one else should have access to, but for explain_this it is in public schema and with PUBLIC execution rights.  And why is explain_this created in public in the first place?  If there is a reason to spread the functions over two schemas, I don't know what it is.  It looks like maybe explain_this was added to the script by someone who didn't know what they were doing.
Granting SELECT on all those catalog table is unneeded on most installations, as those already have SELECT granted for PUBLIC.  However, there is no guarantee this script will be run on &quot;most installations&quot;, so it is reasonable to explicitly grant those permissions rather than relying on someone having not mucked with the defaults.  Most concerning to me would be the lack of schema qualification on those grants.

Based on the info present an attacker could find out the names of
users and superusers of the db, all the tables being used and with the
queries probably a good deal of the table structures. Which could then
be used to craft a targeted attack.

All of those things except for query text will be true for almost any compromised user, even with no special permissions granted.  Are you worried about social engineering attacks?