From f3dece48558d444a5d3b67c6dfb79997dbfe25bf Mon Sep 17 00:00:00 2001 From: Juliana Fajardini Date: Sun, 25 May 2025 17:23:00 -0300 Subject: [PATCH] pgsql: add tests for `query` keyword Related to Task #6259 --- tests/pgsql/pgsql-query-keyword-01/README.md | 11 +++ tests/pgsql/pgsql-query-keyword-01/input.pcap | Bin 0 -> 3142 bytes .../pgsql-query-keyword-01/suricata.yaml | 19 ++++ tests/pgsql/pgsql-query-keyword-01/test.rules | 2 + tests/pgsql/pgsql-query-keyword-01/test.yaml | 32 ++++++ tests/pgsql/pgsql-query-keyword-02/README.md | 11 +++ .../pgsql-query-keyword-02/suricata.yaml | 19 ++++ tests/pgsql/pgsql-query-keyword-02/test.rules | 2 + tests/pgsql/pgsql-query-keyword-02/test.yaml | 91 ++++++++++++++++++ 9 files changed, 187 insertions(+) create mode 100644 tests/pgsql/pgsql-query-keyword-01/README.md create mode 100644 tests/pgsql/pgsql-query-keyword-01/input.pcap create mode 100644 tests/pgsql/pgsql-query-keyword-01/suricata.yaml create mode 100644 tests/pgsql/pgsql-query-keyword-01/test.rules create mode 100644 tests/pgsql/pgsql-query-keyword-01/test.yaml create mode 100644 tests/pgsql/pgsql-query-keyword-02/README.md create mode 100644 tests/pgsql/pgsql-query-keyword-02/suricata.yaml create mode 100644 tests/pgsql/pgsql-query-keyword-02/test.rules create mode 100644 tests/pgsql/pgsql-query-keyword-02/test.yaml diff --git a/tests/pgsql/pgsql-query-keyword-01/README.md b/tests/pgsql/pgsql-query-keyword-01/README.md new file mode 100644 index 000000000..bf757dbc5 --- /dev/null +++ b/tests/pgsql/pgsql-query-keyword-01/README.md @@ -0,0 +1,11 @@ +# Test + +Test `pgsql` `query` rule keyword, with content checks for `SELECT` and `LISTEN`. + +## Pcap + +Shared by Jason Ish. + +## Ticket + +https://redmine.openinfosecfoundation.org/issues/6259 diff --git a/tests/pgsql/pgsql-query-keyword-01/input.pcap b/tests/pgsql/pgsql-query-keyword-01/input.pcap new file mode 100644 index 0000000000000000000000000000000000000000..f7545116401681bd56dc9855f1695f20982c9df6 GIT binary patch literal 3142 zc-no|drVVz6bJC%fG&WN^>j`8V!ZHKdx5ZMH8f zNJY{YL#ZpdG&oYnuV$1^TrbW=qbKg4*(MtyaS4f^B*bY_E7E#9@p&#jJ{jeft&4KR zjQA0m5R}(G;*2{f&K~>v~o{?3{dSQJ&JBr@Acv3Gz-*cd~jm*}kGZk1BfB zfqvoGN^X8(8hYetzb8>fQCqH>!Zj*hVTxJY{PfsT5fDwn#M(_LUo2njby*4~Z4SyQ zZHg2wfL;K-K3KRBk)sKmM6vf~5~&2Ok|maCwN#XtVixzHpO1+l6zh=8yOpFC=onr8 zh+_E#!1mKXlVZ!ihb)TkgV+UH7fU>D`1S&#_Fa}zrmjmrHV2J=I8@9Y zFh$79>WYbfpom#%@XBL>BZoZ+cq*GY4tOTBikAe7^twkNW=s7u`WLGXM&9A}NrFoV z+F{xR$?b(f^ZV!-tr^Y)+jvjpT>-x+xq@&S&!xLXyBG=xK{~F{H)O;8HjtJVH$~6D zAwtu{U@SD0={HshhuyqSPai#NC=9rqE<6jhLg+hHexW}H8$L#hkEQI0_ zOER&7W@U-HwE0wQVu~=hx39?J?`u%RD|lL@Z4wC^Yy4tKRttNa`HkEMbqX=;H-}@< zoW(lFL7bRLBk)B{k7Zr&(LPSQ-lyogQ10536YV-rt3h=Kjz7%Er;%ZMUhsJQ`j`Ac zk29ZOHQk*wwf?Wu{=y3(pQloKq_dO1aHP}b3ayo!Kop-319WoB9q$S{LNP<;7JDmek#36|KV zT~EcQm?93!7FjITqln{{Pq^(4zbEANZD0Hymq`*9;6_LljuQP*#~E;}9+4Lq9W3nW zJoKcpG2hb|FDqgULtm$GV`jF+V*C*MIt}6!T)=BUt6_;{wn-{pWQwpc?X$9I{u#vs z_`?+0_;Z|Eqcz<#o7Ofj14ZJ+tI4nj#H@O29FNN+fivl6~&v| zr}JimO085) any any (msg:"Query keyword check - SELECT"; pgsql.query; content:"SELECT"; sid: 1;) +alert pgsql any any -> any any (msg: "Query keyword check - listen"; pgsql.query; content:"listen"; fast_pattern; nocase; sid: 2;) diff --git a/tests/pgsql/pgsql-query-keyword-01/test.yaml b/tests/pgsql/pgsql-query-keyword-01/test.yaml new file mode 100644 index 000000000..c1e76908c --- /dev/null +++ b/tests/pgsql/pgsql-query-keyword-01/test.yaml @@ -0,0 +1,32 @@ +requires: + min-version: 8.0 +args: +- -k none + +checks: +- filter: + count: 1 + match: + event_type: alert + direction: to_server + alert.signature: Query keyword check - listen + alert.signature_id: 2 + app_proto: pgsql + pcap_cnt: 18 + pgsql.request.simple_query: LISTEN rules; + pgsql.response.command_completed: LISTEN +- filter: + count: 1 + match: + event_type: alert + direction: to_server + alert.signature: Query keyword check - SELECT + alert.signature_id: 1 + app_proto: pgsql + pcap_cnt: 24 + pgsql.request.simple_query: SELECT 1; + pgsql.response.command_completed: SELECT 1 + pgsql.response.data_rows: 1 + pgsql.response.data_size: 1 + pgsql.response.field_count: 1 + pgsql.tx_id: 5 diff --git a/tests/pgsql/pgsql-query-keyword-02/README.md b/tests/pgsql/pgsql-query-keyword-02/README.md new file mode 100644 index 000000000..fcb7b331f --- /dev/null +++ b/tests/pgsql/pgsql-query-keyword-02/README.md @@ -0,0 +1,11 @@ +# Test + +Test `pgsql` `query` rule keyword, with content checks for `SELECT` and `delete`. + +## Pcap + +Reused from test `pgsql-simple-query-rollback`. + +## Ticket + +https://redmine.openinfosecfoundation.org/issues/6259 diff --git a/tests/pgsql/pgsql-query-keyword-02/suricata.yaml b/tests/pgsql/pgsql-query-keyword-02/suricata.yaml new file mode 100644 index 000000000..8bbe08685 --- /dev/null +++ b/tests/pgsql/pgsql-query-keyword-02/suricata.yaml @@ -0,0 +1,19 @@ +%YAML 1.1 +--- + +outputs: + - eve-log: + enabled: yes + filetype: regular + filename: eve.json + types: + - alert + - pgsql: + passwords: false + +app-layer: + protocols: + pgsql: + enabled: yes + stream-depth: 0 + diff --git a/tests/pgsql/pgsql-query-keyword-02/test.rules b/tests/pgsql/pgsql-query-keyword-02/test.rules new file mode 100644 index 000000000..f753580d0 --- /dev/null +++ b/tests/pgsql/pgsql-query-keyword-02/test.rules @@ -0,0 +1,2 @@ +alert pgsql any any -> any any (msg:"Query keyword check - select"; pgsql.query; content:"select *"; nocase; sid: 1;) +alert pgsql any any -> any any (msg:"Query keyword check - delete"; pgsql.query; content:"delete"; nocase; sid: 2;) diff --git a/tests/pgsql/pgsql-query-keyword-02/test.yaml b/tests/pgsql/pgsql-query-keyword-02/test.yaml new file mode 100644 index 000000000..be743db43 --- /dev/null +++ b/tests/pgsql/pgsql-query-keyword-02/test.yaml @@ -0,0 +1,91 @@ +requires: + min-version: 8.0 +args: +- -k none + +pcap: ../pgsql-simple-query-rollback/input.pcap + +checks: +- filter: + count: 1 + match: + event_type: alert + app_proto: pgsql + direction: to_server + alert.signature: Query keyword check - delete + alert.signature_id: 2 + pcap_cnt: 21 + pgsql.request.simple_query: DELETE FROM new_table WHERE NAME='Remus'; + pgsql.response.command_completed: DELETE 1 + pgsql.tx_id: 6 +- filter: + count: 1 + match: + event_type: alert + direction: to_server + alert.signature: Query keyword check - delete + alert.signature_id: 2 + app_proto: pgsql + pcap_cnt: 24 + pgsql.request.simple_query: DELETE FROM new_table WHERE NAME='Londubat'; + pgsql.response.command_completed: DELETE 1 + pgsql.tx_id: 7 +- filter: + count: 1 + match: + event_type: alert + alert.signature: Query keyword check - delete + alert.signature_id: 2 + app_proto: pgsql + direction: to_server + pcap_cnt: 26 + pgsql.request.simple_query: DELETE FROM new_table WHERE NAME='Hermione'; + pgsql.response.command_completed: DELETE 1 + pgsql.tx_id: 8 +- filter: + count: 1 + match: + event_type: alert + direction: to_server + alert.action: allowed + alert.signature: Query keyword check - delete + alert.signature_id: 2 + app_proto: pgsql + pcap_cnt: 28 + pgsql.request.simple_query: DELETE FROM new_table WHERE NAME='Maugre'; + pgsql.response.command_completed: DELETE 1 + pgsql.tx_id: 9 +- filter: + count: 1 + match: + event_type: alert + direction: to_server + alert.signature: Query keyword check - select + alert.signature_id: 1 + app_proto: pgsql + pcap_cnt: 57 + pgsql.request.simple_query: SELECT * FROM new_table; + pgsql.response.code: 25P02 + pgsql.response.file: "d:\\pginstaller_13.auto\\postgres.windows-x64\\src\\backend\\tcop\\postgres.c" + pgsql.response.line: '1105' + pgsql.response.message: "current transaction is aborted, commands ignored until + end of transaction block" + pgsql.response.routine: exec_simple_query + pgsql.response.severity_localizable: ERROR + pgsql.response.severity_non_localizable: ERROR + pgsql.tx_id: 17 +- filter: + count: 1 + match: + event_type: alert + alert.signature: Query keyword check - select + alert.signature_id: 1 + app_proto: pgsql + direction: to_server + pcap_cnt: 84 + pgsql.request.simple_query: SELECT * FROM new_table; + pgsql.response.command_completed: SELECT 8 + pgsql.response.data_rows: 8 + pgsql.response.data_size: 236 + pgsql.response.field_count: 2 + pgsql.tx_id: 26 -- 2.47.2