Set appropriate DB users for the docker image

User Story

The docker image is for the purpose of development and testing. This image must provide the required DB users for testing the gazetteer application.

There are four users for this app

gazetteer user:
- Can add new features to the DB via the app
- Can not access the admin UI or functionality it provides
gazetteer_admin
- Can edit features
- Can access admin UI
gazetteer_user'
- seems to allow select statements on some tables
gazetteer_export
- need to look into still
gaz_owner
- need to look into still

Acceptance Criteria

Docker needs to deploy users with access to these roles so that dev and testing via the QGIS gazetteer app can use them. This will require ensuring they are allocated to the correct DB group

Additional context

What access privileges should each users have?

There are two functions for validating users in the app:

gaz_IsGazetteerUser()
- ensures the user is in the gazetteer_users view
gaz_IsGazetteerDba()
- ensures the user is in the gazetteer_users view
- and the views isbda attribute == True

This view is populated as below.

Takeaways are:

just to be added to the view the user must be at minimum assigned to the gazetteer_admin group role.
to be considered isdba the user must be assigned to the gazetteer_dba group role

https://github.com/linz/gazetteer/blob/ec7caa01bf19aa49521c1c93acf3355954819d8c/src/sql/gazetteer_add_user.sql#L67-L90

CREATE ROLE gazetteer_admin
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;
GRANT gazetteer_user TO gazetteer_admin;

CREATE ROLE gazetteer_dba
  NOSUPERUSER INHERIT NOCREATEDB CREATEROLE;
GRANT gaz_owner TO gazetteer_dba;
GRANT gaz_web_admin TO gazetteer_dba;
GRANT gazetteer_admin TO gazetteer_dba;

These below roles may also be required for testing?


CREATE ROLE gazetteer_export
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

CREATE ROLE gazetteer_user
  NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE;

CREATE ROLE gaz_owner
  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;

linz / gazetteer