From: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
Date: Tue, 8 Nov 2022 05:42:17 +0100
Message-ID: <3b83b029-30c6-a0cc-2993-81910c1ee62b@gmail.com> (raw)
In-Reply-To: <1103549459.284197.1667874591587@office.mailbox.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>
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
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: bo.victor.thomsen@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
In-Reply-To: <3b83b029-30c6-a0cc-2993-81910c1ee62b@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox