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

Question

安装 https://www.postgresql.org/docs/9.1/pgbuffercache.html 后我想从其他非 super 用户访问 pg_buffercache View 。

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

没用

根据 https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.MasterAccounts.htmlRDS_SUPERUSER 角色具有“EXECUTE PG_BUFFERCACHE_PAGES(), SELECT PG_BUFFERCACHE”的权限

是否可以将相同的权限授予其他角色？

Answer 1

您可以为此创建一个函数和一个 View 作为 rds_superuser:

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();

然后将函数上的 EXECUTE 和 View 上的 SELECT 授予应允许查看信息的任何人。