Message-ID: <3b83b029-30c6-a0cc-2993-81910c1ee62b@gmail.com>
Date: Tue, 8 Nov 2022 05:42:17 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.1
Subject: Re: Allowing users to create objects in version controlled schema
Content-Language: en-GB
To: pgsql-admin@lists.postgresql.org
References: <903563259.260181.1667842135503@office.mailbox.org>
 <4240dcf8-2226-15dc-3a0d-1fd76f8c38bc@gmail.com>
 <1581955070.282646.1667869831483@office.mailbox.org>
 <CAOC+FBXaqH=8MwLpbBMk3A27QvQm4JY1tX35E+xm_+P-LyMjkg@mail.gmail.com>
 <1103549459.284197.1667874591587@office.mailbox.org>
From: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
In-Reply-To: <1103549459.284197.1667874591587@office.mailbox.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/3b83b029-30c6-a0cc-2993-81910c1ee62b%40gmail.com>
Precedence: bulk

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
>
>