| 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
| 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 |