Escape dbUser and schemaName while setting role and granting privileges

[1t5j0y]

dbUser and schemaName needs to be escaped while setting role and granting privileges in the ETL app. Currently, ETL fails for dbUsers containing hyphens etc.

Set Role Exception logs

2024-01-10 10:25:03,690 INFO  Avni ETL_Worker-1 [etl.service.EtlService] Running ETL for Schema: conn-check, DB User: conn-check, Schema User: conn-check
2024-01-10 10:25:03,763 ERROR Avni ETL_Worker-1 [etl.service.EtlService] Error in job run
org.quartz.JobExecutionException: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [set role conn-check;]] [See nested exception: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [set role conn-check;]]]
	at org.quartz.core.JobRunShell.run(JobRunShell.java:218)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [set role conn-check;]]
	at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
	... 1 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [set role conn-check;]
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
	at org.avniproject.etl.repository.JdbcContextWrapper.wrap(JdbcContextWrapper.java:30)
	at org.avniproject.etl.repository.JdbcContextWrapper.wrapInOrgContext(JdbcContextWrapper.java:21)
	at org.avniproject.etl.repository.JdbcContextWrapper.runInOrgContext(JdbcContextWrapper.java:10)
	at org.avniproject.etl.repository.EntitySyncStatusRepository.getSyncStatus(EntitySyncStatusRepository.java:38)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702)
	at org.avniproject.etl.repository.EntitySyncStatusRepository$$SpringCGLIB$$0.getSyncStatus(<generated>)
	at org.avniproject.etl.service.OrganisationFactory.create(OrganisationFactory.java:24)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:50)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:35)
	at org.avniproject.etl.scheduler.EtlJob.execute(EtlJob.java:27)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	... 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "-"
  Position: 14

Grant privileges exception

2024-01-10 10:44:37,547 ERROR Avni ETL_Worker-1 [etl.service.EtlService] Error in job run
org.quartz.JobExecutionException: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table conn-check.person_zonal_program_exit ("id" integer, "uuid" text, "is_voided" boolean, "created_by_id" integer, "last_modified_by_id" integer, "created_date_time" timestamp with time zone, "last_modified_date_time" timestamp with time zone, "organisation_id" integer, "individual_id" integer, "address_id" integer, "enrolment_date_time" timestamp with time zone, "program_exit_date_time" timestamp with time zone, "enrolment_location" point, "exit_location" point, "legacy_id" text, "latest_approval_status" text );
grant all privileges on all tables in schema conn-check to conn_check;
grant all privileges on all sequences in schema conn-check to conn_check;
]] [See nested exception: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table conn-check.person_zonal_program_exit ("id" integer, "uuid" text, "is_voided" boolean, "created_by_id" integer, "last_modified_by_id" integer, "created_date_time" timestamp with time zone, "last_modified_date_time" timestamp with time zone, "organisation_id" integer, "individual_id" integer, "address_id" integer, "enrolment_date_time" timestamp with time zone, "program_exit_date_time" timestamp with time zone, "enrolment_location" point, "exit_location" point, "legacy_id" text, "latest_approval_status" text );
grant all privileges on all tables in schema conn-check to conn_check;
grant all privileges on all sequences in schema conn-check to conn_check;
]]]
	at org.quartz.core.JobRunShell.run(JobRunShell.java:218)
	at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.quartz.SchedulerException: Job threw an unhandled exception. [See nested exception: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table conn-check.person_zonal_program_exit ("id" integer, "uuid" text, "is_voided" boolean, "created_by_id" integer, "last_modified_by_id" integer, "created_date_time" timestamp with time zone, "last_modified_date_time" timestamp with time zone, "organisation_id" integer, "individual_id" integer, "address_id" integer, "enrolment_date_time" timestamp with time zone, "program_exit_date_time" timestamp with time zone, "enrolment_location" point, "exit_location" point, "legacy_id" text, "latest_approval_status" text );
grant all privileges on all tables in schema conn-check to conn_check;
grant all privileges on all sequences in schema conn-check to conn_check;
]]
	at org.quartz.core.JobRunShell.run(JobRunShell.java:213)
	... 1 more
Caused by: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [create table conn-check.person_zonal_program_exit ("id" integer, "uuid" text, "is_voided" boolean, "created_by_id" integer, "last_modified_by_id" integer, "created_date_time" timestamp with time zone, "last_modified_date_time" timestamp with time zone, "organisation_id" integer, "individual_id" integer, "address_id" integer, "enrolment_date_time" timestamp with time zone, "program_exit_date_time" timestamp with time zone, "enrolment_location" point, "exit_location" point, "legacy_id" text, "latest_approval_status" text );
grant all privileges on all tables in schema conn-check to conn_check;
grant all privileges on all sequences in schema conn-check to conn_check;
]
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1538)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:393)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:431)
	at org.avniproject.etl.repository.SchemaMetadataRepository.lambda$applyChanges$24(SchemaMetadataRepository.java:298)
	at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	at org.avniproject.etl.repository.SchemaMetadataRepository.applyChanges(SchemaMetadataRepository.java:298)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702)
	at org.avniproject.etl.repository.SchemaMetadataRepository$$SpringCGLIB$$0.applyChanges(<generated>)
	at org.avniproject.etl.service.SchemaMigrationService.migrate(SchemaMigrationService.java:39)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:343)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:391)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:750)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:702)
	at org.avniproject.etl.service.SchemaMigrationService$$SpringCGLIB$$0.migrate(<generated>)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:52)
	at org.avniproject.etl.service.EtlService.runFor(EtlService.java:35)
	at org.avniproject.etl.scheduler.EtlJob.execute(EtlJob.java:27)
	at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
	... 1 more
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "-"
  Position: 18

[1t5j0y]

Found an occurrence in avni-server as well and fixed it as part of this card.

[vinayvenu]

Don't we need this in

• AddColumn
• AddIndex
• RenameColumn
• RenameIndex
• RenameTable
• All the insert sql statements?

[1t5j0y]

Fixed for the above. Insert statements already wrap schema name in quotes.

[vinayvenu]

etl-test organisation