From: Erik Wienhold <ewie@ewie.name>
To: 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: Thu, 10 Nov 2022 03:39:20 +0100 (CET)
Message-ID: <695861386.480654.1668047960677@office.mailbox.org> (raw)
In-Reply-To: <3b83b029-30c6-a0cc-2993-81910c1ee62b@gmail.com>
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>
<3b83b029-30c6-a0cc-2993-81910c1ee62b@gmail.com>
> On 08/11/2022 05:42 CET Bo Victor Thomsen <bo.victor.thomsen@gmail.com> wrote:
>
> 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.
Thanks a lot.
I don't have my team mate's requirement yet so I can't say if private schemas
are an option. If those tables are used as layers, I assume they are meant to
be shared with other users. I think in that case it is also better to have them
manage privileges manually instead of automating that part as I've outlined in
my original post.
It still gives me headaches when I think about users who may create objects with
dependencies on the versioned part of the schema. Those dependencies may prevent
migrations that alter dependent objects. But this can indeed be isolated via
FDW with some overhead to keep the schemas in sync.
--
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: ewie@ewie.name, bo.victor.thomsen@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Allowing users to create objects in version controlled schema
In-Reply-To: <695861386.480654.1668047960677@office.mailbox.org>
* 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