Thread: 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

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.