Re: Issue with a query while running on a remote host

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(dot)Hilbert(at)gmx(dot)net>
wrote:

> 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(dot)postgres(at)gmail(dot)com>
> > To: pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)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(at)*") 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(at)123
> >
> |c22d1779437117852ffcdeb7e9c689f41c502a1dae2687b8845a33bb892f3b66||9255561872|
> > skpanwar2311(at)gmail(dot)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
>
>
>