public inbox for [email protected]
help / color / mirror / Atom feedFrom: Shaheed Haque <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: pgsql-general list <[email protected]>
Subject: Re: Content of pg_publication using a local connection versus network connection?
Date: Mon, 27 Jan 2025 22:20:28 +0000
Message-ID: <CAHAc2jdkEHnRfWCZ77+U60MKGnkciaDoCizDiVJz8zQG2B+mnw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHAc2jdj3EFVqc0WCHnQXL3hiJ_mfJH08WW=VznO0YzCjx-eKA@mail.gmail.com>
<[email protected]>
<CAHAc2jfZqwGum2CxBHD85FzujohjRC+jKuQB+9dXkFGHdVch0A@mail.gmail.com>
<[email protected]>
On Mon, 27 Jan 2025 at 21:54, Adrian Klaver <[email protected]>
wrote:
> On 1/27/25 13:34, Shaheed Haque wrote:
> > Hi Adrian,
> >
> > On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <[email protected]
> > <mailto:[email protected]>> 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 protected]
> > <mailto:[email protected]>
> > * 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 protected]
>
>
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Content of pg_publication using a local connection versus network connection?
In-Reply-To: <CAHAc2jdkEHnRfWCZ77+U60MKGnkciaDoCizDiVJz8zQG2B+mnw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox