Fix issues with postgres configuration

Issue-ID: POLICY-5086
Change-Id: Ib0cbdba681159e3c26a26c835e9db63ba96b1c96
Signed-off-by: adheli.tavares <adheli.tavares@est.tech>

3 files changed, 22 insertions, 21 deletions

diff --git a/packages/policy-xacmlpdp-tarball/src/main/resources/apps/guard/xacml-pg.properties b/packages/policy-xacmlpdp-tarball/src/main/resources/apps/guard/xacml-pg.properties
index af9f81f5..a3446c4c 100644
--- a/packages/policy-xacmlpdp-tarball/src/main/resources/apps/guard/xacml-pg.properties
+++ b/packages/policy-xacmlpdp-tarball/src/main/resources/apps/guard/xacml-pg.properties
@@ -49,6 +49,6 @@ xacml.pip.engines=count-recent-operations,get-operation-outcome
 #
 eclipselink.target-database=PostgreSQL
 jakarta.persistence.jdbc.driver=org.postgresql.Driver
-jakarta.persistence.jdbc.url=jdbc:postgresql://policy-pg-primary:5432/operationshistory
+jakarta.persistence.jdbc.url=jdbc:postgresql://postgres:5432/operationshistory
 jakarta.persistence.jdbc.user=policy_user
 jakarta.persistence.jdbc.password=policy_user
diff --git a/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh b/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh
index b5067c5b..167b2988 100644
--- a/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh
+++ b/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/bin/create-guard-table-pg.sh
@@ -1,7 +1,7 @@
 #!/usr/bin/env sh
 #
 # ============LICENSE_START=======================================================
-#  Copyright (C) 2022-2023 Nordix Foundation. All rights reserved.
+#  Copyright (C) 2022-2024 Nordix Foundation. All rights reserved.
 #  Modifications Copyright (C) 2022 AT&T Intellectual Property.
 # ================================================================================
 # Licensed under the Apache License, Version 2.0 (the "License");
@@ -22,8 +22,8 @@
 
 set -x
 
-SQL_FILE="${POLICY_HOME}/mysql/sql/createguardtable-pg.sql"
-SQL_ADDON_FILE="${POLICY_HOME}/mysql/sql/db-pg.sql"
+SQL_FILE="${POLICY_HOME}/postgres/sql/createguardtable-pg.sql"
+SQL_ADDON_FILE="${POLICY_HOME}/postgres/sql/db-pg.sql"
 
 # Remove escape backslashes if present and save output in temp file
 sed 's/\\//g' "${POLICY_HOME}"/apps/guard/xacml-pg.properties > /tmp/temp.xacml-pg.properties
@@ -37,7 +37,7 @@ fi
 # Extract Maria DB Credential properties from xacml.properties file
 DB_HOSTNAME=$(awk -F[/:] '$1 == "jakarta.persistence.jdbc.url=jdbc" { print $3 $5 }' /tmp/temp.xacml-pg.properties)
 DB_USERNAME=$(awk -F= '$1 == "jakarta.persistence.jdbc.user" { print $2 }' /tmp/temp.xacml-pg.properties)
-DB_PASSWORD=$(awk -F= '$1 == "jakarta.persistence.jdbc.password" { st = index($0,"="); print substr($0,st+1) }' /tmp/temp.properties)
+DB_PASSWORD=$(awk -F= '$1 == "jakarta.persistence.jdbc.password" { st = index($0,"="); print substr($0,st+1) }' /tmp/temp.xacml-pg.properties)
 
 # Remove temp file
 rm /tmp/temp.xacml-pg.properties
@@ -61,7 +61,7 @@ fi
 psql -U postgres -h ${DB_HOSTNAME} -f ${SQL_FILE}
 
 # Execute additional SQL configuration if provided
-if [ -f "${POLICY_HOME}/mysql/sql/db-pg.sql" ]; then
+if [ -f "${POLICY_HOME}/postgres/sql/db-pg.sql" ]; then
     echo "additional SQL to be loaded found"
     psql -U postgres -h ${DB_HOSTNAME} -f ${SQL_ADDON_FILE}
 fi
diff --git a/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/sql/createguardtable-pg.sql b/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/sql/createguardtable-pg.sql
index 501ad77e..62738ae9 100644
--- a/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/sql/createguardtable-pg.sql
+++ b/packages/policy-xacmlpdp-tarball/src/main/resources/postgres/sql/createguardtable-pg.sql
@@ -1,5 +1,5 @@
 -- ============LICENSE_START=======================================================
--- Copyright (C) 2022 Nordix Foundation. All rights reserved.
+-- Copyright (C) 2022, 2024 Nordix Foundation. All rights reserved.
 -- ================================================================================
 -- Licensed under the Apache License, Version 2.0 (the "License");
 -- you may not use this file except in compliance with the License.
@@ -16,22 +16,23 @@
 
 \c operationshistory;
 
-create table if not exists operationshistory (
-    id int(11) SERIAL PRIMARY KEY,
-    closedLoopName varchar(255) not null,
-    requestId varchar(50),
-    actor varchar(50) not null,
-    operation varchar(50) not null,
-    target varchar(50) not null,
-    starttime timestamp not null,
-    outcome varchar(50) not null,
-    message varchar(255),
-    subrequestId varchar(50),
-    endtime timestamp not null default current_timestamp
+CREATE TABLE IF NOT EXISTS operationshistory
+(
+    id             SERIAL PRIMARY KEY,
+    closedLoopName VARCHAR(255) NOT NULL,
+    requestId      VARCHAR(50),
+    actor          VARCHAR(50)  NOT NULL,
+    operation      VARCHAR(50)  NOT NULL,
+    target         VARCHAR(50)  NOT NULL,
+    starttime      TIMESTAMP    NOT NULL,
+    outcome        VARCHAR(50)  NOT NULL,
+    message        VARCHAR(255),
+    subrequestId   VARCHAR(50),
+    endtime        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
 
 create index if not exists operationshistory_clreqid_index on
-    operationshistory(requestId, closedLoopName);
+    operationshistory (requestId, closedLoopName);
 
 create index if not exists operationshistory_target_index on
-    operationshistory(target, operation, actor, endtime);
+    operationshistory (target, operation, actor, endtime);