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 21:34:57 +0000
Message-ID: <CAHAc2jfZqwGum2CxBHD85FzujohjRC+jKuQB+9dXkFGHdVch0A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAHAc2jdj3EFVqc0WCHnQXL3hiJ_mfJH08WW=VznO0YzCjx-eKA@mail.gmail.com>
<[email protected]>
Hi Adrian,
On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <[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]
- 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
> [email protected]
>
>
view thread (3+ 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: <CAHAc2jfZqwGum2CxBHD85FzujohjRC+jKuQB+9dXkFGHdVch0A@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