Thread: Content of pg_publication using a local connection versus network connection?

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
adrian.klaver@aklaver.com

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

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
adrian.klaver@aklaver.com

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 <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com