Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tcpvG-0033DP-GT for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 18:02:51 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tcpvA-002ZLG-K6 for pgsql-general@arkaria.postgresql.org; Tue, 28 Jan 2025 18:02:44 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tcpvA-002ZJ4-4W for pgsql-general@lists.postgresql.org; Tue, 28 Jan 2025 18:02:44 +0000 Received: from mail-wr1-x432.google.com ([2a00:1450:4864:20::432]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcpv6-0020M1-04 for pgsql-general@lists.postgresql.org; Tue, 28 Jan 2025 18:02:42 +0000 Received: by mail-wr1-x432.google.com with SMTP id ffacd0b85a97d-38a8b17d7a7so3241319f8f.2 for ; Tue, 28 Jan 2025 10:02:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738087358; x=1738692158; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=NwL0DBwD/7e8Ubzcz0Gfo7OQjLeIAmaM09XkEaXcq+0=; b=SkYxR7M9z7Fho1y+PG0u7AFDwJXkxEDyUbyUsovCy1XUxkoMERkdCBhgkUQOzuRqhC 0FgPs+bGNuj8JXlMMaBtqFjNZqqLsPUsJTL58+oLFT7I3Ib8vtdDajYmw9x+1qTIrNkl gCLgAAp6c9XxegbjaJTRyiJSow76ASCFimSlBItJ45xCJQX0720Ir13ZRpRGX+589cJu 7ikcDax0EEDfq9/we3oK1qkhiyoPrdNhuWDGEnoFT/uq0cosnuvW81BQ2iRD1kaDxjRd VXGvpkQ7vXIqk336cidWzGQTTFir5fRGsvZPfI2xOmjH9enPbQO2U51aRPBCYoD2JIvJ N97g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738087358; x=1738692158; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=NwL0DBwD/7e8Ubzcz0Gfo7OQjLeIAmaM09XkEaXcq+0=; b=dYixS1I9idY5S38v7HDv4PpvswYax9lz3iyHKqIpP00Ob0KsiN6mrpoJ1dNXLCjrZf ccHLhGHA610FABivLvJC7VE7OLAj31zIrUz9DXrCw5yDawP6EiVJXaKXptLPnDNU1WV4 i/Zmv7tqikGSTpBhYSO3NjhG4wK00Ai2ivIqOFfBGob6hOuTlxyGKr89l5ztb867ghY0 xbGlEdHG33O7I+29VVtTg2h21EJYqH4NP3mN7M9PiX1kTTG+uZ765taQfgbsBcuc2ORx bqaur8ixLxiIc7IubVh2gA0SpE9PdfgmyjHXGR7u8Ra5OsrvzVwrwl+UhXp6o+Sbietf pIcg== X-Gm-Message-State: AOJu0YxnFZsPOAigMLym/7HRWXhux4hZTCgyVqaEYbtq9C2P4Qq5hyBg 8WMGa2vRC0tfPFHAxFwm9e6pwfQUvnwa60je1Zk11afhHDgQ4arm/BKeg/l9K34yt8QF2jfi1NN Nv7AMqSn6ZoGmrGvY8m5rNkHFgiig7JVw X-Gm-Gg: ASbGncsXwJda8nIOOYB98PW7qF1BZAQYYTy8Vn37ey8XZBey3+xXvOuWoBJ5cB0xlpV q0GED70bT1hrLJjQB1HVZWCh1qx3iPokeLA94Xzky1XBytodAqnvzu23VQzVlyPqh0hYmRtfx X-Google-Smtp-Source: AGHT+IEjseYnxx04Xx57BvnVAn8ISHVPQBI6SVDHar4eNPdX9yZapWoFsbzHqS0jAaDZJAaQTGnhdkd/UEOZ+G+5eg0= X-Received: by 2002:a5d:588f:0:b0:38b:e109:1e0d with SMTP id ffacd0b85a97d-38bf57b9dffmr40158167f8f.49.1738087356514; Tue, 28 Jan 2025 10:02:36 -0800 (PST) MIME-Version: 1.0 References: <19043fe9-076b-4d94-a81c-f4292551b8be@aklaver.com> In-Reply-To: From: Shaheed Haque Date: Tue, 28 Jan 2025 18:02:25 +0000 X-Gm-Features: AWEUYZldZKA7WSYYh4U3hjPa_aUdf3JWhGdS_F1ox67kbzgZ7KHpSnPRaLq9BBc Message-ID: Subject: Re: Content of pg_publication using a local connection versus network connection? To: Adrian Klaver Cc: pgsql-general list Content-Type: multipart/alternative; boundary="000000000000794876062cc7ff56" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000794876062cc7ff56 Content-Type: text/plain; charset="UTF-8" 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 wrote: > > > On Mon, 27 Jan 2025 at 21:54, Adrian Klaver > wrote: > >> On 1/27/25 13:34, Shaheed Haque wrote: >> > Hi Adrian, >> > >> > On Mon, 27 Jan 2025 at 20:51, Adrian Klaver > > > 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 >> > >> > * 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 >> > >> > * > > 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.klaver@aklaver.com >> >> --000000000000794876062cc7ff56 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi,

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

<= div>The connect-via-psycopg-over-tunnel bit currently fails. Obviously, tha= t is almost certainly a bug in my code, but I am aware that the Postgres= 9; HBA setup is capable of distinguishing local logins from remote logins, = so I wanted to check if Postgres' login security can similarly distingu= ish between a (remote) psql login and a (remote) psycopg login?
<= br>
Thanks, Shaheed


=
On Mon, 27 Jan 2025 at 22:20, Shaheed Haque <shaheedhaque@gmail.com> wrote:


On Mon, 27 Jan 2025 at 21:54, Adrian Klaver <adrian.klaver@aklaver.c= om> wrote:
adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>=C2=A0 =C2=A0 =C2=A0On 1/27/25 12:41, Shaheed Haque wrote:
>=C2=A0 =C2=A0 =C2=A0 > Hi,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > I'm a novice-ish when it comes to Postgre= s, but I've studied the
>=C2=A0 =C2=A0 =C2=A0docs
>=C2=A0 =C2=A0 =C2=A0 > and not been able to understand why I can see= the rows in
>=C2=A0 =C2=A0 =C2=A0pg_publication
>=C2=A0 =C2=A0 =C2=A0 > via a local psql session, but not when I am c= onnected via the
>=C2=A0 =C2=A0 =C2=A0network.
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Since the network login is (a) successful and= (b) can read the
>=C2=A0 =C2=A0 =C2=A0content
>=C2=A0 =C2=A0 =C2=A0 > of other non-system tables, I guessed that my= problem is row-level
>=C2=A0 =C2=A0 =C2=A0 > security (RLS)....except that from the docs, = I was unable to see
>=C2=A0 =C2=A0 =C2=A0how the
>=C2=A0 =C2=A0 =C2=A0 > login type could affect RLS. What am I missin= g?
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > Here is some context...please do ask if somet= hing else needs to be
>=C2=A0 =C2=A0 =C2=A0 > clarified!
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > - System Postgres 16, AWS RDS version.
>=C2=A0 =C2=A0 =C2=A0 > - The pg_publication tabe looks like this: >=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > foo=3D> =C2=A0\dpS pg_publication
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0Access privileges
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0=C2=A0Schema =C2=A0=C2=A0| =C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0Name =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| Type =C2=A0| = =C2=A0=C2=A0=C2=A0=C2=A0Access privileges =C2=A0=C2=A0=C2=A0=C2=A0|
>=C2=A0 =C2=A0 =C2=A0 > Column privileges | Policies
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0------------+----------------+-------+-------------= --------------+-------------------+----------
>=C2=A0 =C2=A0 =C2=A0 > pg_catalog | pg_publication | table | rdsadmi= n=3DarwdDxt/rdsadmin+|
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| =3Dr/rdsadmin =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0|
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > - When I am logged in as this user via psql, = I=C2=A0 can see:
>
>=C2=A0 =C2=A0 =C2=A0This user is rdsadmin or something else?
>
>
> The username is "dbcorexyz". See more=C2=A0 below.
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > foo=3D> select * from pg_publication;
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0oid =C2=A0| =C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0pubname =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| pubowner | puballtables | = pubinsert |
>=C2=A0 =C2=A0 =C2=A0 > pubupdate | pubdelete | pubtruncate | pubviar= oot
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0-------+-------------------+----------+------------= --+-----------+-----------+-----------+-------------+------------
>=C2=A0 =C2=A0 =C2=A0 > 98923 | vm_db_publication | =C2=A0=C2=A0=C2= =A016478 | t =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0| t =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| t
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0| t =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| t =C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| f
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > - When I connect via psycog, I can read other= tables, but
>=C2=A0 =C2=A0 =C2=A0pg_publication
>=C2=A0 =C2=A0 =C2=A0 > aways seems to return no rows.
>
>=C2=A0 =C2=A0 =C2=A01) What is your connection string?
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 In particular what user are you conn= ecting 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:
>
>=C2=A0 =C2=A0* ssh -i vm_paiyroll.pem awsuser@18.168.196.169
>=C2=A0 =C2=A0 =C2=A0<mailto:awsuser@18.168.196.169>
>=C2=A0 =C2=A0* foo=3D> \conninfo
>
> You are connected to database "foo" as user "dbcorexyz&= quot; on host
> "live-paiyroll-= db-c702180bbf.ci22uuz4wz33.eu-west-2.rds.amazonaws.com
> <http://live-paiy= roll-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 EC= 2
> host, and then connect. Thus the details in this case are:
>
>=C2=A0 =C2=A0*
>
>=C2=A0 =C2=A0 =C2=A0bastion_host.ssh_host is '18.168.196.169', = bastion_host.ssh_usernme isawsuser
>
>=C2=A0 =C2=A0* <psycopg_binary.pq.PGconn [INTRANS] (host=3Dlocalhost= user=3Ddbcorexyz
>=C2=A0 =C2=A0 =C2=A0database=3Dfoo) at 0x7f6bfd554a90>
>
> I *am* dealing with multiple db connections (am working on some
> replication tooling) but AFAICS, both connections are to the same plac= e.
>

Are you sure?

=C2=A0From psql connection:

You are connected to database "foo" as user "dbcorexyz"= on host
"live-paiyroll-db-c7= 02180bbf.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=3Dlocalhost ...)

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 na= vel...and the code. Many thanks for the quick and kind help.

=
Shaheed
=C2=A0



--
Adrian Klaver
adrian.klave= r@aklaver.com

--000000000000794876062cc7ff56--