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 1tcXTI-000ooG-SD for pgsql-general@arkaria.postgresql.org; Mon, 27 Jan 2025 22:20:45 +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 1tcXTH-00F8pm-En for pgsql-general@arkaria.postgresql.org; Mon, 27 Jan 2025 22:20:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tcXTG-00F8pc-P0 for pgsql-general@lists.postgresql.org; Mon, 27 Jan 2025 22:20:43 +0000 Received: from mail-wr1-x432.google.com ([2a00:1450:4864:20::432]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcXTE-001pnw-0j for pgsql-general@lists.postgresql.org; Mon, 27 Jan 2025 22:20:41 +0000 Received: by mail-wr1-x432.google.com with SMTP id ffacd0b85a97d-388cae9eb9fso2922115f8f.3 for ; Mon, 27 Jan 2025 14:20:40 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738016439; x=1738621239; 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=vBVvzJmdDzvrCgwDH1neoAOAPqGUlMtIP1g9+9hhZZc=; b=hRWnuBwK2bPgih20BihlMpdvS3Ygchfic1RL1hYwVJvewylw1SHxG+E+rvRpcqa+h9 iZa1oYhva5MZBlk+HneYMGIpoHMROw/tQDkEDRbieG9/Y3peT0Q07oL5IXSYswlF64VX 8NKKOR0JybkCzlwJt3b6ThusCOT8nS1i+n03OBqCuKsu+Yh5N9OM9mXQ7gLTIsHGLUx7 A8x75YfCLgnr9144fkSoRG1vqoOCpojvfN26IudkpGmMvJaM9/2ozFQ+zf2yXJnWKOvJ VraThxagdsXig/0ZeVlIKp4AaN24StbqZm/2DdcbPmcNb/f3uXyGVRdql4Ly7+8GHaCs /gBg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738016439; x=1738621239; 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=vBVvzJmdDzvrCgwDH1neoAOAPqGUlMtIP1g9+9hhZZc=; b=eSS1djM05cVUv0edO3aIZIOFqoS230AnMBHB+1ISkGYnEl8KQG44l34IAJeFp8xB/o 3aoywbCuAQFMcx6e1OaHNCV5rLissfPehJ/tQqVvWebg/3aXNWFxdEJTkWiojilGKI/A 5UyOp/zFgajfgE9xPnj3NWGPHsSLaVFd1pPXdU/TBi6EIlwqUeSLRfcbvc7P5f1cI8nx Yq1AnYTeBDHHRbdbO6XhXbKkMvzEf0ZvUTJWH5/6kDZn7H6HcxdMDt6nPqifwpszvBPa Z48Z29lNN//yTe7/WbulylYAAFOZlHbWaUp1PE5xxKvQeMAXKiwa7iXN6Nr/4bvl7nwq HMsw== X-Gm-Message-State: AOJu0Yw0R4i2gEj1iYdA3FmrN+Hl2vphzO8SmiV1sMXiqbX9lBXlAzt/ Emrd9IRjb4RwB91s22FdB98eeoQrAoorXwPK6bTMkIjYuUohBre7R6nCzJfnCo9C/e84HXNh6wu OiIjWKtXgW30Z/Sxpe6P1F67e6LQ= X-Gm-Gg: ASbGncvGJi0avfd5Y67a5yJdJ6aJS5Ga4uGceK4Vfwkg7BDYs0pqi6+QTCpZ3zorXpF l+hhM+BDVjS1sba/Pi6UZQPp4ohYn4p9X7hf6ItHTS4ZwfYf6pcG19wVEDtCLDA== X-Google-Smtp-Source: AGHT+IGbvwV+UWMbxCEtoyVA1Ke+SLDDlmBESUWLyrXjzvEbGJEwsRcIZAIS4Bra8aD+NkeKfe/ZbYS46e9NtsU3kiI= X-Received: by 2002:a05:6000:144f:b0:38a:615b:9ec0 with SMTP id ffacd0b85a97d-38bf57c0670mr41246617f8f.54.1738016438933; Mon, 27 Jan 2025 14:20:38 -0800 (PST) MIME-Version: 1.0 References: <19043fe9-076b-4d94-a81c-f4292551b8be@aklaver.com> In-Reply-To: <19043fe9-076b-4d94-a81c-f4292551b8be@aklaver.com> From: Shaheed Haque Date: Mon, 27 Jan 2025 22:20:28 +0000 X-Gm-Features: AWEUYZkJj2Ype9iq9ieOZ-eXjF2N1gHsrrJBNU775hvNWx3tXubOcV8iGJwLx8g 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="00000000000074e084062cb77cb7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000074e084062cb77cb7 Content-Type: text/plain; charset="UTF-8" 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 > > --00000000000074e084062cb77cb7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Mon, 27 Jan = 2025 at 21:54, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 1/27/25 13:34, Shaheed Haque wrote:
> Hi Adrian,
>
> On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <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

--00000000000074e084062cb77cb7--