Is the resultset from these queries has_table_privilege and information_schema.role_table_grants supposed to match?

From: Edwin UY <edwin(dot)uy(at)gmail(dot)com>
To: Pgsql-admin <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Is the resultset from these queries has_table_privilege and information_schema.role_table_grants supposed to match?
Date: 2026-03-23 05:00:14
Message-ID: CA+wokJ-OQ9s=gpEpUgxXXa0creC-izJb7Giztc-_0N=9SW40xg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hi,

Is the resultset from these queries has_table_privilege and
information_schema.role_table_grants supposed to match?
blahp1_abc_update is a role
User patrick has been granted the blahp1_abc_update role
Shouldn't blahp1_abc_update appear in the query from
information_schema.role_table_grant.
information_schema.role_table_grant.shows nothing for table_name =
'job_requests'

blahp1=>
blahp1=> select tablename, schemaname,
has_table_privilege('blahp1_abc_update', schemaname || '.' || tablename,
'SELECT') SELECT
from pg_tables
where tablename = 'job_requests' ;
tablename | schemaname | select
--------------+------------+--------
job_requests | blah | f
job_requests | blahp1_abc | t
(2 rows)

blahp1=> SELECT grantee, privilege_type, table_name
FROM information_schema.role_table_grants
WHERE table_schema = 'blahp1_abc'
;
grantee | privilege_type | table_name
---------+----------------+------------
(0 rows)

blahp1=>
blahp1=>
blahp1=> select table_name, table_schema from
information_schema.role_table_grants where table_name = 'job_requests' ;
table_name | table_schema
------------+--------------
(0 rows)

blahp1=>
blahp1=> select tablename, schemaname,
has_table_privilege('patrick', schemaname || '.' || tablename, 'SELECT')
SELECT
from pg_tables
where tablename = 'job_requests' ;
tablename | schemaname | select
--------------+------------+--------
job_requests | blah | f
job_requests | blahp1_abc | t
(2 rows)

Regards,
Pat

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message David G. Johnston 2026-03-23 05:17:33 Re: Is the resultset from these queries has_table_privilege and information_schema.role_table_grants supposed to match?
Previous Message Ron Johnson 2026-03-17 14:42:59 Re: OS upgrade on postgres servers