Issue regarding PostgreSQL

[NSi1974]

Hi all !
First of all, I really love Prowler, and the work you've done on this tool so far is amazing.
I've seen the possibility to connect and export my Prowler results to a PostgreSQL DB, which is a very good idea to allow me creating specific dashboard on Grafana.
I've install Postgres, and my setup seems ok :
psql (13.7)
Type "help" for help.

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | prowler | table | postgres
(1 row)

postgres=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
NOTICE: extension "uuid-ossp" already exists, skipping
CREATE EXTENSION
postgres=# \d+ prowler
Table "public.prowler"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
----------------------------+------+-----------+----------+---------+----------+--------------+-------------
id | uuid | | | | plain | |
audit_id | uuid | | | | plain | |
profile | text | | | | extended | |
account_number | text | | | | extended | |
region | text | | | | extended | |
check_id | text | | | | extended | |
result | text | | | | extended | |
item_scored | text | | | | extended | |
item_level | text | | | | extended | |
check_title | text | | | | extended | |
result_extended | text | | | | extended | |
check_asff_compliance_type | text | | | | extended | |
severity | text | | | | extended | |
service_name | text | | | | extended | |
check_asff_resource_type | text | | | | extended | |
check_asff_type | text | | | | extended | |
risk | text | | | | extended | |
remediation | text | | | | extended | |
documentation | text | | | | extended | |
check_caf_epic | text | | | | extended | |
resource_id | text | | | | extended | |
account_details_email | text | | | | extended | |
account_details_name | text | | | | extended | |
account_details_arn | text | | | | extended | |
account_details_org | text | | | | extended | |
account_details_tags | text | | | | extended | |
prowler_start_time | text | | | | extended | |
Access method: heap

I've created my .pgpass file also with those values :
localhost:5432:postgres:postgres:REDACTED:prowler

The problem is that each time I try to launch Prowler, I've got an error message :
[root@ip-172-31-26-197 prowler]# ./prowler -f eu-west-1 -d postgresql -c check310
_
_ __ _ __ _____ | | ___ _ __
| ' | '/ _ \ \ /\ / / |/ _ \ '|
| |) | | | () \ V V /| | / |
| ./|| ___/ _/_/ ||___||v2.12.0-27October2022
|| the handy cloud security tool

Date: Wed Nov 16 06:53:09 UTC 2022

Color code for results:

• INFO (Information)
• PASS (Recommended value)
• WARNING (Ignored by allowlist)
• FAIL (Fix required)

This report is being generated using credentials below:

AWS-CLI Profile: [default] AWS API Region: [eu-west-1] AWS Filter Region: [eu-west-1]
AWS Account: [392833984233] UserId: [AIDAVW5V243UQMX3ORB4E]
Caller Identity ARN: [arn:aws:iam::392833984233:user/deployment]

3.10 [check310] Ensure a log metric filter and alarm exist for security group changes - ec2 [Medium]
FAIL! eu-west-1: No CloudWatch group found for CloudTrail events
ERROR: invalid input syntax for type uuid: ""
LINE 1: INSERT INTO prowler VALUES (uuid_generate_v4(),'','default',...
^
If i set a value with the -u option, i've also got an error :
[root@ip-172-31-26-197 prowler]# ./prowler -f eu-west-1 -u test -d postgresql -c check310
_
_ __ _ __ _____ | | ___ _ __
| ' | '/ _ \ \ /\ / / |/ _ \ '|
| |) | | | () \ V V /| | / |
| ./|| ___/ _/_/ ||___||v2.12.0-27October2022
|| the handy cloud security tool

Date: Wed Nov 16 06:36:14 UTC 2022

Color code for results:

• INFO (Information)
• PASS (Recommended value)
• WARNING (Ignored by allowlist)
• FAIL (Fix required)

This report is being generated using credentials below:

AWS-CLI Profile: [default] AWS API Region: [eu-west-1] AWS Filter Region: [eu-west-1]
AWS Account: [392833984233] UserId: [AIDAVW5V243UQMX3ORB4E]
Caller Identity ARN: [arn:aws:iam::392833984233:user/deployment]

3.10 [check310] Ensure a log metric filter and alarm exist for security group changes - ec2 [Medium]
FAIL! eu-west-1: No CloudWatch group found for CloudTrail events
ERROR: invalid input syntax for type uuid: "test"
LINE 1: INSERT INTO prowler VALUES (uuid_generate_v4(),'test','defaul...

I don't do what I'm doing wrong, so if anyone has some idea to help, that would be greatly appreciated.

Regards,
Nicolas

[n4ch04]

Hi @NSi1974, thanks for using prowler !!

The first two fields of the INSERT statement are set as uuid data types. The first one is the finding_id which is generated on the fly with the uuid-ossp postrgesql extension. To insert in the table you also need to set the second field, which is the audit id that is passed with the flag -u. So you will need to generate a uuid an pass it to the prowler command with -u to be able to insert the row.

I'm reviewing the documentation and its not mentioned that that flag is needed, going to include it right now.

[n4ch04]

Included here: #1507

[NSi1974]

Thank you for your quick reply Nacho. It's all good now ! BR Nicolas Le lun. 21 nov. 2022 à 09:22, Nacho Rivera ***@***.***> a écrit :

…

Hi @NSi1974 <https://github.com/NSi1974>, thanks for using prowler !! The first two fields of the INSERT statement are set as uuid data types. The first one is the finding_id which is generated on the fly with the uuid-ossp postrgesql extension. To insert in the table you also need to set the second field, which is the audit id that is passed with the flag -u. So you will need to generate a uuid an pass it to the prowler command with -u to be able to insert the row. I'm reviewing the documentation and its not mentioned that that flag is needed, going to include it right now. — Reply to this email directly, view it on GitHub <#1485 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACNSGEK37QB7BUIA4MAIUDDWJMWOBANCNFSM6AAAAAASCDVIHQ> . You are receiving this because you were mentioned.Message ID: ***@***.***>