Hi Adrian,
On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <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
- 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)
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
adrian.klaver@xxxxxxxxxxx
