nikyraja
commented
11 months ago Thanks Marcus, this is comprehensive and incredibly useful.
My initial preference would be to either 'Build a 'SQL Explorer' tool inside E12' or 'Add a SQL Explorer tool as a 'bolt-on'. These would allow the E12 team to run bespoke queries - it is important that the project team have the ability to do this so we can carry out added value projects, data access requests etc. It would also be great to have our analysts more involved in the 'back-end' of the platform, and this seems like a fitting opportunity.
We can however, also specify some results that are standard & repeated, such as the KPIs, so these queries can be built in and report automatically generated with these results. This will be beneficial as it will minimise the work needed to be done manually by the team, however this will not work for all of our outputs/processes.
We can discuss this further in a meeting - Thanks!
pacharanero
eatyourpeas
RichardBurleyRCPCH
mbarton
Noting some thoughts following meeting today discussing backend DB access for E12 analysts in order that they can perform complex arbitrary queries on the database. Just starting this as an Issue in order to get views from the team. These options are not mutually exclusive, some may suit particular queries or reports better than others.
Options for access include:
1. Allow direct access to a read-only PostGIS role (user)
This option would require us to 'punch a hole' in our firewall to allow access from an external computer into the VPS instance which houses the Live server. We would connect to the Database Data Volume and allow an external tool like pgAdmin to connect to PostGIS on port 5432. This would almost certainly require opening inbound ports (security risk) or tunnelling the connection over a VPN (possibly less risk but adds user complexity) Advantages: Gives essentially complete DB access, enabling any query to be executed. No developer time needs to be spent on Disadvantages: Potentially adds security risk; Non-TRE[^1] access creates a larger data breach risk, and is against general direction of travel on NHS research data access, which should all be via TRE[^2].
2. Build Django queries into the E12 application itself
This is particularly suitable for repetitive and unchanging, regular queries, for example compliance reporting or other reporting obligations which are not going to change much over time. This lends itself to creating a Reporting page which is accessible only to E12 team members, and then allows these pre-made reports to be downloaded in a suitable format. Advantages: can potentially automate away a lot of boring work; Security is similar to existing arrangement in E12 app (2FA); E12 analysts can 'join the dev team' and their Django/Pandas queries can be dropped straight into our codebase; Data breach risk is less because only the fixed, agreed queries can be executed - not any arbitrary query. Disadvantages: requires E12 development work to add each one; queries cannot be bespoke or change rapidly.
3. Build a 'SQL Explorer' tool inside E12
https://pypi.org/project/django-sql-explorer/ is a django plugin which would add SQL execution functionality into the Django Admin interface, which we already have in E12, and which is subject to tight security controls already. Advantages: Built into the application we are already using; would not really require a massive amount of development to implement using a library like this; We can use existing E12 2FA security; Would also work for NPDA and other audit tools. Disadvantages: Would need to ensure ONLYT read-only access is granted; Requires an additional library dependency.
4. Add a SQL Explorer tool as a 'bolt-on'
https://github.com/ankane/blazer is an example of a separate tool which would run on the server in its own Docker container, with access to the same Data Volume as our database, and the credentials of a read-only user role. It would be served at a URL something like https://e12.rcpch.ac.uk/blazer, or something like that. Advantages: Relatively straightforward to implement; Fulfils the definition of a TRE; Less security risk than direct DB access. Disadvantages: Would likely have its own security arrangements (eg username/password) which would be different to E12 login. However this would only be required for a small number of highly trusted users.
5. REST API database access
We could make it possible to obtain certain data through the Django REST API, which potentially makes it available to other applications such as Excel (using REST API calls / Python). Security would likely be via issuing an API key to the consuming application. Advantages: Opens up potential to integrate with external tools; Obviates need to build UI elements. Disadvantages: Would require each model in the DB to be exposed as REST; Multiplicity of models means that potentially a number of separate, linked queries would be required in order to extract the data needed. Python in Excel is relatively new and not widely used.
All of the above is for discussion, happy to build and add to these Advantages/Disadvantages and develop a thorough understanding of the user/business need VS technical implementation difficulty VS security/data breach risk.
[^1]: Trusted Research Environment [^2]: Data access policy update: proposed draft https://www.gov.uk/government/consultations/data-access-policy-update-proposed-draft/data-access-policy-update-proposed-draft