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

From: Shaheed Haque <[email-masked]>
Date: Mon, 27 Jan 2025 22:20:28 +0000

On Mon, 27 Jan 2025 at 21:54, Adrian Klaver <[email-masked]>
wrote:

> On 1/27/25 13:34, Shaheed Haque wrote:
> > Hi Adrian,
> >
> > On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <[email-masked]
> > <mailto:[email-masked]>> 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 [email-masked]
> >     <mailto:[email-masked]>
> >   * 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
> [email-masked]
>
>

---

From: Shaheed Haque <[email-masked]>
Date: Tue, 28 Jan 2025 18:02:25 +0000

Hi,

Based on the nudge from Adrian, I think I am now trying to connect to the
correct/same database through both the original
login-to-EC2-host-then-use-psql-to-RDS and then
setup-SSHTunnel-then-connect-via-psycopg-over-tunnel.

The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that is
almost certainly a bug in my code, but I am aware that the Postgres' HBA
setup is capable of distinguishing local logins from remote logins, so I
wanted to check if Postgres' login security can similarly distinguish
between a (remote) psql login and a (remote) psycopg login?

Thanks, Shaheed

On Mon, 27 Jan 2025 at 22:20, Shaheed Haque <[email-masked]> wrote:

>> >      >             |                |       | =r/rdsadmin
>> >      > 98923 | vm_db_publication |    16478 | t            | t
>>         | t
>> >      >          | t         | t           | f
>> >      >
>> >      >

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 28 Jan 2025 10:06:55 -0800

On 1/28/25 10:02 AM, Shaheed Haque wrote:
> Hi,
> 
> Based on the nudge from Adrian, I think I am now trying to connect to 
> the correct/same database through both the original 
> The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that 
> is almost certainly a bug in my code, but I am aware that the Postgres' 
> HBA setup is capable of distinguishing local logins from remote logins, 
> so I wanted to check if Postgres' login security can similarly 
> distinguish between a (remote) psql login and a (remote) psycopg login?

They both use libpq so I doubt it.

> On

---

From: Shaheed Haque <[email-masked]>
Date: Tue, 28 Jan 2025 18:08:23 +0000

Thanks for the confirmation.

---

From: Adrian Klaver <[email-masked]>
Date: Tue, 28 Jan 2025 10:14:40 -0800

> Thanks for the confirmation.

Your login/connection security is going to be handled by:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

and the auth methods it supports.

> 
> On Tue, 28 Jan 2025 at 18:06, Adrian Klaver <[email-masked]