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

Hi Adrian,

On Mon, 27 Jan 2025 at 20:51, Adrian Klaver <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
- 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

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&gt;&quot; (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 Mon, 27 Jan 2025 at 21:54, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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&gt;&quot;
(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

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

Thanks for the confirmation.

On Tue, 28 Jan 2025 at 18:06, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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