public inbox for [email protected]help / color / mirror / Atom feed
Re: Content of pg_publication using a local connection versus network connection? 5+ messages / 2 participants [nested] [flat]
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-27 22:20 Shaheed Haque <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Shaheed Haque @ 2025-01-27 22:20 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[email protected]> On Mon, 27 Jan 2025 at 21:54, Adrian Klaver <[email protected]> wrote: > 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. > 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 > [email protected] > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-28 18:02 Shaheed Haque <[email protected]> parent: Shaheed Haque <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Shaheed Haque @ 2025-01-28 18:02 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[email protected]> Hi, Based on the nudge from Adrian, I think I am now trying to connect to the correct/same database through both the original login-to-EC2-host-then-use-psql-to-RDS and then setup-SSHTunnel-then-connect-via-psycopg-over-tunnel. The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that is almost certainly a bug in my code, but I am aware that the Postgres' HBA setup is capable of distinguishing local logins from remote logins, so I wanted to check if Postgres' login security can similarly distinguish between a (remote) psql login and a (remote) psycopg login? Thanks, Shaheed On Mon, 27 Jan 2025 at 22:20, Shaheed Haque <[email protected]> wrote: > > > On Mon, 27 Jan 2025 at 21:54, Adrian Klaver <[email protected]> > wrote: > >> 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. >> > > 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 >> [email protected] >> >> ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-28 18:06 Adrian Klaver <[email protected]> parent: Shaheed Haque <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Adrian Klaver @ 2025-01-28 18:06 UTC (permalink / raw) To: Shaheed Haque <[email protected]>; +Cc: pgsql-general list <[email protected]> On 1/28/25 10:02 AM, Shaheed Haque wrote: > Hi, > > Based on the nudge from Adrian, I think I am now trying to connect to > the correct/same database through both the original > login-to-EC2-host-then-use-psql-to-RDS and then > setup-SSHTunnel-then-connect-via-psycopg-over-tunnel. > > The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that > is almost certainly a bug in my code, but I am aware that the Postgres' > HBA setup is capable of distinguishing local logins from remote logins, > so I wanted to check if Postgres' login security can similarly > distinguish between a (remote) psql login and a (remote) psycopg login? They both use libpq so I doubt it. > > Thanks, Shaheed > > > > On -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-28 18:08 Shaheed Haque <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Shaheed Haque @ 2025-01-28 18:08 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: pgsql-general list <[email protected]> Thanks for the confirmation. On Tue, 28 Jan 2025 at 18:06, Adrian Klaver <[email protected]> wrote: > > > On 1/28/25 10:02 AM, Shaheed Haque wrote: > > Hi, > > > > Based on the nudge from Adrian, I think I am now trying to connect to > > the correct/same database through both the original > > login-to-EC2-host-then-use-psql-to-RDS and then > > setup-SSHTunnel-then-connect-via-psycopg-over-tunnel. > > > > The connect-via-psycopg-over-tunnel bit currently fails. Obviously, that > > is almost certainly a bug in my code, but I am aware that the Postgres' > > HBA setup is capable of distinguishing local logins from remote logins, > > so I wanted to check if Postgres' login security can similarly > > distinguish between a (remote) psql login and a (remote) psycopg login? > > They both use libpq so I doubt it. > > > > > Thanks, Shaheed > > > > > > > > On > > -- > Adrian Klaver > [email protected] > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Content of pg_publication using a local connection versus network connection? @ 2025-01-28 18:14 Adrian Klaver <[email protected]> parent: Shaheed Haque <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2025-01-28 18:14 UTC (permalink / raw) To: Shaheed Haque <[email protected]>; +Cc: pgsql-general list <[email protected]> On 1/28/25 10:08 AM, Shaheed Haque wrote: > > Thanks for the confirmation. Your login/connection security is going to be handled by: https://www.postgresql.org/docs/current/auth-pg-hba-conf.html and the auth methods it supports. > > On Tue, 28 Jan 2025 at 18:06, Adrian Klaver <[email protected] > <mailto:[email protected]>> wrote: > > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-01-28 18:14 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-01-27 22:20 Re: Content of pg_publication using a local connection versus network connection? Shaheed Haque <[email protected]> 2025-01-28 18:02 ` Shaheed Haque <[email protected]> 2025-01-28 18:06 ` Adrian Klaver <[email protected]> 2025-01-28 18:08 ` Shaheed Haque <[email protected]> 2025-01-28 18:14 ` 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