Eric -
I am both (PostgreSQL) database and QGIS developer and occasionally a DB
admin. From that point of view:
There is a ton of use cases, where having a private schema for each QGIS
user makes sense. For example, GIS analysis will often create datasets
with limited lifetime, i.e being part of a process. These datasets has
to be stored somewhere. If it's not in a private schema, you'll have
them splattered out over your entire filesystems in different file-based
GIS formats, including some on the users local hard drive.
Further, having even temporary data stored in your database gives the
user opportunity to off-load some heavy-duty GIS analysis to the
database using the PostGIS extension. Think of your database as a "file
system", a "scratch pad".
This kind of work is fundamentally different from a traditional 3-tier
setup where your normal user probably is not even aware of the
underlying data structure. And structure changes can be "hidden" in the
business logic layer.
All schema definitions tables/views/etc are versioned using git in a structured
repository of flat sql files. It works well. Table defs etc get code reviewed
and deployed by more DB-minded engineers,
This is a good idea. When some kind of analysis workflow is worked out /
stabilised , the resulting tables/views etc. can be quality-checked and
transferred to the "proper" database.
You could establish a separate database (including -server ?) and let
QGIS users access the central database using Foreign Data Wrappers.
Med venlig hilsen / Best regards
Bo Victor Thomsen
Den 08-11-2022 kl. 03:29 skrev Erik Wienhold:
On 08/11/2022 02:19 CET Wells Oliver <wells.oliver@xxxxxxxxx> wrote:
Feels like more of a process question and less of a technical postgres
question.
You're right. But I'm wondering what's possible on the technical front.
In our world, each DB user has their own schema they have full access to, and
can grant privileges as required.
Right, the standard approach with search_path = "$user".
All schema definitions tables/views/etc are versioned using git in a structured
repository of flat sql files. It works well. Table defs etc get code reviewed
and deployed by more DB-minded engineers,
That's what I meant with version controlled migrations. And I actually don't
want users to bypass that process to create and reference their own database
structures when those structures should rather be part of the common application
schema.
and people are cognizant of writing optimized SQL.
That's where I have my doubts when it comes to inexperienced "devs" and no
guiding code reviews.
--
Erik
