Re: Content of pg_publication using a local connection versus network connection?

On Mon, 27 Jan 2025 at 21:54, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 1/27/25 13:34, Shaheed Haque wrote:
> > Hi Adrian,
> >
> > On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 1/27/25 12:41, Shaheed Haque wrote:
> > > 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:
> >
> > This user is rdsadmin or something else?
> >
> >
> > The username is "dbcorexyz". See more below.
> >
> > >
> > > 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.
> >
> > 1) What is your connection string?
> > In particular what user are you connecting as?
> >
> >
> > When I use psql, I first have to SSH to an AWS EC2, and then run psql.
> > Thus, the details in this case are:
> >
> > * ssh -i vm_paiyroll.pem awsuser(at)18(dot)168(dot)196(dot)169
> > <mailto:awsuser(at)18(dot)168(dot)196(dot)169>
> > * foo=> \conninfo
> >
> > You are connected to database "foo" as user "dbcorexyz" on host
> > "live-paiyroll-db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com
> > <
> http://live-paiyroll-db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com>"
> (address "172.31.4.93") at port "5432".
> > SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384,
> > compression: off)
> >
> > When I connect via pscopg, I first set up an SSH tunnel through the EC2
> > host, and then connect. Thus the details in this case are:
> >
> > *
> >
> > bastion_host.ssh_host is '18.168.196.169', bastion_host.ssh_usernme
> isawsuser
> >
> > * <psycopg_binary.pq.PGconn [INTRANS] (host=localhost user=dbcorexyz
> > database=foo) at 0x7f6bfd554a90>
> >
> > I *am* dealing with multiple db connections (am working on some
> > replication tooling) but AFAICS, both connections are to the same place.
> >
>
> Are you sure?
>
> From psql connection:
>
> You are connected to database "foo" as user "dbcorexyz" on host
> "live-paiyroll-db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com"
> (address "172.31.4.93")
>
> Note host of 172.31.4.93
>
> In psycopg2 case you again connect to 18.168.196.169 for SSH but then:
>
> (host=localhost ...)
>
> I'm not seeing localhost being equal to 172.31.4.93.
>

Erk. I think you may have got it. I will go examine my navel...and the
code. Many thanks for the quick and kind help.

Shaheed

>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>