public inbox for [email protected]help / color / mirror / Atom feed
Re: Content of pg_publication using a local connection versus network connection? 3+ messages / 2 participants [nested] [flat]
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-27 20:51 Adrian Klaver <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Adrian Klaver @ 2025-01-27 20:51 UTC (permalink / raw) To: Shaheed Haque <[email protected]>; pgsql-general list <[email protected]> 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? > > 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? 2) Are you sure you are connecting to same database? > > Any assistance would be appreciated. > > Thanks, Shaheed > > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-27 21:34 Shaheed Haque <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Shaheed Haque @ 2025-01-27 21:34 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[email protected]> Hi Adrian, On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <[email protected]> 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 [email protected] - 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 - <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 > [email protected] > > ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-27 21:54 Adrian Klaver <[email protected]> parent: Shaheed Haque <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: Adrian Klaver @ 2025-01-27 21:54 UTC (permalink / raw) To: Shaheed Haque <[email protected]>; +Cc: pgsql-general list <[email protected]> On 1/27/25 13:34, Shaheed Haque wrote: > Hi Adrian, > > On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <[email protected] > <mailto:[email protected]>> 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 [email protected] > <mailto:[email protected]> > * 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 [email protected] ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-01-27 21:54 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-27 20:51 Re: Content of pg_publication using a local connection versus network connection? Adrian Klaver <[email protected]> 2025-01-27 21:34 ` Shaheed Haque <[email protected]> 2025-01-27 21:54 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox