[
https://issues.apache.org/jira/browse/FINERACT-2468?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Farooq Ayoade updated FINERACT-2468:
------------------------------------
Description:
On PostgreSQL when using a *non-{{{}public{}}} schema* (e.g. tenant-specific
schema), creating a new datatable causes:
* *GET /datatables* to return *404* with "Datatable not found" for the new
datatable and break the entire list response
* Saving data to the new datatable to fail with "Datatable not found"
* Recovery only possible by deleting the problematic datatable
h2. Steps to reproduce
# Configure Fineract with PostgreSQL and a tenant that uses a
*non-{{{}public{}}} schema* (e.g. schema name = tenant identifier).
# Create a new datatable via POST /datatables
# Call *GET /datatables* (or GET /datatables/ with no filter)
h2. Root cause
In {*}{{fineract-core}}{*}, class {*}{{PostgreSQLQueryService}}{*}:
* *{{getTableColumns()}}* correctly uses *{{current_schema}}* (schema of the
current connection), so the table is found when it lives in the tenant schema.
* *{{getTableIndexes()}}* and *{{isTablePresent()}}* hardcode *{{'public'}}*
for the schema.
When listing datatables, the flow is:
# {{DatatableReadServiceImpl.retrieveDatatableNames()}} loads all permitted
datatables from {{{}x_registered_table{}}}.
# For each datatable it calls
{{{}GenericDataServiceImpl.fillResultsetColumnHeaders(datatableName){}}}.
# {{fillResultsetColumnHeaders()}} calls:
** {{getTableMetaData(tableName)}} → uses {{getTableColumns()}} → *finds the
table* (current_schema).
** {{getDatatableIndexData(tableName)}} → uses {{getTableIndexes()}} → looks
only in *{{public}}* → finds no rows → throws {{IllegalArgumentException("Table
... is not found")}} → mapped to {*}DatatableNotFoundException{*}.
So the table exists in the tenant schema, but the index query looks in
{{public}} and fails, breaking GET /datatables and any operation that needs
index metadata for that datatable.
*Stack trace (relevant part):*
{{org.apache.fineract.infrastructure.dataqueries.exception.DatatableNotFoundException:
Datatable not found.
at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.getDatatableIndexData(GenericDataServiceImpl.java:299)
at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.fillResultsetColumnHeaders(GenericDataServiceImpl.java:98)
at
org.apache.fineract.infrastructure.dataqueries.service.DatatableReadServiceImpl.retrieveDatatableNames(DatatableReadServiceImpl.java:93)
at
org.apache.fineract.infrastructure.dataqueries.api.DatatablesApiResource.getDatatables(DatatablesApiResource.java:92)}}
h2. Fix
*File:*
{{fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java}}
Use *{{current_schema}}* instead of *{{'public'}}* so behavior is consistent
with {{getTableColumns()}} and works with tenant/custom schemas.
h3. 1. {{getTableIndexes}}
*Before:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{String sql = "SELECT indexname FROM pg_indexes WHERE schemaname = 'public'
AND tablename = ?";}}
*After:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{// Use current_schema so tenant/schema-specific tables (e.g. datatables) are
found;
// hardcoding 'public' breaks GET /datatables and datatable operations in
multi-tenant setups.
String sql = "SELECT indexname FROM pg_indexes WHERE schemaname =
current_schema AND tablename = ?";}}
h3. 2. {{isTablePresent}}
*Before:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{Integer result = jdbcTemplate.queryForObject(
"SELECT COUNT(table_name) FROM information_schema.tables " + "WHERE
table_schema = 'public' AND table_name = ?",
Integer.class, tableName);}}
*After:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{// Use current_schema for consistency with getTableColumns/getTableIndexes
(tenant/schema-aware).
Integer result = jdbcTemplate.queryForObject(
"SELECT COUNT(table_name) FROM information_schema.tables WHERE table_schema =
current_schema AND table_name = ?",
Integer.class, tableName);}}
h2. Verification
* With PostgreSQL and a non-{{{}public{}}} schema (e.g. tenant schema):
** Create a new datatable.
** GET /datatables returns 200 and includes the new datatable.
** Saving data to the new datatable succeeds.
* With single-tenant {{public}} schema, behaviour unchanged
({{{}current_schema{}}} is {{{}public{}}}).
was:
On PostgreSQL when using a *non-{{{}public{}}} schema* (e.g. tenant-specific
schema), creating a new datatable causes:
* *GET /datatables* to return *404* with "Datatable not found" for the new
datatable and break the entire list response
* Saving data to the new datatable to fail with "Datatable not found"
* Recovery only possible by deleting the problematic datatable
h2. Steps to reproduce
# Configure Fineract with PostgreSQL and a tenant that uses a
*non-{{{}public{}}} schema* (e.g. schema name = tenant identifier).
# Create a new datatable via POST /datatables
# Call *GET /datatables* (or GET /datatables/ with no filter)
h2. Root cause
In {*}{{fineract-core}}{*}, class {*}{{PostgreSQLQueryService}}{*}:
* *{{getTableColumns()}}* correctly uses *{{current_schema}}* (schema of the
current connection), so the table is found when it lives in the tenant schema.
* *{{getTableIndexes()}}* and *{{isTablePresent()}}* hardcode *{{'public'}}*
for the schema.
When listing datatables, the flow is:
# {{DatatableReadServiceImpl.retrieveDatatableNames()}} loads all permitted
datatables from {{{}x_registered_table{}}}.
# For each datatable it calls
{{{}GenericDataServiceImpl.fillResultsetColumnHeaders(datatableName){}}}.
# {{fillResultsetColumnHeaders()}} calls:
** {{getTableMetaData(tableName)}} → uses {{getTableColumns()}} → *finds the
table* (current_schema).
** {{getDatatableIndexData(tableName)}} → uses {{getTableIndexes()}} → looks
only in *{{public}}* → finds no rows → throws {{IllegalArgumentException("Table
... is not found")}} → mapped to {*}DatatableNotFoundException{*}.
So the table exists in the tenant schema, but the index query looks in
{{public}} and fails, breaking GET /datatables and any operation that needs
index metadata for that datatable.
*Stack trace (relevant part):*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{org.apache.fineract.infrastructure.dataqueries.exception.DatatableNotFoundException:
Datatable not found.
at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.getDatatableIndexData(GenericDataServiceImpl.java:299)
at
org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.fillResultsetColumnHeaders(GenericDataServiceImpl.java:98)
at
org.apache.fineract.infrastructure.dataqueries.service.DatatableReadServiceImpl.retrieveDatatableNames(DatatableReadServiceImpl.java:93)
at
org.apache.fineract.infrastructure.dataqueries.api.DatatablesApiResource.getDatatables(DatatablesApiResource.java:92)}}
h2. Fix
*File:*
{{fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java}}
Use *{{current_schema}}* instead of *{{'public'}}* so behavior is consistent
with {{getTableColumns()}} and works with tenant/custom schemas.
h3. 1. {{getTableIndexes}}
*Before:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{String sql = "SELECT indexname FROM pg_indexes WHERE schemaname = 'public'
AND tablename = ?";}}
*After:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{// Use current_schema so tenant/schema-specific tables (e.g. datatables) are
found;
// hardcoding 'public' breaks GET /datatables and datatable operations in
multi-tenant setups.
String sql = "SELECT indexname FROM pg_indexes WHERE schemaname =
current_schema AND tablename = ?";}}
h3. 2. {{isTablePresent}}
*Before:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{Integer result = jdbcTemplate.queryForObject(
"SELECT COUNT(table_name) FROM information_schema.tables " + "WHERE
table_schema = 'public' AND table_name = ?",
Integer.class, tableName);}}
*After:*
!http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
{{// Use current_schema for consistency with getTableColumns/getTableIndexes
(tenant/schema-aware).
Integer result = jdbcTemplate.queryForObject(
"SELECT COUNT(table_name) FROM information_schema.tables WHERE
table_schema = current_schema AND table_name = ?",
Integer.class, tableName);}}
h2. Verification
* With PostgreSQL and a non-{{{}public{}}} schema (e.g. tenant schema):
** Create a new datatable.
** GET /datatables returns 200 and includes the new datatable.
** Saving data to the new datatable succeeds.
* With single-tenant {{public}} schema, behaviour unchanged
({{{}current_schema{}}} is {{{}public{}}}).
> Datatable not found / GET /datatables 404 on PostgreSQL with non-public schema
> ------------------------------------------------------------------------------
>
> Key: FINERACT-2468
> URL: https://issues.apache.org/jira/browse/FINERACT-2468
> Project: Apache Fineract
> Issue Type: Bug
> Components: Data Tables
> Reporter: Farooq Ayoade
> Priority: Major
>
> On PostgreSQL when using a *non-{{{}public{}}} schema* (e.g. tenant-specific
> schema), creating a new datatable causes:
> * *GET /datatables* to return *404* with "Datatable not found" for the new
> datatable and break the entire list response
> * Saving data to the new datatable to fail with "Datatable not found"
> * Recovery only possible by deleting the problematic datatable
> h2. Steps to reproduce
> # Configure Fineract with PostgreSQL and a tenant that uses a
> *non-{{{}public{}}} schema* (e.g. schema name = tenant identifier).
> # Create a new datatable via POST /datatables
> # Call *GET /datatables* (or GET /datatables/ with no filter)
> h2. Root cause
> In {*}{{fineract-core}}{*}, class {*}{{PostgreSQLQueryService}}{*}:
> * *{{getTableColumns()}}* correctly uses *{{current_schema}}* (schema of the
> current connection), so the table is found when it lives in the tenant schema.
> * *{{getTableIndexes()}}* and *{{isTablePresent()}}* hardcode *{{'public'}}*
> for the schema.
> When listing datatables, the flow is:
> # {{DatatableReadServiceImpl.retrieveDatatableNames()}} loads all permitted
> datatables from {{{}x_registered_table{}}}.
> # For each datatable it calls
> {{{}GenericDataServiceImpl.fillResultsetColumnHeaders(datatableName){}}}.
> # {{fillResultsetColumnHeaders()}} calls:
> ** {{getTableMetaData(tableName)}} → uses {{getTableColumns()}} → *finds the
> table* (current_schema).
> ** {{getDatatableIndexData(tableName)}} → uses {{getTableIndexes()}} → looks
> only in *{{public}}* → finds no rows → throws
> {{IllegalArgumentException("Table ... is not found")}} → mapped to
> {*}DatatableNotFoundException{*}.
> So the table exists in the tenant schema, but the index query looks in
> {{public}} and fails, breaking GET /datatables and any operation that needs
> index metadata for that datatable.
> *Stack trace (relevant part):*
>
> {{org.apache.fineract.infrastructure.dataqueries.exception.DatatableNotFoundException:
> Datatable not found.
> at
> org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.getDatatableIndexData(GenericDataServiceImpl.java:299)
> at
> org.apache.fineract.infrastructure.dataqueries.service.GenericDataServiceImpl.fillResultsetColumnHeaders(GenericDataServiceImpl.java:98)
> at
> org.apache.fineract.infrastructure.dataqueries.service.DatatableReadServiceImpl.retrieveDatatableNames(DatatableReadServiceImpl.java:93)
> at
> org.apache.fineract.infrastructure.dataqueries.api.DatatablesApiResource.getDatatables(DatatablesApiResource.java:92)}}
> h2. Fix
> *File:*
> {{fineract-core/src/main/java/org/apache/fineract/infrastructure/core/service/database/PostgreSQLQueryService.java}}
> Use *{{current_schema}}* instead of *{{'public'}}* so behavior is consistent
> with {{getTableColumns()}} and works with tenant/custom schemas.
> h3. 1. {{getTableIndexes}}
> *Before:*
> !http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
> {{String sql = "SELECT indexname FROM pg_indexes WHERE schemaname = 'public'
> AND tablename = ?";}}
> *After:*
> !http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
> {{// Use current_schema so tenant/schema-specific tables (e.g. datatables)
> are found;
> // hardcoding 'public' breaks GET /datatables and datatable operations in
> multi-tenant setups.
> String sql = "SELECT indexname FROM pg_indexes WHERE schemaname =
> current_schema AND tablename = ?";}}
> h3. 2. {{isTablePresent}}
> *Before:*
> !http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
> {{Integer result = jdbcTemplate.queryForObject(
> "SELECT COUNT(table_name) FROM information_schema.tables " + "WHERE
> table_schema = 'public' AND table_name = ?",
> Integer.class, tableName);}}
> *After:*
> !http://localhost:63342/markdownPreview/2018793464/docs?_ijt=2fh99ghqni20rs3r3bq41b8r3p!
> {{// Use current_schema for consistency with getTableColumns/getTableIndexes
> (tenant/schema-aware).
> Integer result = jdbcTemplate.queryForObject(
> "SELECT COUNT(table_name) FROM information_schema.tables WHERE table_schema =
> current_schema AND table_name = ?",
> Integer.class, tableName);}}
> h2. Verification
> * With PostgreSQL and a non-{{{}public{}}} schema (e.g. tenant schema):
> ** Create a new datatable.
> ** GET /datatables returns 200 and includes the new datatable.
> ** Saving data to the new datatable succeeds.
> * With single-tenant {{public}} schema, behaviour unchanged
> ({{{}current_schema{}}} is {{{}public{}}}).
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
