MIME-Version: 1.0
From: Edwin UY <edwin.uy@gmail.com>
Date: Mon, 23 Mar 2026 18:00:14 +1300
Message-ID: 
 <CA+wokJ-OQ9s=gpEpUgxXXa0creC-izJb7Giztc-_0N=9SW40xg@mail.gmail.com>
Subject: Is the resultset from these queries has_table_privilege and
 information_schema.role_table_grants supposed to match?
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000003a2b38064da9ebf6"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BwokJ-OQ9s%3DgpEpUgxXXa0creC-izJb7Giztc-_0N%3D9SW40xg%40mail.gmail.com>
Precedence: bulk

--0000000000003a2b38064da9ebf6
Content-Type: text/plain; charset="UTF-8"

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

--0000000000003a2b38064da9ebf6
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><span style=3D"font-family:monospace">Hi,</span></div=
><div><span style=3D"font-family:monospace"><br></span></div><div><span sty=
le=3D"font-family:monospace">Is the resultset from these queries has_table_=
privilege and information_schema.role_table_grants supposed to match?</span=
></div><div><span style=3D"font-family:monospace">
blahp1_abc_update is a role</span></div><div><span style=3D"font-family:mon=
ospace">User patrick has been granted=C2=A0the=C2=A0
blahp1_abc_update role</span></div><div><span style=3D"font-family:monospac=
e">Shouldn&#39;t=C2=A0
blahp1_abc_update appear=C2=A0in the query from information_schema.role_tab=
le_grant.</span></div><div><span style=3D"font-family:monospace">
information_schema.role_table_grant.shows nothing for table_name =3D &#39;j=
ob_requests&#39;</span></div><div><span style=3D"font-family:monospace"><br=
></span></div>blahp1=3D<span style=3D"font-family:monospace">&gt;<br>blahp1=
=3D&gt; select tablename, schemaname,<br>=C2=A0 has_table_privilege(&#39;bl=
ahp1_abc_update&#39;, schemaname || &#39;.&#39; || tablename, &#39;SELECT&#=
39;) SELECT<br>from pg_tables<br>where tablename =3D &#39;job_requests&#39;=
 ;<br>=C2=A0 tablename =C2=A0 | schemaname | select<br>--------------+-----=
-------+--------<br>=C2=A0job_requests | blah =C2=A0 =C2=A0 =C2=A0 =C2=A0| =
f<br>=C2=A0job_requests | blahp1_abc =C2=A0| t<br>(2 rows)<br><br>blahp1=3D=
&gt; SELECT grantee, privilege_type, table_name<br>FROM information_schema.=
role_table_grants<br>WHERE table_schema =3D &#39;blahp1_abc&#39;<br>;<br>=
=C2=A0grantee | privilege_type | table_name<br>---------+----------------+-=
-----------<br>(0 rows)<br><br>blahp1=3D&gt;<br>blahp1=3D&gt;<br>blahp1=3D&=
gt; select table_name, table_schema from information_schema.role_table_gran=
ts where table_name =3D &#39;job_requests&#39; ;<br>=C2=A0table_name | tabl=
e_schema<br>------------+--------------<br>(0 rows)<br><br>blahp1=3D&gt;<br=
>blahp1=3D&gt; select tablename, schemaname,<br>=C2=A0 has_table_privilege(=
&#39;patrick&#39;, schemaname || &#39;.&#39; || tablename, &#39;SELECT&#39;=
) SELECT<br>from pg_tables<br>where tablename =3D &#39;job_requests&#39; ;<=
br>=C2=A0 tablename =C2=A0 | schemaname | select<br>--------------+--------=
----+--------<br>=C2=A0job_requests | blah =C2=A0 =C2=A0 =C2=A0 | f<br>=C2=
=A0job_requests | blahp1_abc | t<br>(2 rows)<br><br></span><div><span style=
=3D"font-family:monospace"><br></span></div><div><span style=3D"font-family=
:monospace">Regards,</span></div><div><span style=3D"font-family:monospace"=
>Pat</span></div><div><span style=3D"font-family:monospace"><br></span></di=
v><div><span style=3D"font-family:monospace"><br></span></div></div>

--0000000000003a2b38064da9ebf6--