Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
semik opened this issue Feb 20, 2024 · 2 comments
Open

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

semik opened this issue Feb 20, 2024 · 2 comments
Assignees
@lubomirw
Labels
bug Something isn't working

Comments

@semik
Copy link

semik commented Feb 20, 2024

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.
@semik semik added the bug Something isn't working label Feb 20, 2024
@3keyroman
Copy link
Contributor

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.

@3keyroman 3keyroman assigned lubomirw and unassigned 3keyroman and jakub-moravek Feb 20, 2024
@3keyroman 3keyroman moved this to Todo in CZERTAINLY Feb 20, 2024
@3keyroman 3keyroman moved this to Todo in CZERTAINLY Jul 26, 2024
@lubomirw
Copy link
Contributor

lubomirw commented Sep 2, 2024

We should switch to Flyway migration tool also in Scheduler microservice

@lubomirw lubomirw changed the title fails to start when database user does not have permision to create schema Service fails to start when database user does not have permission to create schema Sep 6, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@lubomirw lubomirw

Labels
bug Something isn't working
Projects
CZERTAINLY
Status: Todo
Milestone
No milestone
Development

No branches or pull requests
4 participants
@semik @3keyroman @lubomirw @jakub-moravek