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 1tcWlG-000kJv-LP for pgsql-general@arkaria.postgresql.org; Mon, 27 Jan 2025 21:35:15 +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 1tcWlF-00EvMn-1n for pgsql-general@arkaria.postgresql.org; Mon, 27 Jan 2025 21:35:13 +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 1tcWlE-00EvMY-Io for pgsql-general@lists.postgresql.org; Mon, 27 Jan 2025 21:35:12 +0000 Received: from mail-wm1-x32c.google.com ([2a00:1450:4864:20::32c]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tcWlC-001pVf-0W for pgsql-general@lists.postgresql.org; Mon, 27 Jan 2025 21:35:11 +0000 Received: by mail-wm1-x32c.google.com with SMTP id 5b1f17b1804b1-4364a37a1d7so50944955e9.3 for ; Mon, 27 Jan 2025 13:35:10 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1738013709; x=1738618509; 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=cnbZPgPUoNVsj+TdPupj2uJ/UDXaezetSG1O7RBSoj4=; b=PsViZCKCEC3dHrzwDfW3FV6KwD8jVRwSLCkUil2qWb92MO+VObEzf8DAIaz2G6wCiU SRTEFghT8bsCBeFl90E5ZTmQnxeqpy10w+YdIj/8GOjf0oGb1SLQXCtLQqURH7ASUZVR c44GXKQHBX5k2ONL6sBmWvB4EBf2qm0LEx/66em6tVKVr0q/PROhfxWgmRWwgYroolE5 jNwhlqtBdMEPBGebevPVVWbCf4SkG5u9UUoyR7EUPbJ2cFGbGViyc8M9wDbiDXEzIsUE 7xoiYsDdRvhi0cmXpzOEmRlAd8gZhiUTBtkPHo+3OuUEs2ePdYvvg6klUlt/oxFJ+I5d 1fJA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1738013709; x=1738618509; 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=cnbZPgPUoNVsj+TdPupj2uJ/UDXaezetSG1O7RBSoj4=; b=GdsznaQWwdRM3tikRhF5FLfBoutpPG1yww3H9/Rt5Fpbg7ciG0vZcFZaSVkNy2HcEG Fu+Re77LlDF6atlOi59KvNLmuX6ZxCrn2Z34pv7CanTUxET91jTmUcy8vz4VbfAXLMFP k7zgwHoZaswFgCkI/YjXXJpXYBPnYEKiV/ntjiYlLUTI8wfhd7+3bSG7n9sfb1DVDXTn rEdhSSKUHnRyJc2YQcvX3Jl1M8f4me6TLSYsW5VPturOpAkaQi45CVgS039WClmaN+Wd JoJLRXuGicuC1LjaDaXjtnf00Kt8XdV0aPsEdnLB443f4/bA6RjFt5b7UPhANOLumIw4 pdOw== X-Gm-Message-State: AOJu0YzeHd7OLVFehLev4pNAgSfXVJz3ayD0z7hyc9WtHExyz0ggygqA pIfZNlT0Gs8S8Yscb8Dy4M1piGwSKpy7FmsKlIlrE27tATkYlxAPCemVgkw9GFPMefYhbCI+4RV a4OXpk8lQTZOdoOYLzPEMsT9KK08= X-Gm-Gg: ASbGnctT5jNzWnuX7qcxJ6Ow4dSiqM5SxV6hndoeLJugWwhPMK/KnRxXet3izsn00r/ 6+nkYCzV9H8pMUtEu6HMIFhu6HszgQkxiv0hKFUv9tQwVc5I8TnP/Tj6NMdKlcA== X-Google-Smtp-Source: AGHT+IHjTLgr+Vu71KVg/3ZCy/2SwIfmX6z8VGel12qgomKB59OSMQKpngDMypzeOwUis5PCyFY+g8kaxMCenk8NlEg= X-Received: by 2002:a5d:64eb:0:b0:385:df4e:3645 with SMTP id ffacd0b85a97d-38bf59ecb69mr42419063f8f.50.1738013708705; Mon, 27 Jan 2025 13:35:08 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Shaheed Haque Date: Mon, 27 Jan 2025 21:34:57 +0000 X-Gm-Features: AWEUYZlEgRm6FZtMxlFH5W3BUMS6y2LG2XbfPzlEjNtxe218jbNLFq9p9a22sOY 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="000000000000b910c1062cb6d930" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000b910c1062cb6d930 Content-Type: text/plain; charset="UTF-8" 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" (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 - 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 > adrian.klaver@aklaver.com > > --000000000000b910c1062cb6d930 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Adrian,

On Mo= n, 27 Jan 2025 at 20:51, Adrian Klaver <adrian.klaver@aklaver.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 t= he docs
> and not been able to understand why I can see the rows in pg_publicati= on
> 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 t= he
> 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=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=A0Access privileges
>=C2=A0 =C2=A0=C2=A0Schema =C2=A0=C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0N= ame =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0| Type =C2=A0| =C2=A0=C2=A0=C2=A0=C2=A0Ac= cess privileges =C2=A0=C2=A0=C2=A0=C2=A0|
> Column privileges | Policies
> ------------+----------------+-------+---------------------------+----= ---------------+----------
> pg_catalog | pg_publication | table | rdsadmin=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| =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|
>
>
> - When I am logged in as this user via psql, I=C2=A0 can see:

This user is rdsadmin or something else?

The username is "dbcorexyz". See more=C2=A0 below.
=C2=A0
>
> foo=3D> select * from pg_publication;
>=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 |
> pubupdate | pubdelete | pubtruncate | pubviaroot
> -------+-------------------+----------+--------------+-----------+----= -------+-----------+-------------+------------
> 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| 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
>
>
> - When I connect via psycog, I can read other tables, but pg_publicati= on
> aways seems to return no rows.

1) What is your connection string?
=C2=A0 =C2=A0 In particular what user are you connecting as?

When I use psql, I first have to SSH to an AWS EC2, a= nd then run psql. Thus, the details in this case are:
You are connected to database "foo" as user &q= uot;dbcorexyz" on host "live-paiyroll-db-c702180bbf.= ci22uuz4wz33.eu-west-2.rds.amazonaws.com" (address "172.31.4.= 93") at port "5432&q= uot;.
SSL connection (protocol: = TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)

When I connect via pscopg, I first set up an SSH tunn= el through the EC2 host, and then connect. Thus the details in this case ar= e:
  • bastion_host.ssh_host is '18.168.196.169', bastion=
_host.ssh_usernme is awsuser
  • <psycopg_binary.pq.PGconn [INTRANS] (host=3Dlocalhost = user=3Ddbcorexyz database=3Dfoo) at 0x7f6bfd554a90>
I *am*= dealing with multiple db connections (am working on some replication tooli= ng) but AFAICS, both connections are to the same place.

<= /div>
Thanks, Shaheed
=C2=A0

2) Are you sure you are connecting to same database?


>
> Any assistance would be appreciated.
>
> Thanks, Shaheed
>
>
>

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

--000000000000b910c1062cb6d930--