information_schema.table_privileges

In PostgreSQL, viewing information_schema.table_privileges only shows privileges granted to or by the current role. This is by design and is documented in the information_schema.table_privileges reference.

postgres=# set role admin_user;
SET

postgres=# create user u;
CREATE ROLE

postgres=# create table t (n integer);
CREATE TABLE

postgres=# grant select on t to u;
GRANT

postgres=# select * from information_schema.table_privileges 
           where table_name = 't' and grantee = 'u';
 grantor     | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
-------------+---------+---------------+--------------+------------+----------------+--------------+----------------
 admin_user | u     | postgres      | public       | t          | SELECT         | NO           | YES

However, when executed under a low-privileged user, the query returns no rows:

postgres=# create user monitoring;
CREATE ROLE

postgres=# set role monitoring;
SET

postgres=> select * from information_schema.table_privileges 
            where table_name = 't' and grantee = 'u';
(0 rows)

This behavior is similar to Oracle’s ALL_TAB_PRIVS, which only shows privileges related to the current user. For full visibility, we want behavior like Oracle’s DBA_TAB_PRIVS.

To do this in PostgreSQL, we must solve two issues:

1. The non-privileged user must be granted SELECT access on safe columns from pg_authid.
2. We must bypass the current-user-only filter used by information_schema.table_privileges.

pg_authid access

pg_authid contains sensitive information (like password hashes), so we limit access to just the necessary columns:

GRANT SELECT(oid, rolname) ON pg_authid TO monitoring;

Remove current user filter

The default filter in the view definition limits results to grants involving the current user or PUBLIC. This can be seen in the PostgreSQL source code:

AND (pg_has_role(u_grantor.oid, 'USAGE')
     OR pg_has_role(grantee.oid, 'USAGE')
     OR grantee.rolname = 'PUBLIC');

Final SELECT

This query returns all privileges granted on tables in the cluster, regardless of the current role:

SELECT CAST(u_grantor.rolname AS text) AS grantor,
       CAST(grantee.rolname AS text) AS grantee,
       CAST(current_database() AS text) AS table_catalog,
       CAST(nc.nspname AS text) AS table_schema,
       CAST(c.relname AS text) AS table_name,
       CAST(c.prtype AS text) AS privilege_type,
       CAST(CASE WHEN pg_has_role(grantee.oid, c.relowner, 'USAGE') OR c.grantable
                 THEN 'YES' ELSE 'NO' END AS text) AS is_grantable,
       CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS text) AS with_hierarchy
FROM (
      SELECT oid, relname, relnamespace, relkind, relowner,
             (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
      FROM pg_class
     ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
     pg_namespace nc,
     pg_authid u_grantor,
     (
       SELECT oid, rolname FROM pg_authid
       UNION ALL
       SELECT 0::oid, 'PUBLIC'
     ) AS grantee (oid, rolname)
WHERE c.relnamespace = nc.oid
      AND c.relkind IN ('r', 'v', 'f', 'p')
      AND c.grantee = grantee.oid
      AND c.grantor = u_grantor.oid
      AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER');

When executed by the low-privileged monitoring role, this query returns:

 grantor   | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
-----------+---------+---------------+--------------+------------+----------------+--------------+----------------
 admin_user | u       | postgres      | public       | t          | SELECT         | NO           | YES