Re: Allowing users to create objects in version controlled schema

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@gmail.com> 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
>
>