IBM Security QRadar SOAR

 View Only

SOAR 48.1.47 - postgres 12.15 - vacuum throws error on monapp.incidents_artifacts_properties table: function digest(text, text) does not exist

  • 1.  SOAR 48.1.47 - postgres 12.15 - vacuum throws error on monapp.incidents_artifacts_properties table: function digest(text, text) does not exist

    Posted Thu May 25, 2023 11:19 AM

    Hello Community,

    thx for joining etc... Anyway - since support dit not help at all and I haven't find any related article I'm posting a new message.

    We are in the procedure of SOAR introduction (go live in less than a month), developing playbooks, integrations (enrichments, tickets....) and setting all necessary IT related stuff (like backup procedure with 3-2-1 principle). We have SOAR as on-premise installation (rhel 7.9) and postgresql (12.15). Installation was done with installation scripts/binaries downloaded from IBM. SOAR AppHost in on separated machine (for each TEST and PROD separately).

    I understand that there is recommended backup procedure which we will follow. Anyway there are recommendations to utilize vacuum procedure to tidy up database. 

    Issue:
    Based on postgresql articles the vacuum binary is recommended for certain scenarios (basically for the most implementations) to reclaim unsued blocks to safe storage space and optimize backup procedure (pgbackrest or any other dump).

    Command which I run before backup:
    sudo -u postgres /bin/vacuumdb --all --analyze

    Error:
    vacuumdb: vacuuming database "apps"
    vacuumdb: vacuuming database "co3"
    vacuumdb: error: vacuuming of table "monapp.incidents_artifacts_properties" in database "co3" failed: ERROR: function digest(text, text) does not exist

    LINE 2: SELECT encode(digest(string_to_encode, CAST ('sha256' AS...
    ^
    HINT: No function matches the given name and argument types. You might need to add explicit type casts.

    QUERY:
    SELECT encode(digest(string_to_encode, CAST ('sha256' AS text) ), 'hex')
    CONTEXT: SQL function "res_sha256" during inlining

    This runs properly on TEST environment. Issue occur only on PROD environment. The difference between environments is deployment - TEST environment was installed directly with 48.1.47 version but productive we were doing step by step upgrade from SOAR v44.2 to v48.1.47. 

    I searched postgresql support and of course all public sources for clues but:
    - postgresql-contrib package is installed on the system (yum list installed | grep postgresql shows installed package and from the same repository as main postgresql)
    - the crypto extension is present on the co3 database and is probably used (if needed) sudo -u postgres psql -d co3 shows:
    co3=# \df digest
                            List of functions
     Schema |  Name  | Result data type | Argument data types | Type 
    --------+--------+------------------+---------------------+------
     public | digest | bytea            | bytea, text         | func
     public | digest | bytea            | text, text          | func
    (2 rows)

    Any ideas, clues?



    ------------------------------
    Marian Trizuliak
    ------------------------------