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

Hi Adrian,

On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <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
- foo=> \conninfo

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") 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
is awsuser

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

Thanks, Shaheed

>
> 2) Are you sure you are connecting to same database?
>
>
> >
> > Any assistance would be appreciated.
> >
> > Thanks, Shaheed
> >
> >
> >
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>