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