Date: Thu, 10 Nov 2022 03:39:20 +0100 (CET)
From: Erik Wienhold <ewie@ewie.name>
To: Bo Victor Thomsen <bo.victor.thomsen@gmail.com>,
	pgsql-admin@lists.postgresql.org
Message-ID: <695861386.480654.1668047960677@office.mailbox.org>
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>
Subject: Re: Allowing users to create objects in version controlled schema
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Importance: Normal
Archived-At: 
 <https://www.postgresql.org/message-id/695861386.480654.1668047960677%40office.mailbox.org>
Precedence: bulk

> On 08/11/2022 05:42 CET Bo Victor Thomsen <bo.victor.thomsen@gmail.com> w=
rote:
>
> I am both (PostgreSQL) database and QGIS developer and occasionally a DB
> admin.=C2=A0 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=C2=A0 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 st=
ructured
> > repository of flat sql files. It works well. Table defs etc get code re=
viewed
> > 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 schem=
as
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 p=
revent
migrations that alter dependent objects.  But this can indeed be isolated v=
ia
FDW with some overhead to keep the schemas in sync.

--
Erik