MIME-Version: 1.0
From: Shaheed Haque <shaheedhaque@gmail.com>
Date: Mon, 27 Jan 2025 20:41:08 +0000
Message-ID: <CAHAc2jdj3EFVqc0WCHnQXL3hiJ_mfJH08WW=VznO0YzCjx-eKA@mail.gmail.com>
Subject: Content of pg_publication using a local connection versus network connection?
To: pgsql-general list <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000045611c062cb6193f"
Archived-At: <https://www.postgresql.org/message-id/CAHAc2jdj3EFVqc0WCHnQXL3hiJ_mfJH08WW%3DVznO0YzCjx-eKA%40mail.gmail.com>
Precedence: bulk

--00000000000045611c062cb6193f
Content-Type: text/plain; charset="UTF-8"

Hi,

I'm a novice-ish when it comes to Postgres, but I've studied the docs and
not been able to understand why I can see the rows in pg_publication via a
local psql session, but not when I am connected via the network.

Since the network login is (a) successful and (b) can read the content of
other non-system tables, I guessed that my problem is row-level security
(RLS)....except that from the docs, I was unable to see how the login type
could affect RLS. What am I missing?

Here is some context...please do ask if something else needs to be
clarified!

- System Postgres 16, AWS RDS version.
- The pg_publication tabe looks like this:

foo=>  \dpS pg_publication
                                      Access privileges
  Schema   |      Name      | Type  |     Access privileges     | Column
privileges | Policies
------------+----------------+-------+---------------------------+-------------------+----------

pg_catalog | pg_publication | table | rdsadmin=arwdDxt/rdsadmin+|
                  |
           |                |       | =r/rdsadmin               |
                  |


- When I am logged in as this user via psql, I  can see:

foo=> select * from pg_publication;
 oid  |      pubname      | pubowner | puballtables | pubinsert | pubupdate
| pubdelete | pubtruncate | pubviaroot
-------+-------------------+----------+--------------+-----------+-----------+-----------+-------------+------------

98923 | vm_db_publication |    16478 | t            | t         | t
        | t         | t           | f


- When I connect via psycog, I can read other tables, but pg_publication
aways seems to return no rows.

Any assistance would be appreciated.

Thanks, Shaheed

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

<div dir=3D"ltr"><div>Hi,<br><br></div><div>I&#39;m a novice-ish when it co=
mes to Postgres, but I&#39;ve studied the docs and not been able to underst=
and why I can see the rows in pg_publication via a local psql session, but =
not when I am connected via the network.</div><div><br></div><div>Since the=
 network login is (a) successful and (b) can read the content of other non-=
system tables, I guessed that my problem is row-level security (RLS)....exc=
ept that from the docs, I was unable to see how the login type could affect=
 RLS. What am I missing?<br><br></div><div>Here is some context...please do=
 ask if something else needs to be clarified!<br></div><div><br></div><div>=
- System Postgres 16, AWS RDS version.</div><div>- The pg_publication tabe =
looks like this:</div><div><span style=3D"font-family:monospace"><span styl=
e=3D"color:rgb(0,0,0);background-color:rgb(255,255,255)"><br></span></span>=
</div><div><span style=3D"font-family:monospace"><span style=3D"color:rgb(0=
,0,0);background-color:rgb(255,255,255)">foo=3D&gt; =C2=A0\dpS pg_publicati=
on
</span><br> =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0Access privileges
<br> =C2=A0=C2=A0Schema =C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Name =
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| Type =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0Access=
 privileges =C2=A0=C2=A0=C2=A0=C2=A0| Column privileges | Policies =C2=A0<b=
r>------------+----------------+-------+---------------------------+-------=
------------+----------
<br> pg_catalog | pg_publication | table | rdsadmin=3DarwdDxt/rdsadmin+| =
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0<br> =C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0| =3Dr/rdsadmin =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=
=A0=C2=A0| <br>
<br><br></span></div><div>- When I am logged in as this user via psql, I=C2=
=A0 can see:</div><div><br></div><div><span style=3D"font-family:monospace"=
><span style=3D"color:rgb(0,0,0);background-color:rgb(255,255,255)">foo=3D&=
gt; select * from pg_publication;
</span><br> =C2=A0oid =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0pubname =C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0| pubowner | puballtables | pubinsert | pubupdate |=
 pubdelete | pubtruncate | pubviaroot =C2=A0<br>-------+-------------------=
+----------+--------------+-----------+-----------+-----------+------------=
-+------------
<br> 98923 | vm_db_publication | =C2=A0=C2=A0=C2=A016478 | t =C2=A0=C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| t =C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| t =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=
=C2=A0=C2=A0| t =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| t =C2=A0=
=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| f<br>
<br><br></span></div><div>- When I connect via psycog, I can read other tab=
les, but pg_publication aways seems to return no rows.<br><br></div><div>An=
y assistance would be appreciated.</div><div><br></div><div>Thanks, Shaheed=
<br></div><div><br></div><div><br></div><div><br></div></div>

--00000000000045611c062cb6193f--