From: | Shaheed Haque <shaheedhaque(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Content of pg_publication using a local connection versus network connection? |
Date: | 2025-01-28 18:02:25 |
Message-ID: | CAHAc2jc9pjEWp+izx43QMsYz5pq_xcYo2TJXPChRmo+D2o_R-w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 <shaheedhaque(at)gmail(dot)com> wrote:
>
>
> 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
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2025-01-28 18:06:55 | Re: Content of pg_publication using a local connection versus network connection? |
Previous Message | Ron Johnson | 2025-01-28 16:38:33 | Re: Log retention query |