Issue with a query while running on a remote host

Deep packet inspection naively scanning for potential
fragments of bash scripts being transferred ?

Karsten

Am Fri, Aug 27, 2021 at 12:32:09PM +0530 schrieb Ninad Shah:

Date: Fri, 27 Aug 2021 12:32:09 +0530
From: Ninad Shah <nshah.postgres@gmail.com>
To: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Issue with a query while running on a remote host

Hello All,

While working with a PostgreSQL database, I came across an issue where data
is not being fetched over the network.

Version : PostgreSQL 11.10
Operating system : RHEL 8.4

*Issue description:*

We tried to execute the below query on the database host using psql prompt,
it works without any issue.

select regexp_replace(state_cd, E'[\\n\\r]+', '', 'g' ) as state_cd,
off_cd, user_cd, regexp_replace(user_name, E'[\\n\\r]+', '', 'g' ) as
user_name, regexp_replace(desig_cd, E'[\\n\\r]+', '', 'g' ) as desig_cd,
regexp_replace(user_id, E'[\\n\\r]+', '', 'g' ) as user_id,
regexp_replace(user_pwd, E'[\\n\\r]+', '', 'g' ) as user_pwd,
regexp_replace(phone_off, E'[\\n\\r]+', '', 'g' ) as phone_off, mobile_no,
regexp_replace(email_id, E'[\\n\\r]+', '', 'g' ) as email_id,
regexp_replace(user_catg, E'[\\n\\r]+', '', 'g' ) as user_catg,
regexp_replace(status, E'[\\n\\r]+', '', 'g' ) as status, created_by,
created_dt, aadhaar, op_dt, regexp_replace(login_ipaddress, E'[\\n\\r]+',
'', 'g' ) as login_ipaddress, regexp_replace(forget_password, E'[\\n\\r]+',
'', 'g' ) as forget_password, regexp_replace(newuser_change_password,
E'[\\n\\r]+', '', 'g' ) as newuser_change_password from tm_user_info
where ( user_cd >= 1068540014 ) AND ( user_cd <= 2137079303 ) ;

While trying to execute the same query over the network using psql prompt,
the execution doesn't finish.

*My Analysis:*

By digging further, we came to see that a specific record was causing the
issue, and by further analysis, we saw that the records that contain a
specific string("*bash@*") in the column user_id are not being fetched over
the network.

To confirm that, we also changed some records manually by creating a test
table. And, we were able to reproduce the issue.

vow4_xdb=# select * from vahan4.test_tbl where user_cd =2106011301;
.
.

But, this issue doesn't occur if we try to fetch on the database host or
via PgAdmin4. In such cases, we get the record in a few milliseconds.

*Surprisingly, this table has only one record.*

There is no table/row-level lock found here.

*Table definition:-*
Table "test_tbl"
Column | Type | Collation |
Nullable | Default | Storage | Stats targe
t | Description
-------------------------+-----------------------------+-----------+----------+---------+----------+------------
--+-------------
state_cd | character varying(2) | | not
null | | extended |
|
off_cd | numeric(5,0) | | not
null | | main |
|
user_cd | numeric(10,0) | | not
null | | main |
|
user_name | character varying(99) | | not
null | | extended |
|
desig_cd | character varying(10) | | not
null | | extended |
|
user_id | character varying(20) | | not
null | | extended |
|
user_pwd | character varying(100) | | not
null | | extended |
|
phone_off | character varying(20) | |
| | extended |
|
mobile_no | numeric(10,0) | | not
null | | main |
|
email_id | character varying(50) | |
| | extended |
|
user_catg | character varying(1) | | not
null | | extended |
|
status | character varying(1) | | not
null | | extended |
|
created_by | numeric(10,0) | | not
null | | main |
|
created_dt | date | | not
null | | plain |
|
aadhaar | numeric(12,0) | |
| | main |
|
op_dt | timestamp without time zone | | not
null | now() | plain |
|
login_ipaddress | character varying(20) | |
| | extended |
|
forget_password | character varying(1) | |
| | extended |
|
newuser_change_password | character varying(1) | |
| | extended |
|
Indexes:
"tm_user_info_pkey" PRIMARY KEY, btree (user_cd)
"idx_tm_user_info_user_id" UNIQUE, btree (user_id)
Replica Identity: FULL

*Record with an issue:-*
state_cd|off_cd|user_cd|user_name|desig_cd|user_id|user_pwd|phone_off|mobile_no|email_id|user_catg|status|created_by|created_dt|aadhaar|op_dt|login_ipaddress|forget_password|newuser_change_password
HR|31|2106011301|SUBASH AUTO HOUSE|DS|subash@123
|c22d1779437117852ffcdeb7e9c689f41c502a1dae2687b8845a33bb892f3b66||9255561872|
skpanwar2311@gmail.com|B|D|2006078673|2021-01-04|0|2021-01-04
14:30:27.715728||N|F
(1 row)

Can anyone help me out here?

Regards,
Ninad Shah

--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Hi Karsten,

I apologize for the delayed response.

There is no script-related transfer happening here. It creates an issue
while using "bash@" inside a column.

Regards,
Ninad Shah

On Fri, 27 Aug 2021 at 12:35, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

On Tuesday, August 31, 2021, Ninad Shah <nshah.postgres@gmail.com> wrote:

Hi Karsten,

I apologize for the delayed response.

There is no script-related transfer happening here. It creates an issue
while using "bash@" inside a column.

That wasn’t what was meant. Ignore the “why” for the moment, the theory is
something in the network or OS sees that string of data and fires off a
rule that causes the data to be filtered. Period. The comment about “bash
script” was just saying that whatever the “something” is might be guessing
that the text sequence “bash@“ has something to do with bash scripts. It
was just a hint. But regardless of why the false positive exists the
theory is that there is one happening in the environment externally to any
PostgreSQL related software.

David J.

Hi David/Karsten,

Thank you for your response. This helped me.

This thread can be closed.

Regards,
Ninad Shah

On Tue, 31 Aug 2021 at 13:26, David G. Johnston <david.g.johnston@gmail.com>
wrote: