Feedback-ID: i76984098:Fastmail
Message-ID: <19043fe9-076b-4d94-a81c-f4292551b8be@aklaver.com>
Date: Mon, 27 Jan 2025 13:54:41 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Content of pg_publication using a local connection versus network
 connection?
To: Shaheed Haque <shaheedhaque@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
References: <CAHAc2jdj3EFVqc0WCHnQXL3hiJ_mfJH08WW=VznO0YzCjx-eKA@mail.gmail.com>
 <cc0ca54f-8e8f-43e4-ab7e-59790ea5b4fd@aklaver.com>
 <CAHAc2jfZqwGum2CxBHD85FzujohjRC+jKuQB+9dXkFGHdVch0A@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAHAc2jfZqwGum2CxBHD85FzujohjRC+jKuQB+9dXkFGHdVch0A@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/19043fe9-076b-4d94-a81c-f4292551b8be%40aklaver.com>
Precedence: bulk

On 1/27/25 13:34, Shaheed Haque wrote:
> Hi Adrian,
> 
> On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.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@18.168.196.169
>     <mailto:awsuser@18.168.196.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.klaver@aklaver.com