public inbox for [email protected]  

help / color / mirror / Atom feed

From: Edwin UY <[email protected]>
To: Pgsql-admin <[email protected]>
Subject: Is the resultset from these queries has_table_privilege and information_schema.role_table_grants supposed to match?
Date: Mon, 23 Mar 2026 18:00:14 +1300
Message-ID: <CA+wokJ-OQ9s=gpEpUgxXXa0creC-izJb7Giztc-_0N=9SW40xg@mail.gmail.com> (raw)

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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Is the resultset from these queries has_table_privilege and information_schema.role_table_grants supposed to match?
  In-Reply-To: <CA+wokJ-OQ9s=gpEpUgxXXa0creC-izJb7Giztc-_0N=9SW40xg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox