Django test database issue

[matthieumarrast]

Problem

Django has a mechanism for creating a test database when running unitary tests, cf. https://docs.djangoproject.com/en/4.2/topics/testing/overview/#the-test-database

Problems:

• 1. This test database, named test_<your database name>, is created with your default database USER/PASSWORD credentials, and your USER does not have the CREATEDB authorization
• 2. If you use postgis, the extension will have to be activated but your user will not have the right to CREATE EXTENSION postgis
• 3. Because of the PostgreSQL Host-Based Authentication Configuration (file ~/var/pgsql/pg_hba.conf) which allow only the "sameuser" (i.e a user name equal ta dabasename) to connect to database (see below), our user cannot connect to the test database.

local all all trust
host sameuser all 0.0.0/0
host sameuser all ::1/128

Workaround solution

1. In your plugin's sql directory, add an sql script alter_role_django_test_compatibility.sql with content:

--connect to database "plugin_<plugin_name>" as user "metwork"
\c plugin_<plugin_name> metwork;
--give role CREATEDB, for django testing purpose
ALTER ROLE plugin_<plugin_name> CREATEDB;
--give role SUPERUSER, for creating POSTGIS extension
ALTER ROLE plugin_<plugin_name> SUPERUSER;

It will give the rights to create the test database and to activate the extension postgis for your test database.
This is not well as we give the superuser role....

2. In you plugin's postinstall script, add:

#!/bin/bash
# add postgresql authorizations, for django testing purpose
echo "host    all plugin_<plugin_name>    0.0.0.0/0   md5" >> ~/var/pgsql/pg_hba.conf
echo "host    all plugin_<plugin_name>    ::1/128   md5" >> ~/var/pgsql/pg_hba.conf
echo "Authorizations added. You must restart mfbase."
exit 0

It will allow your database user to connect to the test database.

Perennial solution

To Be Determined

[GaspardDesmoulins]

The plugin user cannot activate the postgis extension because he/she is not superuser.
In the case where the postgis extension were 'trusted' any user who can --create_db would be able to activate the extension on their database:
(it is explained here: https://www.postgresql.org/docs/current/sql-createextension.html)
The Perennial solution would be to mark POSTGIS extension as trusted. This requires to edit the extension control file:
/opt/metwork-mfext-2.2/opt/scientific_core/share/postgresql/extension/postgis.control > probably a mfext PR to do

There is a blogpost about the procedure here:
https://www.dbi-services.com/blog/postgresql-13-trusted-extensions/

[matthieumarrast]

thank you @GaspardDesmoulins I'm very interested with your solution for postgis extension !
@thebaptiste will probably be able to add this postgis.control file in directory /opt/metwork-mfext-2.2/opt/scientific_core/share/postgresql/ during installation, probably here:

• https://github.com/metwork-framework/mfext/tree/master/layers/layer1_scientific_core/0070_postgis
  or here:
• https://github.com/metwork-framework/mfext/tree/master/layers/layer1_scientific_core/0008_postgresql

[matthieumarrast]

I noticed the file is already present after mfbase installation:

root@modxxx:[/opt/metwork-mfext-2.2/opt/scientific_core/share/postgresql/extension]: cat postgis.control 
# postgis extension
comment = 'PostGIS geometry and geography spatial types and functions'
default_version = '3.4.0'
module_pathname = '$libdir/postgis-3'
relocatable = false

so we have to add trusted = true in this file. TESTED succesfully !

[thebaptiste]

The file postgis.control is created when building postgis in mfext. I will add trusted = true in it.
I hope this will not bring side effects on PostGreSQL.

[thebaptiste]

Does this fix fixes the 3 problems you identified @matthieumarrast or only some of them (problem 2 on postgis) ?

[matthieumarrast]

Indeed the postgis.control fixes only one of the three problems, but the worst, the 2 (where alter... superuser is needed).

[thebaptiste]

Ok. It set problem 2 as resolved ([X]) in your first comment.
The two others are still not resolved ([ ])

[thebaptiste]

Point 2 is fixed in new release mfext 2.2.5, adding trusted = true in file postgis.control
Thanks @GaspardDesmoulins for the fix idea.

[matthieumarrast]

Info: when a reset_db is made from django, the default template1 database from postgresql is used, so instead of using "all" maybe we should declare in pg_hba.conf:

# for resetting database
host    template1 plugin_<plugin_name> ...
# for access to the test database
host    test_<plugin_name> plugin_<plugin_name> ...

[GaspardDesmoulins]

Patch of mfext 2.2.5 is working ! Thanks
Info:
For a django app to be testable on my mfserv, pg_hba.conf should be as follows:

#<connection mode> <database> <user> <IP/Mask | addr/auth-method> [<auth options>] 
local   all all trust
host    sameuser all    0.0.0.0/0   md5
host    sameuser all    ::1/128   md5 
host    all metwork    0.0.0.0/0   md5 
host    all metwork    ::1/128   md5
# tests database population by django for testing
host    test_plugin_name plugin_name 0.0.0.0/0 md5
host    test_plugin_name plugin_name ::1/128 md5
# for test database to be deletable by django
host    postgres plugin_name 0.0.0.0/0 md5
host    postgres plugin_name ::1/128 md5 
# to be able to reset_db from django app
host    template1 plugin_name 0.0.0.0/0 md5
host    template1 plugin_name ::1/128 md5

[thebaptiste]

May be we should have a specific plugin template for django ?