Service fails to start when database user does not have permission to create schema

[semik]

Describe the bug
CZERTAINLY-Scheduler fails to start when database user does not have permission to create schema.

To Reproduce

Steps to reproduce the behavior:

1. I've existing CZERTAINLY deployment running version 2.11.0. The database was populated with some data.
2. uninstalled CZERTAINLY: helm uninstall czertainly-tlm
3. removed permisions on database: REVOKE CREATE ON DATABASE czertainlydb FROM czertainlyuser;
4. installed it back: helm upgrade --namespace czertainly -i --reset-values --values=.../czertainly-values.local.yaml --set keycloakInternal.image.tag=develop-latest --wait --timeout 30m czertainly-tlm czertainly-2.11.0.tg
5. noticed that only POD scheduler-service-deployment--xxx fails to start (except of core, which has dependency on this)

Schema was existing:

postgres=# \c czertainlydb
You are now connected to database "czertainlydb" as user "postgres".
czertainlydb=# \dn+
  List of schemas
Name     |       Owner       |           Access privileges            |      Description       
-------------+-------------------+----------------------------------------+------------------------
 adcs        | czertainlyuser    |                                        | 
 auth        | czertainlyuser    |                                        | 
 core        | czertainlyuser    |                                        | 
 cryptosense | czertainlyuser    |                                        | 
 ejbca       | czertainlyuser    |                                        | 
 emailnp     | czertainlyuser    |                                        | 
 keycloak    | czertainlyuser    |                                        | 
 keystore    | czertainlyuser    |                                        | 
 network     | czertainlyuser    |                                        | 
 public      | pg_database_owner | pg_database_owner=UC/pg_database_owner+| standard public schema
 |                   | =U/pg_database_owner                   | 
 pyadcs      | czertainlyuser    |                                        | 
 scheduler   | czertainlyuser    |                                        | 
 softcp      | czertainlyuser    |                                        | 
(13 rows)

REVOKE CREATE ON DATABASE czertainlydb FROM czertainlyuser;

Logs of the failed container:

semik@semik:~$ kubectl logs scheduler-service-deployment-6c9b5bd656-6fbld 
/opt/czertainly/entry.sh: 4: source: not found
/opt/czertainly/entry.sh: 6: log: not found

 ,---.       ,--.              ,--.        ,--.
'   .-' ,---.|  ,---. ,---.  ,-|  |,--.,--.|  |,---. ,--.--.
`.  `-.| .--'|  .-.  | .-. :' .-. ||  ||  ||  | .-. :|  .--'
.-'    \ `--.|  | |  \   --.\ `-' |'  ''  '|  \   --.|  |
`-----' `---'`--' `--'`----' `---'  `----' `--'`----'`--'

CZERTAINLY-Scheduler 1.0.0
Powered by Spring Boot 3.0.9

2024-02-20T08:17:37.188Z  INFO 7 --- [           main] com.czertainly.scheduler.Application     : Starting Application v1.0.0 using Java 17.0.7 with PID 7 (/opt/czertainly/app.jar started by czertainly in /opt/czertainly)
2024-02-20T08:17:37.194Z  INFO 7 --- [           main] com.czertainly.scheduler.Application     : No active profile set, falling back to 1 default profile: "default"
2024-02-20T08:17:38.167Z  INFO 7 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-02-20T08:17:38.185Z  INFO 7 --- [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 11 ms. Found 0 JPA repository interfaces.
2024-02-20T08:17:38.886Z  INFO 7 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port(s): 8080 (http)
2024-02-20T08:17:38.894Z  INFO 7 --- [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2024-02-20T08:17:38.895Z  INFO 7 --- [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.11]
2024-02-20T08:17:38.959Z  INFO 7 --- [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2024-02-20T08:17:38.961Z  INFO 7 --- [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1707 ms
2024-02-20T08:17:39.140Z  INFO 7 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-02-20T08:17:39.488Z  INFO 7 --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@1bfe3203
2024-02-20T08:17:39.489Z  INFO 7 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2024-02-20T08:17:39.493Z  INFO 7 --- [  "q1"""__:         main] c.c.scheduler.config.SchemaInit          : Going to create DB schema 'scheduler' if not exists.
2024-02-20T08:17:39.501Z  WARN 7 --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schemaInit.SchemaInitBean' defined in URL [jar:file:/opt/czertainly/app.jar!/BOOT-INF/classes!/com/czertainly/scheduler/config/SchemaInit$SchemaInitBean.class]: Failed to create schema 'scheduler'
2024-02-20T08:17:39.501Z  INFO 7 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2024-02-20T08:17:39.505Z  INFO 7 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
2024-02-20T08:17:39.508Z  INFO 7 --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
2024-02-20T08:17:39.533Z  INFO 7 --- [           main] .s.b.a.l.ConditionEvaluationReportLogger : 

Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
2024-02-20T08:17:39.553Z ERROR 7 --- [           main] o.s.boot.SpringApplication               : Application run failed

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'schemaInit.SchemaInitBean' defined in URL [jar:file:/opt/czertainly/app.jar!/BOOT-INF/classes!/com/czertainly/scheduler/config/SchemaInit$SchemaInitBean.class]: Failed to create schema 'scheduler'
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1770)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:598)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:520)
	at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:326)
	at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:324)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:313)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
	at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:313)
	at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:200)
	at org.springframework.context.support.AbstractApplicationContext.getBean(AbstractApplicationContext.java:1155)
	at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:932)
	at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:608)
	at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:146)
	at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:732)
	at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:434)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:310)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1304)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1293)
	at com.czertainly.scheduler.Application.main(Application.java:10)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
	at java.base/java.lang.reflect.Method.invoke(Unknown Source)
	at org.springframework.boot.loader.MainMethodRunner.run(MainMethodRunner.java:49)
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:95)
	at org.springframework.boot.loader.Launcher.launch(Launcher.java:58)
	at org.springframework.boot.loader.JarLauncher.main(JarLauncher.java:65)
Caused by: java.lang.RuntimeException: Failed to create schema 'scheduler'
	at com.czertainly.scheduler.config.SchemaInit$SchemaInitBean.afterPropertiesSet(SchemaInit.java:55)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1817)
	at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766)
	... 28 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for database czertainlydb
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
	at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
	at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
	at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
	at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
	at com.czertainly.scheduler.config.SchemaInit$SchemaInitBean.afterPropertiesSet(SchemaInit.java:53)
	... 30 common frames omitted

Expected behavior
I've got impression that CZERTAINLY should be able to start even when it has no permissions to create schema.

[3keyroman]

It should start without the permission to create database schema.
Looking into the code I think this is a bug:

https://github.com/3KeyCompany/CZERTAINLY-Scheduler/blob/develop/src/main/java/com/czertainly/scheduler/config/SchemaInit.java#L55

            try (Connection conn = dataSource.getConnection();
                 Statement statement = conn.createStatement()) {
                logger.info("Going to create DB schema '{}' if not exists.", schemaName);
                statement.execute("create schema if not exists " + schemaName);
            } catch (SQLException e) {
                throw new RuntimeException("Failed to create schema '" + schemaName + "'", e);
            }

There is an unhandled exception when the user does not have permission to create schema.

It should be fixed.

[lubomirw]

We should switch to Flyway migration tool also in Scheduler microservice