Fetch data from SQL View

Support - Assistance technique
1

Hi all,

I have created a View in my PostgreSQL DHIS2 database. This is because I am accessing some protected tables in the query.
Then I have created a SQL View from DHIS2 to fetch records from the SQL View I created.
When I try to access the endpoint api/sqlViews/view_id/data to to fetch the data I get this error.

{
“httpStatus”: “Internal Server Error”,
“httpStatusCode”: 500,
“status”: “ERROR”,
“message”: “An unexpected error has occured. Please contact your system administrator”
}

2

Hi @Dharshitha
Certain tables in the DHIS2 database have been “protected” because they either contain sensitive security related information (password hashes, names, emails, etc) or have the potential to contain sensitive personally identifiable information (names, telephone numbers, diagnosis, etc). Thus while it is possible to access these tables with an SQL view, the developers have made it slightly more difficult to do, so that you can be totally sure you actually need to do it and understand all of the risks of bypassing the inbuilt security of DHIS2.

So, having said that, if you are determined to use an SQL view on protected tables, I would recommend that you “cloak” your view inside of a Postgresql function. You can create this function directly in the database, and then call the function like “SELECT * FROM my_function()” as an SQL view within DHIS2. This will effectively bypass the security checks which are built in DHIS2 to prevent you from accessing these protected tables. Obviously, you should be very careful who this view is shared with.

There are no great approaches, and I would again suggest extreme caution in accessing these protected tables. If you can use another approach, it it definitely probably a better idea.

Best regards,
Jason

3 Likes
3

Great insight. Thank you.

1 Like