Troubleshooting the user permissions issue in the SQL Query Macro using the SQL for Confluence App
- Akhil Kumar (Deactivated)
- Amith Mathur
- Rama Goberu
Issue Description
Sometimes, when retrieving data from a specific table in the database, you may encounter the error message "An error occurred due to insufficient user permissions." This issue arises specifically when fetching data from that particular table; however, data retrieval and rendering work seamlessly for other database tables.
Steps to troubleshoot
Since there is an issue with the specific table, you can refer to the following steps to troubleshoot further:
You can refer to the logs for additional information regarding the issue. The following details may be available in the logs.
2024-01-24 16:11:14,754 ERROR [http-nio2-8081-exec-10 url: /confluence/display/~Strucks/TF+Datenbankzeugs, /confluence/pages/viewpage.action; user: xxxxx] [swift.confluence.sql.SqlDataHelper] proces
s Error in executing SQL statements : SELECT COUNT(\"EVENT_PK\") FROM \"AO_E4409B_EVENT\";, Exception : org.postgresql.util.PSQLException: ERROR: permission denied for table AO_E4409B_EVENT
-- url: /confluence/display/~Strucks/TF+Datenbankzeugs | userName: xxxxx | page: 990548155 | action: viewpage | traceId: 030d4b79988d444e
2024-01-24 16:11:14,756 WARN [http-nio2-8081-exec-10 url: /confluence/display/~Strucks/TF+Datenbankzeugs, /confluence/pages/viewpage.action; user: xxxxx] [xhtml.view.macro.ViewMacroMarshaller] handleMacroExecutionException Exception executing macro: sql-query, with message: An error occurred due to insufficient user permissions. Contact your administrator for more information.
The log error indicates an issue with accessing the table "AO_E4409B_EVENT". Specifically, it seems to be a permission denied error for the table, as indicated by the exception "org.postgresql.util.PSQLException: ERROR: permission denied for table AO_E4409B_EVENT". This suggests that the application cannot access this table, and PostgreSQL returns an access denied error.
You can verify whether this table is a default or a custom one. Since data can be fetched from the other table, there is a high chance that it is not a default table but a custom one.
You can now review the profile to confirm through which user app is connecting to the database, and you can also check for the owner of the table.
The issue arises when the user used in the profile is not the table's owner or does not have the necessary access permissions.
Database administration queries differ significantly across various database systems, so they may need changes.