AWS RDS: how to grant SELECT PG_BUFFERCACHE to non-aws-superuser

Question

After installing the https://www.postgresql.org/docs/9.1/pgbuffercache.html extention I want to have access to the pg_buffercache view from other non-super user.

GRANT EXECUTE ON FUNCTION pg_buffercache_pages() TO test_monitoring;
GRANT SELECT ON pg_buffercache TO test_monitoring;

doesn't work

According to https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.html RDS_SUPERUSER role has permissions to "EXECUTE PG_BUFFERCACHE_PAGES(), SELECT PG_BUFFERCACHE"

is it possible grant the same to other role?

Answer 1

You can create a function and a view as rds_superuser for that:

CREATE FUNCTION buffercache_for_all()
   RETURNS TABLE (
      bufferid integer,
      relfilenode oid,
      reltablespace oid,
      reldatabase oid,
      relforknumber smallint,
      relblocknumber bigint,
      isdirty boolean,
      usagecount smallint,
      pinning_backends integer
   ) LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS
'SELECT p.bufferid,
       p.relfilenode,
       p.reltablespace,
       p.reldatabase,
       p.relforknumber,
       p.relblocknumber,
       p.isdirty,
       p.usagecount,
       p.pinning_backends
FROM public.pg_buffercache_pages() AS p(
        bufferid integer,
        relfilenode oid,
        reltablespace oid,
        reldatabase oid,
        relforknumber smallint,
        relblocknumber bigint,
        isdirty boolean,
        usagecount smallint,
        pinning_backends integer
     )';

CREATE VIEW buffercache_for_all AS SELECT * FROM buffercache_for_all();

Then grant EXECUTE on the function and SELECT on the view to whoever should be allowed to see the information.