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

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.

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