[FEATURE] Can we remove SUPERUSER from the PostgreSQL create user command?

[gronka]

I would like to use Azure PostgreSQL, but if Tethys requires the SUPERUSER role to work, then it's not possible.

Is your feature request related to a problem? Please describe.
Azure has a PostgreSQL service which does not allow the creation of a SUPERUSER. Azure keeps that role for themselves. I imagine other PostgreSQL PaaS would make the same decision.

Describe the solution you'd like
Remove the SUPERUSER assignment from line 281 of tethys_cli/db_commands.py. Tethys seems to run fine if the superuser is not actually a superuser - but I could easily be missing something.

Describe alternatives you've considered
The alternative offered by Tethys is to set SKIP_DB_SETUP=true, and Tethys seems to run fine after running the following commands with psql:

CREATE EXTENSION IF NOT EXISTS postgis;
CREATE USER tethys_super WITH CREATEDB CREATEROLE PASSWORD 'pass';
CREATE USER tethys_default WITH NOCREATEDB NOCREATEROLE NOSUPERUSER PASSWORD 'pass';
CREATE DATABASE tethys_platform WITH OWNER tethys_default TEMPLATE template0 ENCODING 'UTF8';

[mattw-nws]

Similar issue on AWS RDS, 👍

Things can/will fail if the tethys_super user can't for example create tables and databases (this can be worked around by having all that precreated, but that's likely to be fragile and will I think prevent things such as migrations from working). But no, it doesn't have to be SUPERUSER strictly speaking.

I worked around it by pre-creating the tethys_super user with the rds_superuser instead, similar to below:

# This has to happen for RDS to work, and if it fails, we must not be using RDS...
#TODO: Any reason to be concerned about injection here? Just strip all non-word characters?
psql -h ${TETHYS_DB_HOST} -U postgres -c "CREATE ROLE ${TETHYS_DB_SUPERUSER} NOSUPERUSER IN ROLE rds_superuser INHERIT CREATEDB CREATEROLE NOREPLICATION VALID UNTIL 'infinity' LOGIN PASSWORD '${TETHYS_DB_SUPERUSER_PASS}'" \
|| true

(The || true is a bit of a hack but is minimally sufficient for this to be runable in a script whether I'm on RDS or a local dev env.)

The tethys db commands use IF NOT EXISTS for creating users so this threads the needle. Notably, you must not precreate the tethys_super database, because tethys db fails if it already exists (this might be worth changing/fixing also)--my original attempt just created tethys_super as the RDS master user but the precreation of the user's DB made that approach infeasible.

You could add an optional option/switch to let the user specify a different role to grant tethys_super and use that instead of SUPERUSER... not sure about Azure, but I think this would be sufficient for RDS. And/or, allow tethys db to proceed if the user database already exists, and then for RDS making tethys_super as the master user will I think be sufficient. Additionally there could be some kind of check to make sure that the tethys_super user can create tables or whatnot after creation, but that's gravy.

[ckrew]

I removed the SUPERUSER role from create user command but it caused automated github action tests to fail. Below is the screenshot of the test and here is a link to the actual tests if you want to take a closer look. I am going to add the SUPERUSER role back into my PR for now so that we can get that merged.