Is anyone postgresql expert here?

[Blason_R]

Hi Team,

I am encountering a problem with the PostgreSQL service failing to start on CP R81.10, which has resulted in the CPM being down. Upon conducting some debugging, I noticed that it appears the PostgreSQL superuser is missing (which I think).

This issue arose after I expanded the lv_current disk and rebooted the management server. I have already opened a case with TAC, but I anticipate that it may take some time to resolve, as it might require involvement from R&D.

In the meantime, I am hoping that someone from the community can assist me in rectifying this error or if someone have competency on postgres

Exception in thread "main" org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'setupPreparationBean' defined in class path resource [objectStoreConfiguration.xml]: Invocation of init method failed; nested exception is CpmGeneralException{base='com.checkpoint.management.is.exceptions.CpmGeneralException: Couldn't connect to database', errorCode='CP_ERR_DATABASE_CONNECTION', errorFamily='null', messageForUser='null', message='Couldn't connect to database'}
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1455)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)
        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:294)
        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:225)
        at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:291)
        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:193)
        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:585)
        at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:913)
        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:464)
        at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:139)
        at org.springframework.context.support.ClassPathXmlApplicationContext.<init>(ClassPathXmlApplicationContext.java:93)
        at com.checkpoint.infrastructure.spring.IgnoringDuplicateBeansClassPathXmlApplicationContext.<init>(IgnoringDuplicateBeansClassPathXmlApplicationContext.java:10)
        at com.checkpoint.management.cpm.Cpm.initSpringContext(Cpm.java:9)
        at com.checkpoint.management.cpm.Cpm.main(Cpm.java:168)
Caused by: CpmGeneralException{base='com.checkpoint.management.is.exceptions.CpmGeneralException: Couldn't connect to database', errorCode='CP_ERR_DATABASE_CONNECTION', errorFamily='null', messageForUser='null', message='Couldn't connect to database'}

Now when I tried restarting from scripts

bash -x ./postgres_restart.sh
++ pwd
+ ORIG_DIR=/opt/CPsuite-R81.10/fw1/scripts
+ cd /opt/CPsuite-R81.10/fw1
+ su -s /bin/bash -c '/opt/CPsuite-R81.10/fw1/scripts/postgres_commands.sh restart ' cp_postgres
Running pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
Running pg_ctl reload
server signaled
+ cd /opt/CPsuite-R81.10/fw1/scripts

I guess it has something realted to role postgres

025-05-07 13:42:58.772 IST [27601] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-05-07 13:42:58.816 IST [27602] LOG:  database system was shut down at 2025-05-07 13:42:58 IST
2025-05-07 13:42:58.819 IST [27601] LOG:  database system is ready to accept connections
2025-05-07 13:42:58.863 IST [27601] LOG:  received SIGHUP, reloading configuration files
2025-05-07 13:43:19.723 IST [28451] FATAL:  role "postgres" does not exist
2025-05-07 13:45:19.661 IST [32543] FATAL:  role "postgres" does not exist
2025-05-07 13:45:19.712 IST [32741] FATAL:  role "postgres" does not exist
2025-05-07 13:45:55.423 IST [686] FATAL:  role "admin" does not exist
2025-05-07 13:47:19.700 IST [2195] FATAL:  role "postgres" does not exist
2025-05-07 13:48:19.678 IST [3222] FATAL:  role "postgres" does not exist
2025-05-07 13:49:19.707 IST [6859] FATAL:  role "postgres" does not exist
2025-05-07 13:51:19.671 IST [8942] FATAL:  role "postgres" does not exist
2025-05-07 13:51:19.723 IST [9145] FATAL:  role "postgres" does not exist
2025-05-07 13:51:44.335 IST [27601] LOG:  received fast shutdown request
2025-05-07 13:51:44.337 IST [27601] LOG:  aborting any active transactions
2025-05-07 13:51:44.337 IST [27601] LOG:  background worker "logical replication launcher" (PID 27608) exited with exit code 1
2025-05-07 13:51:44.338 IST [27603] LOG:  shutting down
2025-05-07 13:51:44.359 IST [27601] LOG:  database system is shut down
2025-05-07 13:51:44.442 IST [9450] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-05-07 13:51:44.446 IST [9450] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-05-07 13:51:44.491 IST [9455] LOG:  database system was shut down at 2025-05-07 13:51:44 IST
2025-05-07 13:51:44.494 IST [9450] LOG:  database system is ready to accept connections
2025-05-07 13:51:44.537 IST [9450] LOG:  received SIGHUP, reloading configuration files

[PhoneBoy]

The message 'role "postgres" does not exist' suggests TAC should be involved here: https://support.checkpoint.com/results/sk/sk164656 

[Blason_R]

I have already reached out to those individuals; however, it is surprising that despite providing all the necessary details, the case was assigned to a Level 1 engineer. I repeatedly requested during the call that R&D or a senior engineer be involved, as I have conducted extensive troubleshooting. The response I received was quite unexpected. They instructed me to create a new virtual machine and restore it. I explained that this is an AWS virtual machine, and obtaining the same private IP address, which is utilized for SIC, may not be feasible. I believe that starting anew should not be considered a viable solution. This situation clearly indicates an issue with the database that can be resolved, correct?

[PhoneBoy]

IPs matter insofar as implied rules go, but SIC itself doesn't authenticate with IP addresses.
If the issue is, in fact, database corruption, a backup/restore of the same environment also exhibiting the same behavior would be expected.

[the_rock]

Im not an expert in it by any means, but I have some experience. Here is what I would try.

Based on those errors:

1) connect as super user and run:

\du

2) If role is missing, you can try recreate it:

CREATE ROLE postgres WITH SUPERUSER LOGIN PASSWORD 'your_password';

3) Check pg_hba.conf

line should allow postgres to connect, something along the lines local all postgres peer

4) See if any additional logs that could be helpful

I can also ask one of my colleagues, he is real genius when it comes to this, Im sure he can give way better insight into it.

Andy

[Blason_R]

I appreciate your assistance; however, I am not an expert in PostgreSQL and lack a comprehensive understanding of its functionality. While I am quite familiar with MariaDB/MySQL, PostgreSQL and its customization by checkpoint remain unfamiliar to me. Nevertheless, I will review the pg_hba configuration file to investigate further. Thank you once again for your help.

[the_rock]

Yea, of course, not a problem. Let me check with my colleague tomorrow morning and I will let you know what he says.

Andy